Epics with only Closed Stories

Questions & Answers
1

Hello support team,

I currently have the current measure to get epics with priority in (High, Critical) that have at least one closed story.

Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issue type] = “Epic” AND
([Measures].[Issue priority] = “High” OR [Measures].[Issue priority] = “Critical”)
AND
([Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentHierarchyMember.key)
,[Issue Type].[Story]
,[Status].[CustomClosed]
,[Issue].CurrentMember)>0
)
)

CustomClosed is defined as follows:

Aggregate({
[Status].[Closed],
[Status].[Resolved]
})

However, I need to get epics that have only closed stories.
Your help would be appreciated!

Thank you.

2

Hello @ksmind,

Thanks for posting your question!

We suggest trying a slightly adjusted formula, which will look up if the count of created Stories equals the count of closed Stories & if this criteria is met, it will return “1”. This will allow you to get the list of Epics that have only closed stories.

-- Sum of specified conditions for Issues that are of type 'Epic' and have a 'High' or 'Critical' priority
Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    -- Filter condition for 'Epic' issues with 'High' or 'Critical' priority
    [Measures].[Issue type] = "Epic" AND
    (
      [Measures].[Issue priority] = "High" OR
      [Measures].[Issue priority] = "Critical"
    )
  ),
  -- Conditional check if the number of 'Story' type issues created equals the number closed
  CASE WHEN
    (
      [Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentHierarchyMember.Key),
      [Issue Type].[Story],
      [Status].[CustomClosed],
      [Measures].[Issues created],
      [Issue].Currenthierarchy.DefaultMember
    ) =
    (
      [Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentHierarchyMember.Key),
      [Issue Type].[Story],
      [Status].[CustomClosed],
      [Measures].[Issues closed],
      [Issue].Currenthierarchy.DefaultMember
    )
  THEN
    1
  END
)

I hope this helps!

Best,
Marita // support@eazybi.com

3

Hello @Marita_Norvele ,

Thanks alot for your support. Your solution worked for only Closed stories
Furthermore I wanted also to to get the list of Epics that have only closed/resolved stories. However the solution does not seem to work for only closed/resolved stories. The measure will also filter Epics that have stories with status not in (resolved/closed)

Your help would be appreciated!

Thank you.

4

Hi @ksmind,

Thanks for the follow-up question.

Could you please check if you have defined resolved status as a Closed status in Import options: Issues closed measure?

image
image1518×402 44.9 KB

Once you have updated the Import options, the formula I provided earlier should return Epics with closed or resolved stories.

Best,
Marita // support@eazybi.com