May MDX Puzzle Solution Revealed

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.