Question on Row Level Security / null mean allow to view all data

[tomcch]

Dear all

I have a main table storing Customer_ID and related data like Sales.

And I have a user role table storing which user could access which customer ID like belows
Main Table
Customer_ID | Sales

ABC|$300
XYZ|$4450
DXC|$1000

User Table

User_Email|Customer ID

UserA@abc.com|ABC 

UserB@abc.com|null  

(null mean no restriction to customer_ID in our system)

Would it be possible to let UserB view all the customer data?
Or I have to make the user table like the following?

User_Email|Customer ID

UserA@abc.com|ABC 

UserB@abc.com|ABC 
UserB@abc.com|XYZ
UserB@abc.com|DXC  

Thank you.

[v-rzhou-msft · Accepted solution]

Hi @tomcch ,

According to your statement, I think your requirement is that the User whose Customer ID is null could see all value.

I suggest you to try code as below in Manage Roles 'Main Table'[Custom ID].

[Customer_ID] = 
VAR _LIST = CALCULATETABLE(VALUES('User Table'[Customer ID]),FILTER(ALL('User Table'),'User Table'[User_Email] = USERPRINCIPALNAME()))
RETURN
IF("null" in _LIST,CALCULATE(MAX('Main Table'[Customer_ID])),CALCULATE(MAX('Main Table'[Customer_ID]),FILTER('Main Table','Main Table'[Customer_ID] in _LIST)))

Result is as below.

Best Regards,
Rico Zhou

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

[vanessafvg]

how are you currently restricting with RLS?  not quite clear what you are saying

if you userb to see all the data, then you need to set up a role that allows that