Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 56 ยท Service, Sharing and Security

Row-Level Security

Restrict which rows each person sees in Power BI: static and dynamic RLS roles, USERPRINCIPALNAME, testing as a role, and object-level security.

One semantic model often serves many people who should not all see the same data. A regional manager should see only her region, a sales rep only his accounts, an external partner only their own orders. Row-level security (RLS) solves this inside the model: you write a filter rule, assign people to it, and Power BI applies the filter to every visual, every export and every question, without building separate reports.

This lesson covers static roles, dynamic roles using USERPRINCIPALNAME(), how to test a role, who is and is not restricted, and object-level security (hiding whole tables or columns). Roles are defined in Power BI Desktop and assigned in the service, so the steps are descriptions and the DAX is read-only. The runnable examples are Python stand-ins that apply the same filter logic to pandas tables so you can see precisely which rows survive.

How RLS works

An RLS role is a name plus one or more table filter expressions: DAX conditions that return true or false for each row of a table. When a person assigned to the role opens a report, Power BI adds those conditions to every query. Rows for which the expression is false behave as if they did not exist: they are not in the totals, not in slicers and not in exports.

Because the filter is on a table, it propagates through relationships the way any filter does (see Relationships, Cardinality and Filter Direction). If you filter the Region dimension to "North", only the North rows of every fact table related to it remain. That is why RLS works best on a clean star schema (Star Schema: Facts and Dimensions): put the rule on a small dimension, and let relationships do the rest.

RLS in four steps

Define roles Filter rules in Desktop.
Test them View as a role.
Publish Upload the model.
Assign people Add users or groups in the service.
1 / 2

Static roles

A static role hard-codes the value. In Desktop, open Modeling > Manage roles, create a role such as North Managers, pick the Region table and enter:

DAX
[Region] = "North"

You would then create South Managers with [Region] = "South", and so on. In the service, you open the semantic model's Security page and add people (or Microsoft Entra security groups) to each role.

Static roles are easy to understand and need no extra data. They stop scaling when there are many values: fifty regions means fifty roles to maintain, and every new region needs a new role plus a republish.

Dynamic roles

A dynamic role uses the identity of the person viewing. You add a small security table to the model, mapping people to the values they may see:

UserEmail Region
[email protected] North
[email protected] South
[email protected] East

Then one role, say Regional Access, gets a filter on the security table:

DAX
[UserEmail] = USERPRINCIPALNAME()

USERPRINCIPALNAME() returns the sign-in name of the current viewer (usually the email-style account, such as [email protected]). The security table is related to the Region dimension, so the filter flows from the security table to Region, then to the facts. Asha sees North and East; Ben sees South. Adding a person or a region is now a row in a table, not a new role. A related function, USERNAME(), returns the domain-style name in some contexts, so USERPRINCIPALNAME() is the usual choice in the service.

For this to work, the relationship from the security table to the dimension needs the filter to flow in the right direction. A common design is a many-to-many pattern between users and regions, with the security table filtering the dimension (check Apply security filter in both directions where the relationship needs it, and read the documentation for your scenario because the options are subtle).

A variant puts the email directly on the dimension table, which works when each row belongs to one person:

DAX
[ManagerEmail] = USERPRINCIPALNAME()

Hierarchies such as "a manager sees their own team and everyone below" need a parent-child pattern with PATH and PATHCONTAINS in the filter. It is more advanced and best done after the basics.

Dynamic RLS compares text. If the security table says [email protected] and the sign-in name is [email protected], DAX string comparison with = is case-insensitive, but extra spaces, aliases and a different domain are not. Keep the security table clean and test with real accounts.

Testing roles

Never publish RLS untested. There are two places to test.

  • In Desktop: Modeling > View as, tick one or more roles, and optionally Other user to enter an email for dynamic roles. The report then shows exactly what that role sees. Remember to stop viewing as the role afterwards.
  • In the service: next to the role on the semantic model's Security page, choose Test as role to open a report as that role, or as a specific person.

Check totals, a card with the row count and a slicer or two, and also test a person who is in no role (they should see nothing for restricted models) and one with multiple roles (their access is the union: a person in two roles sees the rows allowed by either).

Who is restricted, and who is not

RLS applies to viewers with Viewer permission, and to people who get the content through an app or a share link. Anyone with Admin, Member or Contributor permissions in the workspace, or who has Write or Reshare on the semantic model, bypasses RLS entirely and sees all data. People often miss this: if you test with your own admin account, you will always see everything.

