This lesson's interactive features are locked, please to keep using them

Iterators

Iterator functions in DAX evaluate an expression row-by-row, then aggregate the results. They allow you to define what should happen in each row before summing, averaging, or comparing.

Aggregators look at a column, while iterators look at each row first.

Let's cover some of the most common functions.

SumX

The SUMX function iterates over a table, evaluating an expression for each row, then sums the results.

Total Sales Value = SUMX(
    sales,
    sales[sale_quantity] * RELATED(products[product_list_price])
)

RELATED lets us access the product_list_price column from the products table, even though this formula is in the sales table. This works because the sales and products tables have a relationship – they both have a product_id column. Power BI can often detect such relationships automatically.

AverageX

The AVERAGEX function works similarly, but averages the results instead of summing them.

Average Sales Value = AVERAGEX(
    sales,
    sales[sale_quantity] * RELATED(products[product_list_price])
)

MinX

The MINX function finds the minimum value of an expression evaluated over a table.

Min Product Cost = MINX(
    products,
    products[product_standard_cost]
)

MaxX

The MAXX function finds the maximum value of an expression evaluated over a table.

Max Product Cost = MAXX(
    products,
    products[product_standard_cost]
)

Iterator function names always end with X.

Assignment

Create a new session_quality_score_avg_scaled measure on the website_sessions table.
- Average the quality scores of all sessions, but only after multiplying each score by 100 to get a range from 0–100.
- Convert the result to an integer after the average is calculated.
You can use the INT function to convert a decimal value to an integer.
Add the average score to the existing multi-value card on the "Cards" page.

Save the project and, from the course directory, submit the CLI tests.