Reports often timeout at month end

Questions & Answers
4

Hi @dwightman,

The performance problems stem from the calculated measure “Count of Subtasks”. It is referenced in all calculated measures selected in the report. See its formula below:

iif([Measures].[Issues with Impact End Date]=0,0,
Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues with Impact End Date]>0
)
)
)

This calculated measure iterates through all issues, returning the number of issues that have a value for the “Issues with Impact End Date” measure. By default, the measure “Issues with Impact End Date” should already return the number of issues, and the calculated measure “Count of Subtasks” shouldn’t be necessary.

One use case where iterating through issues could be necessary is where your report’s dimensions are based on multi-value fields. Are the “Host(s)” or “Responsible Squad(s)” multi-value custom fields? Suppose it is so. In such a case, I recommend improving the calculated measure “Count of Subtasks” formula:

IIf(
  [Measures].[Issues with Impact End Date] > 0,
  Count(Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    IIF(
      [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
      1,
      DateInPeriod(
        [Issue].CurrentHierarchyMember.Get('Impact End Date'),
        [Time].CurrentHierarchyMember
      )
    )
    AND
    [Measures].[Issues with Impact End Date] > 0
  )),
  0
)

Let me know how this goes.
Best,
Roberts // support@eazybi.com