The request
From Marcus Bennett, Head of B2B Sales
“The B2B team gets its own sales report next month. Step one: the team should only ever see business customers, never consumers. Step two: each account rep sees the business customers in the states they cover, and Lisa, who manages the reps, sees everything her reps cover. The rep list changes often, so it lives in a table, not in the model.”
Your goal
Step 1 of 2. Step 1. Write the rule for a static role called B2B on customers: keep only the customers whose segment is business. The visual shows what members of the role see.
- Each user sees only their rows: check every line of the visualnot done yet
Step 2 of 2. Step 2. The model now has a Reps table with one row per covered state: state, rep_email and manager_email. Rewrite the rule so it keeps a customer when they are a business AND the signed-in user is either the rep or the manager listed for the customer's state in Reps.
- Each user sees only their rows: check every line of the visualnot done yet
Step 1 · 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
Loading the editor…
Reference solution
| User | Sales | Customers |
|---|---|---|
| Nicole Adams (B2B) | 5,894,284.44 | 2,018 |
| Lisa Harper (B2B) | 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.
Step 2 · 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
- Reps state, rep_email, manager_email
Loading the editor…
Reference solution
| User | Sales | Customers |
|---|---|---|
| Nicole Adams (rep) | 5,894,284.44 | 2,018 |
| Andrew Collins (rep) | 5,894,284.44 | 2,018 |
| Lisa Harper (manager) | 5,894,284.44 | 2,018 |
| Omar Haddad (manager) | 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 Marcus gets, and why
The lesson
This case puts the whole chapter to work: a static role first, then one dynamic rule that serves reps and their managers from a table Sales Ops maintains.
What you are working in
A practice tool, not Power BI: each step is a security rule on customers, and the visual shows
the Sales and Customers each listed person would see. In Power BI Desktop the same rules go in
Modeling → Manage roles (with Switch to DAX editor), are tested with Modeling → View as
(tick Other user for the dynamic one), and get their members in the Service under the semantic
model’s More options (…) → Security.
Step 1: a role for a segment
The first rule is static: business customers only. It needs no list of states and no emails, because every member of the B2B role sees the same rows. The visual shows it: Nicole and Lisa get identical numbers.
customers[segment] = "business"Step 2: reps and their manager
The Reps table has one row per covered state, with the rep who owns it and that rep’s manager. For each customer, the rule needs two lookups, the rep and the manager for the customer’s state, and keeps the row when the signed-in person is either one. A customer must still be a business.
VAR names each piece once, which makes the rule readable and stops the same lookup being written
twice:
VAR me = USERPRINCIPALNAME()
VAR rep = LOOKUPVALUE(Reps[rep_email], Reps[state], customers[state])
VAR manager = LOOKUPVALUE(Reps[manager_email], Reps[state], customers[state])
RETURN customers[segment] = "business" && (rep = me || manager = me)The brackets matter. && binds tighter than ||, so without them the manager test would stand
alone and let managers see consumer customers too.
States with no row in Reps return blank for both lookups, so their customers stay hidden from everyone in this role. That is the safe default: a new state is invisible until Sales Ops assigns it.
Managers with deeper teams
This rule handles one level: a rep and their direct manager. Org charts with several levels
(a director above Lisa) usually store a path per person and test it with PATHCONTAINS, or keep a
separate table of who may see which state, one row per person and state, tested with
IN CALCULATETABLE(VALUES(…), … = USERPRINCIPALNAME()). The idea stays the same: the rule asks who
is signed in, and a table answers.
Before you ship
Test as each rep, as each manager, and as someone in the role who isn’t in Reps (they should see nothing). Add the B2B team to the role as one security group. Keep builders out of it: as workspace Admins, Members or Contributors they would see every row anyway, and the test would tell you nothing.
Lantern lit!
Practised: row level securitystatic rlsdynamic rlsuserprincipalnamelookupvaluevarboss
- Solved
- No hints
- First run