<!-- https://learnsql.cpetoday.com/analyze/functions -->
# DAX function reference (quick)

Signatures use [optional] arguments. "Scalar" means returns one value, "table" means returns a table.

## Aggregation
- `SUM(Column)`, `AVERAGE(Column)`, `MIN(Column)`, `MAX(Column)`: aggregate a column (MIN/MAX also take two scalars: `MAX(a, b)`). `COUNT(Column)` non-blank numbers, `COUNTA(Column)` non-blank values, `COUNTBLANK(Column)`, `COUNTROWS(Table)`, `DISTINCTCOUNT(Column)`, `DISTINCTCOUNTNOBLANK(Column)`, `APPROXIMATEDISTINCTCOUNT(Column)`.
- Iterator versions take a table and an expression evaluated per row: `SUMX`, `AVERAGEX`, `MINX`, `MAXX`, `COUNTX`, `COUNTAX`, `PRODUCTX`, `MEDIANX`, `GEOMEANX`, `STDEVX.S`, `STDEVX.P`, `VARX.S`, `VARX.P`, `PERCENTILEX.INC`, `PERCENTILEX.EXC`, `CONCATENATEX(Table, Expr, [Delimiter], [OrderBy], [Order])`, `RANKX(Table, Expr, [Value], [Order], [Ties])`.
- Column statistics: `MEDIAN`, `PERCENTILE.INC`, `PERCENTILE.EXC`, `STDEV.S`, `STDEV.P`, `VAR.S`, `VAR.P`, `GEOMEAN`.

## Filter and context
- `CALCULATE(Expr, [Filter1], ...)`: evaluate Expr in a modified filter context. `CALCULATETABLE(Table, [Filter1], ...)`: same, returns a table.
- `FILTER(Table, Condition)`: rows where Condition is true. `ALL([Table | Column ...])`: all rows/values, ignoring filters. `ALLEXCEPT(Table, Column1, ...)`. `ALLSELECTED([Table | Column])`. `ALLNOBLANKROW`. `REMOVEFILTERS([Table | Column])` (CALCULATE modifier). `KEEPFILTERS(Filter)`. `VALUES(Column | Table)`. `DISTINCT(Column | Table)`. `SELECTEDVALUE(Column, [Alternate])`. `HASONEVALUE(Column)`, `HASONEFILTER(Column)`. `ISFILTERED(Column | Table)`, `ISCROSSFILTERED(Column)`, `ISINSCOPE(Column)`, `ISEMPTY(Table)`.
- `USERELATIONSHIP(Col1, Col2)`, `CROSSFILTER(Col1, Col2, Direction)` (NONE, ONEWAY, BOTH), `TREATAS(Table, Col1, ...)`, `RELATED(Column)`, `RELATEDTABLE(Table)`, `LOOKUPVALUE(Result, SearchCol, SearchValue, ..., [Alternate])`.
- `EARLIER(Column, [N])` and `EARLIEST`: older row-context navigation; prefer variables.
- `SELECTEDMEASURE()`, `SELECTEDMEASURENAME()`, `SELECTEDMEASUREFORMATSTRING()`, `ISSELECTEDMEASURE(m1, ...)`: calculation groups.

## Table construction and shaping
`ADDCOLUMNS(Table, "Name", Expr, ...)`, `SELECTCOLUMNS(Table, "Name", Expr, ...)`, `SUMMARIZE(Table, GroupCol1, ..., ["Name", Expr, ...])`, `SUMMARIZECOLUMNS(GroupCol1, ..., [FilterTable ...], "Name", Expr, ...)`, `GROUPBY`, `TOPN(N, Table, OrderBy, [Order], ...)`, `SAMPLE`, `ROW("Name", Expr, ...)`, `DATATABLE("Col", Type, {{...}})`, `GENERATESERIES(Start, End, [Step])`, `CALENDAR(Start, End)`, `CALENDARAUTO([FiscalYearEndMonth])`, `CROSSJOIN`, `GENERATE`, `GENERATEALL`, `NATURALINNERJOIN`, `NATURALLEFTOUTERJOIN`, `UNION`, `INTERSECT`, `EXCEPT`, `ADDMISSINGITEMS`, `TREATAS`, `ROLLUP`, `ROLLUPGROUP`, `ISSUBTOTAL`, `CURRENTGROUP()`, `CONTAINS(Table, Col, Value, ...)`, `CONTAINSROW(Table | {list}, Value)`, `CONTAINSSTRING(Text, Within)`, `IN`.

## Logical and information
`IF(Test, True, [False])`, `IF.EAGER`, `SWITCH(Expr, Value1, Result1, ..., [Else])` (use `SWITCH(TRUE(), ...)` for conditions), `AND(a, b)`, `OR(a, b)`, `NOT(a)`, `TRUE()`, `FALSE()`, `COALESCE(a, b, ...)`, `IFERROR(Expr, Alt)`, `ISBLANK`, `ISERROR`, `ISLOGICAL`, `ISNUMBER`, `ISTEXT`, `ISNONTEXT`, `ISEVEN`, `ISODD`, `BLANK()`, `ERROR("message")`, `NAMEOF(Column | Measure)`, `USERNAME()`, `USERPRINCIPALNAME()`, `USEROBJECTID()`, `CUSTOMDATA()`, `HASONEVALUE`, `PATH`, `PATHITEM`, `PATHLENGTH`, `PATHCONTAINS`, `PATHITEMREVERSE` (parent-child hierarchies).