Further points:

  • RLS defined in Power BI applies to Import and DirectQuery models. With a live connection to Analysis Services, RLS is defined in that model, not in Power BI.
  • Security is only as good as the model. If you share the model with Build permission, people can create their own reports on it, but RLS still applies to them.
  • RLS filters rows, not columns. A restricted person still sees the full list of column names, and measures that ignore filters (for example ALL) can reveal grand totals. Be careful with measures such as "share of total", which would use the full total or the restricted total depending on how they are written.
  • A table with RLS can slow down queries because of the extra filter. Keep expressions simple, and index the columns used in DirectQuery.

Object-level security

Object-level security (OLS) hides entire tables or columns (and measures through the table) from a role: a person in the role does not see the object in the field list, and a report that uses it shows an error for them. It is typically used to hide salary columns or internal margin tables.

OLS is not available in the Desktop interface. You configure it with an external tool such as Tabular Editor (set a table's or column's Object Level Security to None or Read for each role), then publish. It works with RLS and does not replace it. A few cautions: the visuals that use a hidden column break for restricted people, so build a separate report or a version of the page for them; and measures that use a hidden column do not work for the role.

DAX
-- Not DAX you type in Desktop: OLS is set per table or column in an external tool.
-- Role: "Sales Staff"  ->  Table 'Salary'[Amount]  ->  Object Level Security: None

Stand-in: static versus dynamic filtering

Real RLS is applied by Power BI's engine. The next two examples reproduce the filtering with pandas so you can see what each role would keep. The first is static: one role per region.

Python Stand-in: static roles

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second example is dynamic: one rule, UserEmail = USERPRINCIPALNAME(), applied to a security table, whose result then filters the facts through the relationship.

Python Stand-in: dynamic roles with a security table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that [email protected] appears in no row of the security table, so she sees nothing at all. That is the safe default: no mapping, no data. Also notice that the upper-case address matches because the stand-in compares lower-case text, like DAX's case-insensitive = on text.

Measures and RLS together

A measure always runs after RLS filters, so SUM(Sales[Amount]) for Ben gives Ben's total. The surprising cases are measures that remove filters. Compare these two:

DAX
Regional Total = SUM ( Sales[Amount] )

Share of Total =
DIVIDE ( [Regional Total], CALCULATE ( [Regional Total], ALL ( Region ) ) )

ALL(Region) removes filters coming from slicers and visuals, but not the RLS filter, which is applied to the table before the measure runs. So for Ben, "Share of Total" is Ben's share of the rows Ben may see, not a share of the company. That is usually what you want, but explain it on the report: people may compare two regional managers' screens and be confused when the denominators differ.

A security checklist

  • Put the rules on dimension tables and keep the model a clean star schema.
  • Prefer dynamic roles with a security table once you have more than a handful of values.
  • Assign groups, not individuals, in the service.
  • Test with a non-admin account; admins and members bypass RLS.
  • Test people in no role and in several roles.
  • Treat RLS as one layer: also control who can open the workspace, the app and the model (see Governance, Deployment Pipelines and Best Practices).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main advantage of a dynamic RLS role over several static roles?

    A dynamic role filters a security table by USERPRINCIPALNAME(), so access is data in a table. Static roles need a new role and a republish for each new value.
  2. 2. You test your RLS with your own account, and you see all the data. You are an Admin of the workspace. What does this mean?

    RLS applies to viewers. People with edit-level workspace roles (and Write permission on the model) see everything, so you must test as a role or with a non-admin account.
  3. 3. What does USERPRINCIPALNAME() return in a role filter?

    It returns the current viewer's user principal name, such as [email protected], which a dynamic role compares to a column in a security table.
  4. 4. A person is a member of two RLS roles. What do they see?

    Role memberships add up: the person sees every row that at least one of their roles permits.
  5. 5. What does object-level security add that RLS does not?

    OLS hides objects such as a salary column from selected roles. It is configured with an external tool like Tabular Editor and complements, not replaces, RLS.

Summary

  • RLS attaches DAX filter expressions to roles; the filters apply to every visual, export and question, and flow through relationships.
  • Static roles hard-code a value; dynamic roles compare a security table to USERPRINCIPALNAME() and scale to many people.
  • Test with View as in Desktop and Test as role in the service, and include people in no role and in several roles (their access is the union).
  • Workspace Admins, Members and Contributors, and people with Write permission on the model, bypass RLS.
  • RLS filters rows only; object-level security (set in an external tool) hides tables or columns.
  • The Python examples reproduce the filtering as stand-ins; the real roles are set in Desktop and assigned in the service.

Next, see how teams organise all of this at scale in Governance, Deployment Pipelines and Best Practices.