DAX for Geographical Hierarchy in RLS

[Anonymous]

Hi Experts,

Looking for advise and solution.

I have a user table with the below structure.

What I am looking for is a DAX function to use it in the roles where the dax should only consider the highest possible value in hierarchy when there is no lower level restriction available.

For example: the staff1 should be able to see everything in western sector but should be restricted to station kangaroo valley in under Regional>>Souther>>Illawarra_shoalhaven division>>sector>>zone respectively.

So if there is no restricted value at the lower level then the upper value should be considered.

Is this possible or is there an alternative better solution to accomplish this?

Station	Geo_Zone	Geo_Sector	Geo_Division	Full_Name	EMail
Kangaroo Valley	Illawarra_Shoalhaven	Southern	Regional	Staff1	email
*	*	Western	Regional	Staff1	email

[v-xiaoyan-msft]

Hi @Anonymous ,

It sounds like you want to create a dynamic Row Level Security with Manager(Higher) Level Access in Power BI.

You can do this by following a few steps:
1. Use the Username() or UserPrincipalName() function in DAX to identify who is logged in to the report in the Power BI service.
2. Use DAX expressions to identify if the logged in person is a Higher level user.
3. If the user is not at Higher level, only the records related to the user will be displayed, if yes, then all the content will be displayed.

For detailed steps and samples you can refer to the article here .

Hope it helps,

Community Support Team _ Caitlyn

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

[Anonymous]

Hi @v-xiaoyan-msft 

Thank you very much for the response.

Sorry thats not what I am looking for.

My fact transaction/geo dim tables does not have user details. So there is no way I can map the users table to the fact or dimension table. 

I have to maintain a separate table with the user details and their access to a particular geographical locaiton/station. This geo location/station is then joined with the geo dim table.

From here I have to filter the data based on the user table access permissions(geo stations against the users listed).

Appreciate any further help here.