May MDX Puzzle Solution Revealed - 01 Jun 2001

Using the FoodMart 2000 Sales database, determine which promotion caused the most unique customers to purchase products.

WITH MEMBER \[Measures\].\[Customers Per Promotion\] AS 'Count( 
\{\[Unit Sales\]\} * \[Customers\].\[Name\].Members, ExcludeEmpty )'
SELECT \{ \[Customers Per Promotion\] \} on COLUMNS,
 Order(Except(\[Promotions\].\[Promotion Name\].Members, 
 \{\[Promotions\].\[All Promotions\].\[No
 Promotion\]\}) , \[Customers Per Promotion\], DESC ) on ROWS
FROM Sales

This puzzle was a quiz on the distinct count technique covered in "Distinct Count Queries," May 2001. The \[Customers Per Promotion\] measure is a count of the distinct customers with non-empty unit sales for the currently selected promotion. The MDX query evaluates \[Customers Per Promotion\] for each promotion and returns the ordered list of promotions.

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.