February MDX Puzzle Solution Revealed - 22 Feb 2001

Using the FoodMart or FoodMart 2000 Sales cube, determine the total profit for each of the top products (based on Unit Sales) for each member card type.

WITH SET CardTypes as 'CreatePropertySet(\[Store Type\].
	\[All Store Type\], \[Customers\].\[Name\].Members, \[Customers\].
	CurrentMember.Properties("Member Card"))'
SELECT CardTypes on COLUMNS,
 TopCount( \[Product\].\[Product Name\].Members, 1, \[Unit Sales\] ) on ROWS
FROM Sales
WHERE (\[Measures\].\[Profit\])

This query uses the CreatePropertySet() function to create a named set with calculated members for each type of member card. The query uses the TopCount() function to determine the product with the most Unit Sales, then displays Profit for this product and each member card type.