Reporting clients like Power BI and Excel execute DAX queries whenever a field is placed on a report or when a filter is applied. By using SQL Server Management Studio (SSMS), Power BI Report Builder, and open-source tools like DAX Studio, you can create and run your own DAX queries. DAX queries return results as a table right within the tool, allowing you to quickly create and test the performance of your DAX formulas.
Before learning about queries, it's important you have a solid understanding of DAX basics. If you haven't already, be sure to checkout DAX overview.
Keywords
DAX queries have a simple syntax comprised of just one required keyword, EVALUATE, and several optional keywords: ORDER BY, START AT, DEFINE, MEASURE, VAR, TABLE, and COLUMN. Each keyword defines a statement used for the duration of the query.
EVALUATE (Required)
At the most basic level, a DAX query is an EVALUATE statement containing a table expression. At least one EVALUATE statement is required, however, a query can contain any number of EVALUATE statements.
EVALUATE Syntax
EVALUATE <table>
EVALUATE Parameters
Term
Definition
table
A table expression.
EVALUATE Example
EVALUATE
'Internet Sales'
Returns all rows and columns from the Internet Sales table, as a table.
ORDER BY (Optional)
The optional ORDER BY keyword defines one or more expressions used to sort query results. Any expression that can be evaluated for each row of the result is valid.
ORDER BY Syntax
EVALUATE <table>
[ORDER BY {<expression> [{ASC | DESC}]}[, …]]
ORDER BY Parameters
Term
Definition
expression
Any DAX expression that returns a single scalar value.
ASC
(default) Ascending sort order.
DESC
Descending sort order.
ORDER BY Example
EVALUATE
'Internet Sales'
ORDER BY
'Internet Sales'[Order Date]
Returns all rows and columns from the Internet Sales table, in ascending order by Order Date, as a table.
START AT (Optional)
The optional START AT keyword is used inside an ORDER BY clause. It defines the value at which the query results begin.
START AT Syntax
EVALUATE <table>
[ORDER BY {<expression> [{ASC | DESC}]}[, …]
[START AT {<value>|<parameter>} [, …]]]
START AT Parameters
Term
Definition
value
A constant value. Cannot be an expression.
parameter
The name of a parameter in an XMLA statement prefixed with an @ character.
START AT Remarks
START AT arguments have a one-to-one correspondence with the columns in the ORDER BY clause. There can be as many arguments in the START AT clause as there are in the ORDER BY clause, but not more. The first argument in the START AT defines the starting value in column 1 of the ORDER BY columns. The second argument in the START AT defines the starting value in column 2 of the ORDER BY columns within the rows that meet the first value for column 1.
START AT Example
EVALUATE
'Internet Sales'
ORDER BY
'Internet Sales'[Sales Order Number]
START AT "SO7000"
Returns all rows and columns from the Internet Sales table, in ascending order by Sales Order Number, beginning at SO7000.
DEFINE (Optional)
The optional DEFINE keyword introduces one or more calculated entity definitions that exist only for the duration of the query. Definitions precede the EVALUATE statement and are valid for all EVALUATE statements in the query. Definitions can be variables, measures, tables1, and columns1. Definitions can reference other definitions that appear before or after the current definition. At least one definition is required if the DEFINE keyword is included in a query.
The name of a measure, var, table, or column definition. It cannot be an expression. The name does not have to be unique. The name exists only for the duration of the query.
expression
Any DAX expression that returns a table or scalar value. The expression can use any of the defined entities. If there is a need to convert a scalar expression into a table expression, wrap the expression inside a table constructor with curly braces {}, or use the ROW() function to return a single row table.
[1]Caution: Query scoped TABLE and COLUMN definitions are meant for internal use only. While you can define TABLE and COLUMN expressions for a query without syntax error, they may produce runtime errors and are not recommended.
DEFINE Remarks
A DAX query can have multiple EVALUATE statements, but can have only one DEFINE statement. Definitions in the DEFINE statement can apply to any EVALUATE statements in the query.
At least one definition is required in a DEFINE statement.
Measure definitions for a query override model measures of the same name but are only used within the query. They will not affect the model measure.
VAR names have unique restrictions. To learn more, see VAR - Parameters.
DEFINE Example
DEFINE
MEASURE 'Internet Sales'[Internet Total Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
TREATAS (
{
2013,
2014
},
'Date'[Calendar Year]
),
"Total Sales", [Internet Total Sales],
"Combined Years Total Sales",
CALCULATE (
[Internet Total Sales],
ALLSELECTED ( 'Date'[Calendar Year] )
)
)
ORDER BY [Calendar Year]
Returns the calculated total sales for years 2013 and 2014, and combined calculated total sales for years 2013 and 2014, as a table. The measure in the DEFINE statement, Internet Total Sales, is used in both Total Sales and Combined Years Total Sales expressions.
Parameters in DAX queries
A well-defined DAX query statement can be parameterized and then used over and over with just changes in the parameter values.
Reference XMLA parameters by prefixing the name of the parameter with an @ character. Any place in the syntax where a value is allowed, the value can be replaced with a parameter call. All XMLA parameters are typed as text.
Important
Parameters defined in the parameters section and not used in the <STATEMENT> element generate an error response in XMLA.
Parameters used and not defined in the <Parameters> element generate an error response in XMLA.
This learning path introduces Data Analysis Expressions (DAX) and provides you with foundational skills required to enhance semantic models with calculations. It starts by describing Power BI Desktop model structure and how it can be enhanced with DAX calculations. It then describes how you can write DAX formulas and the different types of model calculations, including calculated tables and columns, and measures. Evaluation contexts are introduced, and subsequent lessons describe how to write DAX formulas t
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.