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

[Anonymous]

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]

Hi @Anonymous 

have you created a default role? Have you added you measure to the filter of each table for that role?

Cheers,
Sturla

[Anonymous]

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.

[sturlaws]

So you have set up something like this:

If you measure evaluates to true/false, it is not necessary to write measure=true. 

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'. 

Once that is sorted out, you also have to add members to the role if you want them to see any data.

[Anonymous]

I was trying to simplify the example, but let me describe the full issue with screenshots.

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.

The Security table RLS is: [user email]  = Userprincipalname()

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.

Here are the values in my two tables.

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.

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.

Chuck

[Anonymous]

It appears to me that a change has been made to Power BI that has stopped this from working. What I'm wondering ... is this a permanent change, or is it a bug that I can expect to be fixed?

[sturlaws]

Hi @Anonymous 

sorry for the delayed response, I were not able to get any work done during summer vacation.

If you have a file you can share, you can upload it to dropbox/onedrive/other, and share the link, either here in the forum or send it to me in a private message

Cheers,
Sturla

[Anonymous]

(Perhaps you can send a direct message to me with your email address. Every time I try to DM to you my Dropbox link and instructions, I get an error message that I'm trying to send too many private messages.)

Chuck

[Anonymous]

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.