Different RLS scenario's for the same user in multiple reports based on the same dataset

[JosKuiper]

Hello Community,

I'm looking for the most efficient way to get the following scenario working out the right way:

I have two reports, HR and Illness, both are based on the same dataset, with a live connection, named HR dataset. We do dynamic RLS based on the logged on user with the userprincipalname(). The RLS is comming from a table called [RLS_Table] (example). The RLS filtered fact table is [Fct_Table]. In my model there are some dimension tables between them but for this post they are not relevant. 

I have a user who is allowed to see alle rows in [Fct_Table] for the Illness report, but is not allowed to see all rows in [Fct_Table] for the HR report. The RLS is filtered based on [Organization Unit]. Example tables:

[Fct_Table]

Date	Employee	Organization Unit	Hours
4/1/2024	1123	AAA	8
4/2/2024	1123	AAA	7
4/3/2024	1123	AAA	0
4/4/2024	1123	AAA	8
4/1/2024	1456	BBB	2
4/2/2024	1456	BBB	5
4/3/2024	1456	BBB	8
4/4/2024	1456	BBB	8
4/1/2024	1789	BBB	6
4/2/2024	1789	BBB	8
4/3/2024	1789	BBB	5
4/4/2024	1789	BBB	8

[RLS_Table]

Organization Unit	Userprincipalname	Report
AAA	testuser@example.com	HR
AAA	testuser@example.com	Illness
BBB	testuser@example.com	Illness
CCC	testuser@example.com	Illness
DDD	testuser@example.com	Illness

In this example user testuser@example.com should see rows with [Organization Unit] = 'AAA' in the HR report and should see rows with [Organization Unit] = 'AAA','BBB','CCC','DDD' in the Illness report.

I'm able to solve this but i'm looking for the most efficient solution. Our prefered solution doesn't seem to be possible. Solution we have are:

1. We would like to know which report the user is currently using so that we can do RLS filtering on column [Userprincipalname] and [Report]. It looks like its not possible to use a variable/ parameter to know which report is used... Is there a way to do this?

2. We can split the RLS and FCT tables in the model with a reference to the orignal tables. We then also have to split the permission  roles in the dataset. If we filter the RLS on report level we get a working solution but have to define all measures we use in both reports twice. See Scenario 2 image.

3. We can make two seperate datasets, but the downside of that solution is that we have to do all optimization and changes twice. The risks of difference between the models is big.

Scenario 2 image:

What are the best/ good practices for our scenario? We are looking for the most efficient way to solve this. In our opinion the optimal solution would but be to have a report variable with the report name, but it doesn't seem available.

Thanks in advance!

[mjc543]

I was having a similar issue where the same class of users, let's call them XXX, needed to see different slices of the same data in 2 different reports connected to the same semantic model. Meanwhile there are 2 other classes of users, we'll call YYY and ZZZ.

At first I tried making 2 separate RLS tables (1 for XXX, YYY, and ZZZ users with 1 record each for their respective "home" location, and another just for XXX (again) with access to their home location as well as others in the same geographic area).

After reading this post and user JosKuiper's reply I realized I needed to use 1 consolidated RLS table and append to it what I had put in the separate table. Then I needed to add an additional column called [ReportIndex] where value = 1 as filter to use in report A, value = 2 as filter to use in report B. For the class of users XXX that I was having trouble with (the same people needed to follow 2 different sets of RLS rules that were dependent on report they were viewing), since each user would appear as a row in the RLS table multiple times (exactly once for their home location, and 1 or more additional times for sister locations in the same area), I could use the ReportIndex filter to get rid of the overlap between "home location" rules and "sister location" rules.

This is important because the Power BI RLS mechanism automatically assigns the greatest level of  privilege to each user in cases where rules overlap each other (i.e. RLS rules are additive and are treated as a union, NOT an intersection). Finally, I applied ReportIndex as a report-level filter in the filter pane of both report A and report B, where index value = 1 and 2, respectively in each. Also noteworthy is that when it comes to the overlap of RLS rules and report filters, the opposite is true: Power BI applies the intersection of the filters (filters are not additive, therefore not a union when multiple filters are applied).

• For RLS: rule A + rule B = A ∪ B privlege for each individual user.
• For report filters: filter X (coming from RLS) + filter Y (coming from report slicer) = X ∩ Y for the end-user browsing the report. 

Putting it all together, the lineage looks like this:

I hope this is helpful. Feel free to reply to my comment if anything needs clarification. It worked brilliantly for me with no degradation of performance within the reports themselves!

[CatManKuhn]

Very creative solution. I am trying to do the same, but I have two dimensions that RLS will be based on. In your example, the RLS rules consolidated would be related to Location and DimOther1 for my needs. Do you know of way this would work? Everything I can think of leads me to a circular reference.

[Anonymous · Accepted solution]

Hi, @JosKuiper 

You might consider leveraging composite models and dataset parameters for a more manageable solution.

While Power BI doesn't support using report names directly in RLS, you can emulate this behavior by using dataset parameters in conjunction with the Power BI service's ability to apply different parameter values for each dataset.

Or build a composite model that combines the original dataset with its parameterized version. This approach allows you to maintain a single source of truth while applying different RLS rules based on the parameter value, which acts as a proxy for the report name.

Define RLS rules to account for parameter values. For example, you can set a parameter that toggles between the HR and Illness scenarios, and use this parameter in a DAX expression to dynamically change the RLS filter.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JosKuiper]

Hi!

Thanks for your reply. I'm not sure if we implemented what you wrote in your answer but you brought us in the right direction. What we did is using a hidden slicer in the reports. On the backend we managed to join the report name to the rls rows. By filtering down the report to the reportname, we get the correct rls structure.

[lbendlin]

If you use dynamic RLS your can enumerate all the combinations (five) of Org and report that this user is allowed to see via an Org+Report composite key.

If you want to go fancy you can define rules like "Report = Illness || Organization = AAA" that cover the same scope.