RSL with one level up comparitive

[samdthompson]

Hello, I have a simple model that I want to apply some RLS to. Each region (5) has a manager on which I have limited the data using a simple [Region] = 'Region code' in the roles, that bit working fine. The regions each belong to an Island. I want each region to be able to see there own Avg but also the Island Avg and I guess the National Avg. By putting the region into the roles management, I have effectively filtered all my data to just that region so no Island or National comparitive can be surfaced. I am stumped as to how to do it.

I have attached a sample PBIX and some screenshots of the relationships, the table as non filter, the table as viewed by one of the regions:

Any help, much appreciated.

[samdthompson · Accepted solution]

I solved this problem by adding a second version of the Fact table summarized to region with no RLS applied to it. It works well

[v-sihou-msft]

@samdthompson

I don't think this is possible. Your Island Avg and National Avg are based on data for all regions. However, once you the row level security takes effect, only those "valid" rows are kept. The data for other regions will be removed. Even you calculate the Island/National Avg, it only only calculate the Island/Nation with only one region data, because it evaluates the table context before calculation.

Regards,