Dynamic RLS based on User Organisational Hierarchy

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 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.