Power BI Row-Level Security (RLS): The Complete Guide

RLS Implementation Comparison

Use this table to determine which RLS approach fits your scenario. For most enterprise deployments, dynamic RLS is the correct choice.

1. What Is Row-Level Security in Power BI?

Row-Level Security (RLS) is a Power BI feature that controls which rows of data a user can see in a report or dashboard. Rather than creating separate reports for each user group, you build a single report and define security roles that filter data automatically based on who is viewing it. The filtering happens at the data engine level, meaning users cannot bypass it through visual interactions, drillthrough actions, exporting to Excel, or the Analyze in Excel feature.

Under the hood, RLS works through filter context injection. When you define a role with a DAX expression like [Region] = "West", Power BI injects that filter into the query context before any visual-level filters are applied. This means the filter propagates through all relationships in the model, automatically restricting related tables. If you filter the Geography table to "West," then the Sales, Orders, and Customers tables all automatically filter down to West region records, following the relationship chain.

Why Enterprises Need RLS

For organizations in compliance-regulated industries, RLS is not optional — it is a foundational security control. Here is why it matters:

• •HIPAA compliance — Healthcare organizations must restrict access to Protected Health Information (PHI). RLS ensures a billing analyst sees only their assigned patient cohort, not the entire hospital database.
• •SOC 2 audit controls — SOC 2 Type II audits require evidence of access controls. RLS provides a declarative, auditable security layer that maps directly to access control requirements.
• •Multi-tenant SaaS applications — ISVs embedding Power BI must guarantee tenant isolation. RLS combined with EffectiveIdentity in the Embed API ensures one customer never sees another's data.
• •Regional and departmental access — Sales reps should see their territory. Managers should see their team. Executives should see everything. One report handles all three with dynamic RLS.
• •GDPR data minimization — Article 5(1)(c) of GDPR requires that personal data exposure be limited to what is necessary. RLS enforces the principle of least privilege at the data layer.

Our Power BI architecture team designs RLS as part of every enterprise deployment blueprint. The security model is defined during the architecture phase, not retrofitted after reports are built. Retrofitting RLS onto an existing model often requires restructuring relationships and adding security tables, which is significantly more expensive than designing it in from the start.

2. Static RLS: Step-by-Step Implementation

Static RLS is the simplest form of row-level security. You create roles with hardcoded DAX filter expressions that restrict data to a fixed set of values. Static RLS is appropriate when you have a small number of well-defined access levels that rarely change.

Step 1: Create a Role in Power BI Desktop

Open your Power BI Desktop file. Navigate to Modeling > Manage Roles. Click New to create a role. Give it a descriptive name like West Region. Select the table you want to filter (e.g., Geography), then enter the DAX filter expression.

Step 2: Write the DAX Filter Expression

The DAX expression must evaluate to TRUE or FALSE for each row. Rows where the expression returns TRUE are visible to users in that role. Rows where it returns FALSE are hidden.

// Role: "West Region"
// Table: Geography
[Region] = "West"

// Role: "Finance Department"
// Table: Department
[DepartmentName] = "Finance"

// Role: "Confidential Access"
// Table: Documents
[SecurityLevel] <= 3

Step 3: Publish and Assign Users in the Power BI Service

Publish your report to the Power BI Service. Navigate to the dataset settings, click Security, select the role you created, and add Azure AD users or security groups. Users who are not assigned to any role will see no data at all (unless they are a workspace Admin, Member, or Contributor, who bypass RLS for that workspace).

Step 4: Test with "View As"

In Power BI Desktop, go to Modeling > View as, select the role, and verify the report shows only the filtered data. In the Service, use Test as role from the dataset security settings. Always test with at least one user from each role.

3. Dynamic RLS with USERPRINCIPALNAME()

Dynamic RLS is the enterprise-grade approach. Instead of hardcoding values in each role, you use the USERPRINCIPALNAME()function to capture the logged-in user's Azure AD email address, then filter a security mapping table that controls which data each user can access. You define a single role, and access is managed entirely through data.

Step 1: Build a Security Mapping Table

Create a table in your data source (SQL Server, Excel, SharePoint list, or Dataflow) that maps user email addresses to their authorized data scope:

Notice Alice has two rows — she can see both West and Southwest data. This is the power of dynamic RLS: you grant multi-region access simply by adding rows to a table.

Step 2: Create a Relationship

In your Power BI model, create a relationship from SecurityTable[Region] to Geography[Region] (or whatever your main dimension table is). This relationship allows the RLS filter on the security table to propagate through the model and restrict the fact table data.

Step 3: Define the Dynamic RLS Role

Go to Modeling > Manage Roles, create a single role (e.g., DataAccess), select the SecurityTable, and enter:

// Role: "DataAccess"
// Table: SecurityTable
[UserEmail] = USERPRINCIPALNAME()

When Alice logs in, USERPRINCIPALNAME() returns [email protected]. The filter keeps only her rows in the security table (West, Southwest), and those values propagate through the relationship to filter all connected tables.

Step 4: Sales Rep Territory Example

Here is a complete real-world example. A sales organization wants each rep to see only their assigned accounts:

-- SQL source table
CREATE TABLE dbo.SalesRepAccess (
    RepEmail    NVARCHAR(256) NOT NULL,
    AccountID   INT NOT NULL,
    AccessLevel NVARCHAR(50) DEFAULT 'ReadOnly',
    CONSTRAINT PK_SalesRepAccess PRIMARY KEY (RepEmail, AccountID)
);

-- Sample data
INSERT INTO dbo.SalesRepAccess VALUES
('[email protected]', 1001, 'ReadOnly'),
('[email protected]', 1002, 'ReadOnly'),
('[email protected]', 1003, 'ReadOnly'),
('[email protected]', 1004, 'ReadOnly');

[RepEmail] = USERPRINCIPALNAME()

Create a relationship from SalesRepAccess[AccountID] to Accounts[AccountID]. When [email protected] opens the report, they see only accounts 1001 and 1002. Adding a new rep or changing assignments requires only a data update — no Power BI Desktop changes needed.

4. Advanced Dynamic RLS Patterns

Basic dynamic RLS covers 80% of use cases. The remaining 20% require advanced patterns for hierarchical access, many-to-many relationships, and mixed security levels. These patterns are where most organizations get stuck — and where our DAX optimization team provides the most value.

Pattern 1: Manager Hierarchy (Parent-Child with PATH)

A common requirement: managers should see their own data plus all data for their direct and indirect reports. This uses the DAX PATH() function to flatten the hierarchy.

HierarchyPath =
PATH(Employee[EmployeeID], Employee[ManagerID])

-- Result examples:
-- CEO:           "1"
-- VP Sales:      "1|5"
-- Sales Manager: "1|5|12"
-- Sales Rep:     "1|5|12|47"

CurrentUserID =
LOOKUPVALUE(
    Employee[EmployeeID],
    Employee[Email],
    USERPRINCIPALNAME()
)

PATHCONTAINS(
    Employee[HierarchyPath],
    LOOKUPVALUE(
        Employee[EmployeeID],
        Employee[Email],
        USERPRINCIPALNAME()
    )
)

When the VP of Sales logs in, PATHCONTAINSchecks if their EmployeeID (5) appears in each employee's hierarchy path. It matches all paths containing "5" — their own row and all reports beneath them. The CEO sees everyone because their ID is in every path.

Pattern 2: Many-to-Many Security (Multiple Roles per User)

When users need access to multiple non-hierarchical data scopes (e.g., a consultant working across three departments), you need a bridge table pattern:

UserSecurityBridge:
| UserEmail              | DepartmentKey |
|------------------------|---------------|
| [email protected]    | 10            |
| [email protected]    | 20            |
| [email protected]    | 35            |
| [email protected]    | 10            |

-- Relationship: UserSecurityBridge[DepartmentKey] → Department[DepartmentKey]
-- RLS on UserSecurityBridge: [UserEmail] = USERPRINCIPALNAME()

Pattern 3: Mixed Security Levels

Some users should see all data (executives), while others are restricted. Handle this by adding a "ViewAll" flag to the security table:

// RLS expression on SecurityTable
[UserEmail] = USERPRINCIPALNAME()
||
LOOKUPVALUE(
    SecurityTable[ViewAllFlag],
    SecurityTable[UserEmail],
    USERPRINCIPALNAME()
) = TRUE()

Users with ViewAllFlag = TRUE match every row in the security table, effectively seeing all data. Everyone else sees only their assigned rows.

Pattern 4: Azure AD Group-Based RLS

For large organizations, managing individual user entries in a security table becomes cumbersome. Instead, map Azure AD security groups to data access in your security table, and use a Power Automate flow or Azure Function to sync group membership. In the Power BI Service, assign the Azure AD group to the RLS role instead of individual users. This combines the scalability of dynamic RLS with the manageability of group-based access control.

5. Object-Level Security (OLS)

While RLS controls which rows a user can see, Object-Level Security (OLS) controls which tables and columns are visible. OLS is available in Power BI Premium, Premium Per User (PPU), and Microsoft Fabric capacities. It is configured through Tabular Editor, not through the Power BI Desktop interface.

When to Use OLS

• •Hide salary data from non-HR users while showing all other employee data
• •Hide a staging table that exists in the model for calculation purposes but should never be visible
• •Restrict PII columns (SSN, date of birth, medical record numbers) to authorized roles only
• •Compliance with HIPAA — hide PHI columns from operational users who need aggregate data but not individual records

Configuring OLS in Tabular Editor

1. 1. Download and install Tabular Editor (free version works for OLS).
2. 2. Connect to your Power BI dataset from the Service (or open the local .bim file).
3. 3. Navigate to the table or column you want to restrict.
4. 4. In the Roles section, find the role you want to restrict.
5. 5. Set the MetadataPermission property on the table or column to None.
6. 6. Save the model changes back to the Service.

// C# script in Tabular Editor
var role = Model.Roles["StandardUser"];
var column = Model.Tables["Employee"].Columns["Salary"];
column.ObjectLevelSecurity[role] = MetadataPermission.None;

When a StandardUser views the report, the Salary column does not appear in the field list, cannot be referenced in DAX queries, and any visual that includes it shows a security error. The column is completely invisible to that role. Combined with RLS on the same role, you achieve both row-level and column-level data restrictions in a single security framework.

6. RLS with DirectQuery & Live Connection

RLS behavior differs significantly depending on whether your dataset uses Import mode, DirectQuery, or Live Connection. Understanding these differences is critical for choosing the right data architecture.

Import Mode (VertiPaq)

In Import mode, all data is loaded into the in-memory VertiPaq engine. RLS filters are applied by the formula engine at query time. The full dataset exists in memory, and the filter expression restricts which rows the user can see. This is the most straightforward RLS implementation and offers the best performance because filter evaluation is purely in-memory.

DirectQuery

In DirectQuery mode, Power BI translates DAX queries (including RLS filters) into SQL queries sent to the source database. The RLS DAX expression becomes a WHERE clause. This has important implications:

• •SQL translation — Simple DAX expressions like [Region] = USERPRINCIPALNAME() translate cleanly to SQL. Complex expressions with LOOKUPVALUE or PATH may generate suboptimal SQL or fail to push down entirely.
• •Single sign-on (SSO) — With DirectQuery SSO enabled, the user's identity passes through to the data source. This means both Power BI RLS and any database-level security (e.g., SQL Server row-level security) are enforced.
• •Bidirectional cross-filtering risk — In DirectQuery, bidirectional relationships can expose data that RLS should hide. Apply the security filter direction setting carefully, or restrict cross-filtering to single direction.

Live Connection to SSAS

When connecting to SQL Server Analysis Services (SSAS) or Azure Analysis Services via Live Connection, RLS is defined on the SSAS model, not in Power BI. Power BI passes the EffectiveUserNameproperty to SSAS, and the Analysis Services engine enforces the roles. You cannot define additional RLS in Power BI on top of a Live Connection — the SSAS roles are the sole authority.

For Azure Analysis Services, this works seamlessly with Azure AD authentication. For on-premises SSAS, you need an On-premises Data Gateway configured with the correct delegation settings to pass through user identity.

7. RLS with Microsoft Fabric

