EazyBI - Aggregate/Except with multiple value fields - Calculated Member

[Leonard Musielak]

Hello,

I am trying to create two calculated members (planned and unplanned) based on a field that can have multiple values.  "Planned" is everything with a "PlannedForVersion" of 'Version 5.2' and "unplanned" is everything else. I am using "Aggregate" and "Aggregate except" to calculate the members.  The problem I am running into is that one issue can be planned for multiple versions. Those items are showing up as both a planned and unplanned items. For example, if an item is planned for both Version 5.1 and Version 5.2 it will show up as both planned and unplanned.  I have included the formulas I am using below.  Can you provide assistance as to how I can filter/remove any issue that includes 'Version 5.2' as a value in the "PlannedForVersion" field from "Unplanned"?

Thanks in advance!

Len

Planned:

Aggregate({

[PlannedForVersion].[PlannedForVersion].[Version 5.2]
}
)
)

Unplanned:

Aggregate(
Except(
[PlannedForVersion].[PlannedForVersion].Members,
{ [PlannedForVersion].[PlannedForVersion].[Version 5.2]
}
)
)

[Martins Vanags -eazyBI-]

Hi Len

For calculated member: Unplanned you could use this code below to aggregate all members that are not blank and that has not Version 5.2

Aggregate(
  Except(
    [PlannedForVersion].[PlannedForVersion].Members,
    { 
      [PlannedForVersion].[PlannedForVersion].[Version 5.2],
      [PlannedForVersion].[PlannedForVersion].[(none)]
      }
  )
)

 
Then you could create a new calculated measure in "Measure" dimension for counting issues that have PlannedVersion, except those that are in Version 5.2 (see code below)

NonZero(
Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentMember.get('Created at'),
    [Time].CurrentHierarchyMember)
    AND 
    [Measures].[Issues created]>0
    AND
    ([PlannedForVersion].[Unplanned],
    [Measures].[Issues created])>0
    AND
    NOT ([PlannedForVersion].[Planned],
    [Measures].[Issues created])>0
    
))
)

And then you could filter your report by this new measure when Measure used in columns.

Please contact support@eazybi.com if you have further questions regarding this!

Kind regards,

Martins Vanags / support@eazybi.com