Dynamic security rules with specific hierarchy

[menphis21]

Hi,

I have a table of aging account and also a table of each email of all the employees as below :

Client	Amount	Managing Parter	Partner	Senior Manager	Chairman
A	1000	X	Y	Z	XY

Employees tables :

Name	Email	Role
X	X@msn.com	Partner

i built specifics pages for each kind of roles, Partner, Managing Partners, chairman, etc with different types of kpis.

How can i manage with the security rules and also with my data model to allow  each type of role access to  specific pages?

I also saw this video but my data model is too complex

How to Implement PAGE-LEVEL SECURITY to hide and show pages based on Permissions // Power BI Guide -...

Hoping your help

Thank you

[v-xiaotang · Accepted solution]

Hi @menphis21 

Thanks for reaching out to us.

please refer to these tutorials,

Dynamic Row Level Security with Organizational Hierarchy Power BI - RADACAD

Using Dynamic Row-Level Security with Organizational Hierarchies (bluegranite.com)

Best Regards,

Community Support Team _Tang

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

[menphis21]

Hi,

 Anyone ?

Iam trying also to find a way to user the function filter like that :

if(CONTAINS(Aging,Aging[Chairman Email],USERPRINCIPALNAME()), filter(Aging,Aging[Chairman Email]=USERPRINCIPALNAME()),
if(CONTAINS(Aging,Aging[Manager Email],USERPRINCIPALNAME()), filter(Aging,Aging[Manager Email]=USERPRINCIPALNAME()),0))

but didnt work.

Thank you in advance for your help

[Shaurya]

Hi @menphis21,

You can create the report without segregating them into pages and use Row Level Security to ensure that the right people are viewing it. Refer:

https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

[menphis21]

Thank you @Shaurya ,

But the whole problem is that I have several user fields per line
so I can't join the Aging table with the users table

I also tried another option in DAX for depending on the Userprincipalname, it chooses the field it is based on :

if(CONTAINS(Aging,Aging[Email Chairman],USERPRINCIPALNAME()), Max(Aging[Email Chairman]),
if(CONTAINS(Aging,Aging[Head of Finance Email],USERPRINCIPALNAME()), Max(Aging[Head of Finance Email]),
if(CONTAINS(Aging,Aging[Managing Partner email],USERPRINCIPALNAME()), Max(Aging[Managing Partner email]),
if(CONTAINS(Aging,Aging[Head of email],USERPRINCIPALNAME()), Max(Aging[Head of email]),
if(CONTAINS(Aging,Aging[Email BI Partner],USERPRINCIPALNAME()), Max(Aging[Email BI Partner]),
if(CONTAINS(Aging,Aging[Email Manager],USERPRINCIPALNAME()), Max(Aging[Email Manager]),0))))))=USERPRINCIPALNAME()

Thank you for your help