MDX code snippet Existing
Posted by thomasivarssonmalmo on April 30, 2010
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))
([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];
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
([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];
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];
(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))
([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];
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.I Microsoft BI & Analytics 
SQL Server 2008 SP2 et autres sucreries « La BI ça vous gagne! said
[…] : Existing, Count et Filter via Thomas Ivarsson. Il n’explique pas assez à mon gout, mais en jouant avec ses requêtes on comprend […]
Naveen Das said
I have been intrugued by the article and have been looking into improving the query as suggested by you. I have found that using EXISTS function gives better performance.
With Member Measures.ProdSold As
Count( Exists([Customer].[City].[City],
{[Customer].[Customer Geography].CurrentMember}*{[Product].[Product Categories].[Product].&[575]},”Internet Sales”))
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];