Hierarchical row level security Dax using Pathcontains

Hyuna_8000 — Mon, 09 Sep 2024 14:26:32 GMT

Hi everyone,

I need to create a new role in Manage Roles to set up a row level security according to our hierarchy. I tried to use Pathcontains in the dax but still can't get the code working correctly. Appreciated if anyone can point me the right direction.

Here is my sample data:

Employee Name	Email	Hierarchy
Tom Gland	Tom.Gland@example.com	/Tom Gland
Lily Wy	Lily.Wy@example.com	/Tom Gland/Lily Wy
Incy Wincy	Incy.Wincy@example.com	/Tom Gland/Lily Wy/Incy Wincy
Kent Ford	Kent.Ford@example.com	/Tom Gland/Lily Wy/Incy Wincy/Kent Ford
Russell Barrow	Russell.Barrow@example.com	/Tom Gland/Lily Wy/Incy Wincy/Kent Ford/Russell Barrow
Mary Lance	Mary.Lance@example.com	/Tom Gland/Lily Wy/Incy Wincy/Kent Ford/Russell Barrow/Mary Lance
Beatrix Tong	Beatrix.Tong@example.com	/Tom Gland/Lily Wy/Incy Wincy/Kent Ford/Russell Barrow/Mary Lance/Beatrix Tong

Basically when employees login using their email in PowerBI, they should be able to see every employee reporting to them, directly and indirectly. For example, Kent Ford should see himself, Russell Barrow, Mary Lance, and Beatrix Tong in the dataset when he logs in.

This is my first DAX, it only returns the employee's record themselves, for example for Kend Ford, only the row with his name was visible to him when he logged in:

PATHCONTAINS(Employee[Hierarchy],

MaxX(

Filter(

Employee,

[Email]=USERPRINCIPALNAME()

)

,Employee[Hierarchy]

)

This is my second dax, but it returns nothing:
VAR EmployeeName = LOOKUPVALUE(Employee[Employee Name], Employee[Email], USERPRINCIPALNAME())
RETURN
PATHCONTAINS(Employee[Hierarchy], EmployeeName)

Re: Hierarchical row level security Dax using Pathcontains

lbendlin — Mon, 09 Sep 2024 20:25:44 GMT

Use the Pipe symbol, not the forward slash. Use the email address in the hierarchy, not the name.

Then all you need is

PATHCONTAINS(Employee[Hierarchy],USERPRINCIPALNAME() )

Re: Hierarchical row level security Dax using Pathcontains

Hyuna_8000 — Thu, 12 Sep 2024 05:02:53 GMT

Thank you, the data comes as forward slash so I converted it to the Pipe symbol, the the following DAX works!
VAR EmployeeName = LOOKUPVALUE(Employee[Employee Name], Employee[Email], USERPRINCIPALNAME())
RETURN
PATHCONTAINS(Employee[Hierarchy], EmployeeName)

topic Re: Hierarchical row level security Dax using Pathcontains in DAX Commands and Tips

Hierarchical row level security Dax using Pathcontains

Re: Hierarchical row level security Dax using Pathcontains

Re: Hierarchical row level security Dax using Pathcontains