Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Johnny
Regular Visitor

Dynamic RLS based on User Organisational Hierarchy

Hi,

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.

Eg: http://www.sqlservercentral.com/blogs/koen-verbeeck/2016/04/28/dynamic-security-in-power-bi/

This works fine when the filter and role allign 1 to 1 - (when 1 user has 1 role/responsibility) - eg. RLS Rules:
1) DimUser[Email]=USERNAME()
2) DimSalesResponsible[SalesRespKey]=DimUser[UserSalesRespKey] (measure)

DimUser[UserSalesRespKey]
UserSalesRespKey = LOOKUPVALUE (
DimUser[SalesRespKey];
DimUser[Email]; USERNAME())

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

Rgds,
Johnny

7 REPLIES 7
tlausser
Regular Visitor

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:

Data model with 3 level hierarchyData model with 3 level hierarchy

 

The SitesTable had information about the accounts with the assigned SalesRep as JOIN key.

 

The idea was simply this:

  • A SalesPerson should only see her own accounts
  • A SalesManager should see all accounts for all the SalesReps reporting to her
  • A SalesDirector should see all accounts for all the SalesReps reporting to SalesManagers reporting to her

 

 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:

 

SalesManagerTable:

SalesDirector = LOOKUPVALUE(SalesDirectorTable[Director],SalesDirectorTable[Manager],SalesManagerTable[Manager])

SalesDirectorTable:

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:

 

SalesPerson Role

  • SalesPersonTable: [SalesRep] = USERNAME()
  • SalesManagerTable: false
  • SalesDirectorTable: false

SalesManager Role

  • SalesPersonTable: [SalesManager] = USERNAME()
  • SalesManagerTable: [Manager] = USERNAME()
  • SalesDirectorTable: false

SalesDirector Role

  • SalesPersonTable: [SalesDirector] = USERNAME()
  • SalesManagerTable: [SalesDirector] = USERNAME()
  • SalesDirectorTable: [Director] = USERNAME()

 

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.

UserRegionCountryDesignation
AX Y
BXD 
CY  
D E 
E DQ

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.

 

Regards

Mallikarjun

Hi tlausser

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.

Thankyou

datamodel
Advocate I
Advocate I

Try these: 

 

DimSalesResponsible

[SalesRespKey] = LOOKUPVALUE(

DimUser[UserSalesRespKey]

,DimUser[UserSalesRespKey],FIRSTNONBLANK(DimSalesResponsible[SalesRespKey],TRUE())

,DimUser[Email],USERNAME())

 

DimUser

[UserSalesRespKey] = LOOKUPVALUE(

DimSalesResponsible[SalesRespKey]

,DimSalesResponsible[SalesRespKey],FIRSTNONBLANK(DimUser[UserSalesRespKey],TRUE())

,DimSalesResponsible[SalesRespKey],LOOKUPVALUE(DimUser[UserSalesRespKey],Users[Email],USERNAME()))

 

ankitpatira
Community Champion
Community Champion

@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 🙂

 

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors