Performance Optimization for Power BI Row Level Security Model

[sumanthkakarla]

I am working on a Power BI project where I need to implement Row Level Security (RLS) to control what data users can see. The model is expected to serve up to 1000+ users, thus performance is important. These users are not all expected to be concurrent users, but some proportion could be.  The RLS filter uses the DAX function USERPRINCIPLENAME() to identify the logged in user, and filter data to just records related to their respective company Here is my data model.

as per Micrrosfots suggestion i have joined based on integer columns and here is how my Dynamic RLS is defined and the DAX that i applied ( users from New_account_map table can have access to more than one account, also for all internal users they do have to access to ALL accounts so they will be seeing all the data, only external users should see the assigned company records)

for ex: ( if you see below demo data, Shane has access to three accounts where as bob has access to ALL the accounts /data, if 

Shane logged in he should only see data related to those 3 accounts where as Bob logged in then he should see the entire data)

NEW_USERACCOUNT_MAP sample data

CLIENT_CCOUNT_NUMBER     USERNAME

1234                                         shane@abc.com

2345                                          shane@abc.com

4567                                          shane@abc.com

ALL                                          bob@mycompany.com

Dax RLS Query:

IF (

    MAXX ( FILTER ( 'NEW_ACCOUNT_MAP', upper('NEW_ACCOUNT_MAP'[USERNAME]) = upper(USERPRINCIPALNAME()) ), upper('NEW_ACCOUNT_MAP'[CLIENT_ACCOUNT_NUMBER] )) <> "ALL",

   upper( ('NEW_ACCOUNT_MAP'[USERNAME])) == upper(USERPRINCIPALNAME()),

    TRUE

)

Here's a brief overview of my data model:

• Table 1: TAT only This table contains all the fact detail data which has client_Account_number in it( note it has around 20 M records)
• Table 2: New account Map  Each clienct account number in this table is linked to multiple transactions in the TAT only fact table.
• Table 3: dim_Date this is normal dim_Date table

I appreciate any insights or suggestions you might have to improve performance of my RLS.

Thank you in advance!

[v-pnaroju-msft]

Hi sumanthkakarla,

We are following up to check whether you have raised the support ticket. If you have already done so, we kindly request you to share your feedback on the issue raised.
If a solution has been provided, we would appreciate it if you could share it with the community and mark it as the accepted solution. This will help others facing similar challenges and benefit the broader community.

Thank you.

[v-pnaroju-msft · Accepted solution]

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.

[v-pnaroju-msft]

Hi sumanthkakarla,

Based on my understanding, the newly created dimension tables (Health System, Facility, Test ID) are disconnected dimensions, meaning they are not naturally filtered by Row-Level Security (RLS) applied to the fact table. Power BI does not allow filter propagation from the fact table to the dimension tables in single-directional relationships. By default, filters only flow from dimension tables to fact tables. Consequently, even though RLS is filtering the fact table, the slicers from the new dimension tables will still display all values, as they are not filtered through RLS.

Please follow the approach outlined below, which may assist in resolving the issue:

1. Apply bidirectional relationships only to the slicer dimensions that are necessary for filtering (for example, Health System, Facility, Test ID). Since these dimensions are derived directly from the fact table, Power BI requires a bidirectional flow to enable the fact table, which is RLS-filtered, to pass the filter context back to the dimension tables.
2. Ensure that this is applied only to low-cardinality dimensions (such as Health System) and avoid applying it to all dimensions simultaneously to prevent query performance issues. To accomplish this:
   1. In Model View, click the relationship arrow between the fact table and each dimension table.
   2. Set the Cross-filter direction to Both.
   3. Enable "Apply security filter in both directions" only if the slicers must respond to RLS.

This will ensure that your slicers display only relevant values once RLS is applied.

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who are facing similar queries.

Thank you.

[sumanthkakarla]

Thank you for your reply @v-pnaroju-msft 

