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.