Power BI · Intermediate

Implementing row-level security in Power BI

One published report that shows each person only their own rows — and an entitlement model your examiner can inspect.

Get help with this Skip to configuration

Who this is for

Two summaries, because two audiences read this

If you own the outcome

This is what stops branch-level performance packs circulating as email attachments that immediately diverge. One report, correct for every reader, with the entitlement rules held in one place rather than in a distribution list.

If you have to build it

Role definition, DAX filter expressions, the dynamic pattern using USERPRINCIPALNAME, entitlement tables sourced from your directory, and testing with View As before publishing.

Why it matters

The problem this solves

Institutions produce a performance pack, filter it per region, and email the copies. The copies diverge immediately, nobody is sure which version is being quoted, and there is no single place where entitlement is defined.

The technical fix has existed for years. What stops it is that nobody has been given the authority to decide who is entitled to see what, so the report designer defaults to sending everyone everything and filtering by hand.

Before you start

Prerequisites

Check these before beginning. Most stalled implementations stall on one of them.

Licensing

Power BI Pro at minimum. Fabric capacity (F64 or equivalent) if you intend to use Copilot against the model.

Roles

Workspace member or admin to configure roles; someone with authority to approve who sees what.

Data

A reliable person-to-entity mapping. Directory data or an HR feed is far better than a hand-maintained list.

Decision

Written agreement on the entitlement rules before you build. This is a business decision, not a technical one.

How it works

The concepts worth understanding first

Configuration is straightforward once these are clear. Skipping them is why most first attempts produce something that works and cannot be maintained.

Static roles are simple and do not scale

A static role hard-codes a filter, for example region equals North. It works, but you need one role per region and manual membership management. Fine for five regions, unmanageable for fifty branches.

Dynamic security uses the signed-in identity

USERPRINCIPALNAME() returns the identity of the person viewing the report. Joined against an entitlement table, one role covers every user. Joiners and leavers are handled by the source of that table rather than by editing the model.

Row-level security filters rows, not columns

If a column itself is sensitive regardless of row, that is object-level security, which is a separate mechanism. Conflating the two is a common design error that surfaces at the worst moment.

Configuration

Step by step

Settings shown are the ones that matter, not every field on the form. Values are starting points to validate against your own environment.

Build the entitlement table

One row per person per entity they may see. Source it from your directory or HR system rather than maintaining it manually — a hand-kept list is a leaver risk with a reporting interface.

Include the hierarchy explicitly. A regional director who should see six branches gets six rows, not a rule that walks a tree at query time.

Columns: UserPrincipalName, EntityKey, and optionally a role or level column
Source: Entra ID, HR system or a governed table — not a manually maintained spreadsheet
Refresh: At least daily; leavers should lose access within one refresh cycle
Relationship: Single direction, from the entitlement table to the dimension it filters

Create the role and write the filter

In Power BI Desktop, Modeling then Manage Roles. Apply the filter to the entitlement table and let the relationship propagate it to the fact tables.

Filtering the entitlement table rather than the fact table directly is the pattern that performs well and stays readable.

DAX filter: [UserPrincipalName] = USERPRINCIPALNAME()
Applied to: The entitlement table, not the fact table
Cross-filter direction: Single, from entitlement to dimension to fact
Role name: One dynamic role is usually sufficient; avoid a role per business unit

Handle the executives who see everything

Two workable approaches. Either add rows to the entitlement table covering every entity for those individuals, or add a boolean flag and extend the DAX to bypass the filter when it is set.

The first is more transparent and easier to audit. The second is tidier and harder to review. In a regulated institution we recommend the first.

Approach: Explicit entitlement rows, so the audit trail shows exactly who can see what
Alternative DAX: [UPN] = USERPRINCIPALNAME() || LOOKUPVALUE(Override[SeeAll], Override[UPN], USERPRINCIPALNAME()) = TRUE()

Test with View As before you publish

Power BI Desktop lets you view the report as a specific user. Test at least one account per role type — a branch manager, a regional director, an executive, and someone who should see nothing.

Then test again in the service after publishing, because workspace membership can override what you expect.

Assign members in the service

Roles are defined in Desktop and populated in the service, under the semantic model's security settings. Assign a security group rather than individuals so membership is managed where identity is managed.

Note the exception that catches people out: workspace admins, members and contributors bypass row-level security entirely. Report consumers should be given Viewer access only.

Verify it worked

Use View As in Desktop for one account per role type, including an account that should see nothing.
After publishing, sign in as a genuine test user and confirm the row count matches expectation.
Confirm a workspace Viewer sees filtered data and that nobody who should be a Viewer holds Contributor.
Remove a person from the entitlement source and confirm access is gone after the next refresh.
Check performance with the filter applied on a realistic data volume, not a sample.

Best practice

What we do on every engagement of this type

Source the entitlement table from your directory, never from a manual list
Filter the entitlement table and let relationships propagate, rather than filtering facts directly
Use explicit rows for executives so the audit trail is complete
Assign roles to security groups, not to individuals
Give report consumers Viewer access only — higher roles bypass the security
Document the rule set where an examiner would look for it

Pitfalls

What catches most first attempts

Every one of these is avoidable, and every one of them is common enough that we check for it by default.

!Workspace roles bypassing security

This is the one that produces incidents. A Contributor sees all rows regardless of role membership. Audit workspace access at the same time you configure row-level security, not afterwards.

!Bidirectional relationships defeating the filter

A bidirectional relationship elsewhere in the model can propagate around your security filter. Keep cross-filter direction single wherever row-level security is in play, and test the specific paths.

!Manual entitlement lists

A spreadsheet maintained by one person is a leaver risk. When they are on holiday, or when they leave, access reviews stop happening and nobody notices.

!Assuming it protects the underlying data

Row-level security filters what a report shows. It does not stop someone with dataset access from querying it another way. Model permissions matter as much as the role definition.

Completion checklist

Entitlement rules agreed in writing by someone with authority
Entitlement table sourced from directory or HR, refreshing at least daily
Dynamic role written against USERPRINCIPALNAME and applied to the entitlement table
Tested with View As for every role type including a no-access account
Workspace access audited so consumers hold Viewer only
Rule set documented for examination

Want a second pair of eyes?

Pick one report that currently circulates as filtered exports. We will rebuild it with row-level security against your structure and walk your risk team through how the entitlement would be evidenced.

Request a consultation See our Power BI page We reply to every message within one business day.