Forum Discussion

Caesar_PowerBI's avatar
Caesar_PowerBI
New Member
1 year ago
Solved

Dynamic RLS with many tables and many user groups and non-organisational hierarchies

I have many tables in the PBI dataset.
One of the tables has non-organisational group names, group members, email addresses, user names, members in each of the groups.

The other tables have data that relate to events. The other tables also have hierarchies spread across various tables.

We are required to implement RLS. However, as the group members change fairly frequently, we believe RLS will not be the right feature to us as it would mean frequently changing the group memberships in semantic tables security (within the PowerBI service). Instead we believe that Dynamic RLS would be the right fit for this scenario.

We have tried to implement DRLS however it does not work when actual users view the reports in PBI service. It works OK when when testing in security using 'view as role' function.

Otherwise, RLS works perfectly when it is separately tested by actual viewers.

For RDLS, we have created a measure that makes use of UserPrincipalName(). In the manage roles, we have created a role called Reportuser which calls a DAX such as Email address = UserPrincipalName().

We have already tried using one to many as well as many to many relations between some of the key tables, however that does resolve the issue. 

Are you able to suggest what else we could try ?

I am sorry if I have not explained this clearly as it is quite difficult to do so and I am unable to share any data.

Please ask questions and I shall reply as best as I can.

Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    Hi Caesar_PowerBI ,

    Since your Dynamic RLS (DRLS) works in "View As Role" mode but not for actual users in Power BI Service, the issue is likely related to Power BI Service authentication or relationship filtering across tables. Here’s how to troubleshoot and fix it:

     

    1. Check UserPrincipalName() Format in Power BI Service
    The function USERPRINCIPALNAME() returns different formats in Power BI Desktop vs. Power BI Service:

    Power BI Desktop: [email protected]
    Power BI Service: Might return uppercase/lowercase variations or different formats (DOMAIN\user instead of email).
    Solution: Normalize Email Matching
    Modify your RLS filter to make it case-insensitive:

     

    LOWER ( 'UserTable'[Email] ) = LOWER ( USERPRINCIPALNAME() )

     

    Then test it again in the Power BI Service.

     

    2. Confirm User Table is Related to Data Tables
    Your User Table (with Group Members) must properly filter event data. If relationships are one-to-many, ensure cross-filter direction is "Both".

    Solution: Ensure Correct Relationships
    Check Manage Relationships and confirm that UserTable is correctly linked to event data tables.
    If you're using many-to-many, replace it with bridge tables for better filtering.

     

    3. Use LOOKUPVALUE for Direct Filtering
    If your RLS depends on group membership, Power BI might not correctly propagate filters across multiple tables.

    Solution: Use a Direct Lookup
    Instead of filtering via relationships, create a role with:

     

    'UserTable'[Email] IN VALUES ( 'GroupMappingTable'[Email] )

     

    Or:

     

    VAR UserEmail = USERPRINCIPALNAME()
RETURN CALCULATE ( COUNTROWS ( 'UserTable' ), 'UserTable'[Email] = UserEmail ) > 0

     

    This ensures only relevant rows appear for each user.

     

    4. Validate RLS in the Power BI Service
    Assign users to the role in Power BI Service:
    Power BI Service → Dataset → Security → Add Test Users.
    Test as an actual user (not "View As Role") by signing in with another account.

     

    5. Verify That Users Have Access to the Dataset
    Even if RLS is correct, users must have access to the dataset:

    Power BI Service → Workspace → Dataset → Manage Permissions.
    Ensure users have "Viewer" access (not just report access).

     

    Final Steps:
    Normalize email matching (LOWER()).
    Ensure cross-filtering works (BOTH direction or use LOOKUPVALUE).
    Use IN VALUES() instead of complex relationships.
    Test in Power BI Service with actual user logins.
    Ensure dataset access in Power BI permissions.

     

    Please mark this post as solution if it helps you. Appreciate kudos.

1 Reply

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    Hi Caesar_PowerBI ,

    Since your Dynamic RLS (DRLS) works in "View As Role" mode but not for actual users in Power BI Service, the issue is likely related to Power BI Service authentication or relationship filtering across tables. Here’s how to troubleshoot and fix it:

     

    1. Check UserPrincipalName() Format in Power BI Service
    The function USERPRINCIPALNAME() returns different formats in Power BI Desktop vs. Power BI Service:

    Power BI Desktop: [email protected]
    Power BI Service: Might return uppercase/lowercase variations or different formats (DOMAIN\user instead of email).
    Solution: Normalize Email Matching
    Modify your RLS filter to make it case-insensitive:

     

    LOWER ( 'UserTable'[Email] ) = LOWER ( USERPRINCIPALNAME() )

     

    Then test it again in the Power BI Service.

     

    2. Confirm User Table is Related to Data Tables
    Your User Table (with Group Members) must properly filter event data. If relationships are one-to-many, ensure cross-filter direction is "Both".

    Solution: Ensure Correct Relationships
    Check Manage Relationships and confirm that UserTable is correctly linked to event data tables.
    If you're using many-to-many, replace it with bridge tables for better filtering.

     

    3. Use LOOKUPVALUE for Direct Filtering
    If your RLS depends on group membership, Power BI might not correctly propagate filters across multiple tables.

    Solution: Use a Direct Lookup
    Instead of filtering via relationships, create a role with:

     

    'UserTable'[Email] IN VALUES ( 'GroupMappingTable'[Email] )

     

    Or:

     

    VAR UserEmail = USERPRINCIPALNAME()
RETURN CALCULATE ( COUNTROWS ( 'UserTable' ), 'UserTable'[Email] = UserEmail ) > 0

     

    This ensures only relevant rows appear for each user.

     

    4. Validate RLS in the Power BI Service
    Assign users to the role in Power BI Service:
    Power BI Service → Dataset → Security → Add Test Users.
    Test as an actual user (not "View As Role") by signing in with another account.

     

    5. Verify That Users Have Access to the Dataset
    Even if RLS is correct, users must have access to the dataset:

    Power BI Service → Workspace → Dataset → Manage Permissions.
    Ensure users have "Viewer" access (not just report access).

     

    Final Steps:
    Normalize email matching (LOWER()).
    Ensure cross-filtering works (BOTH direction or use LOOKUPVALUE).
    Use IN VALUES() instead of complex relationships.
    Test in Power BI Service with actual user logins.
    Ensure dataset access in Power BI permissions.

     

    Please mark this post as solution if it helps you. Appreciate kudos.