Get issues that have two labels (both labels)

Questions & Answers
,
1

Hi all,

I want to create a report with all issues that have both Label-1 and Label-2. If I add the labels under the labels dimension and select them, I get all issues that have either one of the labels (Label-1 or Label-2).

I have done some research in the community, question has been asked before but no clear answer.

Thanks in advance,
Nikos

2

Hi @nikbats

A label is a multi-value field in Jira and eazyBI imports it in a separate dimension.
That means there could be different scenarios how issue can have labels (no labels, one label, multiple-labels) which can complicate the query.

One way to create such counter would be using descendants function which goes through issues and checks if issue belongs to the both labels.

NonZero(
  sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      CoalesceEmpty([Measures].[Issue labels], "") MATCHES ".*Label-1.*"
      AND
      CoalesceEmpty([Measures].[Issue labels], "") MATCHES ".*Label-2.*"
  ),
  [Measures].[Issues created count]
))

But that is a quite iterative approach and it requires “Nonempty” cross join in report rows to be enabled as the calculation would go through “Issue” dimension all members to filter the right set for the calculation in each table cell. Thus it could be slow if you use this measure with many other report filters especially if multiple-values are filtered in some page dimension.

If you could tell us more about the context of your report (what other dimensions will be used in the report rows, pages, columns) we could point some directions on how to optimize the calculation steps by adding additional validations to reduce the set for the calculation.

Martins / eazyBI support

3

Similar to this, I would like to be able to pull the story points for any tickets with two labels.

4

@sknight

Try this formula when creating a calculated measure.

NonZero(
  Sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      [Measures].[Issue story points] > 0
      AND
      (Len(CoalesceEmpty([Measures].[Issue labels],"")) - 
        Len(Replace(CoalesceEmpty([Measures].[Issue labels],""),',',''))
      ) > 0
    ),
    CASE WHEN [Measures].[Issues created] > 0
      THEN [Measures].[Issue story points]
    END
  )
)

Don’t forget to enable “Nonempty” cross join in report rows to optimize the query for this measure used in the report.

5

Hi @martins.vanags,

I stumbled upon this thread and am looking to run the same query, but when I tried to implement the first suggested code block, my formula returned no entries. I am curious if there is something that I might be missing? Thanks in advance!

NonZero(
  sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      CoalesceEmpty([Measures].[Issue labels], "") MATCHES ".*Label-1.*"
      AND
      CoalesceEmpty([Measures].[Issue labels], "") MATCHES ".*Label-2.*"
  ),
  [Measures].[Issues created count]
))

eazybi report
eazybi report777Ă—280 25.3 KB

6

@staunk

Try using lowercase label names “label-1” and “label-2” in the formula.
eazyBI imports label names in lowercase.

Martins / eazyBI

1 Like
7

**[quote=“nikbats, post:1, topic:4568, full:true”]
Hi all,

I want to create a report with all issues that have both Label-1 and Label-2. If I add the labels under the labels dimension and select them, I get all issues that have either one of the labels (Label-1 or Label-2).

I have done some research in the community, question has been asked before but no clear answer.

Thanks in advance,
Nikos
[/quote]

I have tried to use the same code but the results are zero

Thanks in advance
Venetia

**

  sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      CoalesceEmpty([Measures].[Issue labels], "") MATCHES ".*s3_triage.*"
      AND
      CoalesceEmpty([Measures].[Issue labels], "") MATCHES ".*restructuring_related.*"
  ),
  [Measures].[Issues created count]
)
8

Hi @Venetia_Tasou

You could check the csv_dimension = true settings to create new “Label CSV” dimension that would combine all possible label combinations in comma separate strings - one combination equals one dimension member.
That would let you filter the report for the exact label combination without requiring calculated measures.

Martins / eazyBI