RLS for 3 Tier Hierarchy – Two Way Filtering

[ahsan005]

Hi All,

I have a 3 Tier Hierarchy (Regional, Area & Territory Manager) for which I’ve successfully implemented RLS by creating a separate RLS _Hierarchy table based on the roles. My lowest granularity is the SAP ID on the basis of which I’ve joined the tables. Data Model screenshot attached and below is the RLS role I’ve created for RLS_Hierarchy table:

IF(
[DIVISION_USERPRINCIPALNAME] = USERPRINCIPALNAME () ||
[AREA_USERPRINCIPALNAME] = USERPRINCIPALNAME () ||
[USERPRINCIPALNAME_MANAGER] = USERPRINCIPALNAME (),
TRUE(),
FALSE()
)

So if a RM logs in, he can see all his areas & territories, Similarly an area manager can see all his territories and territory manager being the lowest in hierarchy can see his territory data only.

For the National View (not affected by RLS & visible to all irrespective of who logs in) I’ve created an aggregated fact_sales table so whoever logs in can see all the data since this table is not part of RLS filteration.

Now my question is that I want to do the same for the Area Manager & Territory Manager. For e.g. If an area manager logs in, he can see the complete value for the region that he belongs to and not only the regional values that correspond to his area. Similarly a territory manager can see the values for his complete area and not only the area values that reflect his territory. Kind of a two-way filtering thing.

Screenshot ‘RLS_Hierarchy’ attached that shows the rows when territory manager abc_789@check.com logs in and can see his territory data but as you can see area Lahore and region North are getting filtered out on the basis of SAP IDs that only belong to 1101.

I know this concept violates the RLS principle but is there a possibility of doing this through changes in the data model? complex DAX as RLS role?

@AlexisOlson Can you please have a look?

[Anonymous]

Hi @ahsan005 ,

Please refer this case, hope it is helpful for you:

Solved: Want to create a hierarchy based Metrics with user... - Microsoft Fabric Community

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ahsan005]

This also works in one direction from higher to lower which I've already implemented. I'm looking for a solution that allows me to go two ways. 

@lbendlin @Ritaf1983 @Greg_Deckler seniors, could you please have a look?

[lbendlin]

What's the point of RLS if you are filtering up?  Can you challenge if RLS is actually required for your use case?

Anyway, your rule can be refactored as

USERPRINCIPALNAME() IN {
[DIVISION_USERPRINCIPALNAME],
[AREA_USERPRINCIPALNAME],
[USERPRINCIPALNAME_MANAGER]}

[ahsan005]

Yes, RLS is required based on the hierarchy that is mentioned. Actually, we have 3 different sets of visuals for Region, Area & Territory so when a terrritory manager 1101, as shown in the screenshot above logs in, he sees the same values in all 3 sets. We are looking for a solution, if possible, to reflect complete values in the higher hierarchy visuals.

So territory manager for 1101 sees his values in territory visuals, but he needs to see the complete (aggregated) values of area 'Lahore' which is made up of a total of 10 territories. So he can see the aggregarted values in Area visual but not the individual territory values. Similarly for Region.

[lbendlin]

Might be easier to use three different reports/audiences.