i tried this bi-drectional apparoach to one of the dimenstion and its working as expected, however the rest of the 2 dimensions are not working, when i try to setup both and apply security filter to both direction its giving me the error for the rest of the dimesnions where users are seeing all the slicer filters which they should not have, its wokring for only one Dimension

Error: Cross filter direction can be applied to only one dimeion when using Dynamic RLS" i will send you the exact error

[v-pnaroju-msft]

Hi sumanthkakarla,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[sumanthkakarla]

Hello @v-pnaroju-msft , after implementing the bridge route still i am facing performance issue, i bealive the filters are casuing my dahboard to perform poor. so i though ot creating three new Dim tables connect to fact and now i am facing a different kind of issue , all the 3 visual filters previously coming from fact now comign from the corresponding dim table, but when i apply Dynamic RLS its not filtering the visual filters and users are seeing the entire list in the filters when he clicks on drop down list.

for example: Helath System filter is showing all the list which it suppose to restric to few helth systems where he has access and like wise for the rest of the filters. Here is how the dim tables are getting populated

Here is how i created a new 43dim tables based on fact tables, 

Health System Dimension = DISTINCT('TAT Only'[HEALTH_SYSTEM])

Facility = DISTINCT('TAT Only'[CLIENT_FACILITY_ACCOUNT_NAME])

TEST_ID = DISTINCT('TAT Only'[TEST_ID_NAME])

so if apply the Dynamic RLS then user should see only relevant health system names assiged to him instead he is seeing all, fact is not filtering the health system dimension, how can the fact should filter health system dimesnion, tried to apply cross filter direction to health system dimesnion realtion ship and its working, but cant make similar kind of change to rest of two dimesnions and i am getting this error. thats where i stuck at this point of time.

[v-pnaroju-msft]

Hi sumanthkakarla,

In the current configuration, the filter flows from CLIENT_LOOKUP to client_account_bridge, thereby allowing all data to pass through unrestricted.

In my view, the issue arises due to the direction of the filter. It should flow in the opposite direction from client_account_bridge to CLIENT_LOOKUP in order to effectively enforce Row-Level Security (RLS) restrictions.

By setting the cross-filter direction to Both and applying RLS on the client_account_bridge table, end users will be able to view only their assigned accounts. Meanwhile, internal users with full access will be able to view all data without any limitations.

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide your valuable kudos. This will assist other community members who may have similar queries.

Thank you.

[v-pnaroju-msft]

Hi sumanthkakarla,

Thank you for your follow-up.

Kindly follow the steps mentioned below, which may assist in resolving the issue:

1. Ensure that relationships are configured with single-directional filtering to support Row-Level Security (RLS):

   • USER_LOOKUP → client_account_bridge.Establish a one-to-many relationship with the filter flowing from USER_LOOKUP to client_account_bridge.

   • client_account_bridge → CLIENT_LOOKUP.Define a many-to-one relationship with the default filter flowing from CLIENT_LOOKUP to client_account_bridge. The RLS filter direction will be managed in Step 2.

   • CLIENT_LOOKUP → TAT Only.Create a one-to-many relationship with the filter flowing from CLIENT_LOOKUP to TAT Only.

   In the Model view, double-click on each relationship to confirm the cardinality, and set the Cross-Filter Direction to "Single" to ensure predictable filter propagation and optimal performance.

2. To ensure that the RLS filter propagates correctly from client_account_bridge to CLIENT_LOOKUP and subsequently to TAT Only:

   • Apply the RLS filter to the client_account_bridge table. This will restrict the bridge table based on the logged-in user, allowing the filter to naturally propagate through the relationships to CLIENT_LOOKUP and TAT Only. This method eliminates the need for bidirectional filtering.

   • Navigate to the Modeling tab, click on Manage Roles, and create a new role named "Dynamic RLS".

   • Apply the RLS filter to client_account_bridge to determine whether the logged-in user has access to all data (based on a predefined condition) or only their assigned accounts.

   • Save the role and test its functionality using the View As feature with sample users (e.g., an internal user with full access and an external user with restricted access).

This approach ensures that the correct data is displayed while maintaining optimal performance by avoiding bidirectional filtering.

