RLS Dynamic

[bboy2025]

Hi guys

I have a table User which filter my fact table

I would like apply a RLS to display data of people who has the same hierarchy than the user who has loged in Power BI Service

For that, I created a role and apply a filter in my table User

[Hierarchy_1] == CALCULATE(SELECTEDVALUE('Dim User'[Hierarchy_1]),'Dim User'[UserPrincipalName]= USERPRINCIPALNAME())

&&

[Hierarchy_2] == CALCULATE(SELECTEDVALUE('Dim User'[Hierarchy_2]),'Dim User'[UserPrincipalName]= USERPRINCIPALNAME())

It doesn't work and displays only data of the user who loged in. It works if I write down the value instead of using CALCULATE....

Do you know how can I implement that?

Thank you

[bboy2025]

@jgeddes please could you tell me the reason why my formula doesn't work ?

[jgeddes · Accepted solution]

If I have a model with a fact table that is filtered by a user dimension table as follows...

I can set RLS on the dim_user table with this statement to filter the fact table with the hierarchy of the current user...

[hierarchy] == MINX(FILTER('dim_user', 'dim_user'[user] = USERPRINCIPALNAME()), 'dim_user'[hierarchy])

Full example data...

Now with RLS on with  user1@fake.com as the user...

Hope this helps.