Power BI row-level security — a deep dive

A Power BI report that shows everyone's data to everyone is rarely the right answer. Sales managers see their team; account executives see their accounts; HR sees their region. Row-level security (RLS) in Power BI semantic models filters data per user, so the same report shows different rows to different people. Designed well, it's transparent and reliable; designed poorly, it leaks data or breaks queries.

The two patterns.

• Static RLS — roles with hardcoded filter values. "Region = US" for the US Sales role.
• Dynamic RLS — roles that filter based on the current user's properties. "AccountManager.Email = USERNAME()" returns only the current user's accounts.

Dynamic is far more flexible; one role serves many users.

Defining a role. In Power BI Desktop:

1. Modeling tab → Manage Roles.
2. Add Role; give it a name.
3. Add DAX expression as a filter on relevant tables.

Example role "Region Manager":

Sales[Region] = LOOKUPVALUE(RegionMapping[Region], RegionMapping[UserEmail], USERNAME())

Filters Sales to rows matching the current user's mapped region.

USERNAME() function. Returns the current authenticated user's identity:

• In Power BI Service: email or UPN.
• In Power BI Desktop: usually DOMAIN\username.

Use USERPRINCIPALNAME() for consistent UPN across surfaces.

Mapping table approach. Common pattern:

• Separate mapping table: UserAccess[UserEmail, Region].
• Role filter joins user email to the mapping.
• Adding/removing user access = editing the mapping table.

Cleaner than hardcoding user emails in roles.

Testing RLS. In Power BI Desktop:

• View as Roles → select role.
• Optionally "Other user" → enter email.
• Report renders as that user would see it.

Test every role before publishing.

Assigning users to roles. In Power BI Service:

• Semantic model → Security.
• Per role, add users or groups.
• Microsoft Entra security groups recommended for scale.

Group-based assignment. Critical at scale:

• Map an Entra group to a role.
• Add user to the group → automatically has the role.
• Remove from group → no access.

Maintenance happens in Entra, not Power BI; cleaner separation.

Multiple roles per user. If user has multiple roles assigned:

• Filters are OR'd.
• User sees union of what each role allows.

So Role A (Region = US) + Role B (Region = EMEA) = sees US + EMEA. Sometimes desirable (regional manager covering two regions); sometimes confusing (overlapping role assignments hide intent).

Object-level security. Beyond rows:

• Hide specific columns from specific roles.
• Hide entire tables.
• Configured in Tabular Editor or Power BI external tools.

Use cases:

• Sales sees customer financial data; Marketing doesn't.
• Engineers see technical fields; Operations doesn't.

Object-level is newer; less commonly used but powerful.

Performance considerations.

• RLS filters apply on every query.
• Complex DAX in roles → slow queries.
• Indexes / relationships help.
• Cardinality of filtering column matters.

For very large datasets with many users, RLS can become a performance limiter; optimise role definitions.

DirectQuery vs Import.

• Import mode — RLS filters applied at query time against imported data.
• DirectQuery — filters translated to source query.
• Direct Lake (Fabric) — filters applied at OneLake query.

All support RLS; mechanisms differ.

Inheritance and composite models. When composite (mixed import + DirectQuery):

• RLS rules must cover both sources.
• Misconfiguration risks data leak from unfilters source.

Audit composite models carefully.

Common pitfalls.

• Role not assigned. User has access to report but no role → sees nothing (or all data, depending on default).
• Default access too broad. No role = all data; should be no role = no access.
• Hardcoded user emails. Mapping table preferred.
• Cross-filter direction not set. Filter doesn't propagate; data leaks.
• Role left for testing. "Test Admin" role with no filter; assigned in production.
• No audit of role changes. Role definitions drift; security gaps emerge.

Audit and testing.

• Periodic role audit — what roles, who's in them, what they see.
• Penetration test — try to access data outside role; should fail.
• Documentation — role purpose and definition documented.

RLS vs workspace permissions.

• Workspace permissions — coarse: can user open this report?
• RLS — fine: which rows does this user see?

Both apply. Workspace permissions give access to the report; RLS filters within.

Dynamic vs static trade-offs.

• Static: simpler, more roles needed, harder to maintain.
• Dynamic: more complex DAX, fewer roles, easier maintenance.

Dynamic almost always wins for deployments with > 10 users.

Cross-system consistency. If Dataverse has its own row-level security (BU-based, hierarchical), aligning Power BI RLS to match is essential:

• Same user → same data in Dataverse and Power BI.
• Mapping table can be sourced from Dataverse user / team structure.

Strategic positioning. Row-level security is foundational for enterprise Power BI. Without it, every report exposes all data; reports become useless for sensitive content. With it, the same semantic model serves diverse audiences appropriately. Investment in proper role design, group-based assignment, and periodic audit pays back continuously. Skipping it means either limiting reporting scope or accepting data exposure — neither acceptable for serious deployments.