Restricting Data Access in Power BI with Row-Level Security

Here is a scene I have watched play out at more than one Australian company. A sales director asks for a dashboard. The BI team builds a beautiful one. Then someone points out that the Queensland regional manager can see New South Wales numbers, the New South Wales manager can see everyone's commissions, and a contractor who should only see one client's data can browse the whole book. Panic. The quick fix that gets floated is always the same: build a separate copy of the report for each group. Twelve reports instead of one. Twelve things to maintain, twelve places for the logic to drift out of sync.

That whole mess is what row-level security exists to prevent. RLS lets you build one report on one semantic model and have Power BI automatically filter the data so each person sees only their slice. The Queensland manager opens the report and sees Queensland. The New South Wales manager opens the same report and sees New South Wales. Same file, same URL, different data. Done properly it is one of the highest-leverage things you can set up in a Power BI environment, and done badly it is a quiet data leak or a support queue full of "why can't I see anything" tickets. Microsoft's own row-level security documentation is the reference; this is the field guide.

What RLS actually does

At the model level you define roles. A role is a name plus a filter written in DAX. You might have a role called "Regional Manager" with a rule on the Territory table that says the Region column has to equal the region assigned to the logged-in user. Then you assign people or, much better, security groups to that role. When someone in that role opens any report built on the model, Power BI silently applies that filter to every query before results come back. The person never sees the rows they are not allowed to see. They do not know the filter is there. As far as they can tell, the data simply is their region.

The filter runs at the data layer, not the visual layer. This is the part people miss. RLS is not hiding visuals or greying out pages. It is removing rows from the underlying result set. That means it holds up even if someone exports to Excel, uses Analyze in Excel, or hits the model through a custom query. There is no "if they click the right button they can see everything" gap, which is exactly what you want when the restriction is a compliance requirement and not just a tidiness preference.

The clever bit is the USERPRINCIPALNAME() function. In your filter you compare a column in the data to the email address of whoever is logged in. So instead of a static rule per region, you build a mapping table: this email owns these regions, that email owns those. One "Users" role with a dynamic filter covers the whole company, and you manage who-sees-what by editing rows in a table rather than by creating a new role every time someone changes territory. This is dynamic RLS, and for anything beyond a handful of fixed groups it is the pattern you want.

Static versus dynamic, and when to use each

Static RLS is where each role has a hardcoded filter. "Australia" role sees Australia, "New Zealand" role sees New Zealand. It is simple, it is easy to read, and it is completely fine when you have a small, stable set of groups that rarely changes. Three sales regions that have been the same for a decade? Static roles are fine and arguably clearer.

Dynamic RLS is where the filter is driven by data. You have a security mapping table linking user emails to the entities they can see, and the DAX filter joins the user's identity to that table. The upside is enormous: one role scales to ten thousand users, and changing someone's access is a data update, not a model change. The downside is that it is more moving parts, and the mapping table becomes a thing you have to keep accurate and refresh on schedule. If the mapping table is stale, people see the wrong data, and stale security data is worse than no automation at all because everyone assumes it is correct.

My rule of thumb: if the access rules fit on a sticky note and never change, static. If access maps to your org structure, your CRM, or anything that shifts month to month, dynamic, and wire the mapping table to refresh from the system of record so nobody is hand-editing it. We have built a fair few of these for clients through our Power BI consulting work, and the dynamic-plus-authoritative-source combination is almost always where a growing business ends up.

Where teams get it wrong

The single most common mistake is testing badly. You build the roles, you use "View as role" in Power BI Desktop, it looks right, you ship it. But "View as role" simulates the filter; it does not simulate the actual person logged in with their actual group memberships in the actual service. Plenty of RLS setups pass the desktop test and then behave differently once published, usually because the person is in more than one role, or in none, or their group membership resolves differently than expected. Always test with real accounts in the published service before you trust it. Ideally borrow an account from each access tier and confirm they see what they should and nothing more.

The second trap is the additive nature of roles. If a user is assigned to two roles, Power BI gives them the union of both, not the intersection. So a person in "Queensland" and "New South Wales" sees both, which is usually intended, but a person accidentally in "Queensland" and a broad "All Regions" role sees everything, and the narrow role you carefully built does nothing. When someone reports they can see too much, the cause is almost always an extra role assignment they should not have. Audit role membership the same way you audit any access.

Third, and this one bites in production: RLS interacts with how the report is shared and how the workspace is set up. Members of a workspace with edit rights are not filtered by RLS on the models in that workspace, because editors can see the model definition. RLS applies to report consumers, not model authors. So if you drop everyone into the workspace as a member to "make sharing easier", you have just handed all of them the unfiltered data. Consumers should get access through an app or through read-only sharing, never through workspace membership. This is the same access-hygiene point that underpins good Power BI governance generally, and it is worth getting right from day one.

RLS is not a substitute for real security

Worth saying plainly: RLS restricts what data appears inside a report. It does not encrypt anything, it does not stop someone with model edit rights from seeing everything, and it is not a replacement for proper permissions on the underlying source. Think of it as the last mile of a data access strategy, not the whole thing. If your source database has no access controls and you are relying entirely on RLS in Power BI to keep the CFO's salary data away from the interns, you have a fragile setup. RLS should be one layer in a stack that includes sensible source permissions, workspace design, and sensitivity labelling where the data warrants it.

For genuinely sensitive material there is also object-level security, which can hide entire tables or columns rather than just filtering rows, and Microsoft Purview sensitivity labels that follow the data out into exports. RLS handles the "you see your rows" problem elegantly. It does not handle "this column should not exist for you at all". Know which problem you actually have before you pick the tool.

How I would roll it out

Start by writing down the access rules in plain English before touching DAX. "Regional managers see their own region. Directors see all regions. Finance sees everything including commissions. External contractors see only their assigned client." If you cannot state the rules in a paragraph, you are not ready to build them, and half the RLS projects that go sideways do so because nobody agreed the rules before the filters got written.

Then decide static or dynamic, build the mapping table if you are going dynamic, and wire it to refresh from the authoritative system rather than a spreadsheet someone updates when they remember. Assign security groups to roles, never individuals, so that access follows group membership and cleans itself up when people move teams. Test with real accounts across every tier in the published service. And document which role does what, because the person maintaining this in a year might not be you, and undocumented security logic is the stuff nobody wants to touch and everybody is scared to change.

Row-level security is one of those features that looks like plumbing and turns out to be strategy. Get it right and you collapse a dozen fragile report copies into one governed model that shows each person exactly what they should see. Get it wrong and it is a leak with a nice interface. If you are staring at a pile of duplicated reports or you have a "why can this person see that" problem you have been putting off, this is the sort of thing we sort out regularly as part of a broader business intelligence practice. Happy to have a chat about what your access model should actually look like before anyone writes a line of DAX.