DAX queries

Reporting clients like Power BI and Excel execute DAX queries whenever visuals display in a report, or a field added to a table, and these DAX queries adjust when a filter is applied. The performance analyzer in Power BI Desktop can show you these DAX queries and even run them in the DAX query view.

By using DAX query view in Power BI Desktop or Write DAX queries in Power BI service, you can create and run your own DAX queries. With Microsoft Fabric, you can further increase your productivity with Copilot to write DAX queries in DAX query view of Desktop or web. In addition to Power BI tools, DAX queries can be run in Fabric notebooks using semantic link to read data from semantic models with python, and with the Execute Queries REST API, also available in Power Automate. Other tools such as SQL Server Management Studio (SSMS), Power BI Report Builder, and open-source tools like DAX Studio, also allow you to create and run 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 in measures or simply view the data in your semantic model. INFO and INFO.VIEW DAX functions can also get information about your semantic model, such as a listing of tables, columns, measures, and much more.

Before learning about queries, it is important you have a solid understanding of DAX basics. If you haven't already, be sure to check out DAX overview.

Keywords

DAX queries have a simple syntax comprised of just one required keyword, EVALUATE. EVALUATE is followed by a table expression, such as a DAX function or table name, that when run outputs a result table. Table expressions that output a result table include:

1. Common DAX functions that output a table, such as SUMMARIZE, SUMMARIZECOLUMNS, SELECTCOLUMNS, FILTER, UNION, TOPN, ADDCOLUMNS, DATATABLE, and many others, work with EVALUATE to output a result table.
2. Tables in the model when referenced by name work with EVALUATE to output a result table showing the data in the table. For example, EVALUATE ‘Table name’ can be ran as a DAX query.
3. Measures in the model or any DAX formula, which return a scalar value, work with EVALUATE to show the value as a result table when enclosed in curly braces. For example, EVALUATE {[Total Sales]} or EVALUATE {COUNTROWS(‘Sales’)} can be ran as a DAX query. These are called table constructors.

There are several optional keywords specific to DAX queries: ORDER BY, START AT, DEFINE, MEASURE, VAR, TABLE, and COLUMN.

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

EVALUATE Example

EVALUATE
	'Sales Order'

Returns all rows and columns from the Sales Order table, as a result table. This can be limited with the use of TOPN or FILTER, and sorted with ORDER BY.

ORDER BY (Optional)

The optional ORDER BY keyword defines one or more columns in the query or expressions used to sort query results. Any expression that can be evaluated for each row of the result is valid. Any column in the query itself is also valid.

Sort by column property in semantic models do not apply to DAX query results. If a column should be sorted by a different column in the model, such as in the case of Month Name, the sort by column should also be included in the DAX query to be used in the ORDER BY.

ORDER BY Syntax

EVALUATE <table>
[ORDER BY {<expression> [{ASC | DESC}]}[, …]]

ORDER BY Parameters

ORDER BY Example

EVALUATE
	SUMMARIZECOLUMNS(
		// Group by columns
		'Date'[Month Name],
		'Date'[Month of Year],
		'Product'[Category],

		// Optional filters
		FILTER(
			VALUES('Product'[Category]),
			[Category] = "Clothing"
		),

		// Measures or explicit DAX formulas to aggregate and analyze the data by row
		"Orders", [Orders],
		"Avg Profit per Order", DIVIDE(
			[Total Sales Profit],
			[Orders]
		)
	)

	// DAX queries do not use sort order defined in Power BI, 
	// sort by columns must be included in the DAX query to be used in order by
	ORDER BY 'Date'[Month of Year] ASC

Returns clothing orders and average profit per order by month, in ascending order by month, as a result table.

TOPN does not choose the specified number of rows to return based on the sort order specified in ORDER BY. Instead, TOPN has its own syntax to optionally specify a sort before the top 100 rows are return. ORDER BY only sorts the result table returned by TOPN.

EVALUATE
	TOPN(
		100,
		'Sales Order',
		// The way the data is sorted before the top 100 rows are selected
		'Sales Order'[SalesOrderLineKey], ASC
	)
	// The way the data is sorted for the results
	ORDER BY
		'Sales Order'[Sales Order] ASC,
		'Sales Order'[Sales Order Line] ASC

Returns the top 100 sales orders sorted by SalesOrderLienKey ascending, then sorts the results first by sales order, then by sales order line.

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

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
	'Sales Order'
	ORDER BY 'Sales Order'[Sales Order] ASC
	// Start at this order, orders before this order will not be displayed
	START AT "SO43661"

Returns all columns from the Sales Order table, in ascending order by Sales Order, beginning at SO43661. Rows before this sales order are not included in the result table.

DEFINE (Optional)

The optional DEFINE keyword introduces one or more calculated entity definitions that exist only for the duration of the query. Unlike EVALUATE, there can only be one DEFINE block with one or more definitions in a DAX query. DEFINE must precede the first EVALUATE statement and are valid for all EVALUATE statements in the query. Definitions can be variables, measures, tables¹, and columns¹. 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.

DEFINE MEASURE is a common scenario to build new measures or edit existing measures in a semantic model. When the measure already exists in the model, the DAX query will use the measure DAX formula defined in the query. This is helpful for testing measures with a DAX query before updating the model.

DEFINE MEASURE is also helpful to build additional analysis with DAX formulas for a specific DAX query where you may not have permission to add a model measure or it is not necessary to have it in the model.

DEFINE Syntax

[DEFINE 
    (
     (MEASURE <table name>[<measure name>] = <scalar expression>) | 
     (VAR <var name> = <table or scalar expression>) |
     (TABLE <table name> = <table expression>) | 
     (COLUMN <table name>[<column name>] = <scalar expression>) | 
    ) + 
]

(EVALUATE <table expression>) +

DEFINE Parameters

[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
	VAR _firstyear = MIN('Date'[Fiscal Year])
	VAR _lastyear = MAX('Date'[Fiscal Year])
	TABLE 'Unbought products' = FILTER('Product', [Orders] + 0 = 0)
	COLUMN 'Unbought products'[Year Range] = _firstyear & " - " & _lastyear
	MEASURE 'Unbought products'[Unbought products] = COUNTROWS('Unbought products')
	
EVALUATE
	'Unbought products'
	
EVALUATE
	{[Unbought products]}

Returns the table defined in the DAX query to show unbought products with an additional defined column referencing defined variables. A measure is also defined and evaluated to count the rows of unbought products.

DEFINE
	MEASURE 'Pick a sales measure'[Orders] = DISTINCTCOUNT('Sales Order'[Sales Order])
	MEASURE 'Pick a sales measure'[Customers] = CALCULATE(
			COUNTROWS(Customer),
			FILTER(
				'Sales',
				[Orders] > 0
			)
		)
	MEASURE 'Pick a sales measure'[Orders per Customer] = DIVIDE(
			[Orders],
			[Customers],
			0
		)

EVALUATE
	SUMMARIZECOLUMNS(
		'Date'[Fiscal Year],
		"Orders", [Orders],
		"Customers", [Customers],
		"Orders per Customer", [Orders per Customer]
	)

Returns a table evaluating three defined measures to show the results by fiscal year. All measures also exist in the model, and Orders per Customer is modified in the DAX query.

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.

The Execute Method (XMLA) method has a Parameters Element (XMLA) collection element that allows parameters to be defined and assigned a value. Within the collection, each Parameter Element (XMLA) element defines the name of the parameter and a value to it.

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.

Related content

DAX statements SUMMARIZECOLUMNS