This document provides a comprehensive reference for the Data Analysis Expressions (DAX) language syntax used in SQL Server Analysis Services, Power BI, and Excel Power Pivot.

Understanding DAX Syntax

DAX is a formula expression language used to create custom calculations for the data models in SQL Server Analysis Services (SSAS) Tabular models, Power BI, and Power Pivot in Excel.

Basic Structure

DAX formulas typically start with an equals sign (=) and consist of functions, operators, values, and object references (columns, tables, measures).

Formulas

A DAX formula can be a simple expression or a complex combination of functions and operators. The general syntax for a DAX formula is:

[Name of Measure] = Expression

Functions

DAX functions are pre-defined formulas that perform specific operations. They are categorized into:

  • Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT, etc.
  • Date and Time Functions: DATE, YEAR, MONTH, DAY, TODAY, NOW, etc.
  • Filter Functions: FILTER, ALL, VALUES, RELATED, etc.
  • Financial Functions: FV, PV, NPER, RATE, PMT, etc.
  • Information Functions: ISBLANK, ISERROR, etc.
  • Logical Functions: IF, AND, OR, NOT, etc.
  • Math and Statistical Functions: ABS, ROUND, SQRT, etc.
  • Parent and Child Functions: PATH, PATHITEM, PATHLENGTH, etc.
  • Relationship Functions: RELATED, USERELATIONSHIP, etc.
  • Set Functions: DISTINCT, UNION, EXCEPT, INTERSECT, etc.
  • Table Manipulation Functions: ADDCOLUMNS, SELECTCOLUMNS, SUMMARIZE, etc.
  • Text Functions: CONCATENATE, LEFT, RIGHT, MID, FORMAT, etc.
  • Time Intelligence Functions: SAMEPERIODLASTYEAR, TOTALYTD, DATEADD, etc.

Operators

DAX supports various operators for performing calculations and comparisons:

  • Arithmetic Operators: +, -, *, /
  • Comparison Operators: =, <, >, <=, >=, <>
  • Text Concatenation Operator: &
  • Logical Operators: && (AND), || (OR)

Common Syntax Elements

Column References

Columns are referenced using the table name and column name within square brackets:

[TableName][ColumnName]

Example: [Sales][Amount]

Table References

Tables are referenced by their name:

TableName

Example: Sales

Measures

Measures are referenced by their name. If the measure is in a different table than the current context, you may need to use the table name qualifier:

[MeasureName]

Example: [Total Sales] or Sales[Total Sales]

DAX Function Syntax Conventions

Most DAX functions follow a common pattern:

FUNCTIONNAME(
    argument1,
    argument2,
    ...
)

Arguments

Arguments can be:

  • Literal values (numbers, strings, dates, booleans)
  • Column references
  • Table references
  • Other DAX expressions or function calls
  • Named measures
Important: The order and type of arguments are crucial for a function to work correctly. Refer to the specific function documentation for detailed argument requirements.

Example Formulas

Calculating Total Sales Amount

Total Sales = SUM(Sales[Amount])

Calculating Sales Year-over-Year Growth

Sales YoY Growth =
VAR CurrentYearSales = [Total Sales]
VAR PreviousYearSales =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN
IF(
    NOT ISBLANK(PreviousYearSales),
    (CurrentYearSales - PreviousYearSales) / PreviousYearSales,
    BLANK()
)

Filtering a Table

High Value Sales = FILTER(Sales, Sales[Amount] > 1000)

Syntax Best Practices

  • Use meaningful names for measures and calculated columns.
  • Use variables (VAR ... RETURN ...) to improve readability and performance of complex formulas.
  • Comment your DAX code for complex logic.
  • Understand the evaluation context (row context and filter context).
  • Test your formulas thoroughly with various data scenarios.

DAX Keywords

DAX includes reserved keywords that have specific meanings:

Keyword Description
ALL Removes filters from a table or specific columns.
CALCULATE Modifies the filter context in which an expression is evaluated.
CARDFINALITY Returns the cardinality (number of distinct rows) of a table.
CLOSEST Returns the closest value to a specified expression.
COMBINEVALUES Concatenates two or more strings with a delimiter.
COUNT Counts rows that contain numbers.
DISTINCT Returns a single column table of distinct values from a column or table.
EARLIER Returns the current value of a column or variable in an outer row context.
FILTER Returns a table that has been filtered.
FIRSTNONBLANK Returns the first value in a column that is not blank.
FOR Used in certain table-valued functions to specify iteration.
GROUPBY Creates a summary table from the input table.
IF Performs a logical test and returns one value if TRUE, and another if FALSE.
IN Used with the SWITCH function to check for value membership.
INTERSECT Returns common rows from two tables.
ISBLANK Checks if an expression evaluates to BLANK.
KEEPFILTERS Preserves existing filters when modifying context.
LASTDATE Returns the last date of the current context.
LASTNONBLANK Returns the last value in a column that is not blank.
LOWER Converts a string to lowercase.
NEXTDAY Returns the day after a specified date.
NEXTMONTH Returns the month after a specified date.
NEXTQUARTER Returns the quarter after a specified date.
NEXTYEAR Returns the year after a specified date.
NOT Reverses the logical value of its operand.
NPER Calculates the number of periods for an investment.
NOW Returns the current date and time.
OR Performs a logical OR operation.
PATH Returns a delimited string representing the path of a hierarchical relationship.
PATHCONTAINS Checks if a path contains a specific item.
PATHITEM Returns an item from a path string at a specified position.
PATHITEMREVERSE Returns an item from a path string starting from the end.
PATHLENGTH Returns the length of a path string.
PKEY Not a standard DAX keyword. May refer to proprietary or custom functions.
PMT Calculates the payment for a loan based on constant payments and a constant interest rate.
PREVIOUSDAY Returns the day before a specified date.
PREVIOUSMONTH Returns the month before a specified date.
PREVIOUSQUARTER Returns the quarter before a specified date.
PREVIOUSYEAR Returns the year before a specified date.
PRODUCT Multiplies all numbers in a column.
PV Calculates the present value of an investment.
QUARTER Returns the quarter of a date.
RATE Calculates the interest rate per period of an annuity.
RANKX Returns the rank of a number in a list of numbers.
RELATED Returns a related value from another table.
REMOVEFILTERS Removes all filters from the specified tables or columns.
ROLLING Not a standard DAX keyword. May refer to custom or patterned functions.
ROUND Rounds a number to a specified number of digits.
ROUNDDOWN Rounds a number down.
ROUNDUP Rounds a number up.
SAMPLE Returns a sample of rows from a table.
SAMEPERIODLASTYEAR Returns a table containing a column of dates shifted one year back in time.
SELECTCOLUMNS Creates a new table with selected columns from an existing table.
SENTENCE Capitalizes the first letter of each word in a string.
SETDIFF Returns rows from the first table that are not in the second table.
SETUNION Returns all rows from both tables, removing duplicates.
SIGN Returns the sign of a number.
SIN Returns the sine of an angle.
SINH Returns the hyperbolic sine of a number.
SNUMBER Not a standard DAX keyword. May refer to custom or patterned functions.
SQRT Returns the square root of a number.
STARTSWITH Checks if a string starts with a specified substring.
STDEV Calculates the standard deviation of a set of numbers.
STDEV.P Calculates the standard deviation based on the entire population.
STDEV.S Calculates the standard deviation based on a sample.
SUBSTITUTE Replaces existing text with new text in a string.
SUM Adds all the numbers in a column.
SUM2 Not a standard DAX keyword. May refer to custom or patterned functions.
SUMA Not a standard DAX keyword. May refer to custom or patterned functions.
SUMALL Not a standard DAX keyword. May refer to custom or patterned functions.
SUMMARIZE Creates a summary table of grouped rows.
SUMMARIZECOLUMNS Creates a summary table from multiple tables.
SUMX Evaluates an expression for each row of a table and sums the results.
SWITCH Evaluates an expression against a list of values and returns a result corresponding to the first matching value.
TAN Returns the tangent of an angle.
TANH Returns the hyperbolic tangent of a number.
TDEGREES Converts radians to degrees.
THISCROSSTAB Refers to the current crosstab in certain calculation contexts.
THISGROUP Refers to the current group in certain calculation contexts.
THISROW Refers to the current row in a row context.
TIME Creates a time value.
TODAY Returns the current date.
TOTALALL Not a standard DAX keyword. May refer to custom or patterned functions.
TOTALBETWEEN Not a standard DAX keyword. May refer to custom or patterned functions.
TOTALBY Not a standard DAX keyword. May refer to custom or patterned functions.
TOTALMTD Returns the total for the month to date.
TOTALQTD Returns the total for the quarter to date.
TOTALRAW Not a standard DAX keyword. May refer to custom or patterned functions.
TOTALTAX Not a standard DAX keyword. May refer to custom or patterned functions.
TOTALYTD Returns the total for the year to date.
TOMETERS Converts radians to meters.
TRANSLATE Not a standard DAX keyword. May refer to custom or patterned functions.
TRAY Not a standard DAX keyword. May refer to custom or patterned functions.
TREATAS Applies a filter to a table as if it were the result of a CALCULATE function.
TTR Calculates the number of periods to repay a loan.
TUPLE Not a standard DAX keyword. May refer to custom or patterned functions.
UNICHAR Returns the Unicode character specified by a number.
UNION Combines two tables vertically.
UPPER Converts a string to uppercase.
USERELATIONSHIP Activates a relationship for a single calculation.
VALUE Converts a text representation of a number to a number.
VAR Declares a variable within a DAX expression.
VAR.P Not a standard DAX keyword. May refer to custom or patterned functions.
VAR.S Not a standard DAX keyword. May refer to custom or patterned functions.
VALUES Returns a single column table of unique values from a column or table.
VARVALUE Not a standard DAX keyword. May refer to custom or patterned functions.
VARVERAGE Not a standard DAX keyword. May refer to custom or patterned functions.
VARVERAGE.P Not a standard DAX keyword. May refer to custom or patterned functions.
VARVERAGE.S Not a standard DAX keyword. May refer to custom or patterned functions.
VARVERAGEX Not a standard DAX keyword. May refer to custom or patterned functions.
VARX Evaluates an expression for each row of a table and returns the variance.
WEEKDAY Returns the day of the week for a date.
WEEKNUM Returns the week number of a date.
XIRR Calculates the internal rate of return for a series of cash flows that are not necessarily periodic.
XNPV Calculates the net present value for a schedule of cash flows that is not necessarily periodic.
YEAR Returns the year of a date.
YEARFRAC Returns the fraction of the year represented by the number of whole days between two dates.

Note: Some keywords might be specific to certain versions or contexts, or might be commonly used in conjunction with other functions.