DAX vs. MDX: An example

SQL Server 2012

I’m often asked about the key differences between DAX and MDX or in more general, the difference between the tabular and the multidimensional model. From my presentation on the PASS Camp 2012 in Germany I’d like to share some of the ideas here.

From the perspective of an expression or query language, one of the most important differences is the concept behind both approaches.

For a cube, we have the concept of a tuple for addressing a cell in the cube space. The axis in the tuple are setting the coordinates. If we have a single tuple, the result is the content of the corresponding cell in the cube. As cube attributes have an All-member which serves as the default (in most cases) if the attribute is omitted from the tuple, we also get aggregated this way. For example, a tuple like

returns the (aggregated) sales amount for the year 2013. Other attributes (for example Product) are on their default members. As you see, there is no need to supply an aggregation function in this case (although MDX has such functions when aggregating values over sets) as the cube contains the definitions about how to aggregate the sales amount. The following sketch illustrates this way, of addressing a value in a cube:

For the tabular model, filters within our pivot table work like filters on the underlying tables. Even with a single selected value, multiple rows can be included in the filter of a table. For example, if you filter the Year the 2013, the underlying date table will be filter to all 365 days of that year. For a given measure all filtered tables are intersected giving the detail values that correspond to the filter. As there are potentially many rows of data after this process, an aggregation function is required to compute the aggregate. This is shown in the following illustration:

If you are more familiar with SQL than with MDX, the concept of filtering and aggregating in DAX will be more familiar to you. In SQL, as in DAX, we’re usually restricting table rows (using the WHERE clause in SQL or the FILTER function in DAX). Then we create groups (using the GROUP BY clause in SQL or the SUMMARIZE function in DAX) and finally we’re computing the aggregates using an appropriate aggregation function (like SUM).

However, many questions that require this process in SQL or DAX can be solved in MDX just by addressing cells. Let my provide you with an example that I frequently use during my MDX trainings: The requirement is to create a computed measure that gives the sales amount of sales on weekend. If you have a strong SQL background your solution in MDX may look like this:

This seems to be a natural approach. Using the descendant function we create a set of all the dates on the selected date member (for example a month, a quarter, a year). We then filter those dates using the filter function, so that only Sunday and Saturday remains in the set. Finally we aggregate the measure sales amount along this set.

Actually this way of approaching this calculation is very similar to SQL or DAX. For example in DAX we would write the same calculation in almost exactly the same way:

Although this looks very similar to the MDX code from above, the MDX code above is close the most complicated solution available. Since the weekday is a cube attribute, we can simply address sales on weekends but using a tuple (ok, a sum of two tuples):

So when writing a DAX query, we rather think

• How do I filter my underlying tables
• What aggregation function do I need

In MDX on the other hand, we rather think

• What axis do I have to address in order to pull my value out of the cube