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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

    I took off the OR, Also in your image it show 1 to many relationship but in may case is many to many because one person can have access to more than one company or department and the fact table contain many company and departments.

    It seems working as expected but the I published it to power BI it work only when I test the roll but not when I login?

    For example I can see only 1 company 6 departments but when I login I can see all companies and departments

    When I test it (Test as Roll in RLS) it works also it works in Power BI Desktop

     

    Thanks,

    Oded Dror

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

     

    Could you tell what strange thing you did. I have a feeling it might work out for me as I am looking for a different solution for a different problem.

     

    Thanks

  • Nazirahmed1982's avatar
    Nazirahmed1982
    Frequent Visitor
    1 year ago

    Hi, Please send me the pbix. I need it for a similar issue i am encountering