Row-Level Security (RLS) in Power BI: A Complete Guide

Row-level security, RLS, lets one Power BI report show each person only the data they are allowed to see: a regional manager sees their region, a sales representative their customers. Microsoft defines it simply: RLS restricts data access for specific users of a semantic model, with filters defined within roles that restrict data at the row level (Microsoft Learn: row-level security). Getting it right needs a clear model, careful testing and an understanding of who it does and does not apply to.

The workflow

1. Define roles and rules in Power BI Desktop, with DAX filter expressions.
2. Publish the semantic model and report to the Power BI service.
3. Add members to each role in the service.
4. Validate each role with Test as role.

Roles are created in Desktop from the Modeling tab with Manage roles. Each rule is a DAX expression that returns true or false for every row of a table; only rows that return true remain visible, and the filter then flows through the model's relationships to related tables (Microsoft Learn: relationships). Users are assigned to roles in the service, never in Desktop.

Static rules

A static rule fixes the value:

[Region] = "West"

It is simple, but it needs one role per region and constant membership changes as people move. For more than a handful of values, dynamic security is easier to run.

Dynamic RLS with a user-mapping table

Dynamic RLS uses one role whose rule compares a column with the signed-in user's identity:

[Email] = USERPRINCIPALNAME ()

In the Power BI service, USERPRINCIPALNAME returns the user's sign-in name, which usually looks like an email address. The rule sits on a table that maps users to what they may see, and relationships carry the filter onward. The Chinook sample database has exactly this structure: each customer is assigned a support representative from the Employee table (Chinook on GitHub). With the rule on Employee and relationships from Employee to Customer to the invoices, each representative sees only their own customers:

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")
employee = pd.read_sql("SELECT EmployeeId, FirstName || ' ' || LastName AS Name, Email FROM Employee", con)
sales = pd.read_sql("""SELECT c.SupportRepId, i.CustomerId, il.UnitPrice * il.Quantity AS Revenue
                       FROM InvoiceLine il JOIN Invoice i ON i.InvoiceId = il.InvoiceId
                       JOIN Customer c ON c.CustomerId = i.CustomerId""", con)

def visible(user_upn):                     # the rule on Employee: [Email] = USERPRINCIPALNAME()
    reps = employee.loc[employee["Email"] == user_upn, "EmployeeId"]
    return sales[sales["SupportRepId"].isin(reps)]

for upn in employee.loc[employee["EmployeeId"].isin(sales["SupportRepId"]), "Email"]:
    rows = visible(upn)
    print(f"{upn:<28} customers {rows['CustomerId'].nunique():>3} | revenue {rows['Revenue'].sum():>8.2f}")
print(f"{'someone.else@chinookcorp.com':<28} customers {visible('someone.else@chinookcorp.com')['CustomerId'].nunique():>3} | revenue {visible('someone.else@chinookcorp.com')['Revenue'].sum():>8.2f}")
print(f"all data (workspace Admin, Member or Contributor): customers {sales['CustomerId'].nunique()} | revenue {sales['Revenue'].sum():.2f}")

jane@chinookcorp.com         customers  21 | revenue   833.04
margaret@chinookcorp.com     customers  20 | revenue   775.40
steve@chinookcorp.com        customers  18 | revenue   720.16
someone.else@chinookcorp.com customers   0 | revenue     0.00
all data (workspace Admin, Member or Contributor): customers 59 | revenue 2328.60

The three representatives' customers add up to all 59, so nothing falls through the gaps. A member of the role whose sign-in name is not in the table sees nothing at all, which is the safe failure. The mapping table must store identifiers in exactly the format USERPRINCIPALNAME returns; Microsoft warns that for external guest users it can return a different format, so test with real guest accounts.

Keep the mapping table maintained

Dynamic RLS is only as current as its mapping table. Load it from a system of record, such as the HR or CRM system, rather than a hand-edited spreadsheet, refresh it with the model, and review it when people change roles. A leaver who remains in the table keeps seeing data if they still have access; a joiner who is missing sees nothing.

Who RLS applies to

This catches many teams out. RLS restricts only users with Viewer permissions. Workspace Admins, Members and Contributors can edit the semantic model, so RLS does not apply to them and they see all data, the last line of the output above. Viewers remain filtered even if they have Build permission, for example when analysing the model in Excel. To restrict someone, give them the Viewer role or share the content through an app.

Membership

In the service, open the semantic model's Security settings and add people or groups to each role. Distribution groups, mail-enabled groups and Microsoft Entra security groups work; Microsoft 365 groups and groups created in Power BI do not. Service principals cannot be role members. A person in several roles sees the combination: roles are additive. Users with Viewer access who are not in any role generally see no data, which makes a missing assignment look like a broken report.

Testing

In Desktop, use View as to preview roles. In the service, Test as role shows a report as a role or a specific person would see it. Know its limits: for dynamic rules it evaluates USERPRINCIPALNAME with your own identity, it does not work for DirectQuery models with single sign-on, and it does not cover paginated reports. For important deployments, sign in as a real Viewer-level test user as well.

Design tips and limitations

• Filter dimensions, not facts. Put rules on small dimension or mapping tables and let single-direction relationships carry them, as in the example. The Apply security filter in both directions option exists for specific dynamic designs but can hurt performance.
• Rows, not columns. RLS cannot hide columns or measures; object-level security does that.
• Aggregations must be filtered by the same rules, or queries fall back to detail data; aggregations for large datasets covers this.
• USERELATIONSHIP can cause errors in measures on RLS-enabled models, according to Microsoft, so prefer designs that do not rely on it.
• DirectQuery can use source security: with DirectQuery, the data source applies its own security based on the user's credentials; with import, it does not, so RLS must enforce the rules.

Clean relationships are the foundation; relationships and cardinality and star schema in Power BI cover them, and publishing, sharing and workspaces covers the workspace roles that decide who RLS applies to.

Row-level security is part of the advanced Power BI concepts module of the Power BI course.

Sources

• Row-level security (RLS) with Power BI (Microsoft Learn) — Microsoft Learn
• Model relationships in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub