Help with Row level Security

[hemanth_423]

I have a fact table with these information

Manager	Supervisor	Agent	Month-Year	Commission
Beth	Tony	ravi	Saturday, January 1, 2022	252
Beth	Tony	bhushan	Saturday, January 1, 2022	358
Beth	Xin	john	Saturday, January 1, 2022	368
Beth	Xin	thomas	Saturday, January 1, 2022	357
Hemanth	rahul	raju	Saturday, January 1, 2022	359

User table:

Role	Username	Email
Manager	Beth	Beth@domian.com
Manager	Hemanth	Hemanth@domian.com
Supervisor	Tony	Tony@domian.com
Supervisor	Xin	Xin@domian.com
Supervisor	rahul	rahul@domian.com
Agent	ravi	ravi@domian.com
Agent	bhushan	bhushan@domian.com
Agent	john	john@domian.com
Agent	thomas	thomas@domian.com
Agent	raju	raju@domian.com

hierarchy ---manager-->supervisor-->agent

When agent log in to power bi service then he can only view his data,
when supervisor logs in he can view the data of agents who reports to him,
when manager logs in he can view the data of supervisors and agents who reports to him.

Thanks in advance

[Jihwan_Kim · Accepted solution]

Hi,

Please check the attached pbix file, and the below pictures.

I suggest having a [Report to] column in Users table, like the attached pbix file.

And then, try creating [Path CC] column in the same table.

Path CC = 
PATH( User[Username], User[Report to] )

Under "Manage roles" menu, 

try wrting a formula like below.

And then, you can try to test the result of RLS.

[hemanth_423]

Thanks a lot for the solution

[Idrissshatila]

Hello,

Check this video to make RLS based on the email https://www.youtube.com/watch?v=axTyP7I2Bso&t=408s

If I answered your question, please mark my post as solution, Appreciate your Kudos👍

Follow me on Linkedin

[hemanth_423]

Hi 

Thank you for the response but i have a differnet scenario where i cant have relationship between fact and security table