cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krajani Member
Member

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? 

 

RLS-User-BU-Model.pngModelRLS-User-BU-user_master-table.pnguser master dataRLS-User-BU-transactions-table.pngtransactions data

 

Thanks 
Kaz

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: RLS design & DAX rule suggestions

Your RLS rule returns a table.  the RLS syntax just needs to you to apply a filter like this.

 

Business_Units[Business Unit] = "BU1"



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
12 REPLIES 12
Super User
Super User

Re: RLS design & DAX rule suggestions

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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
krajani Member
Member

Re: RLS design & DAX rule suggestions

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

Super User
Super User

Re: RLS design & DAX rule suggestions

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
krajani Member
Member

Re: RLS design & DAX rule suggestions

My bad Matt, I should have detailed out. I have established relationships, screenshot below. And, the DAX formula I have implemented as RLS rule, not a measure or column. My understanding is that this will narrow down the business_unti table to just BU1, which will then be propogated across tables. Am I looking at it in an incorrect way?

 

RLS-User-BU-relationships-table.pngrelationshipsRLS-User-BU-RLS-rule.pngRLS rule

 

Thanks
Kaz

Super User
Super User

Re: RLS design & DAX rule suggestions

Your RLS rule returns a table.  the RLS syntax just needs to you to apply a filter like this.

 

Business_Units[Business Unit] = "BU1"



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
krajani Member
Member

Re: RLS design & DAX rule suggestions

Hi Matt,

 

I see what are you saying about the Filter bit, I missed that big time, that, Filter returns a table. At the same time, I want something on the lines of BU in ( 'BU1', 'BU2') rather than just BU = BU1.

Super User
Super User

Re: RLS design & DAX rule suggestions

so how about table[column] = "BU1" || table[column] = "BU2"

 

Double pipe is the OR operator.

 

https://en.wikipedia.org/wiki/Vertical_bar



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
krajani Member
Member

Re: RLS design & DAX rule suggestions

 

Thanks Matt, but, if we use the OR operator, it won't be dynamic. I am after something which is dynamic.  Plus, this needs to work on the email address column so once it is published in the cloud, I can evaluate the employee by using UserName() and show him the BU's he has access to.

 

I have gone through your many-to-many example, and while it does make sense, I haven't been abel to tranlsate it into my scenario.

 

Thanks
Kaz

 

 

Highlighted
Super User
Super User

Re: RLS design & DAX rule suggestions

Why does it need to be dynamic? Surely you either need access to a BU or you don't. If something changes, there needs to be maintenance somewhere, so why not in the RLS groups?

 

I can conceive putting the email address in the BU many side table, and then using the username() as you have described. RLS should then limit the many side by table to only have records for that Users BU(s).  Then the many to many pattern would filter the main data table correctly. But the transactions for the data table that do not belong to that person would still be loaded, just not displayed. I think it is a "more pure" solution to set up one BU group for each BU, then assign each user to one or more groups. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 354 members 3,175 guests
Please welcome our newest community members: