Count of Issues exceeding value of workdays in transition status by priority

Questions & Answers
1

I’m trying to get a count of issues exceeding certain values for workdays in transition status. The comparison values depend on the priority.
What I’ve tried so far either does not work properly or times out.
Here’s one thing I tried to count issues that are Priority P1-Stopper and exceed 1 day in transition status or are P2-High and exceed 2 days in transition status or are P3-Medium and exceed 5 days in transition status:

Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Transitions from status]>0
  AND (
  ([Priority].[P1-Stopper], [Measures].[Workdays in transition status]) > 1
  OR
  ([Priority].[P2-High], [Measures].[Workdays in transition status]) > 2
  OR
  ([Priority].[P3-Medium], [Measures].[Workdays in transition status]) > 5
  )
)
)

Please let me know a correct formula that will run without timeout. Thanks!

2

I came up with something that work reasonably quickly. If there’s any further optimization, let me know, otherwise what I have will work.

NonZero(
Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
CASE [Priority].CurrentMember.Name
  WHEN "P1-Stopper" THEN
    [Measures].[Workdays in transition status] > 1
  WHEN "P2-High" THEN
    [Measures].[Workdays in transition status] > 2
  WHEN "P3-Medium" THEN
    [Measures].[Workdays in transition status] > 5
  ELSE
    CBool(0)
END
)
)
)
3

Hi @BAK

Happy to see you’ve found a solution!

A formula like this will check if the priority of each issue matches with the priority in the rows/columns before checking the values, which would improve the performance of the measure:

NonZero(
  Sum(
    Filter(
      Descendants([Issue].CurrentMember,[Issue].[Issue]),
      [Measures].[Issue priority] = [Priority].CurrentHierarchyMember.Name
    ),
    CASE WHEN
      CASE [Priority].CurrentMember.Name 
      WHEN "P1-Stopper" THEN 1
      WHEN "P2-High" THEN 2
      WHEN "P3-Medium" THEN 5
      END < [Measures].[Workdays in transition status]
    THEN 1
    END
  )
)

There could be some more improvements depending on the context of your report. Also, please note that the “Workdays in transition status” measure will return the number of workdays for a transition status only after issue has exited this status: Import issue change history

​Best regards,
​Nauris