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 your follow-up query. I sincerely apologise for any confusion.
1.The data model employs a bridge table to resolve the many-to-many relationship between USER_LOOKUP and CLIENT_LOOKUP, which is the appropriate approach. However, it is essential to ensure that the relationships are configured with single-directional filtering to facilitate the correct propagation of the RLS filter.
The relationships should be defined as follows:
USER_LOOKUP[USERNAME] → Bridge Table[USERNAME] (One-to-Many, Single Direction).
Bridge Table[CLIENT_ACCOUNT_NUMBER] → CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER] (Many-to-One, Single Direction).
CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER] → TAT Only[CLIENT_ACCOUNT_NUMBER] (One-to-Many, Single Direction).
In the Model View, kindly double-click on each relationship and set the Cross Filter Direction to Single (not Both). This configuration ensures that the RLS filter flows correctly from USER_LOOKUP to TAT Only through the bridge table.
2.Please apply the following DAX expression to the USER_LOOKUP table for RLS and verify if it resolves the issue:
VAR UserHasAllAccess =
CALCULATE(
COUNTROWS('Bridge Table'),
'Bridge Table'[USERNAME] = USERPRINCIPALNAME() &&
'Bridge Table'[CLIENT_ACCOUNT_NUMBER] = "ALL"
) > 0
RETURN
UserHasAllAccess || 'Bridge Table'[USERNAME] = USERPRINCIPALNAME()
Next, navigate to Modeling, select Manage Roles, and create a new role named Dynamic RLS. Apply the above DAX expression to USER_LOOKUP and save the changes. This configuration ensures that internal users have access to all data, whereas external users can view only their respective accounts.
To test RLS, go to Modeling, select View As, and choose the Dynamic RLS role.
If you find our response helpful, kindly mark it as the accepted solution and provide kudos, as this will assist other community members facing similar queries.
Thank you.
Thank you v-pnaroju-msft
I tried to setup the relationship as per your suggestion, but still the relationship between Bridge_Table and Client_lookup is filtering towards Bridge only, but i want the otherway around as the bridge table has many client_Accounts where as client_account has unique accounts and bridge table should filter the client_looup table, the only way i can think of is Cross Filter Direction need to setup to Both instead of Single or i might be missing something here.
2. The Dynamic RLS DAX : I have modifed the DAX little bit as the original one is not working and its working as expected. here is the modified dax
VAR UserHasAllAccess =CALCULATE(COUNTROWS('client_account_bridge'),'client_account_bridge'[USERNAME] = USERPRINCIPALNAME() &&'client_account_bridge'[CLIENT_ACCOUNT_NUMBER_INT] = 1363845086) > 0RETURNIF(UserHasAllAccess,TRUE(),LOOKUPVALUE('client_account_bridge'[USERNAME],'client_account_bridge'[USERNAME], USERPRINCIPALNAME()) = USERPRINCIPALNAME())somehow if i can fix the Cross filter direction then i feel like i am okay with it.
