Unlike DAX and MDX

I am often asked about the key differences between DAX and MDX or in General about the difference between tabular and multidimensional models.

From the viewpoint of the expression or query language, one of the most important differences lies in the interior both approaches.

In Cuba, for addressing cells in space, we have the concept of tuple. Axis in the tuple sets of coordinates. If we only have one tuple, the result is the contents of the corresponding cells in the cube. Since the attributes of the cube there is an element of All, which is the default (in most cases), if the attribute was not included in the tuple, then we perform aggregation as if he was there. For example, the following returns a tuple (aggregate) net sales for 2013

the

in(Date.Calendar.[Calendar Year].&[2013], Measures.[Internet Sales Amount])

Other attributes (Product, for example) are at the level of the default item. As you can see, in this case, there is no need to specify the aggregate function (despite the fact that in MDX there is a function to aggregate values in the sets), because the cube "knows" how to aggregate sales. The following sketch illustrates the method of addressing values in the cube:



For a tabular model, filters in the pivot table work as normal filtering of the base tables. Even if you select one value in a filter table can be assigned to several elements. For example, if you apply a filter in 2013, the base date table is filtered for all 365 days of the year. The results will be crossing all the other tables with the filtered measure. Here we need the aggregate function to calculate the result, because this operation can potentially return many rows of data. This is shown in the following picture:



If you are more familiar with SQL than MDX, the concept of filtering and aggregation in DAX will be more clear. In SQL, as in DAX, we usually limit the table rows (using Where clause in SQL or the FILTER function in DAX). Next, we execute the grouping (using GROUP BY in SQL or the function SUMMARIZE in the DAX), and in the end we calculated the aggregates using the appropriate aggregation function (such as SUM).

However, many tasks require such operations to SQL or DAX, can be solved in MDX only by addressing cells. Let me introduce an example that I often use during lectures. MDX: you want to create a calculated measure that shows the volume of sales during the weekend. If you hardened the SQL box, the decision on the MDX would look something like this:

the

Aggregate( 
filter( 
descendants( 
[Date].[Calendar].currentmember, 
[Date].[Calendar].[Date] 
) 
, 
[Date].[Day of Week].currentmember IS [Date].[Day of Week].[Sunday] 
or [Date].[Day of Week].currentmember IS [Date].[Day of Week].[Saturday] 
) 
,[Measures].[Internet Sales Amount] 
)

This approach seems natural. Using a subsidiary function, we create a set of all dates for the selected item (e.g. month, quarter, year). Next, we filter out this set with a Filter function so that the array remains only Saturday and Sunday. Finally, we aggregate the sales for this set.

In fact, this method is very similar to SQL or DAX. For example, in DAX, we would have made this calculation in much the same way:

the

evaluate( 
summarize( 
filter( 
'Internet Sales' 
, related('Date'[Calendar Year])=2007 
) 
, 'Date'[Month] 
, "Umsatz" 
, Sum('Internet Sales'[Sales Amount]) 
, "UmsatzWE" 
, Calculate( 
Sum('Internet Sales'[Sales Amount]) 
, Or( 
'Date'[Day Name Of Week]="Sunday" 
, 'Date'[Day Name Of Week]="Saturday" 
) 
) 
) 
)

However, despite the fact that this code DAX is very similar to the MDX code that we reviewed just above, still represented MDX version is almost the most difficult of the possible solutions. Since "business day" — cube attribute, we can simply refer to sales on the weekends, but with the help of a tuple (well, okay — the sum of the two tuples):
the

([Measures].[Internet Sales Amount], [Date].[Day Name].[Sunday]) 
+ 
([Measures].[Internet Sales Amount], [Date].[Day Name].[Saturday])

Thus, when writing a DAX query, we rather think

the

the
• How to filter the base table
the
• Which aggregation function to use

In MDX, in turn, we rather think

the

the
• which axis I to invoke to retrieve the desired value from the cube

Article based on information from habrahabr.ru