Row-level security and dynamic permissions — Power BI Module 9

If a report contains data that not every viewer should see — typical in finance, HR, regional sales — row-level security is mandatory before publishing. Power BI's RLS is enforced at the dataset level via DAX filters tied to user identity. Done right, you publish one report that each viewer sees their slice of.

Static RLS

Modeling tab → Manage Roles → New Role.
Name the role (e.g., 'East Africa Sales').
Pick a table; write a DAX filter: [Region] = "East Africa".
Save. In Service, assign users / groups to the role via the dataset's Security settings.

Each user can only be in one effective role at a time. If you have many regions, you'd create many roles — fine for under 20 distinct slices, painful beyond.

Dynamic RLS

text

// 1. Add a permissions table to the data model:
//    UserPrincipalName | AllowedRegion
//    alice@co.com      | East Africa
//    bob@co.com        | West Africa
//    bob@co.com        | Central Africa
//    cfo@co.com        | * (or list each)

// 2. Create a relationship: permissions.AllowedRegion → Sales.Region

// 3. Define a role with this filter on the permissions table:
[UserPrincipalName] = USERPRINCIPALNAME()

// 4. With bidirectional filtering enabled on the relationship,
//    the user's allowed regions cascade through to Sales.
//    Each user sees only their permitted rows.

Dynamic RLS pattern. Add users by inserting rows in the permissions table — no role-management ceremony. Scales to thousands.

Test before publishing

Modeling tab → View as → pick a role and optionally specify a username. The report now renders as if you were that user. Use this to verify each role sees what they should before publishing. Without testing, you'll publish a report where everyone sees everything — the classic RLS-failure embarrassment.

Common RLS pitfalls

Workspace admins bypass RLS by default in their workspace. Test by publishing to a separate test workspace.
Direct dataset connections (XMLA endpoint, paginated reports) honour RLS; embedded scenarios may not — verify per scenario.
If the permissions table doesn't update on refresh, new users won't see anything. Make sure it's part of the scheduled refresh.
Bidirectional filtering on the permissions relationship is usually required. Forgetting it leaves users seeing zero rows.

Exercise

Build a Power BI report on a Sales table with a Region column. Create a permissions table with 3-4 fake user-region mappings. Set up dynamic RLS using USERPRINCIPALNAME() and a permissions table. Test using 'View as Role' with each user. Does each user see only their permitted region? What happens if a user has no row in the permissions table?