DAX in SQL Server Evaluate Statement to Retrieve Data

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.

Explanation

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:

dax query syntax

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 query results

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]
evaluate query results

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 
evaluate query results

Please note the VALUES() function only displays distinct values (excludes duplicates).

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *