June MDX Puzzle Solution Revealed - 13 Jun 2000

Use the FoodMart Sales or FoodMart 2000 Sales cube to answer the following question: In the first quarter of 1997, which three customers bought drink products that had the most unit sales? Read this one carefully because you're not interested in the customers that bought the most drink products; you want the ones that bought drink products that had the most unit sales.

SELECT {[Unit Sales]} ON COLUMNS,  TopCount(    Extract(       Filter({[Drink]}*[Customers].[Name].Members,                    not IsEmpty(Measures.[Unit Sales])),         [Customers]), 3, Measures.[Unit Sales]) ON ROWSFROM [Sales]WHERE ([Time].[1997].[Q1])

This query works by first doing a cross-join of the drink product with the list of all customer names. It then filters these tuples by eliminating the ones with no unit sales. Next, it uses the Extract function to reduce the tuples to only the customer names (removing the drink member from each tuple). Finally, it performs a top count on the remaining customer names to discover the top three based on unit sales.