cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

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 that combination

And my database diagram I have Company table and Department table (I can combine this into one tabe if need it)

How do you approch that scenario?

(I'm thinking TSQL view with string_split() and cross join to generate all possibilities) then do company first and Department second but I need some help.

Like   Principal Name         Company            Department

           a@b.com                  01|16|51             12|14|22|36
            b@b.com                  01|51                 14|22

            c@b.com                   55                      10

Thanks

Oded Dror

1 ACCEPTED SOLUTION

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. 

View solution in original post

8 REPLIES 8
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may select Split Column By Delimiter > Advanced options > Split into Rows in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

 

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

Principal NameCompany           
a@b.com1
a@b.com16
a@b.com51
b@b.com1
b@b.com51
c@b.com55

 

Principal NameDepartment
a@b.com12
a@b.com14
a@b.com22
a@b.com36
b@b.com14
b@b.com22
c@b.com10

 

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. 

Capture4.PNG

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
Not applicable

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

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. 

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

Anonymous
Not applicable

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

Thanks

Oded Dror

Anonymous
Not applicable

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

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors