Forum Discussion
RLS design & DAX rule suggestions
Hi all,
I need help in formulating a design for implementing RLS and writing the appropriate DAX rule for the scenario where I want to enable a user to see all the data of the business unit that he belongs to. I am listing down the tables and sample below. I want to enable John Miler & Mitchel Johnson to view hours for BU1 as well as slice/filter through their teams, while Josh Hazelwood should only be able to see BU2 and his respective team.
My question is, what sort of relationship should I establish between these two tables, and, what sort of DAX rule should I write?
Modeluser master datatransactions data
Thanks
Kaz
Your RLS rule returns a table. the RLS syntax just needs to you to apply a filter like this.
Business_Units[Business Unit] = "BU1"
12 Replies
- MattAllington
Community Champion
You need to use the many to many pattern. Create a table that has all the unique business units. Join both your data tables to this new common data table. Then put your RLS on the business unit table assigning access to the relevant people. Finally, you're measures need to filter the transaction table by using the user master as a filter
=CALCULATE(sum(transactions[number of hours]),'user master')
read my article here for more details
http://exceleratorbi.com.au/many-many-relationships-dax-explained/
Thanks MattAllington, while I am going through your article, can I please get you to look at the following DAX and help me understand why it wouldn't work? It's working in DAX studio and gives me BU1.
FILTER (
VALUES ( business_units[business_unit] ),
AND (
NOT ISEMPTY ( RELATEDTABLE ( transactions ) ),
CALCULATE (
COUNTROWS ( 'user master' ),
'user master'[employee_name] = "Mitchell Johnson"
)>0
)
)Thanks
Kaz- MattAllington
Your data model (image) has no relationships, so I dont see how the RELATEDTABLE functions is working. Plus this formula returns a table (if it works) so you can't use it in a visual.