Calculated member excluding other calculated member

Questions & Answers
1

Hi team,

I am trying to have two different calculated members for our Reporters. One should include our internal team, the other everyone should is not internal.

I have already defined the first calculated member like this (TeamA):

Aggregate(
    {
      [Reporter].[HandsomeGuy],
      [Reporter].[Joe McJoe],
      [Reporter].[Greta McJoe],
      ...
    }
)

Now I want to define the second calculated member based on the first one; so if we need to do changes only need to modify one calculated member, not both.

I tried to do it in several ways, none of them worked:

--- NOT WORKING
Aggregate(
  Except(
    -- set of all reporter
    [Reporter].[User].Members,
    -- set of reporters which should be excluded
    [Reporter].[TeamA]
))

--- NOT WORKING
[Reporter].[All Reporters] - [Reporter].[TeamA]

I also tried to use Exclude function, but seems it does not allow to use sets to filter, at least I did not found the way.

I am sure something like this should be really common, even though I did not found the solution yet. Any clues on how to solve this please?

2

@Aya1990 intrested in this topic

3

Hi, @guillermo.esteban

Try this:

Aggregate(
  Except(
     [Reporter].[User].Members,
     {[Reporter].[HandsomeGuy],
      [Reporter].[Joe McJoe],
      [Reporter].[Greta McJoe]}
 )
)
4

Hi @Erik1

Thank you. I am aware I could define this new calculated member the way you propose.

But I would want to avoid repeating the same list in both calculated members.

5

Hi, @guillermo.esteban

Try this:

Aggregate(
   Except([Reporter].[User].Members, ChildrenSet([Reporter].[TeamA]))
)
2 Likes
6

Greatly appreciated Erik, that works like a charm.
Many thanks.

1 Like