Reporter groups exception filter

Questions & Answers
,
1

Hi Community!

I’m trying to write Exception mdx for Reporter dimension, it looks like:

Aggregate(
  Except(
  [Reporter].Members,
  {
   [Reporter].[Group 1],
   [Reporter].[Group 2]
  }
 )
)

It’s working but I’ve got interesting results - for example, I’m trying to show issues created by user not in specific group. With above MDX I’ve got results for created issues with this filter and without. My suggestions that these user have another groups (it could be jira licence groups or other that I’m not point in filter) and it shows results by them.

Could I use any filter with unique count for this? For example:

  • if user have Group 0 and Group 1, show results for Group 0
  • if user have Group 1, show results for Group 0
  • if user have Group 1 and Group 2, show results for Group 1
2

Hi @andrey.mayskiy ,

When addressing [Reporter].Members without specifying the hierarchy level - it also contains the “All reporters” member. Therefore the exception fails.
You might read more about the Except function with the correct and the wrong examples here - Except.

The correct option to exclude specific Reporters would look like this.

Aggregate(
 Except(
  [Reporter].[Reporter].Members,
  {[Reporter].[Group 1],
   [Reporter].[Group 2]}
 )
)

This would work if “Group 1” and “Group 2” are actual core members of the Reporter dimension.

If “Group 1” and “Group 2” are calculated members, you might need another approach.

In that case, you need to retrieve the core members of groups at the lowest level to exclude them from the set of core members of the Reporter dimension.

The option for retrieving all core reporters except those included in the calculated entity “Group 1” might look like this.

Aggregate(
  Except(
    [Reporter].[Reporter].Members,
    DescendantsSet([Reporter].[Group 1], [Reporter].[Reporter])
  )
)

However, the Aggregate function behaves differently when used for a calculated measure within the Measures dimension or a calculated member within any other dimension.

If it is used within a calculated measure - it sums the numerical values, possibly counting members of overlapping sets twice.
If it is used within other dimensions - it creates a set with the unique core members of the lowest level.

Please send your report definition and more details on how you define different Reporter groups to support e-mail to find the best solution for the unique counts of overlapping Reporter groups.

Regards,
Oskars / support@eazyBI.com

3

Hi @oskars.laganovskis , thanks for your answer!

As I understand here we need to point Reporter name [Reporter].[Andrey Mayskiy].Members?

4

Hi @andrey.mayskiy ,

Not exactly.
If you want to exclude just one member of the set, it might look like this.

Aggregate(
 Except(
--total set of members before exclusion
  [<dimension name>].[<dimension level name>].Members,
--one member to be excluded
  [<dimension name>].[<name of the member to exclude>]))

However, if you already have calculated members to be excluded, the previous expression applies.

Regards,
Oskars / support@eazyBI.com