If you find this response helpful, kindly mark it as the accepted solution and provide your feedback. Your acknowledgment will assist other community members facing similar queries.

Thank you.

[sumanthkakarla]

Thank you @v-pnaroju-msft , i did the same thing like you told me to do, but when i test the Dynamic RLS with one user who has access to only 10 accounts now he is seeing all the data, may be my Dynamic RLS Dax is not workign as epxcted, but just to note the same exact Dax is working on regualr dahabord where we have many-many relation ship. Here are the relation ships and their setup

1. USER_LOOKUP to CLIENT_ACCOUNT_BRIDGE (one-to-many relationship which filters client_account_bridge table using user_lookup table, 

2. CLIENT_ACCOUNT_BRIDGE--->CLIENT_LOOKUP ( many-to-one relationship which filters client_account_bridge using client_lookup)

3.Client_lookup --> Tat Only ( one -to-many relationship wihcih filters Tat only using client_lookup)

4. Finally here is the Dynamic RLS Dax query ( which is wokring in regular dashbaord)

Not sure whats going wrong here.

OVerall Data model:

[v-pnaroju-msft]

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.

[sumanthkakarla]

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

) > 0

RETURN

IF(

    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.

[v-pnaroju-msft]

Hi sumanthkakarla,

Thank you for the update.

Kindly follow the steps mentioned below, which may help resolve the issue:

1. 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.
 

2. 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.

[sumanthkakarla]

@v-pnaroju-msft thank you for your reply. Just confused here

1. 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

1. USER_LOOKUP[USERNAME] → CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER]

   CLIENT_LOOKUP[CLIENT_ACCOUNT_NUMBER] → TAT Only[CLIENT_ACCOUNT_NUMBER]

[sumanthkakarla]

Thank you @v-pnaroju-msft i will give it a try and let you know, our dashbaord has 4 visuals, one of the visual is detailed data, so i can't aggrigate data before importing.

Quick question is, about the revised dax that you are suggesting, so if my logged in user in my account map table and his clienct_account_number says ALL then only i have to show the user with all data if not i have to display the data only relevant to that perticular user, where are we checking the second conditon in your DAX , i will give it a try and let you know

[v-pnaroju-msft]

Hi @sumanthkakarla,

Thank you for your inquiry through the Microsoft Fabric Community Forum.

Implementing Row-Level Security (RLS) in Power BI for a large user base necessitates careful optimisation to maintain performance. Kindly follow the steps outlined below to enhance efficiency:

1. Establish relationships using integer keys instead of strings for efficient joins.Since this has already been implemented, you are on the right track.

2. Instead of using MAXX and FILTER, consider a more direct approach, as illustrated below:

   'NEW_ACCOUNT_MAP'[USERNAME] = USERPRINCIPALNAME() || 'NEW_ACCOUNT_MAP'[CLIENT_ACCOUNT_NUMBER] = "ALL"
   This expression verifies whether the logged-in user has access to specific accounts or all accounts directly, thereby reducing computational overhead.

3. If high granularity is unnecessary, aggregate data before importing to reduce processing load.
4. Measures are computed on demand, making them more efficient than calculated columns. Additionally, simplify DAX expressions to minimise the use of complex filters and calculations within measures.
5. Regularly track usage and assess user concurrency to prevent performance issues.
   

   
   For further reference, please visit:
   Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn
   
   

   If this response is helpful, kindly mark it as the accepted solution and provide kudos to assist other community members.
   
   Thank you.

   
   
   
   
   

[sumanthkakarla]

@v-pnaroju-msft , to avoing many to many relationship i tred the brige table, somehow this is also not working, not sure whats wrong with my data model, userlookup( al distinct users) filter bridge table for a given login user, bridge table should filter client_lookup( distinct client account numebrs), client_lokup should filter the fact table for those users acccessed accounts data, please note i am giving Dynamic RLS as this on user_lookup table , whats worng with my data model ? why i amnot able to see when i apply Dynamic RLS ?