How would you write an MDX query that returns the top three promotions for each state in the United States for 1997? Format the result so that 1997 is the only column and the states are on the rows, with the top three promotions nested in each state.
SELECT {[1997]} ON COLUMNS,
GENERATE( [All Customers].[USA].Children, {[Customers].CurrentMember} *
TOPCOUNT( [Promotion Name].Members, 3, [Customers].CurrentMember) ) ON ROWS
FROM [Sales]
Screen A shows the result of this query. The Generate function is the key to this puzzle. The Generate function determines the top three promotions for each state independently; of course, the list of top promotions can be different for each state. The Generate function evaluates its second parameter (the TopCount formula) independently for each set item in the first parameter (the states in the United States). The Generate function unions the results of each evaluation of the TopCount formula.