Optimizing a complicated measure

Questions & Answers
1

Hi,

We need to measure a KPI defined as : average number of days between the arrival of an issue and the resolution of some subtasks.

I know that it’s a rather convulated way to calculate an “Average time to resolution” …

The start date is defined as :

CASE WHEN NOT IsEmpty([Issue].CurrentHierarchyMember.Get(‘Date entrée TAC’))
THEN [Issue].CurrentHierarchyMember.Get(‘Date entrée PORT’)
ELSE [Issue].CurrentHierarchyMember.Get(‘Date de réception’)
END

If the issue has a ‘Date entrée TAC’, we use ‘Date entrée PORT’, else we use ‘Date de réception’

The ending date is defined as :

CASE WHEN
Count(
Filter(
Filter(
[Issue].[Issue].GetMembersByKeys([Issue].CurrentHierarchyMember.Get(‘Sub-task keys’)),
[Issue].[Issue].CurrentHierarchyMember.get(‘Type Sous-Tache’) = ‘Contrat FTC’
OR [Issue].[Issue].CurrentHierarchyMember.get(‘Type Sous-Tache’) = ‘Contrat FGI’
OR [Issue].[Issue].CurrentHierarchyMember.get(‘Type Sous-Tache’) = ‘Tarif FGI’
),
IsEmpty([Issue].[Issue].CurrentHierarchyMember.get(‘Resolved at’))
)
) = 0
THEN
TimestampToDate(
Max(
Filter(
[Issue].[Issue].GetMembersByKeys([Issue].CurrentHierarchyMember.Get(‘Sub-task keys’)),
[Issue].[Issue].CurrentHierarchyMember.get(‘Type Sous-Tache’) = ‘Contrat FTC’
OR [Issue].[Issue].CurrentHierarchyMember.get(‘Type Sous-Tache’) = ‘Contrat FGI’
OR [Issue].[Issue].CurrentHierarchyMember.get(‘Type Sous-Tache’) = ‘Tarif FGI’
),
DateToTimestamp([Issue].CurrentHierarchyMember.Get(‘Resolved at’))
)
END

We only take the issues having every subtasks on the type ‘Contrat FTC’, ‘Contrat FGI’ and ‘Tarif FGI’ closed.
On this issues, we take the resolution date of the last resolved subtask.

So, I created 2 measures : one for the start date and another for the end date.This works.
BUT, I can’t create another measure calcultaing the number of workdays between those dates (the result is empty).

So, I created a measure including all of the above in :

Avg([Issue].Members,
DateDiffWorkdays(
StartDate,
EndDate,
))

Problem is : it’s slow when it doesn’t just time out.

How could I make it all work faster ?

Thanks !

Regards.

2

Hi Francois!

Firstly, in the Avg(…) function I would suggest filtering the set you are going through. As in the start date, you have specified you are only looking at issues that have ‘Date entrée TAC’, then we can do the same in the Avg(…) additionally adding that issues are created in the current report context

Avg(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  NOT IsEmpty([Issue].CurrentHierarchyMember.Get(‘Date entrée TAC’)) AND
  [Measures].[Issues created] > 0),
DateDiffWorkdays(
  StartDate,
  EndDate
))

This will reduce the set over which the Avg(…) calculation needs to be performed.

Second optimization point here might be to add Cache(…) function around Start and End date calculations - so the dates would be calculated only once in the report context and results would be cached to be re-used in the DateDiff function.

Lauma / support@eazybi.com

1 Like