Filter Issues by count of child level

Questions & Answers
1

We have a 3 level hierarchy as follows in the Advanced settings:
[[jira.issue_hierarchies]]
name = “Portfolio Epic”
all_member_name = “All Issues by SAFe Epic”
levels = [
{name=“Epic”,key_column=“customfield_10410”,issue_type=“Portfolio Epic”},
{name=“Feature”,key_column=“epic_key”},
{name=“Story”,key_column=“epic_parent_key”},
{name=“Sub-task”,key_column=“subtask_key”}
]

I am trying to add an Issue Dimension Calculated member to filter the Portfolio Epic’s that have > 0 Feature’s but this calculation is not working:

Filter(
[Issue.Portfolio Epic].Members,
Count([Issue.Portfolio Epic].Children)>0
)

I get the following error:
Formula is not valid:
Member expression ‘Filter([Issue.Portfolio Epic].Members, (Count([Issue.Portfolio Epic].Children) > 0))’ must not be a set

2

Hi Robaduncan,

The “must not be a set” errors just means that you are returning a set and eazyBI is not sure what you want to do with it. So you must use of the aggregators.
For example, Aggregate(), Count(), Avg(), Sum(), Median().

Aggregate(
  Filter(
    [Issue.Portfolio Epic].Members,
    Count([Issue.Portfolio Epic].Children)>0
  )
)

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

1 Like
3

Thank you for the quick reply GvidoN, but when I use Aggregate it then groups the Epics which defeats the purpose of trying to filter and only select the Epics with at least 1 Feature:
image

4

@robaduncan

Yes. It filters Epics that have at least 1 feature and then aggregates the set. It should not show all the epics.

5

@GvidoN How do I filter the list but NOT Aggregate? I need to filter the list of Epics so only there is a shorter list.

6

@robaduncan

I think you should first remove all the other members from the list and then select you aggregated list.

7

Sorry @GvidoN I am not sure how to do this - when I have unselected all other members all I have is my calculated member which i cannot expand to see the Epics within that have Features, all I see is this. I need to not Aggergate them but just filter them.
image

8

@robaduncan

The filter is probably not working correctly. I cannot test this since this is a custom dimension and I do not have your data.

Here is an example with default hierarchy issues:

Aggregate(
  Filter(
    [Issue.Sub-task].[Parent].Members,
    Count([Issue.Sub-task].Children) > 0
  )
)

09
Screen Shot 2018-07-24 at 20.58.09.png822×526 38.1 KB

21
Screen Shot 2018-07-24 at 20.58.21.png896×525 84.4 KB

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

9

There is a way to “filter” them without aggregating though.

You can create a calculated member:

Count([Issue.Sub-task].CurrentHierarchyMember.Children)

And then use “filter” option with greater than zero:

12

Then remove the column:

45

And the result:

34
Screen Shot 2018-07-24 at 21.10.34.png1235×741 120 KB

10

@GvidoN, I really appreciate you showing this example.

From what I can see your example of:

Aggregate(
Filter(
[Issue.Sub-task].[Parent].Members,
Count([Issue.Sub-task].Children) > 0
)
)

Should be the same as:

Aggregate(
Filter(
[Issue.Portfolio Epic].[Epic].Members,
Count([Issue.Portfolio Epic].Children)>0
)
)

But I get this error:
image

11

My hiearchy is:
image

12

Hello,

You probably have not selected the correct hierarchy when creating the calculated member.

36
Screen Shot 2018-07-26 at 12.19.36.png898×503 42.7 KB

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

1 Like
13

@GvidoN, that was exactly what I was missing! I don’t recall seeing that in the user guide but that simple change solved the problem!