Forum Discussion
Help organizational hierarchy RLS
Hi,
I need some help setting up a hierarchy in RLS in PBI. In this scenario (see data below) we have Selma owning department 1 and 2. If I give her access to department 1 and 2 then she can't see all the departments below her, which she should. Klaus, for example, owns 1.1 so he should see that and everything below that. I'm going a little bit insane: I want to check whether the user logged in is the person responsible for that department and they need to see everything belonging to the departments below that as well.
This article doesn't go into how to handle those cases, as the workaround here isn't enough for my scenario https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
A similar question popped up on the forums years ago but the video referred to isn't there anymore? https://community.fabric.microsoft.com/t5/Desktop/RLS-with-org-hierarchy/m-p/1134611#M516493
Dummy data (note that Selma owns 2 departments, owner is not unique)
| Department | Parent | Owner | Path | Level 1 | Level 2 | Level 3 | Level 4 | |
| 1 | null | Selma | [email protected] | 1 | 1 | null | null | null |
| 2 | null | Selma | [email protected] | 2 | 2 | null | null | null |
| 3 | null | Bill | [email protected] | 3 | 3 | null | null | null |
| 1.1 | 1 | Klaus | [email protected] | 1|1.1 | 1 | 1.1 | null | null |
| 1.2 | 1 | Blair | [email protected] | 1|1.2 | 1 | 1.2 | null | null |
| 1.1.1 | 1.1 | Rene | [email protected] | 1|1.1|3.1.1 | 1 | 1.1 | 1.1.1 | null |
| 2.1 | 2 | Katy | [email protected] | 2|2.1 | 2 | 2.1 | null | null |
| 3.1 | 3 | Sue | [email protected] | 3|3.1 | 3 | 3.1 | null | null |
| 3.1.1 | 3.1 | Wilmer | [email protected] | 3|3.1|3.1.1 | 3 | 3.1 | 3.1.1 | null |
| 3.1.1.1 | 3.1.1 | Richard | [email protected] | 3|3.1|3.1|3.1.1.1.1 | 3 | 3.1 | 3.1.1 | 3.1.1.1 |
yes, we have a rules table that is controlled by the business. It is mostly based on ActiveDirectory but allows for overrides. That table is then matched against USERPRINCIPALNAME in the RLS rule.
5 Replies
Treat your access permissions as lists
Selma = {1,2}
Then in your RLS rules you can test against these lists
Department Level 1 in {1,2} => Selma is permitted
