Row-Level Security (RLS): Enterprise Permission Management in Power BI

"The GM should see all sales, regional managers only their own region, field reps only their own customers." This need is met by Power BI's Row-Level Security (RLS) feature. Well-designed RLS lets you manage dozens of permission levels with a single report + single dataset; a wrong design creates a security hole and hurts performance. In this article we'll cover static and dynamic RLS, the use of USERPRINCIPALNAME and field practices in detail.

01. What Is RLS and What Does It Solve?

Row-Level Security, through filters applied to the data model, lets the same report be seen with a different set of rows by different users. Instead of a manager wearily copying 10 different reports, they build one report; roles and filters do the separation.

RLS works at two layers:

• Model layer: which role sees which rows is defined with DAX filters
• Service layer: which user is assigned to which role is managed in the Power BI Service

If these two layers aren't connected correctly, the user either sees no data or accesses data they aren't authorized for.

02. Static RLS: Simple but Doesn't Scale

In static RLS, a DAX filter is defined manually for each role. For example:

• For "Marmara Role": [Region] = "Marmara"
• For "Aegean Role": [Region] = "Aegean"
• For "Istanbul Dealer Role": [Region] = "Marmara" && [City] = "Istanbul"

This approach works for a small number of roles (2-5), but management becomes hell with 20+ roles. When a new region is added, a model update and republish are required.

03. Dynamic RLS: The Scalable, Correct Approach

In dynamic RLS, a single role is defined and the filter works based on who logs in. The USERPRINCIPALNAME() function returns the email of the logged-in user. This value is matched with a security table called "Users":

• Users table: [Email], [User Name], [Region], [Dealer Code], [Role]
• DAX filter (on the Region dimension): [Region] IN CALCULATETABLE(VALUES(Users[Region]), Users[Email] = USERPRINCIPALNAME())

When a new user is added, you don't change the model; you just add a row to the Users table.

04. Security Table Design

The heart of dynamic RLS is the security table. A well-designed security table:

• Clearly shows the user-access relationship (a user can access multiple regions)
• Can be related to the main dimension tables
• Is easy to manage changes on (sourced from Excel, SharePoint or SQL)
• Supports a role hierarchy (GM = all regions)

A typical implementation for the GM role: a "*" symbol or a blank filter field in the security table; the "if * then all is seen" logic is handled in DAX.

05. Testing and Validation Process

After applying RLS, it's dangerous to proceed on the assumption "we published, it works." Test steps:

• Preview each role with the "View as roles" feature in Power BI Desktop
• Live testing by logging into the Service with different email accounts
• Verify that the total value in the report changes according to the user
• Verify that zero rows are shown when a user applies a filter to an unauthorized dimension
• Measure the performance impact (an RLS query can be 10-30% slower than a non-RLS one)

06. The Difference from Object-Level Security (OLS)

RLS hides rows but all columns are visible. In some scenarios, column-based hiding is needed: like only HR seeing the salary column in a payroll table. Object-Level Security (OLS) is used for this need.

OLS is defined with an external tool like Tabular Editor; there's no interface for it in Power BI Desktop yet. In enterprise scenarios, RLS + OLS are used together: row-based permission + role-based hiding of sensitive columns.

07. Common Traps

Frequent mistakes in RLS implementation:

• The USERPRINCIPALNAME result not exactly matching the email in the Users table (case, spaces)
• Connecting the security table with a bidirectional relationship: performance collapses
• Publishing RLS in a Composite model (mixed DirectQuery + Import) without testing
• Forgetting Excel export: data exported to Excel respects the RLS filter, but the technical team's direct DB access to the data bypasses RLS
• Not redefining RLS after the Service publish in template reports