Power BI Row-Level Security: Dynamic RLS Patterns for Multi-Tenant Enterprise Reporting

Row-level security (RLS) ensures that users only see the data they are authorized to access. In enterprise Power BI deployments serving multiple business units, clients, or geographic regions, dynamic RLS is essential for building a single report that automatically personalizes data visibility based on the authenticated user. This eliminates the need for separate reports or datasets per user group.

Static vs Dynamic RLS

Static RLS uses hardcoded filter values in role definitions. Example: A "West Region" role filters Sales[Region] = "West". This works for simple scenarios but requires creating and maintaining separate roles for every filter combination.

Dynamic RLS uses DAX functions to determine the current user and applies filters based on a security mapping table. A single role handles all users, with the security table determining what each user sees. This scales to thousands of users without creating thousands of roles.

Dynamic RLS Architecture

The dynamic RLS pattern requires three components:

1. Security Mapping Table

Create a table mapping user identities to their authorized data scope:

| UserPrincipalName | Region | Department | CustomerID | |-------------------|--------|------------|------------| | [email protected] | West | Sales | * | | [email protected] | East | Finance | * | | [email protected] | * | * | CUST-001 |

This table can be maintained in a database, SharePoint list, or Excel file. It refreshes with each dataset refresh, so access changes take effect automatically.

2. DAX Filter Expression

The RLS role definition uses USERPRINCIPALNAME() to identify the current user and filters data accordingly:

The expression looks up the current user in the security table and applies appropriate filters. For multi-column security (region AND department), combine filters with AND logic.

3. Relationships

The security table must relate to the main data model. Typically, the security table joins to dimension tables (DimRegion, DimDepartment) which then filter fact tables through existing star schema relationships.

Common Dynamic RLS Patterns

Manager Hierarchy Pattern

Managers should see data for their entire reporting chain, not just their direct team. Implement using a parent-child hierarchy in the security table:

1. Create an Employee table with EmployeeID and ManagerID columns
2. Use DAX PATH() function to generate the full management chain for each employee
3. RLS expression checks if the current user appears anywhere in the PATH for visible employees
4. Managers automatically see data for all direct and indirect reports

Multi-Tenant Pattern

SaaS applications serving multiple client organizations use tenant-based RLS:

1. Every row in every table includes a TenantID column
2. Security table maps user email domains to TenantID
3. RLS expression filters all tables by the user's TenantID
4. Each client sees only their data despite sharing the same dataset

This pattern is common in consulting firms providing Power BI reports to multiple clients from a single deployment.

Date-Based Pattern

Some scenarios require time-based data restrictions:

• Embargo periods: Financial data visible only after earnings release date
• Rolling access: Users see only the last 12 months of data
• Phased rollout: New data visible to analysts immediately but to executives only after validation

Implement by comparing the current date against a date threshold column in the security table.

Geographic Pattern

Users see data for their assigned geographic territories:

1. Security table maps users to geographic regions, countries, or postal codes
2. Multiple region assignments per user supported (one row per user-region combination)
3. Wildcard entries (Region = "*") grant access to all geographies
4. Hierarchical access: country-level assignment includes all regions within that country

Performance Considerations

Dynamic RLS adds minimal overhead when implemented correctly:

• Use dimension table filters: Filter dimension tables, not fact tables directly. The star schema relationship propagates filters efficiently
• Keep the security table small: One row per user-scope combination. For 1,000 users with 5 regions each, that is 5,000 rows maximum
• Index the security lookup: If the security table is in a database, index the UserPrincipalName column
• Avoid complex DAX in RLS: LOOKUPVALUE and PATH functions are efficient. Avoid iterating over large tables in RLS expressions
• Test with realistic user counts: RLS performance can degrade with thousands of concurrent users on undersized capacity

Testing RLS

Thorough testing is critical before production deployment:

1. Desktop testing: Use "View as Role" in Power BI Desktop to simulate different users
2. Service testing: Workspace admins can use "Test as" to verify RLS in the browser
3. Automated testing: Query the XMLA endpoint as different service principals to verify data isolation
4. Edge cases: Test users with no matching security records (should see no data), users with multiple assignments, and admin users who should see everything

Object-Level Security (OLS)

Beyond row filtering, OLS hides entire tables or columns from specific users. Use OLS to:

• Hide salary columns from non-HR users
• Hide cost data from external partners who should only see revenue
• Hide PII columns from analysts who need aggregated data but not individual records

OLS is configured through Tabular Editor and requires Premium or PPU licensing.

Related Resources

• Row-Level Security Guide
• Power BI Governance Framework
• Fabric Security Model
• Power BI Consulting Services