RLS + filter out nulls

[ebee]

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 default, it seems the nulls are always shown, no matter how the dim is filtered by the rls. How would you prevent that? Should I

• Convert null org_id values into -1 and create some custom DimOrganization for that id
• Do a bunch of table filters for the rls role to discard nulls from various fact tables
• Something else

Thanks in advance

[Smalfly]

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 · Accepted solution]

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.

[ebee]

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