Measure to count number of subtasks of specific issue type and status per issue

Questions & Answers
1

I am trying to build a graph similar to the one in the image. On one bar I would like to display the number of issues with 1 subtasks (approved status) and on another bar the number of issues with more than 1 subtasks. I would like to display this graph at the issue level.

image
image1593×385 3.17 KB

Thanks!

2

Hi,

The following formula is for counting the issues having more than one sub-task:

Count(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
Len(CoalesceEmpty([Measures].[Issue sub-task keys],""))-Len(Replace(CoalesceEmpty([Measures].[Issue sub-task keys],""),",",""))
>0
AND
[Measures].[Issues created]>0
)
)

A similar formula counts issues with exactly one sub-task:

Count(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
NOT IsEmpty([Measures].[Issue sub-task keys])
AND
Len(CoalesceEmpty([Measures].[Issue sub-task keys],""))-Len(Replace(CoalesceEmpty([Measures].[Issue sub-task keys],""),",",""))
=0
AND
[Measures].[Issues created]>0
)
)

This formula groups the parent issue counts on the time dimension by the issue creation date

image
image1886×1010 51.8 KB

Kindly,
Janis, eazyBI support

3

Thanks Janis,
In addition to that, how could I make this formula only count the subtasks of a specific status and issuetype? it’s possible?

4

Hi,

Yes, it is possible in several ways with further adjustments to the formula. My previous example uses the list of sub-task keys and counts the number of sub-tasks from it. We can switch to the Sub-task issue hierarchy and use Tuple to count if the parent has sub-tasks of a specific issue type.

The following example counts the number of parents having “Data Task” sub-tasks:

Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
DefaultContext(
([Measures].[Issues created],
 [Issue.Sub-task].[Parent].GetMemberByKey(
   [Issue].CurrentMember.KEY
 ),
 [Issue Type].[Data task]
 ))>0
 AND
[Measures].[Issues created]>0
)
)

You can try another condition (e.g., filter by status) by changing line 8 in the code:

[Issue Type].[Data task]

The following example counts parents having at least one sub-tasks (of any issue type) in Done status:

Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
DefaultContext(
([Measures].[Issues created],
 [Issue.Sub-task].[Parent].GetMemberByKey(
   [Issue].CurrentMember.KEY
 ),
 [Issue Type.By type].[Sub-Task],
 [Status].[Done]
 ))>0
 AND
[Measures].[Issues created]>0
)
)

Kindly,
Janis, eazyBI support

5

Thanks!! its works fine!