Except

Set operation, which returns members of Set1 but removes any members that are in Set2.

Syntax

Except(Set1_Expression, Set2_Expression)

Arguments

Set1_Expression	MDX expression that returns a set of members that should be included in the final set.
Set2_Expression	MDX expression that returns a set of members that should be excluded from the first set in the final set.

Examples

All Priorities except Highest and High

The following formula will aggregate all priorities and not include High and Highest members. Use this formula when defining a new calculated member in the Priority dimension.

Aggregate(
  Except(
    [Priority].[Priority].Members,
    {[Priority].[Highest],
     [Priority].[High]}
  )
)

See the example report Issues created high vs low priority this and previous year in our demo account. The calculated member Medium and Low in dimension Priority uses the formula above.

All In Progress category statuses except In Review status

You can also use the Except() function with sets and members from non-default hierarchies. For this example, create a new member in the Status dimension, in the top left drop-down, choose the [Status.Category] hierarchy and use the following formula:

Aggregate(
  Except(
    [Status.Category].[In Progress].Children,
    {[Status.Category].[In Progress].[In Review]}
  )
)

Note that all references to members and levels in the formula should use the same hierarchy as selected in the measure drop-down.

Correctly referencing members

When defining the Set_Expression_1 (the set from which to take out elements), ensure to define the member level to avoid including the dimension default (All level) member. Otherwise, all excluded members would still be included in the calculated member as part of the All level members.

CORRECT

Aggregate(
  Except(
    -- addresses members of a particular level only
    [Priority].[Priority].Members, 
    {[Priority].[Highest],
     [Priority].[High]}
  )
)

INCORRECT

Aggregate(
  Except(
    -- addresses all members, including All Priorities
    [Priority].Members, 
    {[Priority].[Highest],
     [Priority].[High]}
  )
)