How to apply dynamic Row level Security based on Username and location?

Question

This is related to Row-level security in Power BI. Here is a dummy table on which I want to implement this RLS.

The RLS formula I am using right now is -

[location] = LOOKUPVALUE([location],[login],USERPRINCIPALNAME()) 

My end requirement is to make this RLS work on the case where if a user logs in he should see the data if the location he is in and also should see other users who are in the same location.

But lookup is throwing an error:

A table of multiple values was supplied where a single value was expected

Also, a single user can have access to multiple location and a single location could have multiple users.

How I can achieve this level of Row-level security using either Lookup or using any other possible way?

Answer 1

Your requirement can also be done through this way, you need to work on both the Power BI desktop and app server to get this right.

In Desktop

1. Power BI Desktop allows you to create Roles.Click on Manage Roles and then click on Create New Role.
2. You would need to create Roles based on the Location here.
3. For Example Create a rule Called as Location A and click on the table and give the formula to filters on that table. For example, [Location] = "A".

So, you can create 'n' number of roles like this and then publish the dashboard.

App.PowerBI.Com

1. Now, Open app.powerbi.com and then go to the corresponding dataset and right click on it, select security - you will find the groups there.

2. On the Groups, enter the emailid's that should have access to it. You can add a person in multiple groups, that will allow them to see more than one group.

Testing

1. Desktop allows you to test it as well - you can click on view as roles, to see what the user in that group sees.

Also to remember, only people who have access to the dashboard will be able to see the data inside it. So, you will need to share it with them and as well as need to add them inside the groups. If you fail to do one of it, then they won't see anything on the dashboard.

Most efficient Method

This is a better method and I think this is the one you are asking for,

[login] = username() 

The function username() automatically gets the email address of the user that is logged in.

Create the above mentioned filter on the new role in the required table. This way, you would still be adding those 50000 users manually inside the roles under the security section of the corresponding dataset.

But If they all belong to a particular group in an organization, and your Office-365 team has configured your back-end in the right way, then you will only need to enter the group.

But most companies don't have this configuration on their back-end as it is very difficult to control the data security.

Change the Data Model

To do your requirement, you also need to change your data model.

You will need to have two tables initially.

The Value Table - You can have many columns like name and all in there, but it doesn't matter.

The Email Address Table For Location as your second table.

Now Merge the Locations from Value Table to Email Address Table and that will be your final Table. Use this for visualizations but please be very clear on DAX - as you may have duplicate rows and different values.

Now Create RLS on this Table. It should work.

As the name goes, Row Level Security is applied on each rows. So, the formula will be evaluated for each row individually. So, your only option is to have a flat table with all the values.