Power BI Row-Level Security: One Report, Different Data for Every Viewer
The sales director wants to see every region. Each regional manager should see only their own. The first instinct is to publish a copy of the report per region, and for about a month that feels fine. Then a measure changes, and someone has to fix twelve reports by hand.
Row-level security, usually shortened to RLS, solves this properly. You publish one report and one dataset, and Power BI filters the rows each person is allowed to see before any visual is drawn. The report does not need to know who is looking.
What RLS Actually Does
An RLS role is a filter rule on one or more tables. When someone in that role opens the report, the rule is applied to the table, and the filter flows through your relationships to everything related to it. A rule that keeps only rows where Region equals West on the Region table will also trim Sales, Orders and anything else downstream of it.
That last part is why the star schema matters here. Filters travel from the one side of a relationship to the many side. If your dimensions are clean and your fact table hangs off them, one rule on a dimension secures the whole model.
Static Roles: Quick, and Quickly Painful
The simplest version is a static role. In Power BI Desktop, open the Modeling tab, choose Manage roles, create a role called West, pick the Region table and write a filter such as [Region] = "West". Repeat for each region.
This works, and it is the right way to learn the mechanism. It stops scaling the moment you have forty regions or the manager list changes monthly, because every change means editing the model and republishing.
Dynamic Roles: One Rule for Everyone
The better pattern is a dynamic role. You add a small security table that maps each person to what they may see, for example a column of email addresses and a column of regions. Then you write a single role whose filter compares the logged-in user to that table.
The function that makes it work is USERPRINCIPALNAME(), which returns the signed-in person's sign-in address in the Power BI service. A filter on the security table such as [Email] = USERPRINCIPALNAME() leaves each viewer with only their own mapping rows. Relate that table to the Region dimension and the filter carries through to the facts.
Static role
One role per group, filter typed into the model
Dynamic role
One role, filter reads the signed-in user
Maintenance
Static needs a republish; dynamic needs a table edit
Best for
Static for learning; dynamic for real deployments
When a person needs more than one region, add a row per region for them in the security table. The role does not change at all. Access becomes a data question, which is far easier to hand to someone who is not a Power BI developer.
Setting It Up, End to End
- 1
Build the security table
Email and the key it unlocks, such as Region
- 2
Relate it to the dimension
Filter must reach the fact table
- 3
Create the role
Modeling tab, Manage roles, add the filter
- 4
Test with View as
Check each persona before publishing
- 5
Publish and assign members
Add people or groups in the service
The step most people skip is the second to last one. Power BI Desktop has a View as option on the Modeling tab. Pick your role, and for a dynamic role also tick Other user and type a test email address. The report redraws exactly as that person would see it. Check totals, not just that the report opens. A manager's grand total should match the sum of their own rows, and nothing else.
The Gotcha That Catches Experienced People
RLS only restricts people who open the content as a viewer. Anyone with the Admin, Member or Contributor role in the workspace can edit the dataset and bypass the rules. So if you test by opening the report from your own workspace account, you will see everything and wrongly conclude the security is broken, or worse, wrongly conclude nothing needs fixing.
The practical consequences are simple. Test with View as in Desktop and View as role in the service rather than with your own login. Share through an app, or give end users the Viewer role, rather than workspace edit access. And treat the member list as part of the model's documentation, because the roles in Desktop do nothing until real people or groups are added to them in the service.
A few other habits save trouble:
Where to Practise
RLS only makes sense once you can build a clean model and write DAX filters with confidence. The PowerBI Training course at SkyTrainings covers the Power Query Editor, DAX measures and data flows on real dashboard projects, which is the foundation this builds on. See the syllabus and the next batch on the PowerBI Training course page.