Microsoft Fabric introduces the Direct Lake storage mode, which reads Parquet files directly from OneLake without importing data into VertiPaq or sending queries to a SQL endpoint. RLS in Direct Lake has unique behaviors that differ from both Import and DirectQuery.

Direct Lake Mode RLS

RLS roles are defined in the Fabric semantic model (formerly Power BI dataset) using the same DAX filter expressions as Import mode. When a user queries the model, the engine reads only the relevant Parquet files from OneLake and applies the RLS filter. If the DAX expression is simple enough, the filter is pushed down to the Parquet file scan, avoiding unnecessary data reads.

Direct Lake Fallback Behavior

If your RLS DAX expression is too complex for Direct Lake to evaluate natively (e.g., expressions using LOOKUPVALUE, PATHCONTAINS, or iterator functions), the engine falls back to DirectQuery mode against the Lakehouse SQL analytics endpoint. This fallback is automatic and transparent to the user, but it results in slower query performance. Monitor for fallback events in the Fabric capacity metrics app.

Layered Security in Fabric

Fabric provides multiple security layers that work independently:

• •Workspace permissions — Control who can access the workspace (Admin, Member, Contributor, Viewer roles).
• •Item-level sharing — Share individual reports or semantic models with specific users.
• •RLS in the semantic model — Filter which rows a user sees within a report.
• •OLS in the semantic model — Hide tables and columns from specific roles.
• •OneLake data access roles (preview) — File- and folder-level permissions on the Lakehouse data.

This defense-in-depth approach aligns with Zero Trust principles. Our enterprise deployment team designs Fabric security architectures that combine all five layers for maximum protection in compliance-regulated environments.

8. Testing Row-Level Security

Testing is the most critical and most frequently skipped step in RLS implementation. An untested RLS configuration can expose sensitive data to unauthorized users or, conversely, hide data that users need to do their jobs. Both outcomes are equally damaging.

Testing in Power BI Desktop

1. 1. Go to Modeling > View as.
2. 2. Check the role(s) you want to test. You can select multiple roles to test their combined effect.
3. 3. For dynamic RLS, check "Other user" and enter the email address of the user you want to simulate.
4. 4. Click OK. A yellow banner appears at the top of the report confirming the active role(s).
5. 5. Verify: check totals in card visuals, ensure slicers show only authorized values, drill through to detail pages, and export to Excel to confirm filtered results.
6. 6. Click Stop viewing to return to the normal view.

Testing in the Power BI Service

1. 1. Navigate to the dataset in the workspace.
2. 2. Click the ellipsis (…) > Security.
3. 3. Select the role and click Test as role.
4. 4. Optionally enter a specific user's email under "Now viewing as" to test their exact experience.

Common Testing Mistakes

• •Testing only one user — Always test at least three users: one with full access, one with limited access, and one with no assignment.
• •Not checking cross-visual filtering — Click on a bar chart segment and verify that other visuals filter correctly without leaking restricted data.
• •Forgetting to test export — Export to Excel from a visual and confirm the exported rows are filtered. This catches edge cases where visual-level filters mask an RLS gap.
• •Ignoring workspace role bypass — Workspace Admins, Members, and Contributors bypass RLS. Test with a user who has only Viewer permissions to validate real-world security.

9. RLS Performance Optimization

Every RLS filter expression adds overhead to every query in the report. For a report with 15 visuals, the RLS expression is evaluated 15+ times per page load. If the expression is expensive, the cumulative impact is severe.

Measure the Impact

Use DAX Studio to compare query performance with and without RLS. Connect to your dataset, capture the DAX query from Performance Analyzer, then run it with and without the DEFINE SECURITY clause. The difference in execution time is your RLS overhead.

Optimization Best Practices

