RLS design & DAX rule suggestions

[Anonymous]

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

[MattAllington]

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/

[Anonymous]

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. 

[Anonymous]

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?

relationshipsRLS rule

Thanks
Kaz

[MattAllington · Accepted solution]

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

Business_Units[Business Unit] = "BU1"

[Anonymous]

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.

[MattAllington]

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

Double pipe is the OR operator.

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

[Anonymous]

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

[MattAllington]

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. 

[Anonymous]

Hi Matt,

I would prefer a dynamic solution as my original report has 80 users, and I know that some of the other solutions that I am working on, have multiple groups and over 200 users, at variuos levels. This translates into a considerable chunk of maintenance work which I'd like to avoid, unless it could be automated. You reckon there's a way using PowerShell, or anyother mechanism, that we can automate the synchronisation of the the groups? 

As far as having one group per BU goes, as I want users associated to multiple BUs, be able to look at all of their BUs, I have created one single group with a DAX evaluating multiple BUs, effectively BU=1 || BU=2

Thanks
Kaz

[MattAllington]

Sorry, I've been busy. It seems to me that you have the data somewhere, so I assume this can be loaded into the data model. I would be then looking to leverage the username() function to give access or not. But you would need to test it of course. 

[Anonymous]

Thanks for getting back to me @MattAllington, I have rolled the solution out for UAT. I have leveraged UserName() DAX function and it works.

I was referring to adding indivual member in the list of users under Datasets > Security. 

Thanks

Kaz