DAX in SQL Server Evaluate Statement to Retrieve Data

By: Haroon Ashraf

Overview

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:

1. Constant
2. Operator
3. Value

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.

Evaluate(table)

For example to get all the customer tabular data we write the following DAX Query in the DAX Query Editor:

Evaluate(Customer)

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).