How to count stories without sub-task and epic

[Dipesh_Chouksey]

Hi Experts,

I have created report which is showing total Epic, Story, Defect and sub-task based components(screenshot).
I would like to add two more column in my report. Could you help me with logic.

1. Stories without Epics
2. Stories without Sub task

Could you help me with calculated field formula like this:

Aggregate(
Filter(
  [Issue type].[Story],
  NOT isEmpty([Issue].CurrentMember.Get('Epic Link')) 
  ))

[martins.vanags]

Hi, @Dipesh_Chouksey

Here is what you can try:

1. to count stories without epics you can try to additional issue hierarchy from advanced settings:
   Additional Issue hierarchies
   Then press “import” to create this hierarchy in “Issue” dimension (it will be created automatically once data is imported)
   Then create a new calculated measure using this formula:

Count(
  Filter(
    ChildrenSet([Issue.Epics without project].[(none)]),
      (
        [Measures].[Issues created],
        [Issue Type].[Story]
      )>0
  )
)

2. To count stories without sub-tasks try this formula in calculated measure (for this measure manually select “integer” format):

Sum(
  Filter(
    DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Issue type] = "Story"
    AND
    isEmpty([Measures].[Issue sub-task keys])
    AND
    DateInPeriod([Measures].[Issue created date],
    [Time].CurrentHierarchyMember)
  ),
  CASE WHEN
  (
  [Measures].[Issues created],
  [Issue Type].[Story]
  )>0
  THEN
  1
  END
)

Martins / eazyBI support