Row-Level Security in Power BI: Restricting Data Access by User

Introduction

We had a client come to us a few years ago convinced their Power BI rollout was finished. The dashboards looked sharp, the numbers were right, and leadership loved the demo. Then, three weeks after launch, a regional sales manager in the Midwest opened the report on his phone and saw the full national pipeline, including deals, discounts, and margins from every other region in the company. Nobody had set up row-level security. The model had one dataset, one report, and zero restrictions on who could see what once they clicked into it.

This is one of the more consequential gaps we run into when we take over an existing Power BI environment. Getting the visuals right, getting the DAX right, getting the refresh schedule right, all of that matters, but none of it matters if the wrong person can open a report and see data that was never meant for them. Row-level security, usually shortened to RLS, is the feature inside Power BI that solves this. It lets you publish a single report and a single dataset, and have different users see only the rows of data they are actually allowed to see, based on who they are.

In this guide, we are going to walk through what row-level security actually does, the difference between static and dynamic RLS, how to build and test it properly, and the mistakes we run into most often when we inherit a client's model. We will also share a comparison table and a checklist, so this is something you can act on rather than just read about. Our aim, as always, is to keep this grounded in how we actually build these models for clients, not in theory.

What Row-Level Security Actually Is

Row-level security is a set of rules, built inside your Power BI data model, that filters the rows of data a given user is allowed to see whenever they open a report. It works at the data layer, not the visual layer. This distinction matters more than it sounds. Hiding a page or greying out a button in a report is a design choice. RLS is a security control, enforced by the model itself, so even if someone knows the underlying table names and tries to explore the data outside the visuals you designed, they still only ever see the rows they are entitled to.

The mechanism itself is simple to describe. You define one or more roles inside Power BI Desktop. Each role carries a DAX filter expression that gets applied to a table whenever a member of that role opens the report. If a salesperson's role filters the Sales table down to rows where the SalesRep column matches their name, that is the only data they will ever see, in every visual, every page, and every export, without a single change to the report design itself.

RLS applies on top of your existing model relationships. If your Sales table filters down to a salesperson's own rows, and Sales has a relationship to a Customers table, that relationship will typically carry the filter through, so the salesperson also only sees the customers tied to their own sales. This is what makes RLS genuinely useful at scale. You are not rebuilding logic across every table. You are applying one filter at the right point in the model and letting the existing relationships do the rest, provided those relationships are built the way RLS expects them to be.

Static RLS vs Dynamic RLS

There are two broad ways to build row-level security in Power BI, and picking the right one for your situation is really the first decision that shapes everything else about the build.

Static RLS means the filter condition is written directly into the role, as a fixed value. A role called West Region might carry a filter that says Region equals "West". This works cleanly, and it is the fastest way to get RLS running for a small, stable list of roles. The catch is that every role has to be created and maintained by hand inside Power BI Desktop, and every user has to be manually assigned to the correct role inside the Power BI service after publishing. For three or four fixed roles that almost never change, this is a completely reasonable approach and we still recommend it in that situation.

Dynamic RLS means the filter condition is calculated at the time a user opens the report, based on who they actually are, usually pulled from a function called USERPRINCIPALNAME, which returns the email address of the signed in user. Instead of hardcoding "West" into a role, the role's filter expression looks up the current user's email in a security mapping table, finds which region or which accounts they are allowed to see, and filters the model accordingly. This means you can usually get away with just one or two roles total, covering hundreds or thousands of users, because the actual restriction lives in a data table rather than in the role definitions themselves.

Most of the client environments we build or inherit end up needing dynamic RLS, simply because most businesses have people joining, leaving, and changing territories often enough that static roles become a maintenance burden within a few months. We covered some of the DAX mechanics that make dynamic RLS possible in our broader piece on DAX in Power BI, which is worth a read if row and filter context is still a little fuzzy for you.

Static RLS vs Dynamic RLS at a Glance

Here is the side by side comparison we walk clients through when we are deciding which approach fits their organisation.

How Dynamic RLS Actually Works Under the Hood

