Complicated Dynamic RLS for Employee Data in a Hierarchy

[LaneLourcey]

So I'm trying to impliment RLS for a HR Dashboad, but I've found the RLS setup is so complicated I haven't been able to wrap my head around it yet.

I have table called Headcount, which has the following fields to name a few.

• Email (which is the same as `userprincipalname()`
• ID
• Manager ID
• Division
• Area
• Seniority (Which is the following in decending order: Officer > Director OR General Manager > Area Manager > Team Manager > Coordinator > Contributor

That table has relationships that filter the following tables

• Terminations (Link is ID)
• HR Actions (Link is ID)

and I also would like it to filter another table based on the "Division" but there is no relationship setup there.

So my question is, what is the best approach here? It would be great if the visibility could cascade based off the Manager ID field, but it might be more sustainable to set up the visibility so that you can only see inside of your Division, and someone of lower senority. But, I have no idea how to set that up. I'll continue researching, but if anyone has any exprience setting up something similar I would apprichiate it!

[v-chenwuz-msft · Accepted solution]

Hi @LaneLourcey ,

First, you need a user Seniority level table, for example, officer is 1, Director OR General Manager is 2 and Area Manager is 3, then filter the fact table by the seniority level table via the if the login user is 2 then where the rows level is grater then 2, then this row can be seen by this login user.

I have found some articles about how to do dynamic RLS, you can refer.

https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi 

https://blog.pragmaticworks.com/dynamic-row-level-security-in-power-bi 

https://community.powerbi.com/t5/Community-Blog/Dynamic-Use-of-USERPRINCIPALNAME-for-Dynamic-RLS/ba-... 

I create a sample pbix file in the end and you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LaneLourcey]

That's great stuff! I ended up figuring it out, although the solution I came up with was much more complicated in the end. I used the path function to generate a heirarchy of IDs reporting to each manager, and then used a function to show only rows that had an ID in the user's id chain.