Back to All Engineering Notes
Semantic Models•11 min read•2026-10-11

Row-Level Security in Power BI: Static, Dynamic (USERPRINCIPALNAME) and Manager Hierarchies

How to set up RLS that scales, the dynamic USERPRINCIPALNAME pattern, manager-sees-team hierarchies, why page-level security is not security, and how to test it properly.

Core Engineering Takeaways

  • •RLS only restricts users with the Viewer role. Workspace Admins, Members and Contributors see all data.
  • •Dynamic RLS uses one role and a security table filtered by USERPRINCIPALNAME(), instead of one role per region or department.
  • •Manager hierarchies are handled with PATH and PATHCONTAINS on an employee table.
  • •Hiding report pages is not security. Use RLS for rows and object-level security (OLS) for columns and tables.

1. How row-level security works

Row-level security (RLS) adds a DAX filter to a table for members of a role. When a user who belongs to that role queries the model, the filter is applied, and through relationships it filters every related table too.

Three facts that shape every RLS design:

  • RLS only applies to Viewers. Workspace Admins, Members and Contributors can edit the model, so RLS does not restrict them. Give report consumers access through an app or the Viewer role.
  • RLS filters rows, not columns. If a user can see a row, they can see every column in it. To hide a column or a whole table, use object-level security (OLS).
  • Roles are defined in the model, members are assigned in the service. You write the DAX filter in Power BI Desktop (or Tabular Editor), publish, then add users or groups to the role in the semantic model's security settings.

2. Static RLS — and why it stops scaling

Static RLS hard-codes the filter in each role: a Europe role with [Region] = "Europe", a US role with [Region] = "US", and so on.

It is quick to set up and fine for three or four fixed groups. It becomes a problem when:

  • every new region, department or customer needs a new role and a model change;
  • people need access to several regions, and you start managing overlapping role memberships;
  • the access rules live in the model instead of in data someone in the business can maintain.

Once you have more than a handful of roles, switch to dynamic RLS.

3. Dynamic RLS with USERPRINCIPALNAME

Dynamic RLS uses one role and a security table that maps each user to what they can see. The role filter looks up the signed-in user with USERPRINCIPALNAME(), which in the Power BI service returns the user's sign-in name (usually their email address).

The model

  • A Security table with columns UserEmail and RegionKey — one row per user per region they can see.
  • A relationship from Security[RegionKey] to Region[RegionKey], and Region filtering the fact table as usual.

Apply the role filter on the Security table:

Filter the security table to the signed-in user; relationships carry the filter to Region and the facts.
-- Role: Dynamic Region Access, table: Security
[UserEmail] = USERPRINCIPALNAME()

4. Making dynamic RLS reliable

The filter is one line, but the details around it decide whether it works:

  • Relationship direction. The security table filters the dimension, which filters the fact. If Security is related to Region many-to-one in the wrong direction, enable Apply security filter in both directions on that one relationship only — not across the model.
  • Use USERPRINCIPALNAME, not USERNAME. In Power BI Desktop, USERNAME() returns DOMAIN\user; in the service, both return the UPN. Using USERPRINCIPALNAME() everywhere avoids rules that work in one and fail in the other.
  • Match case and format. Store emails in lower case and compare with LOWER(USERPRINCIPALNAME()) if your source is inconsistent.
  • Guest (B2B) users. For external users, USERPRINCIPALNAME() may return a guest UPN in the user_partner.com#EXT#@yourtenant.onmicrosoft.com format rather than their email. Store the value it actually returns, and add guests to the role directly rather than through security groups.
  • Service principals cannot be role members. In app-owns-data embedding, pass an effective identity in the embed token instead.
  • An "all access" rule. Instead of a second role for administrators, add a flag to the security table, or add those users to a separate role with no filter.

5. Manager hierarchies: managers see their whole team

A common requirement: each salesperson sees their own sales, managers see their team's, directors see everything below them. Use an employee table with EmployeeID, ManagerID and Email, and DAX's parent-child functions.

First, add a calculated column with the path from the top of the hierarchy down to each employee. Then filter the employee table to rows whose path contains the signed-in user's ID:

Each user sees themselves and everyone who reports to them, at any depth.
-- Calculated column on Employee
Path = PATH ( Employee[EmployeeID], Employee[ManagerID] )

-- Role filter on Employee
VAR CurrentEmployee =
    LOOKUPVALUE ( Employee[EmployeeID], Employee[Email], USERPRINCIPALNAME () )
RETURN
    PATHCONTAINS ( Employee[Path], CurrentEmployee )

6. Page-level security does not exist — use RLS and OLS

Searches for page level security in Power BI usually come from wanting some users not to see a page. Power BI has no page-level security. Hiding a page, or building navigation that shows different buttons to different people, only changes what is convenient to click — the data is still in the model and reachable through other visuals, Analyze in Excel, or a new report on the same model.

The secure options are:

  • RLS when the restriction is about rows (which customers, regions, departments).
  • Object-level security (OLS) when a group must not see a column or table at all, such as salary. OLS is typically defined with Tabular Editor.
  • Separate reports or apps for audiences that need a genuinely different experience, built on the same secured model.
If a page shows sensitive data, assume anyone who can see the report can see that data — unless RLS or OLS removes it from their queries.

7. Testing RLS properly

  • In Power BI Desktop, use Modeling → View as, choose the role and enter another user's email in Other user to simulate their USERPRINCIPALNAME().
  • In the service, use Test as role on the semantic model's security page. It has gaps: it does not work for DirectQuery models with single sign-on, paginated reports or dashboards, and Q&A and Copilot results cannot be validated there. For those, sign in as a real Viewer-role test user.
  • Check the totals, not only the rows. A card showing a grand total is the fastest way to spot a filter that is not reaching the fact table.
  • Test someone with no row in the security table. They should see nothing, not everything.

Microsoft's RLS documentation lists the remaining limitations.

Power BI Diagnostic Audit

Want a second pair of eyes on your security model?

Check relationships and model structure in the browser, or have me review your RLS design and test plan.

Get the next Engineering Note

Practical DAX, semantic model and Fabric notes. 1–2 a month, unsubscribe any time.

1–2 useful notes a month. Unsubscribe at any time.

Piyush JainPower BI & Fabric Engineer
#Row-Level Security#RLS#DAX#Security#Semantic Models