Module 8: Performance, security and sharing · Lesson 8.2 · about 16 min
Row-level security
Show each person only their rows, from one report. Create roles with DAX filter rules, use USERPRINCIPALNAME for dynamic security, and test it properly.
Module 8 · about 16 minutes · read first: 5.1. You’ll be able to create static and dynamic security roles and test them.
1. Why this matters
Store managers should see their store, regional managers their region, head office everything, all from one report. RLS does that without making a copy per person, and it’s a standard interview topic.
2. A static role
- Modeling > Manage roles > New, name it “West”.
- Choose the Store table and enter the rule:
[Region] = "West". - Modeling > View as > West to test.
- After publishing, in the Service: semantic model > Security > add people or groups to the role.
The rule is a filter on Store, so it flows through relationships to Sales and Targets, exactly like a slicer that readers can’t remove:
This editor applies a row-level security rule as a slicer: Store[Region] = "West". Split by Store and by Category: the person only ever sees West data. What does Share of All show, and why could that leak information?
Press Run to see the result.
Notice Share of All: ALL(Store) inside CALCULATE does not remove a security filter. RLS filters are applied before everything else, so the person’s “all” is all West stores (here the editor imitates it with a slicer, so ALL removes it: in Power BI, the West share would add up to 100 %). Security never leaks through ALL.
3. Dynamic RLS
One role for everyone, driven by a table:
| Region | |
|---|---|
| manager.west@example.com | West |
| manager.east@example.com | East |
Relate Security[Region] to a Region dimension (or Store), and in the role write on Security: [Email] = USERPRINCIPALNAME(). Each viewer sees only the regions listed for their sign-in. Add a person: add a row, no report change.
4. Test and manage
- View as a role, and as a specific user with dynamic rules (“Other user”).
- Assign groups, not individuals, in the Service.
- RLS filters data rows; to hide whole pages or columns use object-level security (with external tools) or separate reports.
- Remember: workspace Admin, Member and Contributor roles bypass RLS. Share with readers as Viewers or through an app.
Principle
Principle: RLS adds filters to the start of every query for that user. Because filters flow along relationships, filtering a dimension is enough to secure every fact table related to it. Check the model for paths that bypass the filter, such as a fact table not related to the secured dimension.
Try it: with a West role on Store, can a West user see Targets for East? (No: Targets relates to Store, so the filter reaches it.)
5. Worked example
Northbay security. A Security table lists each store manager’s email and StoreKey, related to Store; regional managers have one row per store in their region; head office emails are in a separate “All stores” role with no rule. One report, one model, three groups in the Service.
6. Practice
- Write the rule for a role that sees only Grocery and Home products.
- Why is a group better than individual emails in role membership?
- A regional manager can see all stores. They’re a Member of the workspace. What’s wrong?
Answers
- On Product:
[Category] IN {"Grocery", "Home"}. - People join and leave; IT manages the group, so the report needs no changes.
- Members bypass RLS. Make them a Viewer, or share through an app.
Check yourself
Sources
- Microsoft Learn, Row-level security with Power BI: https://learn.microsoft.com/fabric/security/service-admin-row-level-security
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.