DAX in SQL Server Evaluate Statement to Retrieve Data
By: Haroon Ashraf
Before we start with the Evaluate statement let us quickly understand basic DAX Query Syntax and DAX Formula Syntax. Evaluate is considered a base construct when querying tabular models which means the request is wrapped in Evaluate.
Building Basic DAX Query
A basic DAX query is just like a T-SQL compact mode query.
A DAX query consists of a function and a number of expressions.
According to MSDN an expression in DAX can be one or all of the following:
A value can be a table or table’s column value.
A parenthesis is a special type of operator which is used to give precedence or used for grouping of arguments.
Based on the above facts let's form a simple DAX query syntax:
--DAX Query Syntax with one expression Function(Expression) where Expression can be Expression1,Expression2,..n
This is illustrated as follows:
Let's now replace Expression with a table name:
--DAX Query Syntax replacing Expression with Tabular Model Table Function(Customer)
DAX query syntax with two expressions:
--DAX Query Syntax with two expressions Function(Expression1,Expression2)
DAX query syntax with n expressions:
--DAX Query Syntax with n expressions Function(Expression1, Expression2, …ExpressionN)
Using Evaluate to Retrieve Tabular Data
To retrieve tabular data using DAX query we use the Evaluate clause which takes a table as an expression.
For example to get all the customer tabular data we write the following DAX Query in the DAX Query Editor:
Evaluate is similar to SELECT * FROM in T-SQL.
Ordering Tabular Data by Column
The tabular data can be returned in a particular order by using the Order By clause at the end of DAX query:
Order By (Expression)
We can also order the tabular data Customer by Customer Name using optional Order By clause as follows:
Evaluate(Customer) Order By Customer[Name]
Using Values to Retrieve Tabular Column Data
We can also use the Values() function to display tabular data or single column data.
To write a DAX query to select customer names to be displayed in a descending order use Values() as follows:
EVALUATE(VALUES(Customer[Name])) ORDER BY Customer[Name] DESC
Please note the VALUES() function only displays distinct values (excludes duplicates).