LookupValue returns value that should not be visible because of RLS restriction

[Anonymous]

Hi,

I'm working on a model that needs to have two layers of restriction.

I can't do both at the same time because it is applied on a model that is used for several reports, and not all of them need the over-restriction to take place.

The first layer is based on the business unit that a user has been assigned to, in CRM (D365), and defined as a dynamic RLS in a role. The hierarchy that these business units describe for this customer has 6 levels. This part is working ok. Let's call this table "A".

The second layer is an over-restriction on top of the first one, with the purpose to allow the users to access just some of the lowest level items in the business unit hierarchy. It's defined with two tables:

• The first table ("B1"), has two columns, the first column being a user names list and the second column describing whether the user has access to all lowest level business units within their assigned business unit, or not.
• The second table ("B2") has two columns as well, the first one contains user names again and the second column holds an entry for each of the lowest level business units to which the user has access.

The way it is intended to work, is to set a column in table A, determining for each entry whether it's visible or not to the user, and it is applied as a filter at report level, "IsVisible = Yes".

It all would be easy peasy if i were allowed to use the function USERPRINCIPALNAME() in that column. But i'm not, so i've had to get creative...

In order to ascertain whether a lowest level business unit is visible or not for a given user, i have set RLS to restrict the entries of the tables for the second layer of restriction (B1, B2), to those that are explicitly related to the user that is accessing the model.

This part works fine as well, and if i test the solution by switching to a user who has restrictions, i'm able to see the effect of RLS because there's just one entry in table B1, and the list of allowed business units for that user on table B2.

Now, here's where i found the incoherence. I need to use a formula such as FIRSTNONBLANK() in a column in table A, to resolve if the current user accessing the report has any additional restriction according to the content of B1, and in case there is, which are the allowed items listed in B2.

As i've applied RLS to the tables B1 and B2, only one value should be available to be returned for FIRSTNONBLANK(), and that should be for the current user.

But FIRSTNONBLANK() and MIN(), are returning the first real value of the table before RLS has been applied, which does not match to the actual user accessing the report.

How can this be possible?

I am certainly lost, and in a hurry to get this solved.

Thanks in advance for any hint you can provide.

[Anonymous]

This is what is happening:

https://stackoverflow.com/questions/49091456/default-filter-in-power-bi-row-level-security-not-appli...

[v-yuezhe-msft]

@Anonymous,

Could you please share sample data of your table and post expected result following the guide in this blog:https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490?

In addition, it seems that you have issues about FIRSTNONBLANK(). As per this article, the function returns the first value in the column, filtered by the current context, where the expression is not blank.

Regards,
lydia

[Anonymous]

Hi Lydia,

I'm trying to prepare some data to ilustrate this issue, but it's properly described in the link i attached on the second post.

Could you please give a look to it, and reply back telling me if still more info is needed?

Thanks in advance for your help,

[Anonymous]

Probably the best way to summarize the issue is:

We have two tables in a model, A and B.

B is restricted by RLS and shows entries that comply with the established RLS rule.

Any formula that is used in a column in table A to retrieve a value from table B (LOOKUPVALUE, FIRSTNONBLANK, MIN, MAX...), is not being ruled by the RLS restriction once the model is published to PBI Service.

The formula has somehow access to the whole content of table B, instead of just the content that is allowed according to the rule.

In other words, the formula is getting access to table B BEFORE RLS is applied, and provides the values accordingly. Which in this case, are wrong values, it should provide the result from table B AFTER RLS has been applied.

It's inconsistent because if i pull the content of table B in a visual on a report, in PBI service after RLS has taken place, it will show the right values: just those that RLS allows to pass. But when using a formula to retrieve a value from that table's resultset, it sees the whole table and retrieves values that should not be seen.

[DataDiva]

I am having exactly the same issue! 

At the core, firstnonblank does NOT supply the first nonblank value based on context, because it ignores the context supposedly supplied by the RLS. I played around with it even with a slicer--if you select only one value from a table, FIRSTNONBLANK will still return the original first value based on the unfiltered table. 

Why on earth would FIRSTNONBLANK (or any of the other options) not obey RLS or a filter?