Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shekar25
Frequent Visitor

Row Level Security - multiple roles, tables

I am trying to setup row level security for more than one table. Here is my scenario:

I have a headcount table with region. I created few roles("HC-Asia", "HC-America" etc) for this headcoune table by restrictring region to asia, corporate, america.

I assigned users to these roles in Power BI service and it is working fine when these users logon. Users only see their role -region data. 

Now issue starts when I add additional table to same model. I added "Termination" table which has region columns also. 

I created similar roles -"Term-Asia", Term-Corp", "Term-America" etc that restrict region to specific values. Then I assign user ids to these roles. 

So when users access this report from app, instead of seeing specific regions they see all regions. 

It works when I have roles for one table, it is not working when add roles for second table. I have same users added to these roles on two separate tables.

How can I get this working? Or is it product limiatation that I can not have 2 separate tables for RLS?

5 REPLIES 5
Anonymous
Not applicable

What I did in this case was make a security table with branches the people can see on that table. I use that table to filter all other tables and relate it Many to Many to the others in the report. You could filter your main tables with the security table and use that to drive what they see. Instead of having to make a new role for each new region just have a single role with everyone in it. 

 

Let the table do the work for you. 

Thanks for your response. You are suggesting to use single table with user, region info connecting to Head Count and Terminations. 

I can try that, but right now I have a single "Dates" table that is connecting to HeadCount and Termination table. 

I can not add another table (User-Region) to this joins as it will introduce ambiguity. 

 

Is there any other way  isntead of creating multiple Dates table. 

Anonymous
Not applicable

PowerBIModel.png

All hail my 10/10 paint skillz. I mean use the secuirty table to filter your main table ("facts" in this example). If you filter the fact table to only branches each person should see. Then it will filter all other tables from that if you select apply secuity filter in both directions.

Thanks for the nice picture. Yes, if I just have one fact table it is easier. If I have multiple facts tables which are connected to same dates dimension table, how can I add one more join to that existing schema. Power BI gives ambiguity error. 

Please see my schem below. 

With this setup I can only create RLS or Dynamic RLS on Pro table. Only way I can think of is duplicate Dates table for each of fact table, then use my security table (BU-User and User List) tables as central table and create a join to each of fact tables. 

With that I will loose ability to get measures from each of the fact table for a common date value. 

PBI_Schema.png

Hi @Shekar25 When you are creating roles you should be able to set filters against multiple tables in the Manage Roles windows. In the Manage Roles window, select your role and try creating a [Region] = 'value' in each of the facts tables (Start HC, Term HC, etc.) rather than just one. This should get around your issue. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors