cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lichar
Frequent Visitor

DataSet RLS for different dim table

Hi,

 

I had some troubles to configure the row level security for my dataset and went to a solution I want to be sure is secure.


I have a huge data set that I will sum up as per the following picture :

 

DataSet_Relations.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NAOSCUBE and SALESSKU are two fact tables and all others are dim tables. The first one has values for each country and range and the second one has value for each country and product (they are not under the same scope).

 

ISSUE

Now I wanted to set up RLS using the USERS table by creating a user role that could filter the table using USERNAME(). 
The first issue is that I cannot link USERS table directly on the fact table as I need to filters either on brands, or region or countries directly and even a mix of two of each.
The second issue is that if I want to link USERS to BRAND and COUNTRIES table I have an error stating that I cannot have 2 different pathing to filter one fact table.

 

WHAT I DID

I created different roles based on the way of filtering : Country Role, Region Role and Brand role (I will also create mixed roles).

On the country role for example, I filter COUNTRIES table using the following DAX formula :

[COUNTRY] IN CALCULATETABLE(VALUES(USERS[l_country]),USERS[c_user_mail]=USERNAME())

 

This way I can set up multiple rows in the USERS table with each one having a country if I want the user to see different countries.

 

This works perfectly however I would like to be sure that it is secure as I am not directly filtering the fact table.

 

Thanks for your help

 

Lichar.

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @Lichar ,

 

For your issues, in order to get the star schema instead of circle schema, you may delete the secondary relationships between the dim tables, and create relationships between the USERS table and two fact tables.

 

Then you may change the Cross filter direction of relationships among these tables above from Single to Both , which will take these tables treated as a single table.

 

You can learn more about relationship :https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships.

 

You may set Row-Level Security (RLS) to manage roles, please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently.

 

Best Regards,

Amy

 

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

 

Lichar
Frequent Visitor

Hi @v-xicai ,

 

I didn't get what you mean by secondary relationship.

 

Furthermore I cannot link USERS to the fact tables as USERS includes field such as BRAND that are not included in the fact table. The fact tables has ranges of products which belong to brands.

 

Also I need the dim tables to filter the fact table as all reports are using segment from those dim tables. For example a User that is allowed to see only Brand 1 will be able to filter a report on a specific range. This is done via the dim table ranges.

 

Thanks for your help

 

Regards,

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors