Forum Discussion

hemanth_423's avatar
hemanth_423
Frequent Visitor
3 years ago
Solved

Help with Row level Security

I have a fact table with these information

ManagerSupervisorAgentMonth-YearCommission
BethTonyraviSaturday, January 1, 2022252
BethTonybhushanSaturday, January 1, 2022358
BethXinjohnSaturday, January 1, 2022368
BethXinthomasSaturday, January 1, 2022357
HemanthrahulrajuSaturday, January 1, 2022359

 

User table:

RoleUsernameEmail
ManagerBeth[email protected]
ManagerHemanth[email protected]
SupervisorTony[email protected]
SupervisorXin[email protected]
Supervisorrahul[email protected]
Agentravi[email protected]
Agentbhushan[email protected]
Agentjohn[email protected]
Agentthomas[email protected]
Agentraju[email protected]

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


Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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.

     

     

     

     

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      hemanth_423
      Frequent Visitor
      3 years ago

      Hi 

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