dynamic RLS for hierarchy

[GVTionale]

I am trying to create a dynamic RLS using following DAX in managing roles -

Following is a snapshot of the Employee Table with the Path created -

TRADER_CODE    MANAGER                 EMAIL_ID                                                             EMPLOYEE PATH

SIDD	GOPAL	siddarth@tionale.com.sg	GOPAL|SIDD
ROHIT	SHIVA	negocio4@tionale.com.sg	SHIVA|ROHIT
PRASHT	SANTOSH	prashath@tionale.com.sg	SANTOSH|PRASHT
PEGGY	GOPAL	peggy@tionale.com.sg	GOPAL|PEGGY
KEN	SANTOSH	ken@tionale.com.sg	SANTOSH|KEN
CALVIN	SANTOSH	calvin@tionale.com.sg	SANTOSH|CALVIN
ANANTH	SANTOSH	ananth@tionale.com.sg	SANTOSH|ANANTH

This is connected by 1-many relatinship with the Order Table as follows with the field connecting them as given below

TRADER_OFFICE                               PI_MAIN

TRADER_CODE                                EMPLOYEE_CODE

The role called "user" was created with foll. DAX

PATHCONTAINS([EMPLOYEE PATH],LOOKUPVALUE(TRADER_OFFICE[TRADER_CODE],TRADER_OFFICE[EMAIL_ID],USERPRINCIPALNAME()))

Published this and created the security in service by adding 5 of the above members to the role

However, when i test this in the security area by inputting the email ID, the screen refreshes without any filters being applied

Can someone help me to identify where I am making a mistake?

This is my first attempt at dynamic RLS

regards

[v-yuta-msft]

@GVTionale ,

The measure you write doesn't filter the [ EMPLOYEE PATH] column and the result will be TRUE/FALSE so it doesn't work, you should use measure like below to filter the username column:

[ EMPLOYEE PATH] = USERPRINCIPALNAME ()

Community Support Team _ Jimmy Tao

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

[GVTionale]

This is the link to the blog i followed -

https://insightsquest.com/2018/09/15/parent-child-rls-and-semi-additive-dax/

I find that my DAX and table structures are all same like this but I am not getting the result. Would like to know where I may be making mistake

regards

[GVTionale]

Hope someone could review my problem and advise where i am going wrong. if additional information is needed i can provide the same

regards

[GVTionale]

Thanks for your response.

Can you please send me the correct DAX to be used to create Role? 

regards

[amitchandak]

@GVTionale , Refer this

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

[ibarrau]

Hi. Let me ask you. What should this role filter? if you are just trying to filter orders by users and you have 1 user to many orders then just:

[EMPLOYEE PATH] = USERNAME()

That rule should be enough. If you need to filder other tables please show us some model or expose your requests so we can help you.

Regards,