Row-Level Security in Power BI: Multi-Tenant Data Access

Row-level security (RLS) is the mechanism that ensures each user sees only the data they are authorized to access. In a multi-tenant or multi-department environment, RLS is not optional --- it is the difference between a governed analytics platform and a data breach waiting to happen. Yet Microsoft's own usage telemetry suggests that fewer than 30 percent of organizations with Power BI Premium have implemented RLS on their production datasets.

The reason is not that RLS is conceptually difficult. The reason is that the implementation details are nuanced, the testing process is manual, and the interaction between RLS and other Power BI features (DirectQuery, composite models, embedding, aggregations) creates edge cases that catch teams off guard. This guide covers every aspect of RLS implementation, from the simplest static role to complex dynamic security with Azure Active Directory integration.

---

Key Takeaways

• Row-level security in Power BI filters data at the model level using DAX expressions, ensuring users cannot bypass security by modifying reports or visuals
• Static RLS hardcodes filter values (suitable for small, stable user groups), while dynamic RLS uses DAX functions like USERNAME() and USERPRINCIPALNAME() to filter dynamically based on the logged-in user
• RLS only works in Import and DirectQuery modes --- it does not apply to live connections to Analysis Services (which have their own RLS)
• Object-level security (OLS) hides entire tables or columns, complementing RLS for scenarios where users should not even know certain data exists
• Testing RLS requires the "View as" feature in Power BI Desktop and Service --- never assume RLS works without explicit testing for each role
• RLS in embedded scenarios (Power BI Embedded) requires passing the effective identity in the embed token, which is a common source of implementation errors
• Performance impact of RLS is typically less than 5 percent for well-designed models, but poorly written DAX filters can degrade performance by 50 percent or more

---

Understanding Row-Level Security

What RLS Does

RLS applies DAX filter expressions to one or more tables in a Power BI data model. When a user opens a report, Power BI evaluates the RLS rules and silently filters out rows the user is not authorized to see. The user experiences a normal report --- they simply cannot see data outside their scope.

Critically, RLS operates at the data model layer, not the report layer. This means:

• Users cannot bypass RLS by creating their own reports on the same dataset
• RLS filters propagate through relationships (a filter on Dim_Region automatically filters Fact_Sales through the relationship)
• DAX measures respect RLS context (CALCULATE, SUMX, and other functions operate on the filtered subset)
• Export to Excel, CSV, or PowerPoint only exports the data the user is authorized to see

RLS vs Other Security Mechanisms

RLS is the only mechanism that controls which specific rows of data a user can access. The other mechanisms control access at the workspace, report, or object level.

---

Static Row-Level Security

Static RLS assigns users to roles with hardcoded filter values. This is the simplest implementation and works well for scenarios with a small number of fixed regions, departments, or business units.

Creating a Static Role

In Power BI Desktop:

1. Go to Modeling, then Manage Roles
2. Click Create to add a new role
3. Name the role (e.g., "North America Sales")
4. Select the table to filter (e.g., Dim_Region)
5. Write the DAX filter expression:

[Region] = "North America"

This expression means: when a user is assigned the "North America Sales" role, every table related to Dim_Region will only show rows where the region is North America. A user viewing a sales report will see only North American sales. A user viewing an HR dashboard (if it connects through a regional dimension) will see only North American employees.

Multiple Roles

You can create multiple roles with different filters:

• EMEA Sales: [Region] = "EMEA"
• APAC Sales: [Region] = "APAC"
• Global Executive: No filter (sees all data)

A user can be assigned to multiple roles. When assigned to multiple roles, the filters combine with OR logic --- the user sees the union of all roles' data. For example, a user assigned to both "North America Sales" and "EMEA Sales" sees data from both regions.

Limitations of Static RLS

Static RLS becomes unmanageable when:

• You have more than 10-15 distinct filter values (creating and maintaining 15+ roles is tedious)
• User-to-role assignments change frequently (each change requires a Power BI admin)
• The filter logic is more complex than simple equality (e.g., managers should see their team's data plus their own)
• You have hundreds of users across dozens of business units

For these scenarios, dynamic RLS is the solution.

---

Dynamic Row-Level Security

Dynamic RLS uses DAX functions that evaluate at runtime to determine the logged-in user and apply appropriate filters. The two key functions are:

• USERNAME() — Returns the domain\username or UPN of the current user
• USERPRINCIPALNAME() — Returns the email/UPN of the current user (recommended for cloud deployments)

Setting Up Dynamic RLS

Step 1: Create a security mapping table

This table maps users to their authorized data scope. It can be stored in the data source (database), a SharePoint list, or an Excel file:

Import this table into your Power BI model as SecurityMapping.

Step 2: Create the RLS role

Create a single role (e.g., "DynamicSecurity") with a DAX filter on the security mapping table:

[UserEmail] = USERPRINCIPALNAME()
    || [UserEmail] = "ALL"

Step 3: Create relationships

Establish relationships from SecurityMapping to your dimension tables:

• SecurityMapping[Region] to Dim_Region[Region]
• SecurityMapping[Department] to Dim_Department[Department]
• SecurityMapping[CompanyId] to Dim_Company[CompanyId]

These relationships must be one-to-many from the dimension to the security mapping table, or you can use a bidirectional cross-filter. However, bidirectional filters have performance implications --- a better approach uses CROSSFILTER or TREATAS in the DAX expression.

Step 4: Alternative without relationships (TREATAS approach)

Instead of creating relationships from the security mapping table, you can use TREATAS in the RLS expression on the fact table directly:

VAR CurrentUser = USERPRINCIPALNAME()
VAR UserRegions =
    CALCULATETABLE(
        VALUES(SecurityMapping[Region]),
        SecurityMapping[UserEmail] = CurrentUser
            || SecurityMapping[UserEmail] = "ALL"
    )
RETURN
    [Region] IN UserRegions

This approach avoids the complexity of additional relationships and keeps the security logic self-contained.

Dynamic RLS with Manager Hierarchy

A common requirement is that managers see data for their entire reporting chain. This requires a parent-child hierarchy in the employee or user table.

Approach 1: PATH function

If your user table has a ManagerId column, use DAX's PATH function:

UserPath = PATH(Users[UserId], Users[ManagerId])

Then in the RLS expression:

VAR CurrentUserId =
    LOOKUPVALUE(Users[UserId], Users[Email], USERPRINCIPALNAME())
RETURN
    PATHCONTAINS([UserPath], CurrentUserId)

This expression returns TRUE for the current user's own data and all data belonging to their direct and indirect reports.

Approach 2: Flattened security table

Pre-compute the hierarchy in your ETL process and create a flat security mapping where each manager is listed with all their reports' data scopes. This is more performant at query time because it avoids the overhead of PATH evaluation.

---

Object-Level Security (OLS)

Object-level security hides entire tables or columns from users. Unlike RLS, which filters rows, OLS makes tables or columns completely invisible --- they do not appear in the field list, and any visual referencing a hidden field shows an error.

When to Use OLS

• Hiding salary columns in HR datasets from non-HR users
• Hiding cost-related tables from sales teams who should only see revenue
• Hiding customer PII (email, phone, address) from analysts who only need aggregated data
• Hiding strategic pricing columns from general users

Configuring OLS

OLS is configured through Tabular Editor (an external tool) or XMLA endpoints, not through the Power BI Desktop UI.

In Tabular Editor:

1. Open the model via the external tools ribbon
2. Navigate to the table or column you want to restrict
3. In the Properties pane, find Table Permissions or Column Permissions under each role
4. Set the permission to "None" (default is "Read")

For example, to hide the Salary column in the Employees table from the "Sales" role:

• Role: Sales
• Table: Employees
• Column: Salary
• Permission: None

Users assigned the Sales role will not see the Salary column in the field list and cannot reference it in DAX calculations.

OLS Limitations

• OLS requires Power BI Premium or Pro with XMLA endpoints enabled
• OLS cannot be configured in Power BI Desktop's native UI
• OLS is metadata-level only --- it does not filter rows
• If a measure references a hidden column, the measure itself will error for restricted users

---

RLS with DirectQuery

RLS works with DirectQuery, but the behavior is different from Import mode in important ways.

How It Works

In DirectQuery mode, Power BI translates the RLS DAX filter into a SQL WHERE clause and sends it to the data source. The data source performs the filtering, and only authorized rows are returned.

Single Sign-On (SSO) Pass-Through

When using DirectQuery with SSO to a database like Azure SQL or Azure Synapse, Power BI passes the user's identity to the database. If the database has its own row-level security (e.g., SQL Server's CREATE SECURITY POLICY), that security applies in addition to Power BI's RLS.

Important: If you enable SSO pass-through, Power BI's RLS is bypassed because the database handles security. You must choose one or the other:

• Power BI RLS (defined in DAX, managed in Power BI)
• Database-level RLS (defined in SQL, managed in the database)
• Both (Power BI RLS AND database RLS apply --- the user sees the intersection)

Performance Considerations

RLS filters in DirectQuery add WHERE clauses to every query. If the filter columns are not indexed in the database, performance can degrade significantly. Ensure that:

• RLS filter columns have database indexes
• The DAX filter expression is simple enough to translate to efficient SQL
• You test query performance with the "Performance Analyzer" in Power BI Desktop

---

RLS in Power BI Embedded

Power BI Embedded (embedding reports in custom applications) has unique RLS requirements because the end users may not have Power BI or Azure AD accounts.

App Owns Data Scenario

In the "App Owns Data" embedding pattern, a service principal or master account authenticates to Power BI, and the application passes the user's identity in the embed token.

Generating an embed token with RLS:

When calling the Power BI REST API to generate an embed token, include the identities parameter:

{
  "datasets": [
    {
      "id": "dataset-guid-here"
    }
  ],
  "reports": [
    {
      "id": "report-guid-here"
    }
  ],
  "identities": [
    {
      "username": "[email protected]",
      "roles": ["DynamicSecurity"],
      "datasets": ["dataset-guid-here"]
    }
  ]
}

The username value is what USERPRINCIPALNAME() returns in the DAX expression. The roles array specifies which RLS roles to apply. You can pass any string as the username --- it does not need to be a real Azure AD account.

Common Embedding Mistakes

Mistake 1: Not passing effective identity. If you generate an embed token without the identities parameter, the embedded report shows all data. This is the most common RLS embedding error.

Mistake 2: Passing roles but not username. The username is required for dynamic RLS. Without it, USERPRINCIPALNAME() returns blank, and the DAX filter matches no rows --- the report appears empty.

Mistake 3: Using the service principal's identity. The service principal is a workspace admin and bypasses RLS. You must explicitly pass the end user's identity.

Mistake 4: Hardcoding roles in the embed token for dynamic RLS. If you use dynamic RLS with a single role (e.g., "DynamicSecurity"), always pass that role name. Do not create separate roles for each user --- that defeats the purpose of dynamic RLS.

---

Testing Row-Level Security

View As Role (Power BI Desktop)

In Power BI Desktop, go to Modeling, then View As:

1. Select the role(s) to test
2. Optionally enter a username to test dynamic RLS (this simulates the USERPRINCIPALNAME() value)
3. Click OK

The report now displays data as if you were the specified user with the specified role. Verify:

• KPI cards show the correct filtered totals
• Tables only display rows within the user's scope
• Charts reflect the filtered data
• Cross-filtering between visuals respects RLS boundaries
• Drill-through pages maintain RLS context

View As (Power BI Service)

In the Power BI Service, open the dataset settings and select Security. You can test roles directly by selecting "Test as role" and entering a username.

Automated Testing Checklist

Create a test matrix with the following scenarios:

---

Performance Optimization for RLS

Measure the Impact

Before and after implementing RLS, use the Performance Analyzer in Power BI Desktop to measure query times. Open the Performance Analyzer pane, start recording, interact with the report, and compare DAX query times with and without RLS.

A well-designed RLS implementation adds less than 5 percent overhead. If you see more than 10 percent degradation, investigate the DAX filter expressions.

Optimization Techniques

Keep filter expressions simple. The ideal RLS expression is a single column comparison:

[Region] = USERPRINCIPALNAME()

Avoid complex expressions with multiple CALCULATE, FILTER, or LOOKUPVALUE calls in the RLS filter itself.

Use integer keys instead of text comparisons. Filtering on [CompanyId] = 1 is faster than [CompanyName] = "ECOSIRE Private Limited". Map user emails to integer keys in the security mapping table.

Minimize the number of tables with RLS filters. Apply RLS to dimension tables and let relationship propagation handle fact tables. Applying RLS directly to large fact tables forces Power BI to evaluate the filter on every row of the fact table.

Pre-aggregate when possible. If a user only needs summary-level data, consider creating a pre-aggregated table with the security filter applied during ETL. This reduces the volume of data Power BI needs to filter at query time.

Avoid bidirectional cross-filters. Bidirectional relationships increase query complexity and can conflict with RLS. Use unidirectional relationships (from dimension to fact) and apply RLS on the dimension side.

---

Common Pitfalls and Solutions

Pitfall 1: RLS Not Applied to Workspace Admins

Workspace admins and members with Edit permission bypass RLS. They always see all data. This is by design --- admins need full access to manage the workspace.

Solution: Use the "Viewer" role for business users who should be subject to RLS. Only grant Admin/Member/Contributor roles to the BI team.

Pitfall 2: ALL() Removing RLS Filters

