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

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.