Can we test a measure's value in our RLS?

Question

We have a question about using the value of a measure in our RLS. I thought we had this working, but now we see that the RLS is ignoring the measure and always returning all rows.

Specifically, we have a calculation that determines whether a user has access to a report based on the username and whether that user has an entry in a security table. Then we have a data table with this RLS code:

myMeasures[Report_Access] = True

What we see is that even when the measure Report_Access is calculated as False, the user still is able to see all of the rows in the table. (Our intent though is to block all access when this value is False and only let them see all rows when the value of Report_Access is True.)

All I can think of is that RLS only lets measures to be used on the right-side of the equal sign; it doesn't really let us test the value of a measure because that measure is not a column/field in the dataset. Is that correct? And if so, is there a workaround?

We tried a workaround:

MyMeasures[Report_Access] = True && (User_Status = True || User_Status = False)

where User_Status is a field in the dataset with only True & False values. We were hoping that this would force Power BI to perform the calculation on each row. But it still seems that the test on Report_Access is being ignored.

Any ideas?

Chuck

sturlaws · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
have you created a default role? Have you added you measure to the filter of each table for that role?
&nbsp;
Cheers,Sturla

anonymous · Answer

I'm sorry; I'm not sure I understand your questions.

The measure I'm testing is Report_Access.

This report has five tables, but the visual I'm looking at is using one of the tables. We have the security on that table as

MyMeasures [Report_Access] = True

Report_Access is not a column in the data table.

I don't think I've answered your questions, have I?

I'm not familiar with setting up default roles.

anonymous · Answer

And what's crazy is I'm looking at the documentation I made back in March. I'm getting different results now than what I got a few months ago.

sturlaws · Answer

So you have set up something like this:

&nbsp;
If you measure evaluates to true/false, it is not necessary to write measure=true.&nbsp;Now, from the picture above, there is only filter on 'Table', not on 'Table 2'. If there is a relationship between the two tables, the RLS filter on 'Table' might propagate to 'Table 2'(depending on how the relationship is set up). If there is no relationship between the two tables, you will have to manually set something that filters on 'Table 2'.&nbsp;
&nbsp;
Once that is sorted out, you also have to add members to the role if you want them to see any data.
&nbsp;
&nbsp;

anonymous · Answer

I was trying to simplify the example, but let me describe the full issue with screenshots.&nbsp;I have the table Practice data, and I'm testing to see if Report_access is True (which should determine if anything can be seen or if the whole report is empty) and then also filtering the data that the user can see by location and by organization. The visual I'm viewing only has data from the Practice data table.&nbsp;The Security table RLS is:&nbsp;[user email]&nbsp; = Userprincipalname()&nbsp;Report access is defined:Report_access = contains ('Security', [Report name],[This_report])This_report is set to the name of this report.So the first part of the RLS test on the data table is testing to see if this user even has access to this report.&nbsp;Here are the values in my two tables.&nbsp;These next slides are what I captured and documented back in March. Depending upon the report name, the entries in the Report Data would change appropriately. But today, I've opened the same PBIX file that I saved back in March. And no matter what I set the report name value, the data table report displays all of the entries. And even if I use View as Roles and use an imaginary userid that isn't in the security table, I'm still able to see all of the entries in the data table. It's as if the RLS filter isn't there.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Does the explanation make sense? IIf necessary, it's a small practice file that I ran the original tests on, and I could zip it if we have a way to share files.&nbsp;Chuck&nbsp;&nbsp;&nbsp;

Forum Discussion

Can we test a measure's value in our RLS?

8 Replies

Related content

Measure returning incorrect value

Unit testing in report

Blank columns when doing Measure Name Measure Value approach

Latest Monthly Value measure that resets automatically

SUMX(VALUES(column), [Measure]) returns parent-level value, while ADDCOLUMNS/SUMMARIZECOLUMNS works