Chapter 7 · 9 levels
Advanced DAX
Iterators (SUMX, AVERAGEX) and RELATED for row-by-row maths and weighted KPIs, RANKX and TOPN for rankings, ALL versus ALLSELECTED for shares of a selection, USERELATIONSHIP for a second date, and VAR … RETURN for measures you can read and trust.
StartSUM and CALCULATE get you surprisingly far, but the questions Harbor & Pine’s managers ask next need more: revenue worked out line by line, the average order rather than the average line, a margin weighted by what actually sold, a ranking that survives a re-sort, the share of what the reader picked in a slicer, customers counted by the day they signed up instead of the day they ordered.
Each of those has one DAX pattern behind it, and this chapter teaches them one ticket at a time: iterators (SUMX, AVERAGEX) with RELATED, RANKX and TOPN, ALL versus ALLSELECTED, USERELATIONSHIP for an inactive relationship, and VAR … RETURN to name the pieces of a long measure so it says what it means and computes each piece once.
As in the rest of the track, the exercises run in a practice report built for this site, not in Power BI itself: you write measures and a DAX engine fills the matrix cell by cell the way Power BI would. Each lesson gives the exact place to do the same thing in Power BI Desktop. The boss level is a holiday-quarter channel review that needs three of these patterns together.
Levels
- Revenue line by line with SUMX
from Laura Mitchell, Finance Controller
sumx, iterator, row context, measure
- Average order value with AVERAGEX
from Kevin Park, E-commerce Manager
averagex, values, context transition, iterator, average order value
- A weighted margin with SUMX and RELATED
from Monica Reyes, Merchandise Planner
sumx, related, weighted average, margin, blanks, row context
- Sales of the top five products with TOPN
from Samantha Price, VP of Merchandising
topn, calculate, all, table functions, concentration
- Share of the selection with ALLSELECTED
from Eric Lawson, Head of Big-Ticket Categories
allselected, all, share of total, slicer, divide
- New customers by signup date with USERELATIONSHIP
from Natalie Cooper, Growth Marketing Manager
userelationship, inactive relationship, calculate, date table, role playing dimension
- Fix a measure with VAR and RETURN
from Ryan Patel, Senior BI Developer
var, return, variables, yoy, sameperiodlastyear, readability
- Boss: the holiday channel review
from Victoria Grant, Chief Commercial Officer
sumx, rankx, allselected, averagex, var, boss