Cannot create measure to exclude issues that have ever been in a given status

Questions & Answers
,
1

Hi,
I am just trying to make a pie chart of a given issue type (Incidents) where the slices are determined by if the incident has been in a given status or not.

I believe I have the calculated measure created correctly that identifies issues that have been in the given status (Swarm), but I am having a lot of trouble getting the slice that identifies issues that have not been in the Swarm status. My calculated measure for that is:

Aggregate(
    Except(
    [Transition Status].[Transition Status].Members,
    {
        [Transition Status].[Swarm]
    })
)

This does seem to remove the status when I expand the Table format, but it is not pulling in any issues. Any idea why, or a better way to get the desired report?

2

Any issue moves through several statuses during the lifetime. Dimension Transition status will represent all those statuses. Calculated member with Except will pull in any other status. Therefore, if the issue was in any other status, this calculated member will pick this issue.

You can use simple subtraction to count issues with no specific status:

[Measures].[Issues created]
-
([Measures].[Transitions to status issues count],
[Transition Status].[Swarm])

However, if you would like to use the Time dimension on the report, you might need a more complex formula with issue level calculation. You would need to use one parameter, property suggested as a filter by Time:

Sum (Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- set filter by time if Time dimension is used in report
  -- use date property as filter for performance improvements, select any from section Issue properties
  DateInPeriod(
    [Measures].[Issue created date], -- use any date property
    [Time].CurrentHierarchyMember
  )),
  case WHEN 
  -- there is no transition to this status
   isEmpty(DefaultContext((
     [Measures].[Transitions to status],
     [Transition Status].[Swarm],
     [Issue].CurrentMember
   )))
   THEN
   -- count valid issues
  Val(([Measures].[Issues created],
   [Transition Status].CurrentHierarchy.DefaultMember,
   [Time].CurrentHierarchy.DefaultMember))
  END
)

Here is how both formulas work in the report. I used it with status Review instead of Swarm. Values are valid for both at All times levels. For specific period (on my data, it is already visible for quarters) selection, they show differences in results:

image
image1514×678 68 KB

Transitions to status reviews are for 4 issues. All those 4 issues were created in Q1. They were transited to review in Q1 and Q2. No issues created in Q2 were transitioned to status review yet.

Daina / support@eazybi.com