Power BI row-level security roles: static and dynamic RLS

Row-level security (RLS) restricts which rows each user can see. Roles and their DAX filter rules are defined in Power BI Desktop (or when editing the data model in the service) and published with the model; members are assigned later in the service.

Defining a role

1. On the Modeling tab, select Manage roles, then New, and name the role (a role name can’t contain a comma).
2. Select the table to filter and build the rule with the default editor, or select Switch to DAX editor to write the expression.
3. Save. Each rule is a DAX expression evaluated per row; only rows that return TRUE are visible.

Dynamic rules using USERNAME() or USERPRINCIPALNAME() can only be written in the DAX editor. In the DAX editor, arguments are separated by commas even in locales that normally use semicolons.

Static and dynamic rules

A static rule hard-codes a value, so each audience needs its own role. A dynamic rule uses the signed-in identity, so one role serves everyone; the usual pattern stores each user’s sign-in name in a table related to the data and filters that table. Each line below is the rule for a different role:

-- Static rule on the Region table (one role per region)
[Region] = "West"

-- Dynamic rule on a user-mapping table (one role for everyone)
[UserEmail] = USERPRINCIPALNAME()

When the rule is on a hidden user-mapping table (for example a salesperson table with one row per user and their region), the filter propagates through relationships to the fact tables. Figure 14.1 traces the filter from the signed-in user to the fact table.

Because USERNAME() returns a different format in Desktop, USERPRINCIPALNAME() is the more consistent choice. For guests (Microsoft Entra B2B users), the value can appear as an email address or in a tenant-resolved form containing EXT, so the mapping table must store exactly what USERPRINCIPALNAME() returns.

Write rules that fail safe

Rules for unexpected values should return no rows rather than all rows. A rule like the following, based on a Microsoft Learn example, returns everything for any value other than the one tested, so a typo exposes all data:

IF(
    USERNAME() = "Worker",
    [Type] = "Internal",
    TRUE()
)

Testing each expected value and ending with FALSE() closes that gap:

IF(
    USERNAME() = "Worker",
    [Type] = "Internal",
    IF( USERNAME() = "Manager", TRUE(), FALSE() )
)

How filters propagate

• RLS filters propagate only through active relationships, never through inactive ones, even if a measure uses USERELATIONSHIP. With RLS enabled, USERELATIONSHIP in measures can even cause errors, so role-playing dimensions are best implemented as duplicated tables with active relationships when RLS is used.
• By default RLS filters propagate in one direction, even on bi-directional relationships. Selecting Apply security filter in both directions on a relationship extends them, but only one bi-directional relationship per table can have it and it can hurt performance.
• Filtering dimension tables is usually more efficient than filtering fact tables.
• When a user is in several roles, the filters are additive: they see the union of rows allowed by each role. A role with FALSE() on a table doesn’t block rows another role allows.

Common trap: Using USERNAME() and testing the mapping table in Desktop with DOMAINvalues - in the service USERNAME() returns the UPN, so the mapping must hold UPNs; USERPRINCIPALNAME() returns the UPN in both places.

Common trap: Adding a user to a restrictive role to “deny” rows they get from another role - RLS roles combine as a union, so membership of a broader role always wins.

Common trap: Relying on an inactive relationship (activated with USERELATIONSHIP) to carry an RLS filter to a fact table - RLS filters never propagate through inactive relationships.

Get the whole book

This note is one section of Ultra Transcenders PL-300: Microsoft Power BI Data Analyst, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · PL-300 terms in the glossary · All PL-300 study notes