Different RLS For Different Reports

Question

Hi All,

We have a SSAS tabular model setup with existing RLS. One of our staff has developed a new Power BI report that requires a different set of RLS filters to be used. Is there any way to accomplish this without turning the report into Import mode or creating a new cube?

Essentially, we need all users to only be able to see their data for this report, instead of managers and above having access to all data.

One thing that we have already tried is using a measure to filter the Staff table and applying it to visual filters within Power BI:

CALCULATE(COUNTROWS(Staff), FILTER(ALL(Staff[StaffUserName]), Staff[StaffUserName] = USERNAME())) + 0

But this does not work on all visuals within Power BI and seems like a sub-optimal solution.

Cheers

Answer 1

Security roles in SSAS are linked to the user, not to a specific report.

But there are a couple of other approaches that might work.

1. If you are using SSAS 2019 or above you could use calculation groups. If you create a calculation group that limits the Staff table to just the current user you could then set this calculation item as a report level filter in Power BI. Your calculation item expression would then look something like the following:

CALCULATE( SELECTEDMEASURE(), Staff[StaffUserName] = USERNAME() )

2. The other option if you are using SSAS 2017 or earlier would be to create a second version of every measure you wanted to use in your Power BI Report with an expression similar to the one above but using explicit measure names instead of the SELECTEDMEASURE() function (which only works for calculation groups)

Answer 2

@Lukas Yu -MSFT that will not work. You cannot do Direct Query over SSAS at the moment only Live Connect. And regardless you cannot add RLS rules in the report if it is doing either Live Connect or Direct Query over AzureAS or Power BI.

Answer 3

Hi,

Are you designing report on Power BI Desktop and using SSAS tabular as DirectQuery datasource?

I think you could try add RLS at Power BI level as in Row-level security (RLS) with Power BI. DirectQuery should be supported.