Varying RLS accross a report.

[IsaacRicketts]

The below screenshot gives a very oversimplified example of the data relationship I am working with. I would like to apply Row Level Security for a report that the Managers will be looking at. In this report, I would like the Managers to be able to see overall sales of other sites run by the same Area Manager. However, I would also like the Managers to be able to view the raw sales figures of their own site but not of the other sites overseen by the same Area Manager. The issue I run into is obviously RLS will filter on the lowest level possible - so if I try to give the Managers access to their own raw sales as well as other Site Managers overall sales, then they will be able to view other Site Managers raw sales too which is not what I want. I could creat two almost independent models however due to the size of the file I am working with this is not really possible.

Any help/advice is much appreciated!

[Nicole_PBI · Accepted solution]

Try to create a second fact table, which contains the aggregated overall sales by manager. Don't create a relationship to your RLS table.

In that way the overall sales will not be filtered by RLS. But your first fact table, which contains low level data, will so.