I want to precompute (e.g. in a calculated member) a subset of my fix versions (e.g. all unreleased fix version) as I want to do computations in calculated measures on fix versions and we have a lot of them (most already released) so for performance reasons (report takes 25 secs to generate) it would be nice not to operate on all fix versions but only on a subset.
I found no way to get as a set all unreleased fixversions, I have to compute this set in every measure where I am using it (and I try to use Cache() in the hope that speeds things up).
I tried things like [Fix version.by status].[Status]. ??? how to get the unreleased ones here??
I defined a computed member which creates the set I want by filtering from [Fix Version.by name] (this works) but then trying to access the set like
“[Fix Version.by name].[Unreleased].Members” does not work and
[Fix Version.by name].[Unreleased].Level.Members gives ALL fix versions, not the subset I am looking for.
Side question, I would prefer to work with sets without hierarchy sometimes but I have not found a way to get rid of duplicates, that is why I am using “by name”.
So any help how to reference/access the plain subset of unreleased fix versions without recomputing theme everywhere I am using them would be greately appreciated.
The “Fix Version” dimension has three hierarchies that group versions by most common use cases to make reports faster.
Defautl hierarchy: groups versions by Projects and then by their release status. This hierarchy is helpful if you make a report for a particular project.
By Name hierarchy: groups version with matching names first and only then by projects. This hierarchy is helpful if several projects and teams are working on the same version and you want to get a version overview across projects.
By Status hierarchy: groups all versions by their release status (Archived, Released, and Unreleased) and only then by Projects. This hierarchy is helpful if you want to focus on ongoing and planned releases.
If you would like to run the report only for the unreleased versions, set the dimension “Fix Version” on report pages (or rows) and choose the “Unreleased” status from the “By Status” hierarchy. Here is the dimension description: Jira Core measures and dimensions.
For the calcauted measures, you can use a tuple expression of Unreleased status and the measure of interest. For instance, a tuple expression to count resolved issues that are assigned to Unreleased versions:
More details on calculated measures, tuples, and set expressions are described in the documentation: Calculated measures.
If you would like to work with sets and compare them, this training video, “MDX: Handling sets in calculated measures,” might give some techniques on how to do it: Training videos on specific topics.
I am mostly interested in the second part (in calculated measures).
Currently I use Filter and Sets to compute what you show in the tuple like this:
NonZero(Sum(
Cache(Filter(
Cache(CalculatedChildrenSet([Fix Version.By name].[3 weeks from now Not Released])),
[Measures].[Planned Capacity Per User Selected Milestone (h/w)] > 0
))
,
[Measures].[Planned Capacity Per User Selected Milestone (h/w)]))
I guess I can also do this using a tuple like this
([Measures].[Planned Capacity Per User Selected Milestone (h/w)],
[Fix Version.By name].[3 weeks from now Not Released])
but it seems I am not allowed to do the additional filtering in the tuple.
My main problem is that I have to pre define [Fix Version.By name].[3 weeks from now Not Released] and using caching (otherwise these queries get very slow).
I would prefer to compute the active fix versions (so start date <= now <= release date) as a subset of not released fix versions on the fly (using the [Time].CurrentHierarchyMember.MiddleDate or so) but this is just too slow unless the dates and fix versions is reduced, e.g. by picking only unreleased ones.
Filter(
Cache(CalculatedChildrenSet([Fix Version.By name].[Not Released])), -- have to use this instead of
-- [Fix Version].CurrentHirarchyMember.get('Start Date') > now etc
AND
[Measures].[Planned Capacity Per User Selected Milestone (h/w)] > 0
)
@aherz, in yuor case there are two options how to construct the calculation
Option 1:
In the “Fix Version” dimension, define a new clacued member that would group currently active releases.
Aggregate(
Filter(
--iterate through the versions in the "by status" hierarchy with status unreleased
DescendantsSet([Fix Version.By status].[Unreleased],[Fix Version.By status].[Version]),
--check the version dates compared to current date
DateBetween(
"today",
[Measures].[Version start date],
--if version has no release date; assume it would be tomorrow just to give it some value
CoalesceEmpty([Measures].[Version release date],"tomorrow")
)
)
)
Note, the calculated member is defined in the “By Status” hierarchy; make sure to select it in the calcauted member name when creating it.
Then, yuo create calcauted measures (in Measures) and refer to calculated member “Currently active versions” from Fix Version dimension using a tuple expression
([Measures].[Planned Capacity Per User Selected Milestone (h/w)],
[Fix Version.By name].[Currently active versions])
Option 2:
You can create only a new calcauted measure (in Measures) and incorporate the version filter there.
NonZero(Sum(
Filter(
--iterate through the versions in the "by status" hierarchy with status unreleased
DescendantsSet([Fix Version.By status].[Unreleased],[Fix Version.By status].[Version]),
--check the version dates compared to current date
DateBetween(
"today",
[Measures].[Version start date],
--if version has no release date; assume it would be tomorrow just to give it some value
CoalesceEmpty([Measures].[Version release date],"tomorrow")
)
),
[Measures].[Planned Capacity Per User Selected Milestone (h/w)]
))
I prefer Option 1 as the calculated member “Currently active versions” could be reused in further calculations as well as used in the reports as a page filter if needed.
