Dynamic Row level security with multiple roles, multiple parents and multiple tables - Impossible?

[Anonymous]

Hi All, 
As the title suggest, I'm looking for a way to implement dynamic row-level security automatically within a organizational hierarchy with, at the lowest level, multiple parents.  I've already read several partial solutions, but, I'm not able to figure this out totally.

• This article of Radacad explains perfectly how you can setup RLS within a org hierarchy using Path function. 
• This and this article even explains how you can go about solving this if users have several positions. 
• This post explains how you can create an hierarchy where there are multiple parents.

So why didn't i implement this yet, you ask? Well, I just can work out how I can put all these together... I'm really scratching the edges of my understanding of relational databases, many to many relationships and DAX. So your help in putting this all together is much appreciated. 

My situation is as follows:

1. I have 5 levels within my organization: Top level, Departments, Business units, Teams and Supervisory level. 
2. I already have static RLS in place in the first three levels (up to Business Unit), now the client wants to implement it also for the lower two levels, which would introduce over 500 extra roles, which is obviously not an ideal situation. 
3. I want to be able to load a table which determines the rights (see sceenshot, second table) of each specific person.
4. Each person could have potentially several rights on the same level (see Peter in screenshot example).
5. Each stakeholder should should have rights to see data from each level, and everything below that. So a board member with rights to top level should everything, somebody with supervisory level rights should only see that specific SL.
6. Supervisory level makes this complicated, since a supervisory level could span multiple teams, and if somebody is a manager of a SL, I want that person to see the whole of his/her SL, spanning multiple teams (see ID 8 below)

So yeah, In my (probably very noobish view), this is pretty complicated, especially since it combines implementing row-level security with multiple positions, multiple parents and multiple tables (see screenshot). Has anybody figured this out ever?   Thanks so much in advance!

[Anonymous]

Hi @Anonymous ,

Do these five hierarchy levels of data exist in the same table? Please provide some strength data. In addition, can you give an example to show what kind of data is displayed when selecting the teams level and Supervisory level separately?

Best Regards

Rena

[Anonymous]

Hi @Anonymous , 

Thanks so much for the help! Sorry for the late reply. 
- Yes, most of the time these five cost centers exist in the fact table.

- What do you mean by "strenght data"?

- All the fact tables are around employee data. Most of the time aggregating on something like headcount or fte count and comparing that with previous periods. 

Hope that helps!