I have a minor challenge regarding RLS.
I have a loong list of users in a organizational hierarchy - and as these hierarchies goes - it's not straight forward.
Hence people typically have multiple roles/responsibilities in different levels of the hierarchy
I have tried to set it up by using the existing RLS guides and DAX Expressions - by adding a non-related users table.
This works fine when the filter and role allign 1 to 1 - (when 1 user has 1 role/responsibility) - eg. RLS Rules:
2) DimSalesResponsible[SalesRespKey]=DimUser[UserSalesRespKey] (measure)
UserSalesRespKey = LOOKUPVALUE (
However the measure and filter fails when the user has multiple roles (which I understand as it is a scalar and cannot take multiple values).
I'm trying to get around this by making a calulated table instead of a measure - but RLS filtering does not provide much feedback at the moment - and I'm not sure it's the right way to go.
I hope to get some feedback on this - either in form of a way of making the filter/measure accept multiple values or not using a measure at all to filter the rows in RLS.
Any help is appreciated
We had a similar requirement in a Sales Dashboard with a 3 level hierarchy (Director - Manager - SalesRep).
We started with an executive version of the Dashboard which showed metrics for every salesrep across the entire organization with no RLS involved (which also has the advantage of not requiring Power BI Pro).
The next step was to model the hierarchy using a simple spreadsheet to identify the reporting relationships in three tables as follows:
The SitesTable had information about the accounts with the assigned SalesRep as JOIN key.
The idea was simply this:
A first idea was to use a recursive DAX expression on the SalesPersonTable to see if this particular SalesPerson was either the current user USERNAME() or in a reporting relationship ending with the current user somewhere up the hierarchy. But I couldn't find an example of such a construct and my command of DAX syntax isn't strong enough (good stuff out there though, for example at daxpatterns.com).
A work-around consisted of adding calculated columns to each of the tables lower in the hierarchy pointing to the supervisor up the hierarchy.
The formula used for these columns uses the LOOKUPVALUE function as suggested above in this discussion, with nested calls for a multi-level hierarchy:
SalesDirector = LOOKUPVALUE(SalesDirectorTable[Director],SalesDirectorTable[Manager],SalesManagerTable[Manager])
SalesManager = LOOKUPVALUE(SalesManagerTable[Manager],SalesManagerTable[SalesRep],SalesPersonTable[SalesRep]) SalesDirector = LOOKUPVALUE(SalesDirectorTable[Director],SalesDirectorTable[Manager], LOOKUPVALUE(SalesManagerTable[Manager],SalesManagerTable[SalesRep],SalesPersonTable[SalesRep]))
Then for each hierarchy level a role needed to be defined in the pbix model, with an additional role called SuperUser for the executives (unrestricted access). Now that each row in the SalesPersonTable has the corresponding SalesManager and SalesDirector, the DAX expressions take the usual simple form:
Lastly, after first publishing the model to the Power BI cloud, the dataSet security settings need to have the corresponding employees listed in the security roles to map to the proper role-based RLS settings in the model. As it is based on email addresses, this can be simplified using email distribution groups.
Adding more levels to the hierarchy - such as a Sales VP - requires additions to the model (one new table for that level and a new column for each table below) as well as added RLS formula and roles. So it's a bit clunky, but it works for us.
I'd love to hear ways to do this more elegantly and without need for data model changes.
I have a similar situation. The difference is that I have many unique responsibility for each of the users stored in a table.
The levels can vary from Region,Country,Designation,Sector etc.. So for eq: A can see entire region X data for those with Designation Y, B can see country D data, E can see Country D data for people with designation Q and so on...
I am thinking of using this table and applying RLS filter on Geography and Department table columns by looking up UPN from this responsibility table.
Is there a better alternative way to approach this scenario? 🙂
Hi @tlausser ,
Same way i need to implement in my project like
we have users,Applications,Roles if a user Admin role for particular application, he needs to see all users transactions for that application,if he is not part of Admin he need sto see his transactions only.
how i need to cocnfigure in this case.
can you please help me on this.
When i try to create the calculated column using LOOKUPVALUE in a report using direct query it gives error that lookupvalue cannot be used in direct query mode. Is there any workaround or any other function that can be used to accomplish the same.
[SalesRespKey] = LOOKUPVALUE(
[UserSalesRespKey] = LOOKUPVALUE(
@Johnny With power bi service may update you can use Distribution Lists or AD groups within RLS along with individual users. May be that is the way to go with your requirements.
To some extend.
We have about 16 Sales Districts, 5 Business Areas and 75+ Sales Responsible. Setting Power BI up for a 1 to 1 scenario with AD roles or distribution lists is not be a viable solution.
I still need to make a 1->M (Users->Roles) and M<-1 (Roles<-Row) match. Normally (within BI) you'd create a bridge table (DimUser) to handle this complexity. However this requires the filtering to handle "IN" statements - which DAX isn't handling too gracefully.
Good suggestion though 🙂
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps