MDX code snippet Existing

Written by Thomas Ivarsson on 30 April 2010. Posted in MDX

Reposted from Thomas Ivarsson blog with the author's permission.

If you would like to see how many cities that have sold a certain bike model you can start trying to write something like this on the Adventure Works demo cube.

With Member Measures.ProdSold As

Count(Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0))

Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];

You will then get a result like this.

You can also use Count like this with the same result:

With Member Measures.ProdSold As

Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0).Count

Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];

The same value, 102 cities, will be repeated for each country. What you would like to see is the number of cities for each country. The explantion for this behaviour can be found in the SQL Server 2008 MDX Step By Step book page 116. There is a link to this book up to the right in this blog. Also, the solution to this problem I found in the same book.

Before we proceed to the solution lets see how we can move the filter function from the calculated measure and actually use it on rows to see the actual cities that did sell this Bike. I have changed the query to this.

Select {[Measures].[Internet Order Quantity]} on 0,
(Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0)) On 1
From [Adventure Works];

You will get the result below.

All the 102 cities will appear on the rows axis with it's total internet order quantity.

Finally the solution is to add the Existing operator to the calculated measure like this:

With Member Measures.ProdSold As

Count( Existing Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0))

Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];

Finally we will get what we were looking for. Each country will have the number of cities that sold one unit or more of the selected mountain bike.

thomas-ivarsson

Thomas Ivarsson has been working with the MS BI platform since SQL Server 7 in 1999. Presently he is working in the telecom industry in Sweden, with a data warehouse based on SQL Server 2005. From 1999 to 2007 he worked as a consultant also on the three SQL Server BI platforms. During the latest years he has spent most of time on SSAS, Reporting Services, ProClarity and Performance Point. He also has several years experience of the ETL process with DTS and SSIS. During 2008 and 2009 he has been working with introducing data mining in his daily business to see patterns in a service network behaviours. His blog can be found here: http://thomasianalytics.spaces.live.com

Tags: mdx

MDX code snippet Existing

Similar Articles