How to add the count of transition to status measure based on condition applied to same measure

Questions & Answers
1

Hi,
I am creating a report where i want to add the number of times each bug has moved from ‘QA Ready => QA In Progress’ status more than once.

The problem i m facing is that the total is not correct. I am expecting the total of the following drill down issues only but instead i am getting the sum of all the current members.

Calculated measure created as below

Sum(
Filter(
[Issue].CurrentMember,
[Measures].[Transitions to status]>1
),
[Measures].[Transitions to status]
)

image
image1909×715 43 KB

Please suggest how this can be achieved.

2

Hi @ruchir,

The expression leads in the right direction, but seems to be missing a part.
When approaching the set of all issues, the condition for the number of transitions returns true, and the total aggregated values of all issues in the category are returned which is not the value you expect.
The filter might process a set of individual issues, and you could create that using the Descendants() function.

The expression might be as follows.

Sum(
 Filter(
  Descendants(
  [Issue].CurrentMember,
  [Issue].[Issue]),
-- filter condition for individual issue  
  [Measures].[Transitions to status]>1
 ),
-- numeric part for sum
 [Measures].[Transitions to status]
)

This could get slowish on a larger dataset. Please share the full report context to see how this might be optimized.

Regards,
Oskars / support@eazyBI.com

3

Thanks Oskars, the change you suggested solved my problem. But as you mentioned it becomes slow and most of the times gives me out of time error.

There is another thread already opened for the optimization of this measure. Please do have a look and let me know if you have any further suggestions.

How to improve performance of the query used in calculated measure - Questions & Answers - eazyBI Community

4

Hi @ruchir,

The efficiency of further optimization highly depends on your dataset:

  1. The share of specific type issues in different projects.
  2. The number of projects in your dataset.
  3. The number of issues in your dataset.
  4. The frequency of “double transition”.

That other thread addresses finding the average number of double-or-more transitions.
You might use the AVG() instead of SUM() in that case.

The expression for the total number of transitions might be as follows.

CASE WHEN
-- double transition has taken place - iteration relevant
 [Measures].[Transitions to status]>
 [Measures].[Transitions to status issues count]
THEN
  CASE WHEN
   NOT [Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
  THEN
  Sum(
   Filter(
  --optimized iteration
    Generate(
      Filter(
      --set of relevant issues
         Descendants(
           [Project].CurrentHierarchyMember,
           [Project].CurrentHierarchy.Levels("Project")),
  -- filter condition for project
  -- double transition has taken place - iteration relevant
           [Measures].[Transitions to status]>
           [Measures].[Transitions to status issues count]),
        --set of issues in filtered projects
         DescendantsSet(
          [Issue].[Project].GetMemberByKey(
            [Project].CurrentHierarchyMember.Key),
          [Issue].[Issue])
         ),
-- filter condition on individual issue - only applies for separate issue types
     IIF([Issue Type].CurrentMember.Level.Name = "Issue Type", 
     [Measures].[Issue type] = [Issue Type].CurrentMember.Name, 1)
     AND
-- if time is selected
     IIF(NOT [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember, 
-- created before selected time
     DateBeforePeriodEnd(
       [Issue].CurrentHierarchyMember.Get('Created at'),
       [Time].CurrentHierarchyMember)
      AND
-- not resolved before selected time
      NOT
       DateCompare(
       [Time].CurrentHierarchyMember.StartDate,
       [Issue].CurrentHierarchyMember.Get('Resolved at'))>0
     , 1)
    ),
-- numeric value for SUM
    CASE WHEN
-- only for those with double transition or more
     [Measures].[Transitions to status]>1
    THEN
-- take the transition count
     [Measures].[Transitions to status]
    END
    )
  ELSE
  --general iteration from issues
  Sum(
    Filter(
      DescendantsSet(
          [Issue].CurrentMember,
          [Issue].[Issue]
        ),
-- filter condition on individual issue - only applies for separate issue types
     IIF([Issue Type].CurrentMember.Level.Name = "Issue Type", 
     [Measures].[Issue type] = [Issue Type].CurrentMember.Name, 1)
     AND
-- if time is selected
     IIF(NOT [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember, 
-- created before selected time
     DateBeforePeriodEnd(
       [Issue].CurrentHierarchyMember.Get('Created at'),
       [Time].CurrentHierarchyMember)
      AND
-- not resolved before selected time
      NOT
       DateCompare(
       [Time].CurrentHierarchyMember.StartDate,
       [Issue].CurrentHierarchyMember.Get('Resolved at'))>0
     , 1)
    ),
-- numeric value for SUM
    CASE WHEN
-- only for those with double transition or more
     [Measures].[Transitions to status]>1
    THEN
-- take the transition count
     [Measures].[Transitions to status]
    END
  )    
  END
END

Still, the actual effect depends on the dataset specifics and the selections.

Regards,
Oskars / support@eazyBI.com