DAX Iterators: Row-by-Row Evaluation with SUMX, AVERAGEX & RANKX

Standard aggregation functions (SUM, AVERAGE, MIN, MAX) take a single column as input and aggregate all visible values in that column under the current filter context. However, real-world business formulas frequently require calculating values row-by-row before aggregating—this is the domain of DAX Iterators (commonly known as the "X-Functions").

1. Aggregators vs. Iterators

2. Implementing SUMX: Dynamic Revenue Calculation

Instead of polluting your data model with a calculated column for line total, compute it dynamically:

Filtering Inside an Iterator

Because the first argument of an iterator is any valid Table Expression, you can combine iterators with FILTER:

3. Dynamic Ranking with RANKX

RANKX evaluates a table and ranks each row dynamically according to a measure:

RANKX Parameter Breakdown

1. 1
   Table (ALL(Dim_Product[ProductName])): The list of all items to be ranked. Using ALL removes outer row filters so each product is compared against all products.
2. 2
   Expression ([Total Sales]): The metric used for ranking.
3. 3
   Value (Omitted): Evaluates the expression for the current item.
4. 4
   Order (DESC): Highest sales receives Rank 1.
5. 5
   Ties (Dense): Tied ranks do not skip numbers (e.g., 1, 2, 2, 3 instead of 1, 2, 2, 4).

4. Performance Considerations for Iterators

• Iterators introduce Row Context. When nesting iterators inside other iterators (e.g., SUMX(..., AVERAGEX(...))), the VertiPaq engine must perform quadratic loop iterations, which can degrade report speed on datasets with millions of rows.
• Always filter tables as early as possible using the smallest table expression in the first parameter.