Row Level Security in Power BI: Setup, Dynamic Roles, Testing

Row level security (RLS) in Power BI limits which rows of data each person can see in a report. You define filters inside roles on the semantic model, then assign people to those roles in the Power BI service. A sales manager sees their region. A clinic lead sees their clinic. Everyone opens the same report.

This guide covers how RLS works, how to build static and dynamic roles, where it silently fails, and how to test it properly before you share anything.

What row level security does

RLS restricts data at the row level for people who consume a Power BI semantic model. Microsoft describes it as filters defined within roles. Only rows where the filter returns TRUE stay visible. Everything else is removed from the user’s view of the data.

The filter sits on the semantic model, not on the report page. That matters. A person who opens the report, builds their own visual, or connects from Excel still meets the same filter. Microsoft documents that RLS and object level security are supported for Analyze in Excel and for Export with Live Connection, and that RLS is enforced at the data model level.

Two terms you will see often:

• Role: a named set of filter rules, such as “West Region”.
• Rule: a DAX expression on one table that returns true or false for each row.

RLS controls rows. If you need to hide a whole column or table from some people, that is object level security, a separate feature. The two work together.

Static versus dynamic row level security

There are two ways to build roles. Pick based on how many groups of people you have and how often they change.

Static RLS is simple and easy to explain to an auditor. If you have three regions and they never change, it is a fine choice.

Dynamic RLS is the better fit once you have dozens of managers, territories or clients. Microsoft notes that dynamic rules use DAX functions such as USERPRINCIPALNAME() or USERNAME() together with proper relationships in the model.

How to set up a static role

This takes about ten minutes once your model is loaded.

1. Open your report in Power BI Desktop. Import your data or set up a DirectQuery connection.
2. On the Modeling tab, select Manage roles.
3. Select New, and name the role after the group it serves, for example “West Region”.
4. Choose the table to filter, such as Sales or Territory.
5. Write the filter. For example, [Region] = "West".
6. Save, then use View as on the Modeling tab to check the result.
7. Publish to the Power BI service.
8. In the service, open the semantic model, choose Security, and add people or groups to each role.

You cannot assign users to a role in Desktop. Microsoft states that assignment happens in the service. Use security groups where you can, so you add a person once in your identity system and not in every model.

Filter the dimension table, not the fact table, when you can. A filter on a small Territory table flows through relationships to the large Sales table. It is easier to read and usually faster.

How to build dynamic row level security

Dynamic RLS needs three pieces: a mapping table, a relationship, and one DAX rule.

Step 1: Create a mapping table

The table links a person to the data they may see. A simple version has two columns:

One person can have several rows. That is how you give a manager more than one region without a second role.

Step 2: Relate it to your data

Load the mapping table and relate its Region column to the Region column in your dimension table. Filter direction should run from the mapping table toward the facts. If the filter does not propagate, the role will return nothing or everything, depending on the model.

Step 3: Write the rule

Create a role, for example “Dynamic Region”, and put this rule on the mapping table:

[UserEmail] = USERPRINCIPALNAME()

USERPRINCIPALNAME() returns the signed in user’s identity. Because the mapping table filters the region table, each person sees only their rows. Assign everyone who should be restricted to this one role in the service.

One rule can handle hundreds of users. When a manager changes territory, you edit a row in the mapping table. You do not touch the model.

Handling a manager hierarchy

Some teams need a manager to see their own rows and everyone below them. The usual approach is to flatten the org chart in the mapping table, so the manager has one row per person or region they oversee. Build that table in your warehouse, not in DAX, where possible. It is easier to audit, and it keeps the rule short.

The Viewer role trap

This is the most common reason RLS appears to fail. The model is right, the DAX is right, and a person still sees every row.

The cause is workspace access. Microsoft’s documentation says RLS restricts only users with Viewer permissions. It does not apply to workspace Admin, Member or Contributor roles. Those roles can edit content and read everything.

A related rule applies to apps and embedded scenarios. For a semantic model that uses RLS, any permission higher than Build lets the user view all the data in the model.

Practical checks:

• List who holds Admin, Member or Contributor in each workspace. Assume they see everything.
• Give report consumers the Viewer role, or share through an app.
• Keep model authors in a separate development workspace, and publish to a production workspace where most people are Viewers.
• Remember that sharing a report also shares access to the semantic model. Microsoft warns that a consumer’s read access is not restricted to what is visible in the report by default. Define security on the semantic model.

Hiding a column, a table or a visual is not a control. It tidies the interface. It does not stop someone from querying the data. If a field is sensitive, secure it.

How to test RLS properly

Testing is where many teams get a false sense of safety.

