Power BI Row-Level Security: Static vs Dynamic

Power BI row-level security (RLS) controls which rows a person can see inside a report by applying DAX filters to named roles. Instead of building separate reports for each team or region, you publish one dataset and let RLS decide who sees what. The two patterns you will use are static RLS, where each role carries a fixed filter, and dynamic RLS, where one role compares the signed-in user against a permissions table. Getting the choice right, and testing it properly, is the difference between a trustworthy report and a quiet data leak.

How row-level security actually works

RLS is enforced at the dataset (semantic model) level, not in the visuals. You define roles in Power BI Desktop, write a DAX filter expression for each table that the role should restrict, then publish and assign Microsoft Entra users or security groups to those roles in the Power BI Service. Because the filter lives in the model, it applies everywhere the data is consumed: visuals, Q&A, exports to Excel, paginated reports, embedded views and Copilot for Power BI. A user with no role on a dataset that has RLS defined will see nothing, which is the safe default.

RLS only restricts rows, not columns or measures. If you need to hide entire columns, that is object-level security, a separate feature. And RLS does not apply to workspace members with edit rights, who can see all data, so the people building the report are not a fair test of whether the rules work.

Static RLS: simple, but it does not scale

Static RLS suits a small, stable set of categories. You create one role per value and write a literal filter.

For example, a role called UK Sales might filter the Territory table with:

[Country] = "United Kingdom"

Create a second role for the US, a third for Australia, and so on. This is quick to set up and easy to read. The drawback is maintenance: every new region or team means a new role, a new filter and a new assignment. For more than a handful of fixed groups it becomes brittle, and it is easy to forget to assign someone, which silently shows them nothing.

Dynamic RLS: one role that scales

Dynamic RLS uses the USERPRINCIPALNAME() function, which returns the email of the signed-in user, to filter against a mapping table. You build a small table that links each user's email to the values they are allowed to see, relate it to your dimension tables, and write a single filter.

A typical filter on a user-to-region bridge looks like:

[UserEmail] = USERPRINCIPALNAME()

With the relationships in place, restricting the user table cascades the filter through to sales, customers and so on. Add a new user by inserting a row in the mapping table, not by editing the model. This is the pattern almost every growing organisation should reach for. For managers who need to see their whole team, extend the mapping table with a hierarchy or use a security-group-driven approach.

Setting up RLS step by step

1. In Power BI Desktop, go to Modeling and choose Manage roles.
2. Create a role and write a DAX filter on each table you want to restrict.
3. Use View as roles to preview the report as that role before publishing.
4. Publish the dataset to a workspace in the Power BI Service.
5. In the dataset's Security settings, add users or, preferably, Entra security groups to each role.
6. Confirm the consumers have at least Viewer access to the workspace or app, with the dataset's Build permission.

Using security groups rather than individual users keeps administration in one place and means joiners and leavers are handled by your normal identity process.

Testing your roles before anyone relies on them

Testing is where most RLS problems are caught, or missed. In Desktop, the View as feature lets you impersonate a role, and for dynamic RLS you can also type a specific user's email to confirm the filter resolves. In the Service, use Test as role on the dataset to verify behaviour with live permissions. Always test with a real, restricted user account, not just your own admin login. Check totals against a known figure: if a regional user's revenue total matches the all-data total, your filter is not applying.

Common mistakes that break RLS

The most dangerous failure is the silent one: a report that looks correct to its builder but exposes too much to a consumer. That is why disciplined testing with restricted accounts matters more than the DAX itself.

Where RLS fits in a governed model

RLS is one layer of a well-governed Power BI estate, alongside certified datasets, deployment pipelines and clear workspace roles. If you are standing up security for the first time, it pays to design the mapping tables and relationships before reports proliferate. Our UK Power BI consultancy regularly retrofits RLS into existing models, and our Power BI data modelling best practices guide explains the relationship choices that make dynamic RLS both correct and fast.

If you are unsure whether your current reports leak data or perform well under security, a Trusted Numbers Review reviews your model, RLS design and refresh setup in a fixed two-week engagement, with the fee credited against any follow-on work. Book a review to get a clear, prioritised picture of what to fix first.