Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

RLS multiple combination

Hi there, I would like to implement Dynamic RLS in Power BI but not like Corporate Heirachy manager or report to The data contain multiple companis and departments and the rolle need to be based on...
Need Help
  • jtownsend21's avatar
    jtownsend21
    7 years ago

    I just remembered that I had to do something strange with my tables which required me to use the OR. For yours, you should be able to just use the following: 

    1. On the company table - Company[PrincipalName] = UserPrincipalName() 
    2. On the department table - Department[PrincipalName] = UserPrincipalName()

    If it doesn't work for you I will work up a PBIX file that I can share to demonstrate what I am thinking. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for response,

I was able to create a table based on these combination using TSQL, the issue I'm having is how to set RLS

with multiple column in a table? my table contain company column and department column 

Shoud I create a compusite key for uniqness then create a rool based on this key and User Principal Name?

Iv'e try to do RLS on seperate coluns but it seems to not working.

 

Thanks,

Oded Dror

 

jtownsend21's avatar
jtownsend21
Responsive Resident
7 years ago

Anonymous I believe you will need two tables (one for company and one for department), arranged like so: 

Principal NameCompany           
[email protected]1
[email protected]16
[email protected]51
[email protected]1
[email protected]51
[email protected]55

 

Principal NameDepartment
[email protected]12
[email protected]14
[email protected]22
[email protected]36
[email protected]14
[email protected]22
[email protected]10

 

Then you need to create the relationships between  your fact table(s) (my example below has 2 fact tables) and your two filter tables like the picture below based on the department and company columns. 

Then put filters on your company and department tables in the "Manage Roles" (where you input the RLS). 

OR([Email] = USERPRINCIPALNAME(),
[Email] = USERPRINCIPALNAME())

the exact filter you use in the RLS may vary slightly depending on whether you are using email or some other Id. But this should filter your Company & Department tables which will then filter your fact tables. Side note, you will need to add your users to both groups in your PowerBI service. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

    I created Security roll and compusite key CDKEY = Company + Department to generate unique key Matching to UserPrincipalName Also I created companyDepartment combination table then I put this code (it seems working)

    But I'm not happy with this solutions.

    CompanyDepartment[CDKEY]= CALCULATE(
        VALUES('SecurityTable'[CDKEY]),
         SUMMARIZE(
             SecurityTable,
             SecurityTable[CDKEY]),
             SecurityTable[PrincipalName] = UserPrincipalName()
    )

     

    I did try two tables like you showed but it did not work as expected , I will try one more time and let you know.

    Can you elaborate a liitile bit more about your code?

    Thanks,

    Oded Dror

    • jtownsend21's avatar
      jtownsend21
      Responsive Resident
      7 years ago

      I just remembered that I had to do something strange with my tables which required me to use the OR. For yours, you should be able to just use the following: 

      1. On the company table - Company[PrincipalName] = UserPrincipalName() 
      2. On the department table - Department[PrincipalName] = UserPrincipalName()

      If it doesn't work for you I will work up a PBIX file that I can share to demonstrate what I am thinking. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Please discard the last email it works, I just test it with few users and it works!

        Thanks

        Oded Dror