MDX takes a very long time

Hi Community,

In Measures - User defined I have custom measure “Issues overdue”:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issues due] > 0 AND
    (
      isEmpty([Measures].[Issue due date])
        OR
      DateDiffDays(DateParse([Measures].[Issue due date]), Now()) > 0
    )
  )
)

This MDX takes a very long time and if added additional filter by ticket status it will break by timeout.

Is it possible to optimise this measure?

Hi @andrey.mayskiy ,
you can try to use formula “Issues due not in time” from this demo account report: Issues due and overdue - Issues - Jira Demo - eazyBI

CASE WHEN
DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)
OR
DateInPeriod("Today", [Time].CurrentHierarchyMember)
THEN
NonZero(SUM(
    Filter(
     -- filters issues with due dates
      Descendants([Issue].CurrentMember,[Issue].[Issue]),
        DateInPeriod(
          [Issue].CurrentHierarchyMember.get('Due date'),
          [Time].CurrentHierarchyMember)
        AND
        (
        -- if resolution date is empty then compares due date with now
          (IsEmpty([Issue].CurrentHierarchyMember.get('Resolved at')) AND
          DateDiffDays([Issue].CurrentHierarchyMember.get('Due date'), Now()) > 0)
          OR
        -- if resolution date is NOT empty then compares due date with resolution date
          (NOT IsEmpty([Issue].CurrentHierarchyMember.get('Resolved at')) AND
          DateDiffDays(DateWithoutTime([Issue].CurrentHierarchyMember.get('Due date')), 
          DateWithoutTime([Issue].CurrentHierarchyMember.get('Resolved at'))) > 0)
        )
    ),
    [Measures].[Issues with due date] 
))
END

Kind regards,
Gerda // support@eazyBI.com

Hi @gerda.grantina, thanks for your answer, we will try that!

1 Like

Hi @gerda.grantina!

Is it possible to use non operational report in this case? As we need to drill to issues with due date, thanks in advance!