Performance issues with calculated measure

Questions & Answers
1

Hello,

I’m reporting against a set of very specific issues. To solve this request I have created a calculated measure, but this has been broken by adding extra filtering and at the moment I’m facing performance and time out issues.

I was hoping to get some guidance on steps to take to make this query more efficient, and as such avoid running into a time-out issue.

Problem statement / request:
For a rather small project with less than 1000 tickets, create a report which:

  1. Reports the average time a ticket is spending in a set of specific transition states
  2. For this report, only include the tickets which have been created AFTER Oct-13 2022 (without changing the import of the Jira project into EazyBI - for other reports I need the full set)
  3. Only include the tickets which are currently in issue type “Technical Epic” or “Technical story” (when using the attribute Issuetype, I also get a lot of tickets which used to be of this issuetype in the past, but have been changed in bulk. I only want the issues which are currently of this issuetype - tickets which have been moved to another issue type should be excluded.

Remark: for the initial request, I didn’t consider the impact of changed issue types - as such I didn’t include any filtering in the calculated measure. This solution was working fine. Adding the extra filters related to the issue type broke the report and results in a time-out (same project, same data-set)

Any recommendation to improve the performance or do this more efficient would be very welcome.

Calculated measure:

NonZero(Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateBetween(
    [Measures].[Issue created date],
    '13 Oct 2022',
    'today'
  ) AND
  (([Measures].[Transition from Technical Epic]
)>0
OR
([Measures].[Transition from Technical story]
)>0
) --filters only actual epics and technical stories)
  ),
  CASE WHEN
  [Measures].[Transitions from status]>0
  THEN
  Sum((
    [Measures].[Days in transition status],
    [Time].CurrentHierarchy.DefaultMember
  ))
  END
)
)
2

Hi @Stijn
​Thanks for posting your question!
​I recommend trying the formula below, it will filter out the issues based on their current issue types. However, please double-check if the syntax/spelling is correct for the Issue types “Technical Epic” and “Technical story”.
​If this still doesn’t improve the performance, please reach out to us at support@eazybi.com sharing your report definition (Export and import report definitions) and referring to this community post.

NonZero(
  Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateBetween(
    [Measures].[Issue created date],
    '13 Oct 2022',
    'today'
  )
    AND
    (
    [Measures].[Issue type] = "Technical Epic" --check syntax
    OR
    [Measures].[Issue type] = "Technical story" --check syntax
    )
  )
  ,
  CASE WHEN
  [Measures].[Transitions from status]>0
  THEN
  Sum((
    [Measures].[Days in transition status],
    [Time].CurrentHierarchy.DefaultMember
  ))
  END
  )
)

Best wishes,

Elita from support@eazybi.com

3

Thanks a lot - I’ve updated my reports and compared both next to each other: values and returned tickets are identical, but performance improved significantly - problem considered as resolved!

1 Like