Forum Discussion
Row Level Security - multiple roles, tables
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.
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.