• •Keep DAX expressions simple. A direct column comparison like [UserEmail] = USERPRINCIPALNAME() is evaluated by the storage engine and is extremely fast. Expressions with LOOKUPVALUE, CALCULATE, or FILTER force the formula engine to take over, which is orders of magnitude slower.
• •Use integer keys, not text. Filtering on an integer column ([UserID] = 42) is faster than filtering on a text column ([UserEmail] = "[email protected]"). If performance is critical, add a computed UserID column to your security table.
• •Avoid iterators in RLS. Never use SUMX, FILTER, COUNTROWS, or other iterating functions in an RLS expression. They force row-by-row evaluation for every query.
• •Minimize the security table size. If your security mapping table has 1 million rows, every query must scan it. Deduplicate, remove inactive users, and archive historical entries.
• •Beware of CALCULATE and RLS interaction. The ALL() function in a measure like CALCULATE(SUM(Sales[Amount]), ALL(Region)) can override RLS filters if the table is directly secured by RLS. Use ALLSELECTED() instead to respect RLS while removing only user-applied slicer filters. Consult our DAX cheat sheet for more on filter context functions.
• •Filter on the dimension, not the fact table. Apply RLS on the smallest dimension table and let the relationship propagate the filter to the fact table. Filtering directly on a fact table with 100 million rows is slower than filtering a dimension with 500 rows.

// SLOW: Uses LOOKUPVALUE inside RLS (formula engine)
LOOKUPVALUE(
    SecurityTable[Region],
    SecurityTable[UserEmail],
    USERPRINCIPALNAME()
) = Geography[Region]

// FAST: Direct column comparison (storage engine)
// Apply on SecurityTable, relationship propagates to Geography
[UserEmail] = USERPRINCIPALNAME()

10. The 10 Most Common RLS Mistakes

After implementing RLS for hundreds of enterprise clients, we see the same mistakes repeatedly. Here are the top 10, ordered by how often they occur and how much damage they cause.

11. RLS for Compliance: HIPAA, SOC 2, GDPR, FedRAMP

For organizations in regulated industries, RLS is not just a convenience feature — it is a compliance control. Here is how RLS maps to specific regulatory requirements and what auditors expect to see.

HIPAA: Protecting PHI

HIPAA's minimum necessary standard (45 CFR 164.502(b)) requires that access to Protected Health Information be limited to the minimum necessary for the user's job function. RLS directly implements this by filtering patient records based on the user's care team assignment, department, or facility. Combined with OLS to hide sensitive columns like SSN or diagnosis codes from operational users, Power BI can meet the technical safeguards requirements of the Security Rule.

• •Audit requirement: Document which roles exist, what data they restrict, and which users are assigned. Export this from the Power BI REST API for audit evidence.
• •Implementation: Use dynamic RLS with a security table sourced from your HR/credentialing system, updated automatically during data refresh.

SOC 2: Access Controls & Audit Trails

SOC 2 Trust Services Criteria CC6.1 (Logical and Physical Access Controls) requires documented, enforced access controls over information assets. RLS provides a declarative security model that auditors can review: role definitions are stored in the model metadata, user assignments are tracked in the Power BI Service activity log, and changes to roles are captured in the deployment pipeline history.

• •Audit requirement: Demonstrate that access is reviewed periodically (access certification). Use the Power BI REST API to programmatically export role memberships and compare them to your approved access list.
• •Implementation: Integrate role assignment changes into your change management process. Require approval for additions to the security table.

GDPR: Data Minimization & Purpose Limitation

Article 5(1)(c) of GDPR establishes the data minimization principle: personal data must be adequate, relevant, and limited to what is necessary. RLS ensures that EU customer data is only visible to users with a legitimate business need. Combined with OLS, you can hide PII columns (name, email, address) from users who need only aggregate analytics.

• •Right to access: RLS logs in the Power BI activity log can demonstrate who accessed which data and when, supporting Data Subject Access Requests (DSARs).
• •Implementation: Tag tables containing PII in your documentation, apply RLS + OLS, and automate access review reports.

FedRAMP: Federal Security Controls

FedRAMP (based on NIST 800-53) control AC-3 (Access Enforcement) requires automated enforcement of access authorizations. Power BI in Government Cloud (GCC/GCC High) supports RLS, and the combination of Azure AD Conditional Access + workspace permissions + RLS provides the layered access control FedRAMP requires. Document your RLS architecture in the System Security Plan (SSP) as part of the access control family.

Our team has implemented compliant Power BI environments for federal agencies and government contractors. See our enterprise deployment services for details on FedRAMP-compliant Power BI architectures.

Frequently Asked Questions