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.
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.