April MDX Puzzle Solution Revealed - 11 Apr 2000

In the FoodMart Sales cube, the Name level of the Customers dimension has a member property called Gender. How would you write an MDX query that shows unit sales for male and female customers for drink products sold in California? The trick is to do this task without creating a virtual dimension on the Gender property.

WITH MEMBER \[Measures\].\[Male\] AS 'Sum(CrossJoin(\{\[Unit Sales\]\},
	Filter(\[Customers\].\[Name\].Members,
	\[Customers\].CurrentMember.Properties("Gender") = "M" )))'
MEMBER \[Measures\].\[Female\] AS 'Sum(CrossJoin(\{\[Unit Sales\]\},
	Filter(\[Customers\].\[Name\].Members,
	\[Customers\].CurrentMember.Properties("Gender") = "F" )))'
SELECT \{ \[Male\], \[Female\] \} ON COLUMNS,
  \{ \[Drink\] \} ON ROWS
FROM Sales
WHERE (\[CA\])

This query demonstrates the use of the Filter operation with member properties. The query creates two new calculated members in the Measures dimension. One is the sum of all unit sales for male customers, and the other is the sum of unit sales for female customers.