Exclude issues in certain Transition Status and specific Status

Questions & Answers
, ,
1

I need to create a report of issues that haven’t been in a specific Transition Status and are in a specific Status. In this case, the Transition Status restriction is “Escalated” and the Status is “Resolved”. That is to say the report should return all the Issues in Status = “Resolved” and never have passed by Transitions Status “Escalated”.
I defined a calculated member in the Transition Status dimension that aggregates the statuses I am interested in using the next expression (expression1):

Aggregate(
Except(
[Transition Status].[Transition Status].Members,
{[Transition Status].[Escalated],
[Transition Status].[Cancelled] (optional restriction)
}
)
)

Besides, I use expression1 in a measure

([Measures].[Transitions to status issues count],
[Transition Status].[expression1])

However, I haven’t been able to filter the result with the specific Status. Is it possible someone will give me a hand in completing my report?

Thanks in advance :slight_smile:

2

Hi,

The solution to this use case requires iteration through the issues to count issues that never were in some statuses.

The formula could look like this:

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issue status]="Resolved"
  AND
    IsEmpty(
      ([Measures].[Transitions to status],
       [Transition Status].[Some statuses])
    )
  )
))

Some statuses is an aggregated members of statuses you wish to exclude:

Aggregate(
{[Transition Status].[Escalated],
[Transition Status].[Cancelled]}
)

Kindly,
Janis, eazyBI support

3

Janis, thank you for your answer.

I have applied the solution according to your indications. However, the obtained results are not filtered as is required. These are in the column ‘Resolved by Support Team’. I have checked them and some of them have passed by the transition stated ‘Escalated’.

image
image757×679 9.14 KB

Besides, the number shown is quite high in comparison to the real state.
Furthermore, I included in the filter DateInPeriod, trying to filter the Issues updated in the period selected: “Current Month”

DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember )

But It didn’t work, it didn’t show any number as result.

At the end I have:

Column - Resolved by Squads

NonZero(Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issue status]=“Resolved”
AND
(
([Measures].[Transitions to status],
[Transition Status].[TicketsEscalated]) > 0
)
)
))

Where TicketsEscalated =
Aggregate(
{[Transition Status].[Escalated]}
)

Column - Resolved by Support Team

NonZero(Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issue status]=“Resolved”
AND
IsEmpty(
([Measures].[Transitions to status],
[Transition Status].[TicketsEscalated])
)
)
)
)

I hope you can help me!!
Thank you.