Dynamic Row Level Security based on Hierarchies

[Anonymous]

Hi,

I'm in need for help with setting up Dynamic Row Level Security, based on USERPRINCIPALNAME, but with a hierarchy structure in place as well.

Case is as follows: I'm working on a fixed costs dashboard for our organization with fact data on different hierarchy levels: Business Group / Region / Company Code / Cost Center. Because of the sensitivity of the numbers it's not possible to share the entire report with all the potential report users. Hence the need to apply Row Level Security. Some users should only see a few company codes or cost centers, others are allowed to view an entire BG, or only a specific region. Some users are even allowed to see all the data.

This RLS complexity causes my issue. In more "simple" reports I'm managing RLS using a separate excel file in which I would list the users, the company codes and the combination of both which basically indicates which user is allowed to view something and is maintainted on the lowest level. An example of how that's applied you'll see in attached sample pbix file on https://we.tl/t-oB8tCRgypi or below screenshots:

Managing this however with hundreds of company codes and even thousands of cost centers is not maintainable.

What I would need is to know how to set-up certain hierarchies that I can assign to users. So for example when I assign a user the EUROPE role this user can see all companies that belong to region Europe. Or role MATERIALS would assign all Materials company codes to that user. Furthermore a combination of both should be possible (i.e. Materials but only for Europe company codes. I also need to have the possibility to assign individual company codes to users.

Any help would be very much appreciated!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For Dynamic Row Level Security with Hierarchy case, you may refer to these blog:

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multiple-positions-in-many-to-many-relationship-part-1

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

Please understand that these links are provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

Best Regards,

Lin