RLS for 3 Tier Hierarchy -- Two Way Filtering

[ahsan.systs50]

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?

@Melissa Could you please have a look? And congratulations on the launch of your book!

[ahsan.systs50]

@Melissa or anyone? looking for an answer

[Melissa]

Hi @ahsan.systs50,

Is your inquery still open?
Then please share a sample PBIX file with some mock-up data?

[ahsan.systs50]

Hi @Melissa ,

Yes, would love to hear from you. Attaching some sample data.

MockUp Data.pbix (493.0 KB)

[Melissa]

Hi @ahsan.systs50,

Based on the PBIX, could you provide clear examples of the expected behavior when users X, Y, or Z log in?
Thank you! That will save me time.

[ahsan.systs50]

Hi @Melissa

Sure. So the hierarchy goes like this, Division Manager → Area Manager → Territory Manager.

When the DM logs in he can see data for all his AMs & TMs. Similarly when an AM logs in he can see all his TMs. This is the expected behaviour.

However, we have different visuals for Division, Area & Territory in the dashboard. What I am trying to achieve is that when an AM logs in, he should be able to the complete values for his Division and not only the part that reflects his Division (A division is made up of multiple Areas). Likewise a TM should be able to able to see the complete picture of his Area & Division. So for e.g. If the volume of sales for TM 1101 is 3.2Mil, he should be able to see the complete (aggregated) volume of his area i.e. 54Mil – this number comes from all the territories under that Area. Also he should be able to see the complete value of his Division i.e. 250Mil – this number comes from all the Areas under that division. So TM 1101 doesn’t need to see the other territories but just the aggregated values one step above in the hierarchy. Likewise an AM should be able to see the aggregated value for his Division.

How the RLS works for TM 1101 is shown in the attached screenshot. I hope this is clear to understand.