All eazyBI for Jira eazyBI for Confluence Private eazyBI

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 which should be included in the final set.

Set2_Expression

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

Examples

The following formula will aggregate all priorities without High and Highest

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.

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 a part of 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]}
  )
)

See also