Forum Discussion
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"
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
Community ChampionWhy 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.
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.
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