August 2003 MDX Puzzle Solution


Problem: How would you write a query to find the top 10 customers based on unit sales who aren't single and female? Can you do it without listing any of the three other combinations (i.e., single male, married male, and married female)?

Solution: As Listing A shows, I answered this problem by filtering out the single, female customers before ranking the remaining customers with a TOPCOUNT() function. As you can see in the FILTER() function's second parameter, I used the virtual dimensions Marital Status and Gender, along with Unit Sales, to determine whether a particular customer in the top 10 was single and female. To be sure I had the right customers, I also created measures to return the marital status and gender for each displayed customer. I included those measures on the result's columns along with Unit Sales.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.