DataSet RLS for different dim table

[Anonymous]

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 :

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.

[v-xicai]

Hi @Anonymous ,

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.

[Anonymous]

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,