## Math and statistics
`DIVIDE(Num, Den, [Alt])`, `ABS`, `ROUND(n, digits)`, `ROUNDUP`, `ROUNDDOWN`, `MROUND`, `CEILING`, `FLOOR`, `INT`, `TRUNC`, `MOD`, `QUOTIENT`, `POWER`, `SQRT`, `EXP`, `LN`, `LOG`, `LOG10`, `SIGN`, `FACT`, `RAND`, `RANDBETWEEN`, `PI`, `CURRENCY`, `CONVERT`, trig functions, `XIRR(Table, Values, Dates, [Guess])`, `XNPV`, `IRR`, `NPV`, `PMT`, `FV`, `PV`, `RATE`, `NPER`, `SLN`, `DB`, `DDB`.

## Text
`CONCATENATE`, `&`, `CONCATENATEX`, `LEFT`, `RIGHT`, `MID`, `LEN`, `FIND` (case sensitive, errors), `SEARCH` (case insensitive), `SUBSTITUTE`, `REPLACE`, `TRIM`, `UPPER`, `LOWER`, `PROPER`, `REPT`, `EXACT`, `VALUE`, `FORMAT(Value, "format")`, `FIXED`, `UNICHAR`, `UNICODE`, `TOCSV`, `TOJSON`. FORMAT strings: dates `"yyyy-MM-dd"`, `"mmm yyyy"`, `"dddd"`; numbers `"#,0.00"`, `"0.0%"`, `"$#,0"`.

## Date and time
`DATE(y, m, d)`, `TIME`, `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `QUARTER`, `WEEKDAY(Date, [Type])` (2 = Monday is 1), `WEEKNUM(Date, [Type])`, `EOMONTH(Date, Months)`, `EDATE`, `TODAY()`, `NOW()`, `UTCTODAY`, `UTCNOW`, `DATEDIFF(Start, End, Interval)` (SECOND ... YEAR), `DATEVALUE`, `TIMEVALUE`, `YEARFRAC`.

## Time intelligence (need a date column of a proper date table)
- Period to date: `DATESYTD(Dates, [YearEnd])`, `DATESQTD`, `DATESMTD`, `TOTALYTD(Expr, Dates, [Filter], [YearEnd])`, `TOTALQTD`, `TOTALMTD`.
- Shifts: `SAMEPERIODLASTYEAR(Dates)`, `DATEADD(Dates, N, Interval)`, `PARALLELPERIOD(Dates, N, Interval)`, `PREVIOUSDAY/MONTH/QUARTER/YEAR`, `NEXTDAY/MONTH/QUARTER/YEAR`.
- Windows: `DATESBETWEEN(Dates, Start, End)`, `DATESINPERIOD(Dates, Start, N, Interval)`.
- Boundaries: `FIRSTDATE`, `LASTDATE`, `FIRSTNONBLANK(Col, Expr)`, `LASTNONBLANK(Col, Expr)`, `FIRSTNONBLANKVALUE`, `LASTNONBLANKVALUE`, `STARTOFMONTH/QUARTER/YEAR`, `ENDOFMONTH/QUARTER/YEAR`, `OPENINGBALANCEMONTH/QUARTER/YEAR`, `CLOSINGBALANCEMONTH/QUARTER/YEAR`.

## Window and visual-level functions (newer)
`INDEX`, `OFFSET`, `WINDOW`, `RANK`, `ROWNUMBER`, `PARTITIONBY`, `ORDERBY`, `MATCHBY`: functions that navigate an ordered table, great for running totals and previous-row comparisons without time intelligence. Visual calculations add `RUNNINGSUM`, `MOVINGAVERAGE`, `PREVIOUS`, `NEXT`, `FIRST`, `LAST`, `COLLAPSE`, `EXPAND`, `RANGE` which refer to the visual's own axes.
```
Running Total (window) = SUMX(WINDOW(1, ABS, 0, REL, ALLSELECTED('Calendar'[Year-Month]), ORDERBY('Calendar'[Year-Month])), [Total Sales])
Previous Month Sales = CALCULATE([Total Sales], OFFSET(-1, ALLSELECTED('Calendar'[Year-Month]), ORDERBY('Calendar'[Year-Month])))
```
Requirements vary by build; check Desktop's version and the documentation if a function is not recognized.

## Relationships and security
`RELATED`, `RELATEDTABLE`, `USERELATIONSHIP`, `CROSSFILTER`, `TREATAS`, `USERPRINCIPALNAME()`, `USERNAME()`, `CUSTOMDATA()`.

## Query language (DAX query view, DAX Studio)
```
DEFINE
    VAR StartDate = DATE(2017, 1, 1)
    MEASURE Sales[Test Sales] = SUM(Sales[SalesAmount])
EVALUATE
    SUMMARIZECOLUMNS('Calendar'[Year], Category[Category], "Sales", [Test Sales])
ORDER BY 'Calendar'[Year], [Sales] DESC
```
`EVALUATE` returns a table; `DEFINE` creates query-scope VARs, MEASUREs, COLUMNs and TABLEs; `ORDER BY` sorts, `START AT` pages. INFO functions (`INFO.TABLES()`, `INFO.COLUMNS()`, `INFO.MEASURES()`, `INFO.RELATIONSHIPS()`) list model metadata in DAX query view.

## Operators precedence summary
`^`, then unary minus, `* /`, `+ -`, `&`, comparisons, `NOT`, `&&`, `||`. Use parentheses when mixing. `IN` accepts a table or a list `{1, 2}`; comparing a column to a list needs a column on the left.
