Row-level security 2 layers

[D_G]

I have data model as below:

I need to set up row-level security so that user could see all sales made by division where he is working and sales from any other division to specific country. Eg if user is employed in US division, he has to see all US division sales and sales from any other division to US.  Can somebody help me?

[v-mdharahman]

Hi @D_G,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to implement a RLS for your users. As @freginier already responded to your query, please go through his response and mark it as solution if it solves your issue.

I would also take a moment to thank @freginier, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[freginier · Accepted solution]

Hey there!

Here's how you can set this up using RLS:

• Create Roles in Power BI Desktop:

  • Go to Modeling in Power BI Desktop and click Manage Roles.

  • Create a new role for each layer of security (e.g., one for the division and another for the country).

• Define the First Layer: Division-Specific Security:

  • For the first layer, you want to ensure that users can see data related to their division. If the user is part of the "US" division, they should be able to see all sales data for the "US" division.

  • You can use a DAX expression for the division filter:

• [Division] = USERPRINCIPALNAME()

• • • This filter will allow users to see only the data where the Division matches their login ID (assuming you have some way of tying the user to their division via USERPRINCIPALNAME()).

• Define the Second Layer: Country-Specific Security:

  • The second layer is to ensure users can see sales made to a specific country. If the user is from the US, they should see sales for the US, but also sales from other divisions related to the US.

  • You can create a DAX expression to ensure that users see sales for the ShippingCountry (or Country) for their division as well as any sales to the US:

• [ShippingCountry] = "US" || [Division] = USERPRINCIPALNAME()

• Combine the Two Layers:

  • You will need to combine both of these security filters in the role definition. Here’s an example DAX expression for your role that combines both conditions:

• ([Division] = USERPRINCIPALNAME()) || ([ShippingCountry] = "US")

• • This way, users will only see data where either the Division matches their login, or the ShippingCountry is the US.

• Test the Security:

  • After setting up the roles, go to Modeling > View as Roles in Power BI to test the security settings.

  • You can enter different user names to see how the report changes based on the RLS settings.

Hope this helps!

😁😁