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