Forum Discussion

ebee's avatar
ebee
Helper I
1 year ago
Solved

RLS + filter out nulls

Hi, Let's say I have a DimOrganizations table that is affected by RLS. It has a one to many relationship to some Fact table via org_id column. Now, the Fact table has some nulls in its org_id. By de...
Need Help
  • ebee's avatar
    ebee
    1 year ago

    Oh!! You are correct. In a normal RLS role, nulls are not shown.

     

    I think I found a bug. If you filter a user table with RLS dax ie. containsstring( username(), [user] ), it will show all nulls from any linked fact tables.

     

    Using 

    containsstring( username(), [user] ) && not( isblank( [user] ))
    fixes it.
Smalfly's avatar
Smalfly
Responsive Resident
1 year ago

Hi ebee ,

 

that is strange. According to my experience, the default is that null values are always filtered out by RLS.

Are you sure your RLS is doing what it is supposed to do apart from the null values? Hence, that everything has been set-up correctly?

 

If so, can you please share some sample data with us?

  • ebee's avatar
    ebee
    Helper I
    1 year ago

    It is easy to create a test using enter data which confirms nulls are kept.

    Kimball recommends to never have nulls on foreign keys so maybe I'll go with that. I'll replace nulls with -1 or something and create a corresponding dimension row for that labeled "Undefined".

  • ebee's avatar
    ebee
    Helper I
    1 year ago

    Oh!! You are correct. In a normal RLS role, nulls are not shown.

     

    I think I found a bug. If you filter a user table with RLS dax ie. containsstring( username(), [user] ), it will show all nulls from any linked fact tables.

     

    Using 

    containsstring( username(), [user] ) && not( isblank( [user] ))
    fixes it.