Transitions from status counted multiple times when using calculated member aggregate function

Questions & Answers
,
1

I have a problem calculating median days in transition status. I use this custom measure:

Median(Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
([Measures].[Transitions from status],[Time].CurrentHierarchy.DefaultMember) > 0
),
[Measures].[Days in transition status])

Then in columns I also add “Transition status” so I can selecte a specific Transition status.

Some issues are calculated with a value which is a multiple of the actual “Days in transition status”. the factor is the same as the value of “Transitions from status” measure.

image
image3187×548 135 KB

It looks like this behaviour is cause by the use of such a “calculated member”

Aggregate({
[Project].[P1].[API],
[Project].[P1].[Web]
})

What I want to accomplish with the calculated member is to get all Issues in Project P1 which have component API or Web,
But now in my measure “Days in transition status” is summed up not per issue, but by issue/component.

Can I build the “calculated member” in another way so the measures are not summed up?

2

After changing the calculated measure as shown below the result looks OK now.

Expected:

  1. take median of all “Days in transition status” (issue can be more than once in transition status)
    → ( [Measures].[Days in transition status], [Time].CurrentHierarchy.DefaultMember )
  2. use measure only for period when issue was the last time in transition status
    → see Filter
  3. do not sum up if report filter selects multiple components and issue is linked to this multiple components (this is the problem why I created the post initially)
    → solved by divide with ( [Measures].[Transitions from status], [Time].CurrentHierarchy.CurrentMember)
NonZero(
  Median(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      DateInPeriod(
        (
          [Measures].[Transition from status last date],
          [Transition Status].CurrentMember,
          [Time].CurrentHierarchy.DefaultMember
        ),
        [Time].CurrentHierarchyMember
      )
    ),
    ( [Measures].[Days in transition status], [Time].CurrentHierarchy.DefaultMember ) /
    ( [Measures].[Transitions from status], [Time].CurrentHierarchy.CurrentMember)
  )
)

The solution works, but the performance is very bad. Is there some better (more performant) way to get the same result?

3

Hi @DiBe,

If you are looking for median days in transition status for the specific Components, you can try to import the Component field separately from the Project dimension. Then you can use its property inside the Filter() function, escaping the multiplication of the multi-value field in pages. See the eazyBI documentation for more information on importing Component as a JavaScript calculated custom field - https://docs.eazybi.com/eazybijira/data-import/custom-fields/javascript-calculated-custom-fields#JavaScriptcalculatedcustomfields-Component.

The calculated measure then could look similar to the one below:

NonZero(Median(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    (CoalesceEmpty([Issue].CurrentHierarchyMember.Get('Component'),'') MATCHES 'MacOS'
    OR 
    CoalesceEmpty([Issue].CurrentHierarchyMember.Get('Component'),'') MATCHES 'Linux')
    AND
    DateInPeriod(
      ([Measures].[Transition to status last date],
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember
    )
  ),([Time].CurrentHierarchy.DefaultMember,[Measures].[Days in transition status])
))

Best,
Roberts // support@eazybi.com