December MDX Puzzle Solution Revealed - 20 Dec 2000

Using the FoodMart (or FoodMart 2000) Sales cube, how can you determine which male customers with at least a college education bought the most different types of products?

WITH member \[Measures\].\[Prod Count\] AS 'Count( Filter( \[Product\].\[Product
   Name\].Members, NOT IsEmpty(\[Unit Sales\]) ) )'
SELECT \{\[Unit Sales\]\} ON Columns,
   TopCount(\[Customers\].\[Name\].Members, 10,( \[Prod Count\], \[Bachelors
	Degree\], \[M\] ) + ( \[Prod Count\], \[Graduate Degree\], \[M\] )) ON Rows
FROM \[Sales\]

This MDX query works by using a calculated measure called Prod Count, which returns the number of product types that had unit sales. The query uses the TopCount function to return the top 10 customers based on the Prod Count measure. You further limit the customers to males with a college education by adding the Prod Count for males with bachelor's degrees and Prod Count for males with graduate degrees. But watch out! This query takes a while to execute.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.