ballardchalmers - home

Contact us 01342 410223

Blog

How to return your three best-selling products for each year using MDX and DAX

Tuesday, 22 April 2014

DAX (Data Analysis eXpressions) and MDX (MultiDimensional eXpressions) are the two main BI (business intelligence) query languages for SSAS (Microsoft SQL Server Analysis Services). For most of the time, these two main query languages (DAX and MDX) can return exactly the same results – at present, MDX has the edge on functionality, so some results are not yet possible in DAX, but this will change in the future. A third BI language, DMX (Data Mining eXtensions) is for more specialised work against an SSAS data mining model.

Whether you choose to write queries in DAX or MDX is often a matter of personal preference. You can use both DAX and MDX against an SSAS tabular model, you can also use both against an SSAS multidimensional cube. There is even currently limited support for writing DAX inside MDX!

It is difficult to decide between MDX and DAX as a first-choice query language. Both work against tabular models and multidimensional cubes. Both can return the same results. Of course, both are ultimately much easier and less verbose than SQL to produce meaningful BI results. It is up to you to decide between MDX and DAX as your main query language. Ideally, you should try both. Here are some very broad generalisations, but, they do not apply in every case! MDX has more functionality and is supported by more client tools – this is likely to change in the future. DAX can be faster than MDX at returning the results – although highly-aggregated results may be quicker in MDX. Both are tricky to learn at first if you are from an SQL background – but, eventually they become much more intuitive than SQL. Often DAX is preferable for tabular models and MDX for multidimensional cubes – but not always so! MDX has some limitations when used against a tabular model. DAX has some more fundamental limitations when used against a multidimensional cube – in particular, the multidimensional metadata is not always suitable – DAX likes measures to part of tables (or dimensions) and you are unlikely to have measures in dimensions in a multidimensional database, except in the specific case of fact or degenerate dimensions (even then, the measures in question are very unlikely to be suitable). It is possible to have a multidimensional database in the Standard Edition of SSAS, full SSAS tabular models (as opposed to Power Pivot) require the Business Intelligence or Enterprise Edition of SSAS. A list at the end of this article illustrates client tool support for DAX and MDX.

This blog shows how to use both MDX and DAX to perform a common BI requirement, namely, how to return the three best-selling products for each year. The data is from the Adventure Works 2012 Tabular Model, which you can download from codeplex (you could also easily adapt the MDX examples for the Adventure Works 2012 Multidimensional database – also from codeplex – the DAX examples will not all work against the multidimensional metadata). Some simplifications have been made in the syntax to best illustrate the similarities and differences between MDX and DAX – some of the DAX, in particular, could be better-written from a performance point-of-view. The DAX and MDX queries were all written and executed in the SSMS (SQL Server Management Studio) MDX query editor. They are generally in pairs so you can run the MDX and DAX together and have two result sets at the same time, you will need GO between each query for this to work. Let’s start by looking at sales for each year written in MDX then DAX (where the MDX and DAX results are the same, only one result is shown – where the results are different, the DAX results are shown after the MDX results):

-- MDX sales for 2005 to 2010

select

[Measures].[Internet Total Sales] on columns,

[Date].[Calendar Year].[Calendar Year] on rows

from

[Model]

 

go

 

-- DAX sales for 2005 to 2010

evaluate summarize('Date', 'Date'[Calendar Year], "Sales", [Internet Total Sales])

 

2005      $3,266,373.66
2006      $6,530,343.53
2007      $9,791,060.30
2008      $9,770,899.74
2009      (null)
2010      (null)

Now to hide 2009 and 2010 when there were no sales:

-- MDX non empty, hides years with no sales 2009 2010

select

[Measures].[Internet Total Sales] on columns,

non empty [Date].[Calendar Year].[Calendar Year] on rows

from

[Model]

 

go

 

-- DAX 'Internet Sales' not 'Date', hides years with no sales 2009 –

-- 2010

evaluate summarize('Internet Sales', 'Date'[Calendar Year], "Sales", [Internet Total Sales])

 

2005      $3,266,373.66
2006      $6,530,343.53
2007      $9,791,060.30
2008      $9,770,899.74

The table used by the DAX query has been changed from Date to Internet Sales – in general, in DAX it is better to use a ‘fact’ rather than a ‘dimension’ table in a SUMMARIZE query. Now to list our best-selling products (results have been truncated for space reasons):

-- MDX product sales descending for all time Mountain-200 Black
-- sizes 46 and 42 and Silver 38

select

[Measures].[Internet Total Sales] on columns,