Dynamic RLS relies on three pieces working together, and understanding each one makes the whole feature far less mysterious. The first piece is a security mapping table, a simple table you add to your model that lists each user's email address alongside whatever they are allowed to see, such as a region, a cost centre, or a list of account IDs. This table does not come from your source system. You build it deliberately, and you keep it current.

The second piece is the relationship between that mapping table and the rest of your model. The mapping table needs to connect, directly or indirectly, to the fact table you want restricted, using the same kind of relationship logic that drives every other filter in your model. If that relationship is missing, badly configured, or set to the wrong filter direction, dynamic RLS will not filter the way you expect, and this is the single most common reason dynamic RLS fails silently during testing.

The third piece is the DAX expression inside the role itself. A typical dynamic role filters the mapping table using something close to UserEmail equals LOWER(USERPRINCIPALNAME()), wrapping the comparison in LOWER because email addresses are not always cased consistently between your source system and how Power BI receives them from Azure Active Directory. That single filter on the mapping table then flows through the relationship to restrict every table connected to it.

A detail that trips up a lot of teams building this for the first time is that USERPRINCIPALNAME only returns a meaningful value once a report is published and opened through the Power BI service, or through Power BI Desktop's own Live preview of roles. It does not reflect a real signed in identity while you are just building the report locally, which is exactly why testing this properly, rather than assuming it is working, matters so much.

How RLS Affects Refresh and Report Performance

Row-level security is not free. Every filter it applies is real work the engine has to do, and the impact depends heavily on how the mapping table and its relationship are built. A clean, well indexed mapping table with a simple one to many relationship into the fact table adds a barely noticeable amount of overhead. A messy mapping table, one built from multiple merged sources with duplicate rows or an ambiguous relationship, can slow report rendering noticeably, particularly on larger fact tables.

One pattern we watch for closely is RLS layered on top of a bidirectional relationship. Bidirectional filtering already asks the engine to do more work than a single direction filter, and stacking a security filter on top of that combination can occasionally produce unexpected results, not just slower ones, where a filter propagates further than intended and either over-restricts or under-restricts what a user sees. Our general guidance is to keep the relationship that RLS depends on as a single direction wherever the model allows it, and to reserve bidirectional filtering for the rare cases that genuinely need it, well away from anything security related.

Many to many relationships raise the same concern. If your security mapping table connects to the fact table through an intermediate bridge table, rather than directly, take the time to trace that chain end to end and confirm the filter actually reaches every table you expect it to. We have seen models where a user's role technically filtered the bridge table correctly, but the relationship between the bridge and the final fact table was set to not filter in that direction, so the restriction quietly stopped one hop too early.

None of this means RLS should be avoided on large models. It means the same modeling discipline that keeps a Power BI file fast in general, clean relationships, single direction filters where possible, and a mapping table that is no larger or more complicated than it needs to be, is exactly what keeps RLS fast too. Security and performance are not in tension here. A well built model tends to deliver both.

Building an RLS Role Step by Step

The exact clicks vary a little by Power BI Desktop version, but the sequence we follow on almost every engagement looks like this.

• Confirm the model relationships are already correct before touching RLS, since RLS filters travel along existing relationships and cannot fix a broken model
• Add or confirm a security mapping table that lists each user and what they are permitted to see, and load it into the model like any other table
• Build a relationship from the mapping table to the fact table you want restricted, checking the filter direction carefully
• Open Manage Roles in Power BI Desktop and create a new role, giving it a clear, descriptive name rather than something generic like Role1
• Write the DAX filter expression for that role, applied to the mapping table for dynamic RLS, or applied directly to the fact table for static RLS
• Use View As to preview the role from inside Desktop, checking totals, visuals, and any tables that should now be restricted
• Publish the report to the Power BI service, then assign real users or security groups to the role from the dataset's security settings
• Test again after publishing, this time as an actual assigned user rather than through the Desktop preview, since the two do not always behave identically

Testing RLS Properly

