How to write queries in DAX to interrogate SSAS tabular models
Part three of an eight-part series of blogs

As well as creating measures to aggregate data in tabular models using DAX, you can also write queries to extract data - this blog shows you how!

  1. Writing DAX queries
  2. DAX and SQL compared
  3. The EVALUATE command in the DAX query language (this blog)
  4. Using SUMMARIZE to group or aggregate DAX query data
  5. Filtering in DAX queries using CALCULATETABLE or FILTER
  6. Adding columns in a DAX query using ADDCOLUMNS
  7. Combining the results of two or more tables
  8. Other useful DAX functions

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 12 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The EVALUATE command in the DAX query language

This section shows how you can list out columns or rows from a table, without aggregation.

Listing all of the rows in a table

This is the most basic DAX query (the brackets are optional):

-- list out all of the transactions

EVALUATE

( 'Transaction' )

Here's what this would display:

Transaction rows

The query lists out the rows in the Transaction table in your model.

 

Remember that you only need the single apostrophes around the table name because Transaction is a reserved word in SQL Server.  I wish I'd called this table something else!

Sorting rows using ORDER BY

Sorting works just like it does in SQL.  For example, this query would sort transactions with the most expensive first.  Where two or more transactions have the same price, they will be sub-sorted so the one with the lowest quantity sold comes first:

-- show most expensive transactions first (for

-- two or more transactions having same price,

-- sort by quantity sold)

EVALUATE

( 'Transaction' )

ORDER BY

'Transaction'[Price] DESC,

'Transaction'[Quantity]

Here's the start of the results of running this query:

Lilst of transactions

The most expensive transactions come first, starting with the ones where fewest items were bought at a time.

 

Starting at a particular row

You can choose to begin output only from a particular value (this is especially useful when you want to implement paging in a client application):

EVALUATE

( 'Transaction' )

ORDER BY

'Transaction'[Price] DESC,

'Transaction'[Quantity]

-- start from items costing £10

START AT

10

This will display the same results as the previous query, but only start with goods costing £10:

The first items

The first of the 10,650 transactions listed by this query.

 

Because 10 is the first "start at" value, it is assumed to refer to the first order by column (ie the transaction price).

Returning the top N rows

You can use the TOPN function to return only the first batch of rows from a table:

-- show the 5 cheapest products

-- in price order

EVALUATE

TOPN ( 5, Product, Product[FullPrice], 1 )

ORDER BY

Product[FullPrice]

The arguments to the function are as follows:

Argument What it holds
1 The number of rows to show
2 The table to show the rows from
3 The field to sort by
4 0 (descending order), 1 (ascending order).  If omitted, this defaults to descending, unlike the ORDER BY clause which defaults to ascending.

So the above query would return this:

First 5 products

The first 5 products in ascending order (because we used 1 for the order).

 

Note that the TOPN clause doesn't guarantee to return results in any particular order, which is why we need to add the ORDER BY clause above.

Sampling rows

To speed up processing (perhaps for testing purposes), you can return a sample of results: 

-- show 10 sample transactions

-- (one per month)

EVALUATE

SAMPLE(

10,

'Transaction',

RELATED(Calendar[MonthNumber]),1

)

This would return 10 sample rows - yours will be different!

Ten sample rows

The query picks 10 rows from the transactions table, attempting to pick one per month (because there are 13,000+ transactions equally distributed over months, this should succeed). The final argument changes the default sort order from 0 (descending) to 1 (ascending).

 

 

All of the above examples list out the rows in a table; time now to look at how to group or summarise data, using the SUMMARIZE command.

This blog has 0 threads Add post