A regional sales director opens a dashboard and sees revenue figures from three territories that are not hers. She screenshots it, forwards it to her VP, and by the end of the day the BI team is explaining to an auditor why the security model failed. The DAX was correct. The relationships were correct. The security mapping table had gone stale six weeks earlier when a reorganization moved four salespeople to new regions and nobody updated the file.

This is the pattern behind most Power BI row-level security failures. The technical implementation is usually fine. The operational discipline around it is not. Retrofitting RLS after a model is in production typically forces relationship restructuring, new security tables, and validation across every downstream report, which is why getting it right the first time is materially cheaper than getting it wrong. With the global average cost of a data breach reaching $4.44 million in 2025 and 60% of cloud security incidents traced to misconfigurations, an RLS leak in a widely-shared semantic model is exactly the class of risk boards now ask about (Axis Intelligence, Worldmetrics).

This guide is written for mid-market BI teams: five to fifty roles, a few hundred to a couple thousand users, no dedicated data governance office. It gives you three deliverables you can act on this week: a decision framework, a canonical dynamic RLS pattern, and a test protocol that surfaces the failures auditors otherwise find first.

TL;DR

Row-level security in Power BI filters which rows of a semantic model a user can see, based on a DAX expression evaluated per user. Most implementations fail not from bad DAX but from ungoverned security mapping tables, additive role membership, and insufficient testing. Get three things right on day one and you will not have to rebuild.

Key Takeaways

• Dynamic RLS is the default beyond a handful of roles. One role plus a security mapping table beats fifty static roles every time.
• Additive role membership is the #1 silent failure. A user in two roles sees the union of their filters, not the intersection.
• Write defensive DAX that fails closed. Unknown users should return zero rows, not all rows.
• Treat the security mapping table as a governed data product, with a named owner, a source of truth, and a change-approval workflow.
• Test with a matrix, not a click. Positive cases, negative cases, and edge cases (multi-role, terminated, empty mapping) all belong in your protocol.

Why Most Power BI RLS Implementations Fail

Three failure modes account for nearly every RLS incident in production. None of them are exotic. All of them are avoidable if you know to look for them.

Additive Role Membership

When a report user is assigned to multiple RLS roles, Power BI unions the filters. Unlike SQL Server's "once denied always denied" model, RLS filters are additive, so report users see table rows that represent the union of those filters (Microsoft Learn). Teams accustomed to database security assume intersection and get union. The result is silent over-permission that only surfaces during an audit or an unhappy conversation with a stakeholder.

The Unknown User Default

Consider this DAX rule: IF(USERNAME() = "Worker", [Type] = "Internal", TRUE()). It looks reasonable. It also returns all rows for any username that is not exactly "Worker", including typos, service accounts, and B2B guests. The safer pattern tests for each expected value so an unexpected value results in the table returning no rows. Fail closed, not open. This single defensive practice prevents a category of leaks that most tutorials never mention.

Silent Failure of the Mapping Table

Dynamic RLS depends on a security mapping table that ties user identities to the dimension values they can see. When that table goes stale, RLS keeps working. It just filters against wrong data. Nothing throws an error. Nothing appears in the audit log. The user who moved regions three months ago is still seeing their old territory, and the person who took their place is seeing nothing at all.

The BabyBots RLS First-Time-Right Framework

Before writing DAX, answer five questions. The pattern falls out of the answers.

Five Questions to Choose Your RLS Pattern

• Question 1: How many distinct audiences need different data? One means skip RLS and use separate models. Two to five means static RLS can work. Six or more means dynamic.
• Question 2: Does access follow a hierarchy or a flat lookup? Hierarchy points to PATH and PATHCONTAINS on an employee dimension. Flat lookup points to a mapping table joined by USERPRINCIPALNAME.
• Question 3: Where does the truth about "who sees what" live today? HRIS means automate the sync. Entra ID groups means map groups to roles. A manual spreadsheet means you have governance debt to resolve before go-live.
• Question 4: What is the operational cadence of access changes? Daily changes require an automated pipeline. Monthly changes can survive a manual workflow with approvals.
• Question 5: What is your Fabric or Direct Lake posture? Direct Lake and Import both point to semantic-model-layer RLS. Warehouse consumed by other tools may require warehouse-level policies too.

Pattern Summary

Static RLS.

• When to use: Two to five audiences with hard-coded values that rarely change.
• Pros: Simple to build and explain.
• Cons: Role explosion at scale. Manual user assignment. Redeploy for every change.

Dynamic RLS with mapping table.

• When to use: Six or more audiences, flat access model.
• Pros: One role serves everyone. Access changes are data changes, not model changes.
• Cons: Requires a governed mapping table and a defensible refresh cadence.

Manager hierarchy RLS.

• When to use: Managers see themselves plus their reporting chain.
• Pros: Handles arbitrary depth with PATH and PATHCONTAINS.
• Cons: Depends on a clean employee-to-manager relationship in the source system.

The teams who got RLS wrong almost never wrote bad DAX. They wrote correct DAX against a mapping table that quietly went stale six months later.

The Canonical Dynamic RLS Pattern

For the majority of mid-market implementations, one pattern covers most of the ground. Build it once, understand it deeply, and reuse it.

Step 1: Build the Security Mapping Table

Create a table with at least two columns: the user's UPN (email address they sign in with) and the dimension value they are allowed to see. For regional sales, that is UserEmail and Region. For multi-territory access, add one row per user-region combination. The table joins to your Region dimension on Region, not to your fact table.

Step 2: Create a Single Dynamic Role

In Power BI Desktop, open Manage Roles and add one role called "Dynamic User Access." On the security mapping table, apply this DAX filter:

[UserEmail] = USERPRINCIPALNAME()

That is the entire filter. The mapping table now returns only rows belonging to the signed-in user. Because it joins to the Region dimension, and the Region dimension filters the fact table through an active relationship, RLS propagates automatically. Microsoft's guidance is explicit on this: enforce RLS filters on dimension tables and rely on well-designed relationships to ensure RLS filters propagate to other model tables (Microsoft Learn). Avoid LOOKUPVALUE inside RLS when a relationship achieves the same result.

Step 3: Assign Users or Entra ID Groups

In the Power BI Service, assign the role to Entra ID security groups rather than individual users when possible. This lets network administrators manage security group memberships in Microsoft Entra ID (Microsoft Learn), which is the right handoff for most mid-market teams. Just be explicit about who owns which groups.

Step 4: A Note on Direct Lake and Fabric

If you are on Microsoft Fabric, enforce RLS at the semantic model layer. Enforcing it at the warehouse layer causes Direct Lake queries to fall back to DirectQuery mode to abide by row-level security (Microsoft Learn), which negates the VertiPaq performance benefit that made Direct Lake attractive in the first place. Semantic-model RLS keeps queries fast and centralizes the security definition.

The Security Mapping Table as a Governed Data Product

This is the layer competitors gloss over and where most implementations quietly fail. Treat the mapping table with the same rigor you would apply to any operational dataset.

Governance Attributes

• Owner: A named person accountable for accuracy, typically in the BI or data platform team, not "IT" as a category.
• Source of truth: HRIS export, Entra ID group membership, or a manual list with dual approval. Document which, and never blend two sources without a reconciliation step.
• Refresh cadence: Aligned to the operational cadence of access changes. Daily refresh for high-turnover environments, weekly for stable ones. Automate the refresh; do not depend on someone remembering.
• Change-approval workflow: Every new row or deletion has a ticket, an approver, and a timestamp. This is what the auditor will ask to see.
• Audit trail: Retain historical versions. If a user reports seeing wrong data, you need to reconstruct what the mapping looked like on the date of the incident.

Testing Row-Level Security in Power BI

A single "View as Role" click is not a test. It is a smoke check. A defensible test protocol runs every time the model changes.

The RLS Test Matrix

Positive case.

• What to test: Each role sees the rows it should see.
• How to test: View as a canary user for each role. Compare row counts to expected values.
• Pass criteria: Row counts match the expected list within tolerance.

Negative case.

• What to test: Users outside a role see nothing they should not.
• How to test: View as a user who should see Region A. Confirm Regions B, C, D are absent from every visual.
• Pass criteria: Zero rows from unauthorized dimensions across all report pages.

Multi-role user.

• What to test: Additive filter behavior when a user is in two roles.
• How to test: Assign a test account to two roles. Confirm the user sees the union, and that this is intentional.
• Pass criteria: Observed access matches documented intent, not accident.

Unknown user.

• What to test: A user not in the mapping table.
• How to test: Add a UPN that does not exist in the mapping table and view as that identity.
• Pass criteria: Zero rows returned. Fails closed.

Terminated user.