This is the step we see skipped most often, usually because everything looked correct in the View As preview inside Power BI Desktop, and the team assumed publishing would simply carry that same behaviour into production. It does not always. The View As feature in Desktop simulates a role, but it is still you, the report author, looking at your own data connection, and certain connection types and certain gateway configurations behave differently once a report is actually published and a different, restricted user opens it.

The only test we fully trust is asking an actual member of a role, or using a dedicated test account assigned to that role, to open the published report themselves and confirm what they see. We specifically check three things every time. First, that the restricted user sees only what they should, nothing extra. Second, and just as important, that they are not accidentally seeing nothing at all, which happens more often than people expect when a relationship is broken or a mapping table row is missing for that user. Third, that totals and KPI cards reflect the filtered numbers correctly, since a badly built measure using functions like ALL or REMOVEFILTERS can quietly bypass RLS and show unfiltered totals even though the detail-level visuals look correctly restricted.

That third point deserves its own mention, because it is the one that catches experienced report builders off guard. Certain DAX functions are explicitly designed to ignore filters, and RLS is enforced as a filter. If a measure uses ALL on a table that RLS is meant to restrict, that measure can return a number that includes rows the current user should never see, even while every visual around it looks properly filtered. Any measure using ALL, ALLEXCEPT, or REMOVEFILTERS on an RLS protected table needs a manual review before you consider RLS finished.

Common Mistakes We See With RLS

We run an access review as part of most Power BI health checks now, specifically because the same handful of issues keep showing up across otherwise well built models.

• Assuming the View As preview in Desktop is the same as production behaviour, and skipping the real published-user test entirely
• Using ALL, ALLEXCEPT, or REMOVEFILTERS inside a measure on a table that RLS is supposed to protect, which quietly leaks unfiltered totals
• Letting the security mapping table go stale, so a person who left the company six months ago can still open the report and see live data
• Building the mapping relationship in the wrong filter direction, so the role technically exists but never actually restricts anything
• Forgetting that RLS does not apply to dataset owners, workspace admins, or anyone with build permission on the underlying dataset, all of whom can bypass RLS entirely by design
• Applying RLS only to a report's visible tables and forgetting about a hidden or supporting table that a user could still reach through Analyze in Excel or a live connection

That admin bypass point is worth sitting with for a moment, because we get asked about it often. RLS in Power BI was never designed to restrict people who have edit or admin rights on the workspace or dataset itself. It restricts viewers. If your organisation needs to prevent an admin from seeing certain data, that is a workspace permissions and governance question, not something RLS alone will solve, and it is exactly the kind of gap we walk through when we are asked to review a client's broader Power BI architecture.

How We Approach This at Versich

When our team scopes a Power BI Consulting engagement that touches sensitive data, whether that is sales commissions, patient information, or financial results, row-level security is one of the first things we design, not one of the last things we bolt on before launch. We ask who needs to see what, how often that access changes, and whether the organisation already has a clean source for that mapping, such as an HR system or an existing security group structure, before we write a single role.

For most clients we lean toward dynamic RLS from the start, even if the current headcount is small, because it is far cheaper to build it right the first time than to migrate a live model from static roles later. Where a client already has a Power BI environment with no RLS in place, or RLS that was built quickly and never properly tested, our Power BI Support Services engagements typically start with exactly the access review we described above, checking every role against a real test account before we call it secure.

We also see this come up constantly for clients running Power BI on top of NetSuite, where financial data naturally needs to be restricted by subsidiary, department, or role. Our NetSuite and Power BI Integration Services are built with that restriction in mind from the extraction layer onward, so the mapping table RLS depends on is accurate from day one rather than something we have to reverse engineer later. If a broken model is the underlying problem, it is also worth reading our piece on Power BI architecture explained, since RLS only ever works as well as the relationships underneath it.

If your team is deciding between bringing in a consultant to design the security model or a developer to build it, we would say the two go together more than people expect. We touched on this same theme in our earlier post on query folding versus calculated columns, and the honest answer is the same here. Getting the architecture decision right matters just as much as the DAX itself, and skipping either one tends to show up later as a support ticket, or worse, as a data exposure nobody noticed until it was too late.

How Dynamic RLS Actually Works Under the Hood

