In this post I will explain how the “except” function works in MDX.

I have a basic query that looks like this

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Subcategory].[Subcategory] ON 1
FROM [Adventure Works]

And the output will look like this

But the customer wants me to remove bikes from the resultset. To do this I extend my query to use the “filter” function.

	
SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY Except (
  [Product].[Subcategory].[Subcategory],
  { [Product].[Subcategory].&[2], [Product].[Subcategory].&[1], [Product].[Subcategory].&[3] }
) ON 1
FROM [Adventure Works]

Lets sort the resultset using the order function

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY Order (
  Except (
    [Product].[Subcategory].[Subcategory],
    { [Product].[Subcategory].&[2], [Product].[Subcategory].&[1], [Product].[Subcategory].&[3] }
  ),
  [Measures].[Internet Sales Amount],
  DESC
) ON 1
FROM [Adventure Works]

Nice!!