Row-Level Security in Power BI: Show Each Manager Only Their Own Numbers
- Matt Lazarus
- 6 days ago
- 5 min read
The request sounds small: "can each state manager see only their own state?" The common answer is the expensive one - copy the report per state, filter each copy, and maintain fifteen diverging versions forever after.
Row-level security is the engineered answer: one report, one model, and per-user data boundaries enforced where they cannot be bypassed. It is also a feature with sharp edges - filters that leak, patterns that crawl, and a testing step that gets skipped right up until the wrong person sees the wrong payroll line.
This guide covers how RLS works, the dynamic pattern that scales, and the discipline that keeps it both fast and safe.
Key Takeaways
RLS replaces report copies with model rules - one governed model serving every user their own slice.
The dynamic pattern scales: USERPRINCIPALNAME against a security table beats hard-coded roles from the first dozen users.
Security lives in the model, never the visual layer - page filters and slicers are curtains, not locks.
What Problem Does Row-Level Security Actually Solve?
It eliminates the filtered-copies anti-pattern: maintaining one report per branch, region or manager, with multiplied maintenance, guaranteed version drift, and the standing risk that someone gets sent the wrong copy. RLS moves the boundary from the distribution layer to the data layer - the same report shows each user only the rows their identity entitles them to.
The economics are stark. Fifteen copies means every change made fifteen times, every error fixed fifteen times, and an audit asking which copy is current. One model with RLS means one change, one fix, and an access rule that can be read, tested and certified.
How Does RLS Work Mechanically?
A role is a named set of DAX filter expressions attached to tables in the model; when a user in that role queries the model, the engine appends those filters to every query invisibly. The user does not see a filtered report - they see what is, for them, the entire dataset.
The static version hard-codes the rule: a role "NSW" filtering the region table to New South Wales, with users assigned to roles in the service. It works, and it stops scaling almost immediately - twelve regions means twelve roles, and every personnel change means an admin edit. Static roles suit a handful of coarse, stable boundaries and nothing else.
What Is the Dynamic Pattern - and Why Does It Win?
One role, one filter, and a security table that maps user accounts to the data they may see. The filter compares the connected user's identity - USERPRINCIPALNAME() - against that table, so access changes by editing data rather than editing the model. At any scale past trivial, dynamic is the only maintainable pattern.
The working parts:
A security table - user email against region, branch or customer key - sourced from wherever that truth already lives (HR system, CRM ownership, a governed list), refreshed like any other table.
One filter expression on the security table matching USERPRINCIPALNAME(), with relationships carrying the restriction to the facts.
Hierarchy support: a manager-to-team mapping (flattened parent-child) lets a state manager see every branch beneath them while branch managers see one - roll-up access without role explosion.
Joiners, movers and leavers now flow through automatically with the source system. The model never changes; the data does.
How Do You Test RLS Before It Tests You?
With View As, every role, against a written matrix of who should see what - before publication, and after every model change. RLS failures are silent: nothing errors, the wrong rows simply appear, and the first detection is usually a user mentioning they can see a colleague's numbers.
The discipline that holds: a test matrix listing representative users per boundary and the row counts or totals they should see; View As checks in Desktop against that matrix; a second check in the service after publication (service identity behaviour is the truth that matters); and re-testing whenever relationships or the security table's logic change - the two edits that most often break filters quietly. Five minutes of matrix testing per release is the entire cost of never sending the apology email.
Why Must Security Live in the Model, Not the Report?
Because everything in the visual layer - page filters, slicers, hidden pages - travels with the report and dies with curiosity. Any user with build permission, an export path or Analyze in Excel sees the full dataset behind the curtain. RLS is enforced by the engine on every query from every tool; a slicer is enforced by hope.
The same principle decides where RLS itself lives as estates mature: in the shared semantic model, once, rather than re-implemented per report. One certified model carrying both the business logic and the security boundary is the architecture every governed estate converges on - and the pattern any serious Power BI consulting engagement establishes early, because retrofitting it later means re-testing every report that grew up without it.
What Does RLS Cost in Performance?
Well-written filters cost little; badly written ones tax every visual for every user. The engine applies role filters to every query, so an expensive expression - lookups inside the filter, bi-directional relationships doing the propagation, or filters on enormous dimension tables - multiplies across the entire audience's every click.
The fast patterns are boring on purpose: filter small security and dimension tables, let single-direction relationships carry the restriction to facts, and keep the filter expression to a comparison rather than a calculation. If a report is fast for admins and slow for everyone else, the role filter is the suspect - profile it the way any measure is profiled. Designed this way, RLS routinely serves hundreds of users from one model with no perceptible cost - the boundary doing its work invisibly, which is the whole idea. The same boundary, incidentally, is what any future AI or natural-language feature inherits: what Copilot may surface per user is decided by exactly these rules, which makes getting them right a forward investment as much as a present one. Robust per-user boundaries are also standard fare in dashboard development for any multi-team rollout.
Does RLS Replace Workspace and App Permissions?
No - it completes them. Workspace roles and app audiences decide who can open a report at all; RLS decides which rows they see once inside. The layers answer different questions, and each is routinely misused to do the other's job - most commonly by granting broad access and trusting RLS alone, or by cloning reports per audience instead of writing one role.
One sharp edge deserves a permanent place in your checklist: members and admins of a workspace bypass RLS entirely. Security is only enforced for viewers and app consumers - which is why production audiences belong in apps, and why "let's just add finance to the workspace" is the sentence that quietly disables your security model.
One Model, Every Truth, Each to Their Own
Row-level security converts an unscalable distribution problem into a governed data rule: one report, one model, one set of definitions - and a boundary that follows identity wherever the data is consumed. The copies retire, the drift ends, and access becomes something you can prove rather than assert.
Build it dynamic, test it against a matrix, and keep it in the model. The state managers get their own numbers - and you get your maintenance back.
