The request
From Victor Ramirez, Marketing Director
“The web channel page crawls. Our contractor traced it to the Web Sales measure; the numbers are right, it's just slow. Can you rewrite it so it gives the same figures without the FILTER?”
Your goal
The old measure is in the editor as a comment. Write a new Web Sales measure that returns the same numbers (completed web orders, sum of order_items[line_total]) in every cell of the category matrix, using CALCULATE with column filters and no FILTER function.
- Fill
[Web Sales]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Web Sales]
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
| category | Web Sales |
|---|---|
| Decor | |
| Furniture | |
| Kitchen | |
| Lighting | |
| Outdoor | |
| Textiles | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Victor gets, and why
The lesson
Victor’s measure returns the right numbers. That is exactly why slow measures survive: nobody looks at them until a page takes ten seconds to answer a click.
What you are working in
This page is a practice report, not Power BI: a matrix with products[category] on its rows and a
DAX editor. When you press Check, the engine evaluates your measure in every cell, compares
each one with the expected numbers, and also checks that FILTER is gone, since speed is the point
of the ticket. It can’t time your formula the way Power BI does.
In Power BI Desktop, select the measure in the Data pane, edit it in the formula bar and press Enter. Then Optimize → Performance analyzer → Start recording → Refresh visuals, and compare the DAX query time before and after.
What the slow version does
Web Sales = SUMX(
FILTER(order_items, RELATED(orders[channel]) = "web" && RELATED(orders[status]) = "completed"),
order_items[line_total])FILTER walks every row of order_items visible in the cell, follows the relationship to
orders twice per row with RELATED, and builds a new table of the rows that pass. SUMX then
walks that table. Most of this is row-by-row work for the single-threaded formula engine, and it
grows with the fact table.
What the fast version does
Web Sales = CALCULATE(
SUM(order_items[line_total]),
orders[channel] = "web",
orders[status] = "completed")Each condition is a column filter: CALCULATE turns orders[channel] = "web" into “the
channel column, value web”. Filters on orders reach order_items through the relationship, the
same way a slicer does. What remains is a plain SUM over a filtered column, which the storage
engine does in one fast scan. Two filter arguments are combined with AND.
Same numbers, every cell
The matrix rows filter products, which filters order_items; the new filters on orders add to
that. So each category row gets its completed web sales, and the Total row gets all of them. A
CALCULATE column filter replaces an existing filter on the same column, so if the page had a
channel slicer, this measure would ignore it and always show web. That is what Victor asked for.
When FILTER is the right tool
Keep FILTER when the condition can’t be written as a test on one column: comparing two columns
(order_items[unit_price] > RELATED(products[unit_price])) or testing a measure. Even then,
filter the smallest table that works, such as FILTER(VALUES(orders[order_id]), …), never the
whole fact table.
Common mistakes
- Dropping the status test. Cancelled and refunded web orders creep in.
FILTER(ALL(orders), …)inside CALCULATE. It removes the slicer filters on orders too.- Quotes around column names.
"orders[channel]"is text, not a column.
Lantern lit!
Practised: performancecalculatefiltersumxstorage enginecolumn filters
- Solved
- No hints
- First run