• What to test: A user whose mapping row was removed.
• How to test: Delete a row, refresh, view as that user.
• Pass criteria: Zero rows returned within one refresh cycle.

Regression after model change.

• What to test: Nothing broke after a schema or relationship change.
• How to test: Re-run the full matrix.
• Pass criteria: All prior tests still pass.

Measuring Performance Impact

RLS is not free. Microsoft's guidance is to measure the performance impact of RLS filters in Power BI Desktop by using Performance Analyzer. First, determine report visual query durations when RLS isn't enforced. Then, use the View As command on the Modeling ribbon tab to enforce RLS and determine and compare query durations (Microsoft Learn). Do this before deployment, not after users complain. Note also that enabling bi-directional security filtering can negatively impact query performance in models with many relationships or large datasets (Microsoft Learn), so leave it off unless you have tested with it on.

What RLS Does Not Do

Be honest with executive stakeholders about scope. RLS filters rows, not columns or tables. To hide a column or a whole table from a role, you need Object-Level Security. RLS also only restricts data access for users with Viewer permissions on a semantic model. It does not apply to workspace Admin, Member, or Contributor roles (Microsoft Learn), which changes who you can safely give workspace access to. And RLS does not protect the underlying data source. A user with direct SQL access to the warehouse bypasses RLS entirely.

Frequently Asked Questions

When should we use static instead of dynamic RLS?

Only when you have five or fewer stable audiences that rarely change. The moment you add a sixth role, the operational cost of static assignment exceeds the setup cost of a dynamic mapping table. Beyond that, dynamic wins on every dimension: fewer roles to manage, no republish for access changes, and a single DAX rule to audit.

What happens when a user is assigned to multiple RLS roles?

Power BI unions the filters. The user sees rows that satisfy any of the assigned roles, not rows that satisfy all of them. This is the opposite of what many teams assume. If you need intersection semantics, model it in the DAX itself, not through multiple role assignments.

Who should own the security mapping table?

Name a person, not a function. The owner is accountable for accuracy, refresh, and change approvals. In most mid-market organizations this sits with the BI lead or Power BI CoE, with a documented handoff from HR for identity events. "IT owns it" is not an answer that survives an audit.

How do we prove RLS works to an auditor?

Retain four artifacts: the DAX rule definitions, the source-of-truth documentation for the mapping table, the change-approval log, and the most recent test matrix execution results. Together they demonstrate that the control is defined, sourced, governed, and validated.

How does RLS behave in Microsoft Fabric with Direct Lake?

RLS is supported for Direct Lake semantic models. However, if a DAX query falls back to DirectQuery mode due to unsupported features, RLS filters still apply but performance characteristics may change (Microsoft Learn). Enforce RLS at the semantic model layer for interactive reporting to preserve in-memory performance.

Can RLS hide specific columns or tables?

No. RLS operates on rows only. To hide columns or tables from certain roles, use Object-Level Security. Communicate this clearly to stakeholders who ask for "column-level RLS" — the feature they need has a different name.

Sources

• Row-level security (RLS) guidance in Power BI Desktop — Microsoft Learn
• Row-level security (RLS) with Power BI — Microsoft Fabric / Microsoft Learn
• Row-Level Security in Fabric Data Warehousing — Microsoft Learn
• Dynamic Row-Level Security (RLS) Implementation in Power BI (Part 1) — Microsoft Fabric Community Blog
• Data Breach Statistics 2026: Costs, Causes and Industry Data — Axis Intelligence Research (summarizing IBM Cost of a Data Breach Report 2025)
• Data Security Statistics 2026: Latest Research — Worldmetrics

Closing Perspective: RLS Is a Workflow, Not a Feature

The teams who get RLS wrong almost never write bad DAX. They write correct DAX against a mapping table that quietly goes stale six months later. The teams who get it right recognize that RLS is not a security feature you configure once. It is a governed data product you operate, connected to identity events on one side and to a repeatable test protocol on the other.

The mid-market opportunity is to embed this discipline before the model scales, not after. Automate the mapping-table refresh against Entra ID or HRIS. Run the test matrix on every model change. Log every mapping-table edit with an approver. Do those three things and RLS becomes an audit asset rather than an audit finding, and your next reorganization does not turn into your next data-exposure incident. This operational-workflow layer is exactly where BabyBots spends its time with mid-market data teams: turning fragile manual controls into durable governed capabilities, so security holds up the tenth time an org chart changes, not just the first.