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]
}
)
)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.