Row Level security (RLS) with 3 levels of access

[Anonymous]

Hi Team,

I have a requirement from my manager for adding row level security to the report that i have created. 

The RLS should work with 3 levels of access

1.  Manager  -  should be able to see only his reportees

2.  User  -  User/associate should be able to see only his/her data

3.  Admin  -  should be able to view everything

I have a list of employees with their manager names in a table. 

Provided this, how to implement the RLS for these 3 levels

[amitchandak]

@Anonymous , In case you have not referred this, Please refer to this

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

[Anonymous]

@amitchandak Thanks Amit. Yup i have read this. I have the direct query method for connecting to the DBs. So Path() function doesnt work in direct query i guess.  

The other alternative would be creating this Mgr1, Mgr2, Mgr3 ..... etc  in the Database itself.

However, my question on the "ADMIN" part still remains as a question.

We can create the roles for Managers and associates. But how about the admins? like someone from HR team should have access to all associates details?

[Anonymous · Accepted solution]

@amitchandak  I was able to get to the solution I was looking for with your help. Below is the sample of the solution.

I used "Johnson,Tim@xyz.com" ,  in the Dax formula for the DRLS role to verify how it interacts.    "Johnson,Tim@xyz.com" should be changed to USERPRINCIPALNAME()  while working with the actual dataset.