Iterator functions evaluate an expression for each row of a table and then aggregate the results into a single value. Common iterators include SUMX, AVERAGEX, MINX, MAXX, COUNTX, and COUNTAX. For example, SUMX(Sales, Sales[Qty] * Sales[Price]) computes revenue per transaction before summing. The key distinction between SUM and SUMX is that SUM aggregates a single column directly, while SUMX can evaluate multi-column expressions row by row. AVERAGEX likewise produces a row-by-row average, supporting conditional or weighted averages that a simple AVERAGE cannot. COUNTROWS counts the rows of a table expression and is the recommended way to count records after filtering, while COUNT counts only numeric values and COUNTA counts non-blank cells including text and dates.
Several functions retrieve values across tables. RELATED pulls a column value from a related table when a many-to-one relationship exists, working only inside row context such as a calculated column or an iterator. RELATEDTABLE returns all rows on the many side of a relationship that relate to the current row, useful for counting or aggregating related transactions. LOOKUPVALUE retrieves a value from a column based on one or more search conditions without requiring an active relationship, which is convenient when working with unrelated tables, though RELATED is preferred when a relationship exists.
Other frequently used functions include FILTER, which returns a subset of a table matching a Boolean condition and is commonly nested inside CALCULATE to introduce complex dynamic filters. DIVIDE performs division with an optional alternate result when the denominator is zero, avoiding #DIV/0! errors. SELECTEDVALUE returns the value of a column when exactly one value is selected in the filter context, otherwise returning an alternate result, and is shorthand for the IF(HASONEVALUE(...), VALUES(...), alternate) pattern. HASONEVALUE and HASONEFILTER are companion functions used to detect single selections, while ISBLANK checks for blanks, BLANK returns a blank, and COALESCE returns the first non-blank among its arguments. SWITCH evaluates an expression against multiple values, providing a cleaner alternative to nested IF statements.