This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Writing DAX queries DAX and SQL compared The EVALUATE command in the DAX query language (this blog) Using SUMMARIZE to group or aggregate DAX query data Filtering in DAX queries using CALCULATETABLE or FILTER Adding columns in a DAX query using ADDCOLUMNS Combining the results of two or more tables Other useful DAX functions This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

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!

Writing DAX queries
DAX and SQL compared
The EVALUATE command in the DAX query language (this blog)
Using SUMMARIZE to group or aggregate DAX query data
Filtering in DAX queries using CALCULATETABLE or FILTER
Adding columns in a DAX query using ADDCOLUMNS
Combining the results of two or more tables
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

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:

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:

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

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

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:

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!

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.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

The EVALUATE command in the DAX query language

Listing all of the rows in a table

Sorting rows using ORDER BY

Starting at a particular row

Returning the top N rows

Sampling rows

Head office

London

Manchester