Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Row Level Security for 2 merged dataset with different RLS rules

Hello everyone,

 

I am facing an issue with row level security (RLS) rules with 2 datasets of different RLS rules combined into one dataset. I was wondering if anyone here came across a similar situation and knows how to go about this, please?

Current setup (some bits were redacted for privacy reasons):

Dataset No 1:

This dataset contains 2 tables:

Kristyna_0-1674732946971.png
The Career Band filed can have values such as A, B, C or D.

And there's a static RLS setup (for each career band A, B, C or D, there's been a role created in PBI Desktop and users assigned in PBI Service).

 

Our test user should be able to see just values for career band A.

 

Dataset No 2.:

This dataset contains 3 tables:

Kristyna_1-1674733194147.png

We have the same fact table containing information about People. There are 2 dimension tables for Location (contains the list of Regions, Countries and Cities) and Focus Group (containing values such as Global Functions, Global Operations or Global Warehousing).

There's a dynamic RLS rule set up so our test user should be able to see just the rows for Country UK and from Focus Group he should be able to see only Global Functions. For the dynamic setup to work, another RLS Users table has been created and it is basing the security on the UPN of the logged in user. The DAX code has been produced and also users added into the corect groups in Power BI service.


NOTE: both of these datasets work absolutely fine as expected when tested separately (using the function View As in Power BI Desktop and Test as Role in Power BI Service).

 

Dataset No 3. (combined dataset):

The previous 2 datasets are now merged into one combined dataset (it's a DirectQuery).

Because both of these are using the same factPeople table, the dimCareerBand table from the first dataset has been joined to the factPeople table from the second dataset so it looks as follows:

Kristyna_2-1674733794948.png

 

This new combined dataset is now to be used for a final report PBIX file.

However, it seems like the RLS rules are not being brought through to this as I would expect them to.

 

This new combined dataset doesn't have any further RLS setup. It's is also not possible to test it by "View as" or "Test as Role" and the RLS won't work for anyone who's a Member, Contributor or an Admin of it). It should work only for a user account that is in the "Viewer" role group.

 

Our test user has been listed only in the Viewer group, also has Build permissions on the datasets.

 

When running the tests, we noticed the following:
1. When we use just the fileds from the second dataset, the user correctly see only the rows which he is entitled to see:

Kristyna_3-1674734214799.png

 

However, as soon as we add the field from the first dataset, the table is completely blank and the user doesn't see any rows at all. I would expect him to see this:

Kristyna_4-1674734293265.png


If we leave the original factPeople table in the datamodel, the static RLS works fine, the user sees this:

Kristyna_5-1674734375344.png

However, we would need to make this to work together.

Another solution we found was to use the TREATAS function, therefore we created a following measure:

Kristyna_6-1674734585108.png

which work as in it gives you the correct values for Empoyees when you are allowed to see all the data but hasn't resolved the issue with the test user not being able to see anything.

 

Would anyone have any ideas as what we can try in order to get these 2 datasets with differet RLS rules applied to get to work, please? Any help would be realy apppreciated!

0 REPLIES 0

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.