Calculated Measure to get the number of Bugs per Epic

Questions & Answers
, ,
1

Hello, great community!
I’m working on different projects in Jira and I’d like to create a custom Measure that will show the number of Bug tickets per Epic.

In our projects we have 3 ways how do we ‘assign’ Bugs to Epics:

  • Using the default ‘Epic Link’ Jira feature
  • Using the ‘Linked Issues’ feature and link Bug to Epics
  • Using the ‘Linked Issues’ feature and link Bug to child Stories

All of them can be applied at the same time :upside_down_face: (Bug is linked to Epic with ‘Epic Link’ and linked to child story using ‘Linked Issues’. Or the Bug can be linked only to child Story using ‘linked isssue’)

I’ve already imported the following ‘Advanced Setting’ and added it to a Cube as a dimension and issue property:

[jira.customfield_bugs]
name = "Bugs"
inward_link = ["is blocked by", "relates to"]
outward_link = ["discovered", "causes", "relates to"]
issue_type = "Bug"
dimension = true
multiple_values = true

Now I’m stuck. I need to create a report that will show the Number of Bugs per Epic in different Jira projects. Later I will create an Avg function for it to track how many bugs we have per Epics during the time.

Could someone please help me to create a Measure that will show Number of bugs per Epics. It should go through all Epic child issues (stories) and get the number of ‘linked’ Bugs.
If the same bug is linked to the Epic using ‘Epic Link’ and at the same lime is linked to one of Epic child stories using ‘Linked issues’ → it should be counted as 1, not to have duplicates. The same logic if Bug is linked to Epic using ‘Epic link’ and at the same time is linked to Epic using ‘Linked Issues’.

I’ve spent days ‘fighting’ with that, but no result

Thank you in advance!

2

I don’t have the answer you are looking for, I’m too new at this but your question is certainly an interesting one.

I am sure there are other ways, but something from my point of view, it seems like conditional logic will be involved.

https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/case-statement

3

Hi @Anton

After you imported linked field “Bugs” from the import option page, you could try creating new calculated measure that would count all linked bugs and chidlren bugs together:

CASE WHEN
[Issue.Epic].CurrentMember.level.name = "Epic" --to calculate the results just for epics
THEN
--children bugs
Count(
  Filter(
    Childrenset([Issue.Epic].CurrentMember),
    [Measures].[Issue type] = "Bug"
  )
)
+
--all linked bugs
  NonZero(Sum(
    Descendants([Bugs].Currentmember, [Bugs].[Bugs]),
    -- counts how many times Bugs are reference with issues, this works as a filter as well
    [Measures].[Issues created]
 ))
END

Then select the “Epic” level from the Issue.Epic hierarchy.

Martins / eazyBI

image
image1920×844 120 KB

4

Hey @martins.vanags ,

It works, thank you :+1:!

How do you think, is it possible to add a time dimension to such a report?
I’ve tried to add Time dimension into the rows but it doesn’t work as I expected. It sorts the data in the table by Epic creation date, so in case I have an Epic created in April and a new Bug has been reported in May → ‘+1’ will go to April.
I would like to have the sorting in the output not by Epic creation date but rather by Bug creation date.

Example:

  • in April I reported 2 new Bugs related to the Epic or Story. Epic has been created a long time ago.
  • In May I reported 5 New Bugs. 3 of them belong to Epic or Story and 2 are not (just some findings).

The desired result table:

Month: All related bugs
April: 2
May: 3

Thank you in advance

5

Hei @Anton

try this formula

CASE WHEN
[Issue.Epic].CurrentMember.level.name = "Epic" --to calculate the results just for epics
THEN
--children bugs
Count(
  Filter(
    Childrenset([Issue.Epic].CurrentMember),
    [Measures].[Issue type] = "Bug"
    AND
    [Measures].[Issues created]>0
  )
)
+
--all linked bugs
  NonZero(Sum(
  Filter(
    Descendants([Bugs].Currentmember, [Bugs].[Bugs]),
    DateInPeriod(
      [Linked bugs].CurrentMember.get('Created at'),
      [Time].CurrentHierarchyMember
    )
    ),
    -- counts how many times Bugs are reference with issues, this works as a filter as well
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember)
 ))
END

Martins / eazyBI