Except and Validating Dimension Hierarchies

The function takes two sets as its mandatory parameters and removes those members in the first set that also exist in the second. In other words it returns only those members that are not in common between the two sets, but note that members that are unique to the second set are not included in the result set.

Except is a useful function in a variety of situations, for example when selecting all the top selling products except for 1 or 2 you already know are uninteresting or irrelevant, or selecting all the cost centres with high IT costs – except for the IT department.

The simplest example is to have a first set of 2 members and a second set of 1 of those members:
EXCEPT (
{ [Product].[MidasJCCO],[Product].[MidasJCFI] },
{ [Product].[MidasJCCO] }
)
Which returns MidasJCFI, the only member not in common between the two sets.

For the purposes of maximum clarity in the rest of this section only, we will drop the [Product] reference and trust that these product names are uniquely in the Product dimension on our server.

This query returns nothing:
EXCEPT({ [Product].[MidasJCCO],[Product].[MidasJCCO] },{ [Product].[MidasJCCO] })

This example returns all Products, except for MidasJCCO and the Demand Loan family.
{ EXCEPT(
{ TM1SUBSETALL( [Product] )},
{ [MidasJCCO], Descendants([Demand Loan]) }
)}

The optional extra ALL parameter allows duplicates to remain prior to the determination of the difference; i.e. matching duplicates within the first set are discarded, while non-matching duplicates are retained.

A simple example where there are duplicate members in the first set:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI] },
{ [MidasJCFI] }
)
Returns MidasJCCO (because duplicates are discarded without ALL), while:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI] },
{ [MidasJCFI] }
, ALL)
Returns MidasJCCO, MidasJCCO (as ALL allows the duplicate MidasJCCO members to be retained).

Note that ALL has no effect on the following query as MidasJCFI is the only member not in common between the two sets and so this is the only result either way:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI] },
{ [MidasJCCO] }
)
Returns MidasJCFI.

Remember, the members in the first set that also exist in the second are eliminated, hence (both instances of) MidasJCCO is eliminated

So if you were to ask for EXCEPT({Apples, Apples, Oranges, Oranges}, {Apples, Pears}) then the final set would be{Oranges} without ALL and {Oranges, Oranges} with ALL. Because matching duplicates in the first set are eliminated first (that is, duplicates in the first set that match a member in the second set), Apples (the only member in the second set that matches a pair of duplicates in the first set, is eliminated.

To put the fruit down and return to our demo model we can write the equivalent query against products:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI],[MidasJCFI] },
{ [MidasJCCO],[MidasHDBK] }
)
Returns just one MidasJCFI (the equivalent of Oranges above) while:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI],[MidasJCFI] },
{ [MidasJCCO],[MidasHDBK] }
, ALL)
Returns two instances of MidasJCFI.
These results are due to the fact that, in the example with ALL, MidasJCCO is eliminated due to a matching member in set 2, while MidasJCFI is reduced to 1 instance due to the lack of ALL. MidasHDBK has no impact because it could not be subtracted from set 1 as it was not in set 1. When ALL was used in the second example, the two MidasJCCO members were still eliminated due to a match in set 2, and MidasHDBK was still irrelevant, but this time the two MidasJCFI members were left alone due to the ALL allowing duplicates.

A final example, similar to the last but slightly expanded:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI],[MidasJCFI] },
{ [MidasHCBK],[MidasHDBK] }
)
Returns MidasJCCO, MidasJCFI; while:
EXCEPT (
{ [MidasJCCO],[MidasJCCO],[MidasJCFI],[MidasJCFI] },
{ [MidasHCBK],[MidasHDBK]}
,ALL)
Returns MidasJCCO, MidasJCCO, MidasJCFI, MidasJCFI.

Note: the following section does not work in v9.1 SP2, but does work in v9.0. Your mileage may vary.
A particularly clever use of Except is to check a TM1 dimension for a valid structure. A simple query can return a list of members that do not eventually roll up into a particular consolidated member. This could be included in a TI process to automate the consistency checking of dimensions after an update.

This example returns all the members in the dimension that do not roll up into All Products:
EXCEPT (
TM1SUBSETALL( [Product] ),
TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE ))
Modifying this slightly makes it return base-level members that do not roll up into All Products:
EXCEPT (
TM1FILTERBYLEVEL(TM1SUBSETALL( [Product] ), 0),
TM1FILTERBYLEVEL(TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE ), 0))

This query returns members that have been consolidated twice or more at some point under the given consolidated member – this will often mean there has been an accidental double-count.
EXCEPT (
TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE ),
TM1SUBSETALL( [Product] ), ALL)
It will return one instance of the multi-consolidated member for each time it is consolidated greater than once – i.e. if it has been consolidated 4 times then it will return 3 instances.
This is due to the fact that TM1SUBSETALL( [Product] ) will only return one instance of a member that has been consolidated multiple times while the TM1DrilldownMember function will return all the instances. You are reminded that [Dimension].[Member] is actually a shortcut that usually works in TM1 but because the MDX specification allows for member names to be non-unique within a dimension the full address of a member is actually [Dimension].[Parent1].[Parent2]…[Member]. Therefore more specific references to duplicate members may be needed, for example [Product].[Demand Loan].[MidasHCBK] will address a different instance of MidasHCBK than would [Product].[Discount Loan].[MidasHCBK]. In this case, with the Except function, they are treated as if they are different member names altogether.