Forum Discussion

tomcch's avatar
tomcch
Frequent Visitor
2 years ago
Solved

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

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

[email protected]|ABC 

[email protected]|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

[email protected]|ABC 

[email protected]|ABC 
[email protected]|XYZ
[email protected]|DXC  

 

Thank you.

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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