Project Botticelli

Status message

See the new version of this article on Tecflix.com! Why? Read our announcement.

Querying with DAX Purchase this course

30 January 2013 · 4 comments · 13315 views

Data Analysis Expressions as a Query Language

Querying with Data Analysis Expressions using DAX Studio by Marco Russo

The Data Analysis Expressions (DAX) language is commonly used to define measures and calculated columns in PowerPivot and in tabular models. However, you can also write powerful queries in DAX, and in this 46-minute video Marco Russo, mentor and book author from sqlbi.com, introduces the tools, such as the free DAX Studio, and the necessary new syntax, showing it to you in 7 detailed demos.

The syntax of a DAX query is based on the EVALUATE statement. It is simple, and any DAX expression returning a table can be used in a DAX query by using EVALUATE. You can add local measures, that are valid only within the query, by using DEFINE, and you can control the sort order by using ORDER BY. In the EVALUATE demo you will see how a simple query that returns all the rows and the columns of a table can be adjusted to control its sort order, and the first row returned, by using ORDER BY and START AT statements.

DAX has several other functions that are useful mainly in DAX queries, all of which are discussed by Marco and shown in his demos. The ROW function returns a table made of only one row, that is composed of a number of columns, which need to be specified as the parameters—this is very useful for testing measures while developing your models. The CALCULATETABLE and FILTER functions apply a filter to data that you want to return in a query. In order to change the projection of data in columns of the resulting table, you can use ADDCOLUMNS. It adds new columns to a table expression, writing the DAX expression that will be evaluated row by row, for each added column. SUMMARIZE (with the optional ROLLUP) groups data into a smaller number of rows in the result, and it is similar to the GROUP BY behaviour of a SELECT statement in SQL. Even if you write queries in MDX, you can still use DAX expressions in them. The MDX query syntax can include a definition of local DAX measures, which have the same syntax, and the scope, of a local measure, in a DAX query.

Caching affects query performance: all queries are processed by the formula engine, which forwards requests to the storage engine—xVelocity—in order to retrieve raw data. Complex calculations happen at the formula engine level, whereas storage engine can perform only simple operations, but in a very scalable way. In case you use DAX, there is only one cache level, which is the storage engine itself (xVelocity). However, by using MDX you have two levels of cache: at both the formula engine and the storage engine levels. However, performance of DAX is usually better, because of better query execution plans which DAX generates.

Make sure to watch the remaining videos in our series on DAX and gain advanced skills that will help you get the most out of this powerful Microsoft language for analytics.

Log in or purchase access below to the premium version of this content.

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

Comments

Iman Ahmadi · 26 February 2013

Hi Marco, I have installed Excel 2013 64bit with DAX Studio 64bit but still can't use it with my PowerPivot data model even when I create a Pivottable. Any suggestions!
Thanks very much

Marco Russo · 26 February 2013

DAX Studio is still not compatible with Excel 2013 by now, it works only with Excel 2010. We are working on the new version that I hope will be released in March and will support Excel 2013, too.

Iman Ahmadi · 28 February 2013

Thanks so much.

Iman Ahmadi · 10 April 2013

Hello Marco, is there any other tools beside DAX Studio that we can use?
Thank you.