Cubing — Except function (MDX)

The Except function takes two sets of tuples and returns a set that consists of the members of the first set that are not members of the second set.

Returns: A set of tuples.

Syntax


>>-Except--(--SetExpression1--,--SetExpression2--+--------+--)-><
                                                 '-,--ALL-'

Parameters

SetExpression1: Specifies the set from which to exclude members.
SetExpression2: Specifies the set that contains the members to be excluded from the first set.
ALL: Specifies that the function retain duplicates within the first set. If you do not specify ALL, the function excludes duplicate members of the first set from the result set.

Example

The following example uses the Except function to retrieve the sales amount for all product departments with a name containing the word "GIRL" but not the word "TEEN."

Query

SELECT {[Price Analysis].[Measures].[Sales Amount]} 

   ON AXIS(0), 

   {Except(Filter([Price Analysis].[Product].[All Products].Children, 

   InStr([Price Analysis].[Product].CurrentMember.Name, "GIRL") > 0),

   Filter([Price Analysis].[Product].[All Products].Children, 

   InStr([Price Analysis].[Product].CurrentMember.Name, "TEEN") > 0))} 

   ON AXIS(1)

FROM [Price Analysis]

Result

Product	Sales Amount
GIRLS JUNIORS	37848.56
GIRLS SCHOOL APPAREL	38151.76