DAX Syntax Elements

Applies to: SQL Server Analysis Services, Power BI, Analysis Services Tabular

Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a Microsoft Power BI, Analysis Services Tabular, or SQL Server Analysis Services formula to create custom calculations. DAX is used to define custom calculations for tabular data models.

This document details the syntax elements that make up DAX formulas. Understanding these elements is crucial for writing effective DAX expressions.

DAX Formula Structure

A DAX formula always begins with an equals sign (=). The rest of the formula consists of a function, arguments, and potentially operators and constants. 

=FunctionName(Argument1, Argument2, ... )

Functions

DAX functions perform specific operations on one or more values and return a value. They are categorized into various types:

  • Aggregation Functions: Perform calculations over columns. Examples include SUM, AVERAGE, MIN, MAX.
  • Date and Time Functions: Manipulate date and time values. Examples include TODAY, NOW, DATE.
  • Filter Functions: Modify the context in which an expression is evaluated. Examples include FILTER, CALCULATE, ALL.
  • Financial Functions: Perform financial calculations. Examples include FV, PV, NPV.
  • Information Functions: Return information about the data type or value. Examples include ISBLANK, ISERROR.
  • Logical Functions: Perform logical operations. Examples include IF, AND, OR.
  • Math and Statistical Functions: Perform mathematical operations. Examples include ABS, ROUND, SQRT.
  • Model Functions: Interact with the data model. Examples include RELATED, USERELATIONSHIP.
  • Parent and Child Functions: Work with hierarchical data. Examples include PATH, ANCESTOR.
  • Relationship Functions: Manage relationships between tables. Examples include RELATEDTABLE.
  • Set Functions: Perform set-based operations. Examples include UNION, INTERSECT.
  • String Functions: Manipulate text strings. Examples include CONCATENATE, LEFT, LEN.
  • Time Intelligence Functions: Perform time-based calculations. Examples include DATESYTD, SAMEPERIODLASTYEAR.

Operators

DAX supports the following types of operators:

  • Arithmetic Operators: + (addition), - (subtraction), * (multiplication), / (division).
  • Comparison Operators: = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <> (not equal to).
  • Text Concatenation Operator: & (combines two text strings).
  • Logical Operators: && (AND), || (OR), NOT() (negation).

Constants

Constants are literal values that are not calculated but are directly entered into a formula. These include:

  • Strings: Text enclosed in double quotes (e.g., "Sales").
  • Numbers: Integers or decimals (e.g., 100, 123.45).
  • Booleans: TRUE or FALSE.
  • Dates: Enclosed in double quotes and formatted according to regional settings (e.g., "2023-10-27").

References

DAX formulas can reference columns, measures, and tables within your data model.

  • Column References: Typically in the format 'TableName'[ColumnName].
  • Measure References: Can be referenced directly by their name, or qualified as 'TableName'[MeasureName] in some contexts.
  • Table References: Can be referenced by their table name.

Comments

Comments are ignored by the DAX engine and are used for documentation purposes.

  • Single-line comments: Start with //.
  • Multi-line comments: Enclosed in /* ... */.
// This is a single-line comment
/*
  This is a
  multi-line comment
*/
= SUM( 'Sales'[Amount] ) // Calculate total sales amount

Formatting

DAX formulas are case-insensitive. However, consistent formatting improves readability.

Common Elements and Their Syntax

  • Column Names: Enclosed in square brackets, especially if they contain spaces or reserved keywords (e.g., [Sales Amount]). If the column name is simple, brackets might be optional in some tools but are good practice.
  • Table Names: Often used to qualify column references, especially in complex models or when ambiguities might arise. The format is 'TableName'.
  • Measures: Can be referred to by their name, e.g., [Total Sales].

By mastering these DAX syntax elements, you can build powerful analytical models and generate insightful reports.