Need Calculated field that shows the number of issues that a custom field has a certain value

We are looking to measure the the number of tickets that get reopened in a given sprint.

Everytime a ticket goes into this status, automation for jira automatically updates a custom checkbox field (called reopened) to “Yes”

This way we can then report on the number of tickets being reopened.

I have the following calculated member, but the results are incorrect when I compare to jira


Results should be 85

I also tried the following calculated members syntax with no luck

count([Measures].[Issue Reopened],ExcludeEmpty)

Hi @jeffb

You may want to try using another approach: counting issues having the transitions to. “Reopen” during sprint time, instead of counting issues with this value “Yes”.
For that, use either transition status “Reopened” (if you have such) or transitions that are considered as reopening accordingly to your workflow (Done => Open, Done => In Progress, etc.) together with measure "Transition to status issues count".

  1. If you have a particular status Reopened, you may create a measure with a tuple:
([Measures].[Transitions to status issues count],
 [Transition Status].[Reopened])
  1. Otherwise, use transitions you consider as reopening. Start with creating a calculated member in Transition dimension where you aggregate all reopening transitions, for instance, as the following (I name the calculated member “Reopened transitions”):
Aggregate({[Transition].[Done => In Progress],
   [Transition].[Done => To Do],
   [Transition].[Done => Review]})

Use your transitions!
Finally, use this calculated member in the measure with the code:

([Measures].[Transitions to status issues count],
 [Transition].[Reopened transitions])

Measure “Transitions to status issues count” would take into account only transitions happened during the selected sprint.

Ilze /