Descendants filter to only one level

Questions & Answers
1

Is it possible to get the children but only to a single level? Example is Epics with all Child Stories (excluding bugs, and also excluding subtasks under the child stories). The below is giving me ALL descendants down to the sub tasks. When I try to filter them via other logical test, I get a timeout.

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
…

The context is to get a ratio of child stories under each epic by an attribute.
Epic 123 has three child stories: 1 story that is green, and 2 stories that are blue. The number i want is 1/3: the epic is one-third green.
The final report would be a list of epics with that ratio next to it.
Epic 123: 33% green
Epic 456: 51% green
etc.

2

Hi lpattontr,

Not sure if this works for you but you could simply use “Issue Type” dimension with formula:

([Issue Type].CurrentMember, [Measures].[Issues created]) / 
([Issue Type].DefaultMember, [Measures].[Issues created])

37
Screen Shot 2019-03-07 at 10.04.37.png1232×372 47.5 KB

3

Hi!

I would like to add another example of using Tuple in this kind of calculation.

If you use the Epic dimension together with Issues created measure you see the Issues that are exactly linked to the Epic and not the epic issue sub-tasks. With the following calculation, you can get how many issues are in the epic excluding (subtracting) bugs

([Measures].[Issues created] -
(
  [Measures].[Issues created],
  [Issue Type].[Bug]
))

Further, you can also get the number of issues that have State (I assume this is a custom field that is imported as dimension giving the color of each story) green.

(([Measures].[Issues created], [State].[Green]) -
(
  [Measures].[Issues created],
  [State].[Green],
  [Issue Type].[Bug]
))

The final percent calculation would be as follows, giving how much of each Epic Link on the rows is green.

(([Measures].[Issues created], [State].[Green]) -
(
  [Measures].[Issues created],
  [State].[Green],
  [Issue Type].[Bug]
))
/
([Measures].[Issues created] -
(
  [Measures].[Issues created],
  [Issue Type].[Bug]
))

Lauma / support@eazybi.com

4

Thanks for the suggestions. My metric is a measure, not a dimension, so couldn’t see where to take it from here. I found another question closer to what I want to achieve, How to Count Issues (History) without Story Points

5

Yes, another way is to filter the Issue dimension and return the set of issues and perform some calculations for that. In this case, you would like to filter Issue.Epic hierarchy and go down to Parent level.

Sum(
     Filter(Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Parent]),
...

A good function for debugging MDX that helps to see what set is returned on each level is SetToStr(…)

SetToStr(
  Filter(Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Parent]),
    [Measures].[Issues created] > 0 AND
    NOT [Measures].[Issue Type] MATCHES "Bug"
))

Lauma / support@eazybi.com

1 Like