Dynamic RLS with 2 different roles

[Rogermiddleton]

Hi everyone,

I have an employee table that filters the other fact tabel. We want to implement RLS on the employee table as following:

Name	E-mail	E-mailmanager	IsManager
Fred	Fed@outlook.com		1
James	James@outlook.com	Fed@outlook.com	0
Nancy	Nancy@outlook.com	Fed@outlook.com	0

Role 1: Employee

Should filter the employee table as: [E-mail] = USERPRINCIPALNAME ()

Role 2: Manager 

Should filter the employee table as: [E-mail manager] = USERPRINCIPALNAME ()

I am using E-mail Distribution lists in the security of the Dataset. We only have lists which contain both employees and managers. So if we make 2 different roles and add the list to both roles we get an error message.

What we want to do now is implement 1 dynamic RLS role with the help of the added column [IsManager] (0 or 1). So USERPRINCIPALNAME should check column [IsManager] and then apply Role 1 Filter OR Role 2 Filter. It shouldn't filter on the [IsManager] column.

Is this possible and if yes what should the DAX formula be? Help is much appreciated!! (and if an other solution better fits our problem, we are happy to know)

Kind regards,

Roger

[amitchandak]

@Rogermiddleton , refer if this blog can help

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

[Rogermiddleton]

Hi @amitchandak ,

Thanks for your reply. I found this blog, but the solution described lets a manager see the data of everyon below him/her in the entire path. So also 2/3 levels lowel. This doesn't work for us. The manager is only allowed to see the data from the employees directly 1 level below him/her.

Roger

[v-janeyg-msft · Accepted solution]

Hi, @Rogermiddleton 

After my test, you can try this:

[E-mail] = USERPRINCIPALNAME () || [E-mailmanager] = USERPRINCIPALNAME () 

Best Regards,

Community Support Team _Janey

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