RLS Retain Null Values for All Users

[DataSundowner]

Hello everyone. I have a fact table with LocationID and a location dimention table that connects to LocationID in the facts table. Then I have a RLS table that has locationID and user email, and I'm joining the RLS table with the location dimention table. It works fine to show each user the locations of what they are supposed to see. However, there are also data without a locationID (nulls) in the fact table. What should I do if I want everybody to be able to see those data with null locationIDs in the fact table? Thanks! 

[jdbuchanan71 · Accepted solution]

Hmm, try it like this.

VAR _UPN = USERPRINCIPALNAME ()
RETURN
	'LocationDim'[locationID]
	IN CALCULATETABLE ( DISTINCT ( 'RLS'[locationID] ), 'RLS'[Email] = _UPN )
	|| ISBLANK ( 'LocationDim'[locationID] )

[DataSundowner]

@jdbuchanan71 This one worked! Thank you! 

[jdbuchanan71]

@DataSundowner 

What if you disconnect the RLS table and apply the RLS rule to the LocationDim table like this.

VAR _UPN = USERPRINCIPALNAME ()
RETURN
    'LocationDim'[locationID]
        = LOOKUPVALUE ( 'RLS'[locationID], 'RLS'[Email], _UPN )
        || ISBLANK ( 'LocationDim'[locationID] )

[DataSundowner]

Hi @jdbuchanan71 I tried what you suggested, but received the following error when I viewed as a user from RLS. 

[v-henryk-mstf]

Hi @DataSundowner ,

For data without a locationID (nulls) in the fact table. for the presence of other fields in the fact table, I think we should try to add a judgment condition to the formula to achieve this requirement.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataSundowner]

Hi Henry. Thank you for your reply. Please see the screenshots below. My goal is to allow everyone in RLS table to be able to see data with null locationID in the fact table.