Dynamic RLS in Power BI with mapping tables

João Barros 22 de November de 2024 2 min read

Power BI's Row-Level Security (RLS) ensures John sees only North sales and Mary only South, even though both use the same report. Dynamic RLS uses the authenticated user's email address to filter data automatically.

Mapping architecture

Instead of creating one role per region (which does not scale), create a dim_UserRegion table:

| Email                  | Region   |
|------------------------|----------|
| john@company.com       | North    |
| mary@company.com       | South    |
| admin@company.com      | *        |

Configure the Role in Power BI Desktop

In Modelling → Manage Roles, create the DynamicSalesRep role and apply the following DAX filter on the dim_Region table:

[Region] = LOOKUPVALUE(
    dim_UserRegion[Region],
    dim_UserRegion[Email], USERPRINCIPALNAME()
) || LOOKUPVALUE(
    dim_UserRegion[Region],
    dim_UserRegion[Email], USERPRINCIPALNAME()
) = "*"

Publish and assign

After publishing to the Power BI Service, go to Dataset → Security and add the Azure Active Directory users or groups to the DynamicSalesRep role. You do not need to add each person — the filtering logic is resolved by the mapping table.

Test

Use View As → Other User in Power BI Desktop or Test as Role in the Service to verify what each user sees before publishing to production.

Conclusion

Dynamic RLS with mapping tables is the approach recommended by Microsoft for scalable row-level security. By centralizing permissions in a data table, organizational changes become a simple row update — without touching the report.