Forum Discussion
Helper IIRLS Retain Null Values for All Users
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!
Hmm, try it like this.
VAR _UPN = USERPRINCIPALNAME () RETURN 'LocationDim'[locationID] IN CALCULATETABLE ( DISTINCT ( 'RLS'[locationID] ), 'RLS'[Email] = _UPN ) || ISBLANK ( 'LocationDim'[locationID] )
6 Replies
v-henryk-mstfCommunity Support
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.
DataSundownerHi 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.
- jdbuchanan71
Super User
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.
- jdbuchanan71
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!