In Power BI Desktop, use View as on the Modeling tab. Pick a role and, for dynamic rules, type the user name to simulate. This is good for catching DAX errors early.

In the Power BI service, you can use Test as role on the semantic model’s security page. It has a limit you need to know. For dynamic RLS, Microsoft cautions that Test as role uses your own identity. So it does not show how the report filters for another employee or a guest user.

To confirm a dynamic rule, do this:

1. Ask a real user in each role to open the report, or sign in as a test account that has only the Viewer role.
2. Compare the totals they see with the totals you expect from the source system.
3. Try the same account through Analyze in Excel and through any embedded view.
4. Record the result for your audit file.

Debug the identity string

The usual dynamic RLS bug is a mismatch between what USERPRINCIPALNAME() returns and what is in your mapping table. This is most common with guest users. A partner might appear as user@partner.com in your table but as user_partner.com#EXT#@tenant.onmicrosoft.com in Microsoft Entra ID.

A quick fix is a temporary measure:

Current User = USERPRINCIPALNAME()

Place it in a card on a draft page, then have the guest open the report. Whatever shows up is the exact string your mapping table must contain. Delete the measure before you go live.

Limits and gotchas worth knowing

• Roles filter, they do not block write access. RLS in Power BI is about what people read.
• USERELATIONSHIP. Microsoft warns that with RLS enabled, this DAX function can cause unexpected errors. Redesign measures to use model relationships instead.
• Desktop with live connections. You cannot define roles in Desktop for Analysis Services live connections. Define them in the Analysis Services model.
• Multiple roles. If a user belongs to more than one role, they see the combined set of rows. Plan for that when you design roles.
• Performance. Complex filters run on every query. Keep rules simple, filter dimensions, and test with realistic data volumes.
• Row counts leak. If a total card is built on unfiltered logic elsewhere, it can reveal more than intended. Test every page, not just one.

DirectQuery and database level security

Power BI RLS is application level security. It lives in the model. For teams on DirectQuery, there is another option: let the database enforce permissions.

With single sign on, the user’s identity passes through to the source. The database then applies its own rules, and access is managed once, centrally. SQL Server, for example, supports row level security with filter predicates that silently filter the rows available to read operations.

Which should you pick?

• Model level RLS is quicker to deliver and works with imported data. It suits most dashboards.
• Database level security suits regulated data, many tools reading the same source, or a policy that says permissions live in one place.
• Both together is common in healthcare and finance, where a second layer is worth the effort.

This choice belongs in your wider data strategy and governance plan, not in a single report.

Our perspective

Most RLS problems we see are not DAX problems. They are access problems and data problems.

The DAX for a dynamic role is one line. What breaks is everything around it. A mapping table that nobody owns. A workspace where half the team is a Contributor. A guest user whose email does not match. A report that was tested only by its author.

When we build RLS, we start with the people, not the model. We ask who needs to see what, who approves access, and who updates the mapping when someone changes jobs. If those answers are vague, the technical build will drift out of date within a quarter.

We also push for the mapping table to come from a governed source, such as an HR system or a warehouse table, and not from a spreadsheet kept by one analyst. That is a data flow problem. It is the same kind of bottleneck we diagnose in reporting generally: a good looking dashboard sitting on an unreliable feed.

Last, test as a real user, in every channel they will use. Check the app, the Excel connection and any embedded view. If you plan to embed reports for outside customers, our executive checklist for Power BI embedded analytics covers the security questions to settle first.

Getting help with RLS

If your reports hold patient, financial or customer data, RLS deserves a design review before launch. Our Power BI consulting work covers role design, dynamic security, DAX, performance tuning and training for your team. If you are moving from another tool and need to rebuild security, our Tableau to Power BI migration guide explains what to plan for. To talk through your situation, start at pnielanalytics.com and book a free assessment.

FAQ

What is row level security in Power BI?

It is a feature that filters the rows of data a person can see in a semantic model. You define the filters in roles using DAX, and the filters apply whenever that person views the data, including through Excel connections to the model.

Should I enable row level security?

Enable it when different people should see different parts of the same data, or when a report contains sensitive rows. If everyone is allowed to see everything, you do not need it. Remember that it only applies to users with the Viewer role.

How do I see row level security in Power BI?

In Power BI Desktop, open the Modeling tab and select Manage roles to see the rules. Use View as to preview a role. In the service, open the semantic model’s Security page to see who is assigned to each role.

Why does a user still see all the data?

Check their workspace role first. Admin, Member and Contributor roles bypass RLS. Then check that they are assigned to a role, and that the identity string matches your mapping table.