DAX Query Syntax

This document provides a comprehensive overview of the DAX (Data Analysis Expressions) query syntax used in SQL Server Analysis Services (SSAS) Tabular models and Power BI. DAX queries are used to retrieve data from a data model, similar to how you would use SQL to query a relational database.

Note: DAX queries are typically executed using the XMLA (XML for Analysis) protocol or through tools like DAX Studio or Power BI Desktop's Performance Analyzer.

Basic Query Structure

A basic DAX query consists of a EVALUATE statement followed by a DAX expression that returns a table.


EVALUATE
    <Table Expression>

The most common table expression is a table name, which simply returns all rows and columns of that table.

Example: Returning all data from a table


EVALUATE
    'Sales'

Common DAX Query Constructs

Filtering Data

You can filter data using functions like FILTER, CALCULATETABLE, or by applying filters directly within the query.

Using CALCULATETABLE

CALCULATETABLE modifies the filter context for a table expression.


EVALUATE
CALCULATETABLE(
    'Sales',
    'Sales'[Year] = 2023
)

Using FILTER

FILTER iterates over a table and returns rows that satisfy a condition.


EVALUATE
FILTER(
    'Sales',
    'Sales'[Product Category] = "Electronics" && 'Sales'[Sales Amount] > 1000
)

Summarizing Data

Use functions like SUMMARIZE, SUMMARIZECOLUMNS, or aggregation functions within ADDCOLUMNS to group and aggregate data.

Using SUMMARIZECOLUMNS

SUMMARIZECOLUMNS is the recommended function for creating summary tables, as it is more flexible and performant.


EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Category],
    'Date'[Year],
    "Total Sales", SUM('Sales'[Sales Amount]),
    "Total Quantity", SUM('Sales'[Quantity])
)

Using SUMMARIZE

SUMMARIZE groups rows by specified columns and calculates aggregated values.


EVALUATE
SUMMARIZE(
    'Sales',
    'Sales'[Region],
    "Average Sales", AVERAGE('Sales'[Sales Amount])
)

Ordering Results

The ORDER BY clause is used to sort the results of a DAX query.


EVALUATE
    'Sales'
ORDER BY
    'Sales'[Sales Amount] DESC

You can specify multiple columns for ordering.


EVALUATE
    'Sales'
ORDER BY
    'Sales'[Region] ASC,
    'Sales'[Sales Amount] DESC

Limiting Results

Use the TOPN or BOTTOMN functions to retrieve the top or bottom N rows based on an expression.


EVALUATE
TOPN(
    10,
    'Sales',
    'Sales'[Sales Amount], DESC
)

You can also use TOPNS for multiple levels of ranking.

DAX Query Operators

DAX supports various operators for calculations and comparisons.

Operator Description Example
+, -, *, / Arithmetic Operators 'Sales'[Sales Amount] * 1.05
=, <, >, <=, >=, <> Comparison Operators 'Sales'[Sales Amount] > 1000
&& (or &&) Logical AND 'Sales'[Region] = "North" && 'Sales'[Sales Amount] > 500
|| (or ||) Logical OR 'Product'[Category] = "Books" || 'Product'[Category] = "Movies"
~ (or ~) Logical NOT ~('Sales'[Region] = "West")
& Text Concatenation 'Customer'[FirstName] & " " & 'Customer'[LastName]

DAX Query Functions

DAX provides a rich set of functions for data manipulation, aggregation, time intelligence, and more. Some commonly used functions in queries include:

For a complete list and detailed descriptions of DAX functions, refer to the DAX Functions reference.

Tip: When building complex DAX queries, it's often beneficial to start with simpler queries and gradually add complexity, testing each step. Tools like DAX Studio are invaluable for writing, executing, and debugging DAX queries.

Best Practices

This document covers the fundamental aspects of DAX query syntax. Explore the related documentation for deeper insights into specific functions and advanced techniques.