Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tomcch
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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)))

vrzhoumsft_0-1708928823406.png

Result is as below.

 

vrzhoumsft_1-1708928847707.png

vrzhoumsft_2-1708928862987.png

 

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.

 

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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)))

vrzhoumsft_0-1708928823406.png

Result is as below.

 

vrzhoumsft_1-1708928847707.png

vrzhoumsft_2-1708928862987.png

 

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
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.