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?
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.
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.