Skip navigation

October MDX Puzzle Solution Revealed - 19 Oct 2000

From the FoodMart (or FoodMart 2000) Sales cube, list the beer products that have at least 100 unique customers. For each of these popular beers, display the unit sales and the number of customers.

WITH member \[Measures\].\[NumCust\] AS 'Count( Filter(
	\[Customers\].\[Name\].Members, Not IsEmpty(\[Unit Sales\]) ) )'
	SET \[Popular Beers\] AS 'Filter(\[Beer\].Children, \[NumCust\] > 100)'
SELECT \[Popular Beers\] ON columns,
	\{ \[Unit Sales\], \[NumCust\] \} ON rows
FROM Sales

To solve this puzzle, I created a calculated member that returns the number of unique customers for a particular product. Then, I created a set that included the beer products that had more than 100 unique customers.

TAGS: SQL
Hide comments

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.
Publish