Function reference
A quick reference of the functions you will use, grouped by purpose.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Find the DAX function you need by its purpose.
- Read the syntax and notes for the common aggregation, filter, date and text functions.
- Use the operator precedence summary to read a complex formula.
- Recognize which functions need a proper date table.
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])andEARLIEST: 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.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.