<!-- https://learnsql.cpetoday.com/analyze/iterators -->
# Iterators and table functions

## Aggregators versus iterators
`SUM(Sales[SalesAmount])` aggregates a column. Its generalization, `SUMX(Table, expression)`, evaluates an expression for every row of a table and then sums the results. Use an X function whenever the value to add up needs row-level arithmetic: `SUMX(Sales, Sales[SalesQuantity] * Sales[UnitPrice])`. The family: SUMX, AVERAGEX, MINX, MAXX, COUNTX, COUNTAX, PRODUCTX, MEDIANX, PERCENTILEX.INC/EXC, STDEVX.P/S, RANKX, CONCATENATEX, GEOMEANX.

> Diagram: SUMX visits each row of a sales table, multiplies quantity by price on that row, and adds the results to get 820. SUM of quantity alone gives 20 units.

## Average of ratios versus ratio of sums
`AVERAGEX(Sales, DIVIDE(Sales[SalesAmount], Sales[SalesQuantity]))` is the simple average of line prices. `DIVIDE([Total Sales], [Units Sold])` is the weighted (quantity-weighted) average. They differ, and for money the weighted one is nearly always what is meant. Same for margin %: divide total margin by total sales instead of averaging row margins.

## Iterating over a dimension and context transition
`SUMX(VALUES(Customer[Customer]), [Total Sales])` computes the measure once per customer (the measure reference triggers context transition) and adds the results. The grain of the iteration decides the answer: iterate Product to total after a per-product rule, iterate Month for monthly averages.
```
Average Sales per Customer = AVERAGEX(VALUES(Customer[CustomerKey]), [Total Sales])
Average Daily Sales = AVERAGEX(VALUES('Calendar'[Date]), [Total Sales])
Peak Month Sales = MAXX(VALUES('Calendar'[Year-Month]), [Total Sales])
Capped Commission = SUMX(VALUES(Employee[Employee]), MIN([Sales Commission], 5000))
```
Totals that do not match the visible rows are explained by the iteration grain: the total row of a matrix is evaluated with no row filter, so a measure with an IF on a per-row condition needs an iterator over the row level to make totals add up (the "subtotal fix" in the patterns topic).

## RANKX and TOPN
```
Product Rank = RANKX(ALLSELECTED(Product[Product]), [Total Sales], , DESC, Dense)
Top 10 Products Sales = CALCULATE([Total Sales], TOPN(10, ALLSELECTED(Product[Product]), [Total Sales], DESC))
Others = [Total Sales] - [Top 10 Products Sales]
```
RANKX(table, expression, [value], [order], [ties]): the table must be the set you are ranking against; use ALL or ALLSELECTED on the column in the visual so the rank is computed across the right set. In a visual that shows product rows, RANKX(ALLSELECTED(Product[Product]), ...) ranks each row among the products selected. Add ties handling (Dense or Skip). TOPN(n, table, orderBy, order, ...) returns the n rows; ties can return more than n.

## Table functions that build tables
- `FILTER(table, condition)`: row by row filter. `ALL(table)`, `ALL(column)`, `VALUES`, `DISTINCT`, `ALLSELECTED`, `ALLEXCEPT`.
- `ADDCOLUMNS(table, "name", expression, ...)`: adds computed columns (the new column is referred to as [name]). `SELECTCOLUMNS(table, "name", expr, ...)`: projects a new set of columns.
- `SUMMARIZE(table, groupCol, ...)`: groups; avoid adding aggregate expressions in SUMMARIZE, use `ADDCOLUMNS(SUMMARIZE(...), "Total", [Total Sales])` instead. `SUMMARIZECOLUMNS(groupCols, filters, "name", expr, ...)`: the engine behind most visuals; it removes rows where all measures are blank.
- `GROUPBY`: groups with iterators over CURRENTGROUP(); rarely better than SUMMARIZE.
- `CROSSJOIN(t1, t2)`, `GENERATE(t1, t2)` / `GENERATEALL` (apply a table expression for every row), `NATURALINNERJOIN`, `NATURALLEFTOUTERJOIN`, `UNION`, `INTERSECT`, `EXCEPT`, `GENERATESERIES(start, end, step)`, `DATATABLE`, `ROW("name", value)`, `{...}` table constructors such as `{("Sales", 1), ("Cost", 2)}`.
- `TOPN`, `SAMPLE`, `ADDMISSINGITEMS`, `ROLLUP`/`ROLLUPGROUP`/`ISSUBTOTAL` (with SUMMARIZE), `CALCULATETABLE(table, filters)`.
```
Customer Sales Table =
ADDCOLUMNS(
    SUMMARIZE(Sales, Customer[Customer]),
    "Sales", [Total Sales],
    "Orders", [Order Lines]
)
Customers Above Average =
VAR Avg = AVERAGEX(ALLSELECTED(Customer[Customer]), [Total Sales])
RETURN COUNTROWS(FILTER(ALLSELECTED(Customer[Customer]), [Total Sales] > Avg))
```

## Counting
- `COUNTROWS(table)`: rows. `COUNT(column)` counts non-blank numbers; `COUNTA` non-blank anything; `DISTINCTCOUNT(column)` and `DISTINCTCOUNTNOBLANK`. `COUNTBLANK`. `APPROXIMATEDISTINCTCOUNT` for DirectQuery speed.
- Count of customers who bought: `DISTINCTCOUNT(Sales[CustomerKey])`. Count of customers in a dimension regardless of sales: `COUNTROWS(Customer)`. They differ whenever the dimension has rows without sales.
- Count of items meeting a measure condition: `COUNTROWS(FILTER(VALUES(Product[Product]), [Total Sales] > 100000))`.

## Text, logic and information functions you will use constantly
`IF`, `SWITCH(TRUE(), cond1, result1, cond2, result2, default)`, `AND/OR/NOT`, `COALESCE`, `ISBLANK`, `ISNUMBER`, `ISTEXT`, `ISERROR`, `IFERROR`, `CONCATENATEX(table, expr, ", ")`, `FORMAT`, `LEFT/RIGHT/MID/LEN/FIND/SEARCH/SUBSTITUTE/TRIM/UPPER/LOWER`, `VALUE`, `FIXED`, `USERNAME`, `USERPRINCIPALNAME`, `LOOKUPVALUE`, `CONTAINSROW`, `CONTAINS`, `NAMEOF`, `SELECTEDMEASURE`, `SELECTEDMEASUREFORMATSTRING`.

## Iterator performance
- The cost of an iterator is rows iterated times the cost of the inner expression. Iterate a dimension column (hundreds or thousands of values), not the fact table (millions), and avoid measures inside iterators over huge tables.
- `SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])` over a fact table is fine: it is a simple row calculation the engine runs in one pass. Putting a measure or CALCULATE inside forces a context transition per row.
- Prefer `COUNTROWS(FILTER(...))` over nested iterators, and `CALCULATE(..., column filter)` over `FILTER(table, ...)` where possible.
