Reporting tools such as Power BI and Excel perform DAX queries automatically when you add a field to a report or apply a filter. However, you can also craft and execute your own DAX queries using tools like SQL Server Management Studio (SSMS), Power BI Report Builder, and open-source solutions like DAX Studio. These tools provide the capability to create and run custom DAX queries, and they display query results in tabular form directly within the tool. This functionality enables you to efficiently develop and assess the performance of your DAX formulas.
Keywords
DAX queries utilize a straightforward syntax consisting of a mandatory keyword, EVALUATE, along with various optional keywords like ORDER BY, START AT, DEFINE, MEASURE, VAR, TABLE, and COLUMN. These keywords serve to specify statements that apply throughout the query's execution.
Evaluate
In its simplest form, a DAX query consists of an EVALUATE statement that includes a table expression. While at least one EVALUATE statement is mandatory, it's worth noting that a query can include multiple EVALUATE statements.
Evaluate Syntax
EVALUATE <table>
Evaluate Example
EVALUATE
'Internet Sales'
Returns all rows and columns from the Internet Sales table, as a table.
Order By
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 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
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 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
The DEFINE keyword in DAX queries allows you to create one or more temporary definitions for calculated entities. These definitions are only applicable during the execution of the query and exist within the scope of the entire query. You can define variables, measures, tables, and columns using the DEFINE keyword. These definitions can reference each other, whether they appear before or after in the query, providing flexibility in constructing complex queries. If you include the DEFINE keyword in your query, you must have at least one definition within it.
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 Example
DEFINE Example
DAXCopy
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.
SWETA SARANGI