DataDiva's avatar
DataDiva
Icon for Advocate II rankAdvocate II
7 years ago
Status:
New

RLS does not work with firstnonblank/lastnonblank/selectedvalues

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-applied-through-lookupvalue?noredirect=1&lq=1 

 

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

 

Thanks,

Alexandra

 

Data Modeling

2 Comments

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
    DataDiva
    Icon for Advocate II rankAdvocate II
    7 years ago

    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. 

Completed Ideas