Find all issues without some labels

I try to find all issues without some labels.

My issues have labels to dispatch between teams, and may have labels for something else.

IssueA : LabelTeam1
IssueB : LabelTeam2 , OtherLabel1
IssueC : OtherLabel2
IssueD : (no label)

I would like to find Issue not affected to a team (here C and D). But with my calculation, I also have B in the results.

Columns: Measures/Open issues
Row: Label with a new calculated member:

Aggregate(
  Except(
    [Label].[Label].Members,
    {
      [Label].[LabelTeam1],
      [Label].[LabelTeam2]
    }
  )
)

In JQL, I do that and that’s work:
labels not in (LabelTeam1, LabelTeam2) OR labels is EMPTY)

Could you help me please?

Hi Freddy,

You are on the right track, but as eazyBI takes each label separately with the measures, we will also need to create a calculation in Measures dimension. Please do the following:

In label dimension create calculated measure with labels you wish to exclude:

Aggregate(
  {
    [Label].[LabelTeam1],
    [Label].[LabelTeam2]
  }
)

In Measures, create and use a calculated member that will subtract the issues with any of the labels from total (using the measure from the previous step)

NonZero([Measures].[Issues due] -
(
  [Measures].[Issues due count],
  [Label].[Labels to except]
))

Let me know if I missed something and you have some further questions!
Lauma / support@eazybi.com

1 Like

Thank you Lauma!
It works for ‘All Labels’. But how can I have a Pie chart with the dispatch ‘LabelTeam1’, ‘LabelTeam2’,… ‘not affected’?

I have a solution, maybe not the best.

In row I display all my labels about teams, plus ‘All Labels’.

My calculation in Measures dimension is:

CASE [Label].CurrentMember.Name
WHEN "All Labels" THEN 
  NonZero(
    [Measures].[Issues created count]
    -
    ([Measures].[Issues created count],[Label].[TeamsLabels])
  )
ELSE
  NonZero([Measures].[Issues created count])
END

And with my first example, I have:

LabelTeam1 : 1 (A)
LabelTeam2 :  1 (B)
All Labels :  2 (C,D)

(And I have also rename ‘All Labels’ to ‘noTeamLabel’ to be clear with my users).

Thank you very much Lauma for your help. EazyBI is a great tool!

Here is an alternative solution using a new dimension for label combinations: