Forum Discussion
Performance Optimization for Power BI Row Level Security Model
Hi sumanthkakarla,
We sincerely apologize for the inconvenience caused.
As the issue remains unresolved, we kindly request you to raise the matter at Issues - Microsoft Fabric Community for investigation. Alternatively, you may raise a Microsoft support ticket using the link provided below. They will be able to offer specific insights related to your account and potential resolutions.
Microsoft Fabric Support and Status | Microsoft Fabric
If you find our response helpful, we would appreciate it if you could mark it as the accepted solution and provide kudos. This will assist other community members who may be facing similar queries.
Thank you.
Hi sumanthkakarla,
Thank you for the update.
Kindly follow the steps mentioned below, which may help resolve the issue:
-
Internal users should have access to all data, whereas external users should only be able to view data linked to specific accounts.The revised DAX logic should ensure that if the logged-in user has "ALL" as their CLIENT_ACCOUNT_NUMBER, they are granted full access. Otherwise, they should only be able to view data associated with their assigned accounts.
VAR UserHasAllAccess =
CALCULATE(
COUNTROWS( 'NEW_ACCOUNT_MAP' ),
'NEW_ACCOUNT_MAP'[USERNAME] = USERPRINCIPALNAME() &&
'NEW_ACCOUNT_MAP'[CLIENT_ACCOUNT_NUMBER] = "ALL"
) > 0
RETURN
UserHasAllAccess ||
'NEW_ACCOUNT_MAP'[USERNAME] = USERPRINCIPALNAME()
This optimized DAX logic eliminates unnecessary FILTER and MAXX functions, reducing computational overhead.
-
Correcting Table Relationships (Single-Directional Filtering)
USER_LOOKUP[USERNAME] → CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER]
CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER] → TAT Only[CLIENT_ACCOUNT_NUMBER]
USER_LOOKUP should filter CLIENT_LOOKUP, which in turn filters TAT Only.To enhance performance, avoid many-to-many (M:M) relationships. Ensure single-directional filters for correct data propagation.
If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.
v-pnaroju-msft thank you for your reply. Just confused here
Correcting Table Relationships (Single-Directional Filtering)
USER_LOOKUP[USERNAME] → CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER]
CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER] → TAT Only[CLIENT_ACCOUNT_NUMBER]
USER_LOOKUP should filter CLIENT_LOOKUP, which in turn filters TAT Only.To enhance performance, avoid many-to-many (M:M) relationships. Ensure single-directional filters for correct data propagation.
If you see my data model, USER_LOOKUP AND CLIENT_LOOKUP are not directly connected together, they are connected through bridge table, so i am not quite follwing you, how will user_lookup will filter client_lookup as there is no dicrect realtionship with these
USER_LOOKUP[USERNAME] → CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER]
CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER] → TAT Only[CLIENT_ACCOUNT_NUMBER]
