4.2Row-level security (RLS)
The essentials in 30 seconds
- RLS filters rows by user: a role in Desktop carries a DAX filter (e.g.
[Region] = "Algiers"), tested with "View as", then assigned in the Service. - You filter the dimensions (the filter propagates to the facts through the relationship) rather than the facts directly — and you always test before publishing.
- Dynamic RLS: a single role that filters via
USERPRINCIPALNAME()and a security table (email → scope) — the pro standard, instead of one role per person.
Running mini-case — Zineb opens the report to the regional managers
Zineb, a management controller at an agri-food SME in Algiers, publishes a single sales report for three regional managers — but each must only see their own region, because the figures by sector are confidential. She applies the method: in Modeling → Manage roles, she creates "Region Algiers" with the DAX filter [Region] = "Algiers" placed on the Region dimension. She tests with "View as": the whole report is restricted to Algiers. Then she prepares the industrialization: a Security table (Email, Region) related to the dimension, and a "Dynamic" role whose filter is Security[Email] = USERPRINCIPALNAME(). She notes the milestone: the actual assignment of users will be done in the Service (level 5).
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 4.2.
1. One report, many readings
One and the same report, different readings: the Algiers manager sees only Algiers, the general manager sees everything. This is row-level security (RLS): roles defined in Desktop (Modeling → Manage roles) carry DAX filters like [Region] = "Algiers". RLS filters the visible rows according to the signed-in user: one report, different scopes. Zineb therefore defines one role per scope.
2. Filter the dimensions, always test
You place the filter on the dimensions (Region, City): through the relationship, it propagates to the fact tables — more reliable than filtering the facts directly. And you always test with "View as" (Modeling): you see the report through the eyes of a role before publishing. Zineb simulates "Region Algiers" and checks that nothing spills over.
3. Dynamic RLS
Creating one role per person doesn't scale. The dynamic version industrializes it: a security table (Email → Region) related to the dimension, and a single role whose filter is Security[Email] = USERPRINCIPALNAME(). Each user then sees only their own scope, without a dedicated role. Zineb sets this standard — it's also the expected reflex for the PL-300.
🔎 Concrete example — 🔎 Concrete example — The dynamic role
Zineb fits all the scopes into a single role:
Security table (Email, Region) related to the Region dimension.
"Dynamic" role, filter on Security:
Security[Email] = USERPRINCIPALNAME()
→ each user sees only their own scope, without a dedicated role.Reading: a single role is enough for everyone — the security table holds the email → region mapping.
Lesson: test with "View as" by specifying an email from the table: the scope follows the identity.
Section vocabulary
- RLS (Row-Level Security)
- Filter the visible ROWS according to the signed-in user: one report, different data scopes.
Simple example: Zineb restricts the report to each manager's region.
What it is not: sharing a report where everyone sees everything.
Reflex: confidential data → RLS.
- Role
- A named set of DAX filters (Modeling → Manage roles): "Region Algiers" = [Region]="Algiers".
Simple example: Zineb creates one role per scope, then a dynamic role.
What it is not: a filter placed by hand on a single visual.
Reflex: role = named, reusable DAX filter.
- View as
- The test in Desktop (Modeling → View as): see the report through the eyes of a role before publication.
Simple example: Zineb simulates "Region Algiers" and only sees Algiers.
What it is not: testing with your own author account.
Reflex: RLS is always tested, before publication.
- USERPRINCIPALNAME()
- The function that returns the signed-in user's email: the key to dynamic RLS.
Simple example: Zineb uses it to filter rows by identity.
What it is not: a filter frozen on a single value.
Reflex: dynamic RLS → USERPRINCIPALNAME().
- Security table
- The email → scope mapping table (region, city…) that a single dynamic role traverses to filter.
Simple example: Zineb lists Email and Region there for her regional managers.
What it is not: a dedicated role per person.
Reflex: one dynamic role + one security table.
In practice — In practice — From static role to dynamic RLS
- Modeling → Manage roles → New: "Region Algiers" with the DAX filter [Region] = "Algiers" (on the Region dimension — the best choice).
- Test: Modeling → View as → check the role: the whole report is restricted to Algiers. Exit test mode.
- Dynamic version: create a Security table (Email, Region) with 2-3 rows, relate it to the Region dimension, then a "Dynamic" role filtered by Security[Email] = USERPRINCIPALNAME().
- Test again with "View as" by specifying an email from the table: the scope follows the identity. (The actual assignment of users is done in the Service — level 5.)
Step-by-step script — securing the report like Zineb (8 min)
1) Modeling → Manage roles → New, name it "Region Algiers", select the Region dimension and write the DAX filter [Region] = "Algiers". 2) Modeling → View as → check the role: the whole report is limited to Algiers; uncheck to return. 3) Create a Security table (Email, Region) with 2-3 rows and relate Region to the Region dimension. 4) "Dynamic" role: filter Security[Email] = USERPRINCIPALNAME(). 5) Test with "View as" by entering an email from the table: the scope follows the identity. The assignment of users will be done in the Service.
If the test changes nothing, check the relationship direction and that the filter is indeed on the DIMENSION (the filter propagates to the facts).
Copy-paste template — my RLS checklist
Roles: Modeling → Manage roles → New
Static filter: [Region] = "Algiers" (on the DIMENSION, not the facts)
Test: Modeling → View as (never with your own account)
Dynamic: Security table (Email, Region) + role filtered by
Security[Email] = USERPRINCIPALNAME()
User assignment: in the Service (level 5).By region
Zineb creates a "Region Algiers" role: the Algiers manager only sees their region.
"View as" test
Zineb simulates each role before publishing: no scope spills over.
One role for all
Zineb replaces per-person roles with a single dynamic USERPRINCIPALNAME() role.
Dimensions first
Zineb filters the Region dimension: the filter propagates to the sales through the relationship.
✓ Best practices
- Filter the dimensions (the filter propagates to the facts through the relationship).
- Test with "View as" before any publication.
- Prefer dynamic RLS (USERPRINCIPALNAME + security table).
✗ Common pitfalls
- Filtering the facts directly instead of the dimensions.
- Publishing RLS without having tested it.
- Creating one role per person (unmanageable; prefer dynamic).
Key takeaways
- RLS filters data per user: a single report, multiple views.
- Filter the DIMENSIONS rather than the facts directly.
- "View as" before any publication: RLS is always tested.
- Dynamic RLS (USERPRINCIPALNAME + security table) avoids one role per person.
48h challenge — apply it tomorrow
Like Zineb, create an RLS role on a report (e.g. [Region] = "Algiers"), test it with "View as", then build the security table + the dynamic role (USERPRINCIPALNAME). Write in one sentence who should see what in your organization.
- I can create an RLS role with a DAX filter and test it ("View as").
- I filter the dimensions and I know why.
- I know how to build dynamic RLS with USERPRINCIPALNAME and a security table.
Frequently asked questions
Does RLS apply to me too, the author?
In Desktop, no (except in test mode): you see everything. In the Service, administrators and workspace members are not subject to RLS — it applies to viewers. So don't conclude that it "doesn't work" by testing it with your own account.
Does RLS protect against data export?
Yes for the scope: a viewer limited to Algiers exports only the Algiers rows. But it does not replace report access permissions (who can open it): the two layers complement each other.
Which function is the key to dynamic RLS?