If you have spent any time writing DAX for Power BI, you've probably noticed that a lot of the most powerful functions end in the letter X (e.g. SUMX, AVERAGEX, RANKX, etc.). That X stands for iterator, and understanding what makes these functions different from their non-iterating counterparts will help to optimise your work using DAX.
What Makes a Function an Iterator?
Most aggregation functions in DAX take a single column reference and aggregate it directly:
Total Sales = SUM(Sales[SalesAmount])
This is fast and simple because DAX can aggregate a column without assessing each row individually.
Iterator functions work differently. Instead of pointing at a column, they take a table as their first argument and an expression using columns as their second, evaluating that expression once per row before combining the results:
Total Sales X = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Here, SUMX() walks through every row of the Sales table, calculates Quantity UnitPrice for that specific row, and then sums up all those individual results. This is fundamentally different from SUM() because it can perform a calculation row-by-row then perform an aggregation function at the end, after all the calculations without requring a separate row.
This row-by-row evaluation is called row context, and it's the defining feature of every iterator function.
Why You Can't Just Use a Calculated Column Instead
Why not just create a calculated column and then SUM that column?
Sometimes that's a perfectly valid approach. But iterators give you flexibility that calculated columns can't:
-
They respect filter context dynamically. An iterator recalculates its row-by-row expression fresh every time filters change (from slicers, visuals, or other measures), while a calculated column is computed once when the data refreshes and stored statically.
-
They avoid bloating your data model. Calculated columns take up memory and storage, measures using iterators are computed on demand.
-
They can reference measures, not just columns, letting you build row-level logic that itself depends on other calculations.
A Note On Performance
Iterators are powerful, but that power comes at a cost. Row-by-row evaluation is inherently more computationally expensive than direct column aggregation. A few things to keep in mind:
-
Avoid iterating over huge tables unnecessarily. If a calculation in a large table can be done with a simple SUM or CALCULATE, prefer that over an iterator.
-
Filter early. Narrowing a table before iterating (rather than iterating over everything and discarding results) generally performs better.
-
Use variables to store intermediate results and avoid recalculating the same expression multiple times within a measure.
Wrapping Up
Iterator functions give DAX its row-level flexibility. They let you compute something that doesn't exist as a stored column, react dynamically to filter context, and combine multiple columns or measures into a single row-by-row calculation before aggregating. Understand that "X" functions mean "loop through this table, evaluate this expression per row, then combine the results" a huge portion of DAX's behavior, including its performance quirks, starts to make a lot more sense.
There are many more iterator functions than those that have been mentioned here. For more, visit Microsoft's documentation site at: https://learn.microsoft.com/en-us/dax/aggregation-functions-dax