Dynamic RLS relies on three pieces working together, and understanding each one makes the whole feature far less mysterious. The first piece is a security mapping table, a simple table you add to your model that lists each user's email address alongside whatever they are allowed to see, such as a region, a cost centre, or a list of account IDs. This table does not come from your source system. You build it deliberately, and you keep it current.

The second piece is the relationship between that mapping table and the rest of your model. The mapping table needs to connect, directly or indirectly, to the fact table you want restricted, using the same kind of relationship logic that drives every other filter in your model. If that relationship is missing, badly configured, or set to the wrong filter direction, dynamic RLS will not filter the way you expect, and this is the single most common reason dynamic RLS fails silently during testing.

The third piece is the DAX expression inside the role itself. A typical dynamic role filters the mapping table using something close to UserEmail equals LOWER(USERPRINCIPALNAME()), wrapping the comparison in LOWER because email addresses are not always cased consistently between your source system and how Power BI receives them from Azure Active Directory. That single filter on the mapping table then flows through the relationship to restrict every table connected to it.

A detail that trips up a lot of teams building this for the first time is that USERPRINCIPALNAME only returns a meaningful value once a report is published and opened through the Power BI service, or through Power BI Desktop's own Live preview of roles. It does not reflect a real signed in identity while you are just building the report locally, which is exactly why testing this properly, rather than assuming it is working, matters so much.

How RLS Affects Refresh and Report Performance

Row-level security is not free. Every filter it applies is real work the engine has to do, and the impact depends heavily on how the mapping table and its relationship are built. A clean, well indexed mapping table with a simple one to many relationship into the fact table adds a barely noticeable amount of overhead. A messy mapping table, one built from multiple merged sources with duplicate rows or an ambiguous relationship, can slow report rendering noticeably, particularly on larger fact tables.

One pattern we watch for closely is RLS layered on top of a bidirectional relationship. Bidirectional filtering already asks the engine to do more work than a single direction filter, and stacking a security filter on top of that combination can occasionally produce unexpected results, not just slower ones, where a filter propagates further than intended and either over-restricts or under-restricts what a user sees. Our general guidance is to keep the relationship that RLS depends on as a single direction wherever the model allows it, and to reserve bidirectional filtering for the rare cases that genuinely need it, well away from anything security related.

Many to many relationships raise the same concern. If your security mapping table connects to the fact table through an intermediate bridge table, rather than directly, take the time to trace that chain end to end and confirm the filter actually reaches every table you expect it to. We have seen models where a user's role technically filtered the bridge table correctly, but the relationship between the bridge and the final fact table was set to not filter in that direction, so the restriction quietly stopped one hop too early.

None of this means RLS should be avoided on large models. It means the same modeling discipline that keeps a Power BI file fast in general, clean relationships, single direction filters where possible, and a mapping table that is no larger or more complicated than it needs to be, is exactly what keeps RLS fast too. Security and performance are not in tension here. A well built model tends to deliver both.

Building an RLS Role Step by Step

The exact clicks vary a little by Power BI Desktop version, but the sequence we follow on almost every engagement looks like this.

• Confirm the model relationships are already correct before touching RLS, since RLS filters travel along existing relationships and cannot fix a broken model
• Add or confirm a security mapping table that lists each user and what they are permitted to see, and load it into the model like any other table
• Build a relationship from the mapping table to the fact table you want restricted, checking the filter direction carefully
• Open Manage Roles in Power BI Desktop and create a new role, giving it a clear, descriptive name rather than something generic like Role1
• Write the DAX filter expression for that role, applied to the mapping table for dynamic RLS, or applied directly to the fact table for static RLS
• Use View As to preview the role from inside Desktop, checking totals, visuals, and any tables that should now be restricted
• Publish the report to the Power BI service, then assign real users or security groups to the role from the dataset's security settings
• Test again after publishing, this time as an actual assigned user rather than through the Desktop preview, since the two do not always behave identically

Testing RLS Properly

