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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhiram342
Employee
Employee

RLS DAX Query Optimization

Hi Team,

 

I have implemented RLS on below model but the performance is slow. can some please suggest way to optimize DAX Code.

RLS Tables:

User Product Sub: A User can have access to atleast one products. ( Each User will have atleast one or more entry in Table)

User Product Sub WW : User has access different categories of Products. ( Each User will have single entry in table)

Fact Sales: Sales Reported for each Customer. Due to Telemetry, we see null Customer ID in Facts. We want to show sales for products even for null customers only for user who are part of Product Sub WW.

Product Table: Product ID and Product Name

RLS DAX code on Product Table:

VAR PRODUCTS =
CALCULATETABLE (
VALUES ( 'User Product Sub'[ProductID] ),
'User Product Sub'[UserAlias] = USERPRINCIPALNAME () && 'User Product Sub'[Is Authorized User] = TRUE()
)
VAR WWAccess = MAXX(FILTER('User Product Sub WW', 'User Product Sub WW'[UserAlias] = USERPRINCIPALNAME () && 'User Product Sub WW'[Is Authorized WW User] = TRUE), 1)
RETURN
'Product'[ProductID] IN PRODUCTS || ('Product'[ProductID] = BLANK() && WWAccess =1)

Please find the attached model with sample data.

 

Model Diagram.jpg

Sample Data:

Sample Data.jpg

Thanks,

Abhiram

 

3 REPLIES 3
lbendlin
Super User
Super User

Why are customers linked to products?  These should be independent dimension and the product ID should be listed in the Sales table.

Hi @lbendlin - Thank you for your response. Adding ProductID to Fact requires us to reprocess all the fact data and it takes 2-3 days to reprocess historical data. As per existing design, we have relationship between Product and Customer Table. Product Table is small  (130 rows) and Customer is large ( 9 Million rows). 

 

Please suggest if there is way that i could optimize  DAX query in RLS 

 

Thanks,

Abhiram

Do the normalization in Power Query, or challenge the need for RLS in this scenario.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors