Exclude issues that were in a certain status

Hi, I want to create a calculated member that excludes issues that were/are in some statuses (eg: Closed, Cancelled etc).
I saw this one, but my issues can transit after Cancelled aswell, so this partially helps me:

Aggregate(
Except(
–set of all statuses
[Status].[Status].Members,
–list of exceptions
{ [Status].[Canceled] })
)

Is there something like “status was in “Cancelled”” or “status was not in “Cancelled”” from JIRA?
Thanks!!!

Hi @KrAxXx09,

Welcome to the eazyBI community!

The requirement is quite complicated because you want to consider current and past statuses. In eazyBI, the current status is represented by the Status dimension, but past statuses are in the Transition Status dimension. Both work differently with different measures.

The safest option is iterating through all issues and filtering them by the particular conditions - currently not in the status and has no transitions to the status. Unfortunately, such calculations can come with a report performance penalty. The calculated measure formula could look similar to the one below:

NonZero(Count(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    IsEmpty(
      CoalesceEmpty(
        ([Measures].[Transitions to status],
        [Transition Status].[Review]),
        ([Measures].[Issues created],
        [Status].[Review])
      )
    )
    AND
    [Measures].[Issues created]>0
  )
))

Replace the status and transition status “Review” with your status. You could further optimize the calculated measure, but the details depend on your report structure. In some cases, you could use addition and subtraction of tuples. But again, it depends on the report structure.

Please review our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Best,
Roberts // support@eazybi.com