Microsoft Learn

DAX Syntax

Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX provides a collection of functions, operators, and constants that can be combined to build formulas to perform custom calculations and queries on data in tabular models.

Core Concepts

DAX formulas consist of several key components:

  • Functions: Pre-defined formulas that perform calculations. DAX includes over 200 functions, categorized by their purpose (e.g., aggregation, date and time, financial, logical, text, time intelligence).
  • Operators: Symbols that specify the type of calculation or comparison to perform (+, -, *, /, &, =, <, >).
  • Constants: Literal values that are not calculated (e.g., numbers like 10, strings like "Total Sales", dates like 2023-10-27).
  • Variables: Used within a formula to store intermediate results, which can improve readability and performance.
  • Syntax Elements: Parentheses to enclose arguments, commas to separate arguments within a function, and colons for range references.

Basic Formula Structure

A typical DAX formula starts with an equals sign (=) and includes a function name followed by parentheses containing its arguments. Arguments can be other functions, column references, or constants.

Example: Simple Summation

To calculate the total sum of a column named 'SalesAmount' in a table named 'Sales', you would use the SUM function:

Total Sales = SUM(Sales[SalesAmount])

Example: Filtering and Aggregation

To calculate the total sales for a specific year (e.g., 2023), you can use the CALCULATE function to modify the filter context:

Sales 2023 = CALCULATE(SUM(Sales[SalesAmount]), DimDate[Year] = 2023)

Key DAX Functions

Here are some of the most commonly used DAX functions:

Aggregation Functions

  • SUM: Adds all the numbers in a column.
  • AVERAGE: Calculates the average of numbers in a column.
  • MIN: Returns the smallest value in a column.
  • MAX: Returns the largest value in a column.
  • COUNT: Counts the number of rows containing numbers.
  • COUNTA: Counts the number of non-blank rows.

Logical Functions

  • IF: Checks a condition and returns one value if TRUE, another if FALSE.
  • AND: Returns TRUE if all arguments are TRUE.
  • OR: Returns TRUE if any argument is TRUE.

Text Functions

  • CONCATENATE: Joins two or more text strings.
  • LEFT, RIGHT, MID: Extracts characters from a string.
  • LEN: Returns the number of characters in a text string.

Time Intelligence Functions

These functions are crucial for performing time-based calculations like year-to-date, month-over-month, etc.

  • TOTALYTD: Calculates the total of an expression for the year to date.
  • SAMEPERIODLASTYEAR: Returns a set of dates in the previous year, shifted so that the dates correspond to the input dates.
  • DATEADD: Returns a table containing a column of dates that represents a sequence of dates before or after the specified dates.

Time Intelligence Example: Year-over-Year Growth

To calculate the year-over-year sales growth:

Sales YoY Growth % =
VAR CurrentYearSales = SUM(Sales[SalesAmount])
VAR PreviousYearSales = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR('DimDate'[Date]))
RETURN
    DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales)

Filter Context and Evaluation Context

Understanding how DAX evaluates formulas is key. DAX formulas are evaluated in two main contexts:

  • Filter Context: The set of filters applied to the model before a DAX expression is evaluated. This can come from slicers, filters in visuals, and filter arguments within DAX functions (like CALCULATE).
  • Row Context: The current row being processed in a table. Row context is often created by iterator functions (e.g., SUMX, AVERAGEX) or by relationships.

Important Note on Iterators

Iterator functions (like SUMX, AVERAGEX, FILTER) are powerful. They iterate over each row of a table, evaluate an expression for that row, and then perform an aggregation on the results. This is different from simple aggregation functions that operate on an entire column at once.

Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

Operators in DAX

DAX supports several types of operators:

  • Arithmetic: +, -, *, /
  • Comparison: =, <, >, <=, >=, <>
  • Text: & (concatenation)
  • Logical: AND, OR, NOT

Common Syntax Patterns

  • Column References: Always use the format 'TableName'[ColumnName].
  • Table References: Use the format 'TableName'.
  • Measures: Measures are DAX formulas that produce a single scalar value. They are typically defined in the model and then used in visuals. They don't have an explicit table name in their definition, but they are often stored in a dedicated "Measures" table.

Performance Tip

Avoid iterating over large tables unnecessarily. Use CALCULATE to modify filter context where possible, as it's generally more efficient than row-by-row iteration for simple aggregations.

Further Resources

For comprehensive details and advanced usage, refer to the official DAX documentation and the DAX function reference.

In This Section

Related Topics