The DAX ALL() function removes all filters from a table, including RLS filters. If a measure uses ALL() on an RLS-filtered table, it may expose data the user should not see.

-- DANGEROUS: This measure ignores RLS on Dim_Region
Total Global Sales =
CALCULATE(SUM(Fact_Sales[Revenue]), ALL(Dim_Region))

Solution: Use ALLSELECTED() instead of ALL() when you want to remove slicer/visual filters but preserve RLS filters:

-- SAFE: This measure preserves RLS filters
Total Sales for Context =
CALCULATE(SUM(Fact_Sales[Revenue]), ALLSELECTED(Dim_Region))

Pitfall 3: CALCULATE Overriding RLS

CALCULATE with explicit filter arguments can override RLS in certain scenarios, particularly with REMOVEFILTERS:

-- DANGEROUS: REMOVEFILTERS is equivalent to ALL
Total Revenue All Regions =
CALCULATE(SUM(Fact_Sales[Revenue]), REMOVEFILTERS(Dim_Region[Region]))

Solution: Audit all DAX measures for ALL, REMOVEFILTERS, and ALLEXCEPT usage. Ensure they do not reference RLS-filtered columns.

Pitfall 4: Composite Models and RLS

In composite models (mixing Import and DirectQuery), RLS must be defined separately for Import tables and DirectQuery tables. A single RLS role can contain filters for both, but the behavior differs:

• Import tables: RLS filter is evaluated by the Power BI engine
• DirectQuery tables: RLS filter is translated to SQL and sent to the source

If the DirectQuery source does not support the DAX function used in the RLS filter, the query will fail.

Pitfall 5: Paginated Reports Ignoring RLS

Power BI paginated reports (created in Report Builder) can bypass dataset RLS if they connect directly to the data source. To enforce RLS, paginated reports must connect through the Power BI dataset (not directly to the database) and the user must have an assigned RLS role.

---

Enterprise RLS Architecture Pattern

For large organizations, ECOSIRE recommends a standardized RLS architecture:

Security Layer Design

1. Security mapping table stored in a central database (Azure SQL or SharePoint list)
2. Single RLS role named "DynamicSecurity" using USERPRINCIPALNAME()
3. Azure AD group sync that automatically populates the security mapping table based on group membership
4. Hierarchy support using a pre-flattened parent-child table
5. Audit trail logging which users accessed which data (via Power BI activity logs and the REST API)

Governance Process

1. Data stewards maintain the security mapping table
2. Changes are reviewed and approved through a change management process
3. Monthly audits compare Power BI RLS assignments against the HR system of record
4. Quarterly penetration testing verifies RLS effectiveness

This architecture scales to thousands of users across hundreds of datasets while maintaining a single point of security administration.

---

FAQ

Does RLS work with Power BI free licenses?

No. RLS requires Power BI Pro or Premium Per User licenses for all users consuming RLS-protected reports. Free license users can only access content in a Premium capacity workspace, and even then, they need a Pro or PPU license to be assigned RLS roles. In Power BI Embedded scenarios, the end users do not need Power BI licenses --- RLS is enforced through the embed token.

Can I implement RLS based on Azure AD groups instead of individual users?

Not directly. Power BI's RLS evaluates DAX expressions against USERPRINCIPALNAME(), which returns the individual user's email. However, you can create a security mapping table that maps Azure AD groups to data scopes and populate it using Microsoft Graph API or Azure AD group membership sync. The DAX expression still filters by the user's email, but the security mapping table provides the group-to-data mapping.

What happens if a user is not assigned to any RLS role?

If RLS is defined on a dataset and a user is not assigned to any role, the user sees no data. The report loads but all visuals show blank or zero. This is the secure default --- Power BI assumes no access unless explicitly granted. However, workspace admins and members with Edit permission bypass RLS and always see all data regardless of role assignments.

Can RLS filter data in real-time dashboards?

Yes. RLS works with both Import and DirectQuery modes. In DirectQuery mode, the RLS filter is translated to a SQL WHERE clause and sent to the database with every query, so the filtering happens in real-time. In Import mode, the filtering is applied in-memory when the user opens the report. Both modes provide the same security guarantee --- the user only sees authorized data.

How do I audit who accessed what data through RLS?

Power BI provides activity logs through the Microsoft 365 admin center and the Power BI REST API. These logs record report views, dataset refreshes, and export operations, including the user's identity. However, the logs do not record which specific rows a user viewed. For detailed data access auditing, enable database-level auditing (e.g., PostgreSQL pgaudit or Azure SQL auditing) to log the actual queries generated by DirectQuery with RLS filters applied.