The request
From Jessica Turner, Head of Sales Operations
“We now have three territory managers and the list of states changes every quarter. I don't want a role per person. Sales Ops keeps a Territories table with each state and the email of the manager who owns it. Can one rule read that table and show each manager just their states?”
Your goal
The model has a Territories table (state, manager_email; one row per state). Write one rule on the customers table that keeps a customer when the manager_email of their state in Territories equals the signed-in user's email. The visual shows what each person sees, including someone who isn't in Territories.
- Each user sees only their rows: check every line of the visualnot done yet
Your role rule
- Visual
- A card of [Sales], [Customers] for each user
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")Customers = COUNTROWS(customers)
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
- Territories state, manager_email
Loading the editor…
Reference solution
| User | Sales | Customers |
|---|---|---|
| Ava Reed | 5,894,284.44 | 2,018 |
| Luis Ortega | 5,894,284.44 | 2,018 |
| Grace Kim | 5,894,284.44 | 2,018 |
| Hannah Moore (not in Territories) | 5,894,284.44 | 2,018 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Jessica gets, and why
The lesson
A static role holds a fixed list. With ten regions you maintain ten roles, and every reshuffle is a model change. Dynamic RLS uses one role whose rule asks who is signed in? and looks the answer up in a table the business maintains.
What you are working in
This page is a practice tool, not Power BI. You write one rule on customers; the engine runs it
once per person in the list, with USERPRINCIPALNAME() returning that person’s email, and the
visual shows what each one sees. It follows Power BI’s rules, so the same rule works in Desktop.
In Power BI Desktop: load the Territories table (Excel, SharePoint list or a database table) with
Home → Get data, then Modeling → Manage roles → New, name the role, select customers,
Switch to DAX editor, type the rule and Save. Test it with Modeling → View as: tick
Other user, type a manager’s email, tick the role too, and press OK.
Who is signed in
USERPRINCIPALNAME() returns the user principal name of the person viewing the report, which in
most companies is their work email, such as ava.reed@harborandpine.com. In Desktop, before
publishing, it returns your own sign-in, which is why you test with View as → Other user.
USERNAME() exists too, but in Desktop it returns DOMAIN\user; USERPRINCIPALNAME() gives the
same answer in Desktop and the Service, so prefer it.
Looking up the owner
LOOKUPVALUE(Territories[manager_email], Territories[state], customers[state]) = USERPRINCIPALNAME()Read it inside out: for this customer’s state, find the Territories row with the same state and
return its manager_email; then compare that with the signed-in email. It needs one row per state
in Territories, otherwise LOOKUPVALUE can’t pick a single email and fails. States nobody owns
return a blank, which never equals an email, so those customers are hidden from everyone.
The relationship version
Many models relate the security table instead: Territories[state] → customers[state], with the
rule on Territories, Territories[manager_email] = USERPRINCIPALNAME(). The filter on Territories
flows to customers and on to orders. When one manager can own a state together with someone else,
the mapping table has several rows per state and the rule needs a test that accepts a list:
customers[state] IN CALCULATETABLE(VALUES(Territories[state]), Territories[manager_email] = USERPRINCIPALNAME())Common mistakes
- Emails typed in the rule. That is a static role in disguise.
- Case and spaces. DAX compares text ignoring case, but a trailing space in the table breaks the match. Trim it in Power Query.
- Leaving the mapping table readable. Hide it in the report view, or every reader can see who owns what.
- Nobody in the role. A dynamic rule still needs people (or a group) added to the role in the Service.
Lantern lit!
Practised: row level securitydynamic rlsuserprincipalnamelookupvaluesecurity table
- Solved
- No hints
- First run