Get issues that have two labels (both labels)

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

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 tuples and descendants function which goes through issues and checks if issue belongs to the both labels.

NonZero(
  Count(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      ([Measures].[Issues created],
      [Label].[Label-1]) > 0
      AND
      ([Measures].[Issues created],
      [Label].[Label-2]) > 0
    )
  )
)

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

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

@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.