RLS does not work with firstnonblank/lastnonblank/selectedvalues

[DataDiva]

There seems to be a bug in how RLS relates to firstnonblank/lastnonblank/selectedvalues kind of formulas. If you use one of these formulas on a table that is filtered by RLS, it will bring in data as if the RLS was not applied. This seems like a serious issue, and certainly is causing a number of problems for formulas we are trying to write.

I'm not the only one who's experienced this challenge: https://stackoverflow.com/questions/49091456/default-filter-in-power-bi-row-level-security-not-appli...

But it looks like over a year later it's still the same way.

Thanks,

Alexandra

[v-yuta-msft]

@DataDiva ,

I can reproduce this issue. But RLS will be applied to other tables when they have relationship with each other. So if you don't build a relationship between Location and MyLocation based on column [Location Key], RLS will not be applied to other tables. 

My sample tables are like below:

In my test, I have input RLS rule below on 'Location':

  

Then I add the column using DAX:

Column = LOOKUPVALUE(
    'MyLocation'[Location Value],
    'MyLocation'[Location Key],
    'Location'[Location Key]
)

The result is not the expect result as he said.

 

But If we build a relationship between table 'Location' and 'MyLocation', the result will be normal.

 

Regards,

Jimmy Tao

[DataDiva]

I have a problem that in order to create a bridge table to set a relationship for the RLS to the main table, it's either a many-to-many join, or it has to go through another reference table. By that point, the RLS still doens't work through the relationship. 

Microsoft really needs to fix this bug. I don't understand how you could possibly allow anything in the model to access data that should be filtered by RLS.