All eazyBI for Jira eazyBI for Confluence Private eazyBI

Except

Set operations which returns members of set1 but removes any members that are in set2.

Syntax

 Except( Set_Expression_1 , Set_Expression_2 )

Arguments

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

Examples

Following formula will aggregate all priorities without High and Highest

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

See 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