Iterators (SUMX) & RELATED
Power BI · DAX (Data Analysis Expressions)
Theory
Iterator functions, the X functions such as SUMX, AVERAGEX, MINX, MAXX and COUNTX, go through a table row by row, evaluate an expression for each row (in row context), and then aggregate the results. SUM(Sales[Amount]) can only add an existing column; SUMX(Sales, Sales[Qty] * RELATED(Products[Price])) calculates a line value for every order and adds those up.
RELATED fetches a value from the 'one' side of a relationship for the current row, like a lookup that follows the model: on the Sales row for order 103 (ProductID P3), RELATED(Products[Price]) returns 500, so the line value is 3 × 500 = ₹1,500. RELATEDTABLE goes the other way and returns the related rows from the many side. Grouped by product, the measure gives Headphones ₹1,800, Keyboard ₹2,400 and Desk Lamp ₹2,000.
Why we use it
Many business values do not exist as a column: revenue from quantity times price, weighted averages, margins. Iterators compute them on the fly without adding stored columns.
When to use it
Use an iterator when the value must be computed per row before aggregating, or when you need per-entity results like AVERAGEX over Customers. If the per-row value is simple and needed often, a column in Power Query may be cheaper.
How it works
- SUMX(Sales, expression) starts
- Take the next Sales row (row context)
- RELATED fetches Products[Price] for that row
- Evaluate Qty × Price
- More rows? Yes: Go to the next row. No: Add up all row results.
- Return the total
Syntax
SUMX(<table>, <expression>)
AVERAGEX(<table>, <expression>) MAXX / MINX / COUNTX(<table>, <expression>)
RELATED(<column>) // from many side to one side
RELATEDTABLE(<tableName>) // from one side to many sideExample
Revenue =
SUMX(
Sales,
Sales[Qty] * RELATED(Products[Price])
)
// Row 101: 2 × 600 = 1,200 ... total 6,200
Avg Order Value = AVERAGEX(VALUES(Sales[OrderID]), [Total Sales]) // 6,200 / 6 = 1,033.33
// Calculated column on Customers
Order Count = COUNTROWS(RELATEDTABLE(Sales)) // Asha 2, Ravi 2, Meera 1, Karan 1Key points
- X functions evaluate an expression per row, then aggregate
- SUM(col) is shorthand for SUMX(table, col)
- RELATED needs a relationship and works from the many side
- RELATEDTABLE returns related rows from the one side
- Iterating huge tables with complex expressions can be slow