Performance Issues | Better Solution to calculate the average?

Questions & Answers
1

Hello everyone,

I have the problem that the following MDX formula works, but does not really perform well.
Does anyone have a suggestion to make the MDX more performant?

-- annotations.group = KPI
-- Displays the average time from creation to first assignment for all tickets in the selected period.
Avg( -- Calculation average
  Filter( -- Filtering tickets
    Descendants([Issue].CurrentMember,[Issue].[Issue]), --
    -- issue created in Time dimension period
    DateInPeriod(
      [Issue].CurrentMember.Get('Created at'),
      [Time].CurrentHierarchyMember
    )
    -- Filter all Issues with empty Dead Time
    AND NOT IsEmpty([Issue].CurrentMember.Get('Dead Time'))
    -- Filter all issues with selected Customer Request Type and selected Support Tickets
    AND [Measures].[Issues created] > 0 
  ),
  
  -- Get Dead Time of current issue and convert seconds in minutes
    [Issue].CurrentMember.Get('Dead Time')
) / 60

The basic problem is that I also want to use other dimensions to control which tickets are used for the calculation. Without this, everything loads without a band. With I have corresponding problems.

2

Hi @Luke ,
When you import a numeric custom field like “Dead Time” as a measure in eazyBI, the app during the data import automatically creates several ready-to-use measures without requiring any additional configuration: Jira custom fields

  • “Dead Time created” - calculates the total of Dead Time values across all created issues
  • “Dead Time resolved” - calculates the total across all resolved issues
  • “Dead Time due” - calculates the total across unresolved issues
  • “Dead Time closed” - calculates the total across all closed issues (if you’ve specified closed statuses)
  • “Dead Time with due date” - calculates the total across issues with due dates

Additionally, eazyBI generates hidden measures like “Issues with Dead Time created” which counts the number of issues containing any value in this field.

With these pre-calculated measures available, you can simply create a calculated measure using:

CASE WHEN 
[Measures].[Issues with Dead Time created] > 0
THEN
[Measures].[Dead Time created] / [Measures].[Issues with Dead Time created]
END

This approach is much more efficient than your current MDX formula because it uses pre-aggregated values instead of filtering and calculating at the individual issue level. The measures will automatically respect any dimension filters applied to your report, solving your performance issues while maintaining the ability to control which tickets are included in the calculation.

Best,
Gerda // support@eazybi.com

1 Like
3

Hi Gerda,

Thank you very much. Also for the explanation.
This helps me a lot. It’s a difference like night and day.

1 Like