Calculated Measure combining 2 filters

Hi,

I’m really struggling with an issue that intuitively seems fairly easy, and would really appreciate it if anyone were able to help.

Essentially I’m trying to combine two filters. The requirement is to identify sub-tasks added to a sprint associated to user stories committed to in a sprint.

I’ve two separate custom measures that each work, and return the component parts. The first returns sub-tasks added to a sprint. The second user stories committed to in a sprint. However, I don’t know how to combine them or (given they create mutually exclusive conditions) nest them. For the sake of de-bugging I’m returning a comma separated list of issue Keys.

This returns the Issue Keys of sub-tasks added to a sprint:

Generate(
  Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      
      [Issue Type].[Sub-task] > 0 AND 
      [Measures].[Sprint issues added] > 0
        ),
     
      Cast([Issue].[Issue].CurrentHierarchyMember.get('DISPLAY_KEY') as string),
      ", "
)

This returns the Issue Keys of sub-tasks associated to user stories committed to in a sprint:

Generate(
  Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      
      [Issue Type].[User Story] > 0 AND
      [Measures].[Sprint issues committed] > 0
        ),
     
      Cast([Issue].[Issue].CurrentHierarchyMember.get('Sub-task keys') as string),
      ", "
)

(I’m also able to work the opposite way round, ie return this issue key of user stories having filtered for sub-tasks using the following: Cast([Issue].[Issue].GetMemberByKey([Issue].CurrentHierarchyMember.get("Parent issue key")).Get("Parent User Story Key") as string)) .

Both of these custom measures work independently, and the screenshot below shows the results (V and V1 being the custom measures described above) when they’re run side by side.

I’d like a single custom measure that combines these (ie returns only the issue keys circled in colour).
Essentially I’m looking to join the two custom measures using issue key. In pseudo code here is what I need help with:

Generate(
  Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      
      [Issue Type].[Sub-task] > 0 
      AND [Measures].[Sprint issues added] > 0
      
      -- NEED TO ADD THESE CONDITIONS:
      AND [Issue Type] of Parent Issue Key = ‘User Story’
      AND [Sprint Issues Committed] of Parent Issue Key > 0

        ),
     
      Cast([Issue].[Issue].CurrentHierarchyMember.get('DISPLAY_KEY') as string),
      ", "
)

Is anyone able to suggest how I can include conditions related to an issue and its parent issue within a single filter like this?

Hi,

The following formula counts the sub-tasks in the sprint whose parents are committed to the sprint:

Sum(
  
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    NOT IsEmpty([Measures].[Issue parent key]) --iterate sub-tasks
  ),
  CASE WHEN
  ([Measures].[Sprint issues committed],
   [Issue].[Issue].GetMemberByKey(
     [Issue].CurrentHierarchyMember.get('Parent issue key')
   )
   )>0
   THEN
      1
  END
)

The formula might need adjustments if you use other dimensions in the report, depending on whether the dimensions shall be applied to the parents or sub-tasks.

Kindly,
Janis, eazyBI support