For each Epic select 1 child Story with the latest Due Date and group by Status

Questions & Answers
1

Hello!

I need to create a pie chart with grouping by Status of the following issue set:
for each Epic select 1 child Story with the latest Due Date.
I guess it should be a calculated measure. The main thing that challenges me is how to select Story by given criteria for each Epic, considering that neither Epic Link nor Issue dimension should be presented at Rows or Columns.
Could you please help me to compose the calculation?

Many thanks!

2

Hi,

Please, find here one version of how it could be calculated by using the MDX formula. The idea is to iterate over the Epics and find for each epic the issue with the latest due date. Considering that the Status dimension is in the report rows, the formula is the following:

Nonzero(Count(
  Filter(Descendants([Issue.Epic].CurrentMember,[Issue.Epic].[Epic]),
    ([Measures].[Issues created],
      [Status].DefaultMember)>1 -- For epics with at least one child  
      AND
    ([Measures].[Issues created], --check if the child with the latest due date is within the context 
     [Issue].[Issue].GetMemberbykey(
       Order([Issue.Epic].CurrentHierarchyMember.Children,
         [Measures].[Issue due date],
         DESC
       ).Item(0).Key),
     [Issue.Epic].CurrentHierarchy.DefaultMember
     )>0
  )
))

I also should have mentioned that working with the hierarchies and checking the values in different levels of the hierarchies is one of the most tricky areas of MDX techniques.

Kindly,
Janis, eazyBI support