Forum Discussion

Shekar25's avatar
Shekar25
Frequent Visitor
7 years ago

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 ...
Need Help
Shekar25's avatar
Shekar25
Frequent Visitor
7 years ago

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. 

rpittet's avatar
rpittet
Regular Visitor
6 years ago

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.