order([Product].[Product Name].[Product Name], [Measures].[Internet Total Sales], desc) on rows

from

[Model]

 

go

 

-- DAX product sales descending for all time Mountain-200 Black
-- sizes 46 and 42 and Silver 38

evaluate summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales])

order by [Internet Total Sales] desc

 

Mountain-200 Black, 46                                   $1,373,469.55
Mountain-200 Black, 42                                   $1,363,142.09
Mountain-200 Silver, 38                                  $1,339,462.79
Mountain-200 Silver, 46                                  $1,301,100.10
Mountain-200 Black, 38                                   $1,294,866.14
Mountain-200 Silver, 42                                  $1,257,434.57
Road-150 Red, 48                               $1,205,876.99
Road-150 Red, 62                               $1,202,298.72
Road-150 Red, 52                               $1,080,637.54

And just the three best-selling products across all time:

-- MDX best 3 products for all time

select

[Measures].[Internet Total Sales] on columns,

topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales]) on rows

from

[Model]

 

go

 

-- DAX best 3 products for all time

evaluate topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales])

order by [Internet Total Sales] desc

 

Mountain-200 Black, 46                                   $1,373,469.55
Mountain-200 Black, 42                                   $1,363,142.09
Mountain-200 Silver,38                                   $1,339,462.79

This time we use the MDX and DAX CROSSJOIN functions to cross join the years with the three best products:

-- MDX crossjoin

-- same three products for every year

-- 2005 missing

select

[Measures].[Internet Total Sales] on columns,

non empty crossjoin([Date].[Calendar Year].[Calendar Year], topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales])) on rows

from

[Model]

 

go

 

-- DAX crossjoin

-- same three products for every year

-- sales for each product is total across all time, and we have
-- 2005, 2009, 2010!

evaluate

crossjoin(all('Date'[Calendar Year]),

topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales])

)

order by 'Date'[Calendar Year] asc, [Internet Total Sales] desc

 

2006      Mountain-200 Black, 46                                   $163,927.86
2006      Mountain-200 Black, 42                                   $159,829.66
2006      Mountain-200 Silver, 38                                  $136,713.69
2007      Mountain-200 Black, 46                                   $679,399.00
2007      Mountain-200 Black, 42                                   $627,269.94
2007      Mountain-200 Silver, 38                                  $613,471.64
2008      Mountain-200 Black, 46                                   $530,142.69
2008      Mountain-200 Black, 42                                   $576,042.49
2008      Mountain-200 Silver, 38                                  $589,277.46

2005      Mountain-200 Silver, 38                                  1339462.7904
2005      Mountain-200 Black, 42                                   1363142.0934
2005      Mountain-200 Black, 46                                   1373469.5482
2006      Mountain-200 Black, 46                                   1373469.5482
2006      Mountain-200 Black, 42                                   1363142.0934
2006      Mountain-200 Silver, 38                                  1339462.7904

The second DAX results are truncated but include 2007 to 2010 as well. Both the MDX and the DAX return the incorrect results! The same three products appear to be the three best-sellers for each year. The MDX result is missing 2005 when there were sales – see our queries earlier on. The truncated DAX results include 2005, but also 2009 and 2010 when there were no sales. Also, the DAX result is showing total sales across all years for each individual year! What really happened in 2005:

-- MDX for just 2005

-- road bikes not mountain bikes, as mountain bikes not sold in 2005

select

[Measures].[Internet Total Sales] on columns,

crossjoin([Date].[Calendar Year].[Calendar Year], topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales])) on rows

from

[Model]

where [Date].[Calendar].[Year].[2005]

 

go

 

-- DAX for just 2005

-- road bikes not mountain bikes, as mountain bikes not sold in 2005

evaluate

crossjoin(filter(all('Date'[Calendar Year]), 'Date'[Calendar Year] = 2005),

calculatetable(topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales]), 'Date'[Calendar Year] = 2005))

order by [Internet Total Sales] desc

 

2005      Road-150 Red, 62             $593,992.82
2005      Road-150 Red, 48             $547,475.31
2005      Road-150 Red, 44             $500,957.80

Both queries return the correct results for just 2005. The reason why the earlier CROSSJOIN results for all years are wrong is due to how MDX and DAX both handle ‘filter context’. Filter context is a difficult concept in MDX (though important) and a very difficult concept in DAX (though vitally important). Filter context warrants a blog (or ten blogs) on its own – maybe for a later blog or blogs. In the meantime, you are referred to the BISM Tabular Model book by Marco Russo, Alberto Ferrari, and Chris Webb. In this blog, I merely show you the solution – by using the MDX and DAX GENERATE functions – which propagate the filter context from the year onto the three best-selling products:

-- MDX generate 1 of 1

-- now 2006 shows road bikes and 2005 is back also with road bikes
-- and 2007 and 2008 show slightly different mountain bikes

select

[Measures].[Internet Total Sales] on columns,

non empty generate([Date].[Calendar Year].[Calendar Year], crossjoin([Date].[Calendar].currentmember, topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales]))) on rows

from

[Model]

 

go

 

-- DAX generate 1 of 2

-- now 2005 shows road bikes not mountain bikes

evaluate

generate(all('Date'[Calendar Year]),

topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales]))

order by 'Date'[Calendar Year] asc, [Internet Total Sales] desc

 

go

 

-- DAX generate 2 of 2

-- hiding zero sales

evaluate filter(

generate(all('Date'[Calendar Year]),

topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales])), [Internet Total Sales] > 0)

order by 'Date'[Calendar Year] asc, [Internet Total Sales] desc

 

2005      Road-150 Red, 62                               $593,992.82
2005      Road-150 Red, 48                               $547,475.31
2005      Road-150 Red, 44                               $500,957.80
2006      Road-150 Red, 48                               $658,401.68
2006      Road-150 Red, 52                               $608,305.90
2006      Road-150 Red, 62                               $608,305.90
2007      Mountain-200 Black, 46                                   $679,399.00
2007      Mountain-200 Black, 42                                   $627,269.94
2007      Mountain-200 Silver, 46                                  $617,531.62
2008      Mountain-200 Silver, 38                                  $589,277.46
2008      Mountain-200 Black, 38                                   $587,517.44
2008      Mountain-200 Silver, 46                                  $579,997.50

At last, we got there. Both the MDX and the second of the two DAX queries return the results we are looking for. The first of the two DAX queries is also largely correct – only it includes 2009 and 2010 for all products, which are all empty.

Finally, here is a summary of where and how you can use DAX and MDX queries in full, or in part (DAX and MDX expressions/calculations have many more options):

SSMS - MDX - in MDX query editor - MDX queries against multidimensional cubes

SSMS - DAX - in MDX query editor - DAX queries against multidimensional cubes

SSMS - MDX - in DMX query editor - MDX queries against multidimensional cubes - but no metadata

SSMS - DAX - in DMX query editor - DAX queries against multidimensional cubes - but no metadata

SSMS - MDX - in MDX query editor - MDX queries against tabular models

SSMS - DAX - in MDX query editor - DAX queries against tabular models

SSMS - MDX - in a DAX query - currently not supported

SSMS - DAX - in a MDX query - limited supported - for calculated measures only

SSRS Report Designer - MDX - in MDX query designer - click the set-square last button (Design Mode)

                                                                                                                              to paste MDX query written in SSMS

SSRS Report Designer - DAX - in DMX query designer - click the set-square last button (Design Mode)

                                                                                                                              to paste DAX query written in SSMS

SSRS Report Builder - MDX - click the set-square last button (Design Mode) to paste MDX query

                                                                                                                              written in SSMS

SSRS Report Builder - DAX - not supported

MDX Studio (unsupported free download) - MDX - for writing MDX queries – does not support

                                                                                                                              tabular models

DAX Studio (unsupported free download) - DAX - for writing DAX queries from Excel – supports

multidimensional, full SSAS tabular, and Power Pivot tabular

Power Pivot - MDX - paste MDX as you import

Power Pivot - DAX - paste DAX as you import

Excel - MDX - Named Sets, Calculated Measures, Calculated Members, also OLAP Pivot Table

                                                                                                                              Extensions (unsupported free download)

Excel - DAX - currently not directly supported

Power View - MDX - currently not directly supported - but supports multidimensional via DAX

Power View - DAX - currently not directly supported - DAX is generated automatically

PerformancePoint - MDX - queries for analytic grids and charts

PerformancePoint - DAX - currently not directly supported

.NET - MDX - fully supported

.NET - DAX - fully supported

 

By Art Tennick

Art Tennick is a Microsoft Business Intelligence trainer, consultant and author based in the UK with over 15 years of BI exposure and the author of 20 published books and hundreds of magazine articles. To contact Art, email art.tennick@ballardchalmers.com or visit him on LinkedIn.

Have a comment? Let us know below, or send an email to enquiries@ballardchalmers.com

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.