RLS for one category and for all. Is it possible?

[silvias]

Hello,

I have a report for him I have RLS by "Institute" so all the professionals of an institute only see the data of their institute.

The problem is, I need everyone to be able to have "two views": their institute (as up to now) and the overall (the sum of all the institutes). I don't want them to be able to filter by institutes other than their own, but I do want them to be able to access the sum of all institutes and to select their institute individually.

I can't use Username() because it would be a role issue. For each institute there are many professionals.

Is there any way to do this other than having two different reports, one for the global and one with the filter at the institute level? For example, something that makes RLS security only apply to one page of the report?

Thank you very much.

[lbendlin · Accepted solution]

You do this by having a hidden "shadow" copy of your fact data that has no RLS. You caclulate the "all institutes"  data from there, and the institute specific data from the RLS based main table.

WARNING: Security by obscurity does not work. Any reasonably skilled user can unhide that hidden table.  So the very least you should do is remove the institute identifiers from the shadow table to anonymize the data.

[silvias]

Thank @lbendlin.

Now I have another doubt. You could do it the way you indicate (doubling fact tables and measurements, thus having two sheets: one with RLS and one with the overview). I also found another way: Leaf-level security. The model and measurements would look exactly as I have it, only that I would make a copy of the sheet filtered by each Institute and a button from global vision that takes me to the vision of the Institute and ee button dynamically for role takes me to one page or another, as indicated in: https://www.linkedin.com/pulse/page-tab-level-security-conditional-navigation-power-bi-a%25C5%259F%2...

The thing is that I have a very large model with many measurements (it makes method 1 difficult) and I also have many sheets and many institutes (it makes option 2 difficult, having to make one copy for each sheet and institute). As Best Practice, what do you advise me? Follow method 1 or method 2?

Thank you very much in advance!!