Use “except” in MDX to remove dimension members

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

except 1

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]

except 2

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]

except 3

Nice!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s