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:
- Reports the average time a ticket is spending in a set of specific transition states
- 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)
- 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.
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 ) )