This is the step we see skipped most often, usually because everything looked correct in the View As preview inside Power BI Desktop, and the team assumed publishing would simply carry that same behaviour into production. It does not always. The View As feature in Desktop simulates a role, but it is still you, the report author, looking at your own data connection, and certain connection types and certain gateway configurations behave differently once a report is actually published and a different, restricted user opens it.

The only test we fully trust is asking an actual member of a role, or using a dedicated test account assigned to that role, to open the published report themselves and confirm what they see. We specifically check three things every time. First, that the restricted user sees only what they should, nothing extra. Second, and just as important, that they are not accidentally seeing nothing at all, which happens more often than people expect when a relationship is broken or a mapping table row is missing for that user. Third, that totals and KPI cards reflect the filtered numbers correctly, since a badly built measure using functions like ALL or REMOVEFILTERS can quietly bypass RLS and show unfiltered totals even though the detail-level visuals look correctly restricted.

That third point deserves its own mention, because it is the one that catches experienced report builders off guard. Certain DAX functions are explicitly designed to ignore filters, and RLS is enforced as a filter. If a measure uses ALL on a table that RLS is meant to restrict, that measure can return a number that includes rows the current user should never see, even while every visual around it looks properly filtered. Any measure using ALL, ALLEXCEPT, or REMOVEFILTERS on an RLS protected table needs a manual review before you consider RLS finished.

Common Mistakes We See With RLS

We run an access review as part of most Power BI health checks now, specifically because the same handful of issues keep showing up across otherwise well built models.

• Assuming the View As preview in Desktop is the same as production behaviour, and skipping the real published-user test entirely
• Using ALL, ALLEXCEPT, or REMOVEFILTERS inside a measure on a table that RLS is supposed to protect, which quietly leaks unfiltered totals
• Letting the security mapping table go stale, so a person who left the company six months ago can still open the report and see live data
• Building the mapping relationship in the wrong filter direction, so the role technically exists but never actually restricts anything
• Forgetting that RLS does not apply to dataset owners, workspace admins, or anyone with build permission on the underlying dataset, all of whom can bypass RLS entirely by design
• Applying RLS only to a report's visible tables and forgetting about a hidden or supporting table that a user could still reach through Analyze in Excel or a live connection

That admin bypass point is worth sitting with for a moment, because we get asked about it often. RLS in Power BI was never designed to restrict people who have edit or admin rights on the workspace or dataset itself. It restricts viewers. If your organisation needs to prevent an admin from seeing certain data, that is a workspace permissions and governance question, not something RLS alone will solve, and it is exactly the kind of gap we walk through when we are asked to review a client's broader Power BI architecture.

How We Approach This at Versich

When our team scopes a Power BI Consulting engagement that touches sensitive data, whether that is sales commissions, patient information, or financial results, row-level security is one of the first things we design, not one of the last things we bolt on before launch. We ask who needs to see what, how often that access changes, and whether the organisation already has a clean source for that mapping, such as an HR system or an existing security group structure, before we write a single role.

For most clients we lean toward dynamic RLS from the start, even if the current headcount is small, because it is far cheaper to build it right the first time than to migrate a live model from static roles later. Where a client already has a Power BI environment with no RLS in place, or RLS that was built quickly and never properly tested, our Power BI Support Services engagements typically start with exactly the access review we described above, checking every role against a real test account before we call it secure.

We also see this come up constantly for clients running Power BI on top of NetSuite, where financial data naturally needs to be restricted by subsidiary, department, or role. Our NetSuite and Power BI Integration Services are built with that restriction in mind from the extraction layer onward, so the mapping table RLS depends on is accurate from day one rather than something we have to reverse engineer later. If a broken model is the underlying problem, it is also worth reading our piece on Power BI architecture explained, since RLS only ever works as well as the relationships underneath it.

If your team is deciding between bringing in a consultant to design the security model or a developer to build it, we would say the two go together more than people expect. We touched on this same theme in our earlier post on query folding versus calculated columns, and the honest answer is the same here. Getting the architecture decision right matters just as much as the DAX itself, and skipping either one tends to show up later as a support ticket, or worse, as a data exposure nobody noticed until it was too late.