RLS DAX Query Optimization

[abhiram342]

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.

Sample Data:

Thanks,

Abhiram

[lbendlin]

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

[abhiram342]

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

[lbendlin]

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