Downloads

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

Filter and context

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)

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.