How to filter on a multi-value custom field checkbox?

Hi all,

Was hoping that perhaps somebody here could help me with this.
I have a custom field checkbox where issues can be put into a certain organization. But also multiple.
For example:
Organization A
Organization B
Organization C

An issue can be in either one of them, but also in 2 or 3 of them. (example case, in reality we have 10+ options).

When I create a report to display the hours spent on total, I need to filter per organization. So when I add my custom field as a “Page” I can select for example: “Organization A”.
However, it will result in only showing issues that have exclusively Organization A selected. Not the ones that have Organization A, among others. Basically what I want is, just like with JQL:
“Organization in (“Organization A”)”

If I use the “multiple value” option and basically select all organizations, then the total amounts of hours written are incorrect. (multiplied by the amount of organizations found…). So if Org A, B and C are found, then it takes the total amount of hours and multiplies it times 3. Not sure why it does this…

When I just show them all, it does something like this:
It would show something like
Organizations TOTAL: 50 hours
Organization A: 40 hours
Organization B: 25 hours
Organization C: 35 hours
It understands that there is overlap, and it shows the correct total. 50 hours, instead of the actual sum or all organizations. So I figured I’ll use a calculated field, to only include my set of selected organizations and thus get a correct total for my subset.

I tried to do it with a calculated field using the following:
[Organization].[Organization A],
[Organization].[Organization B],
[Organization].[Organization C]

But the result totals are incorrect. Because there is overlap… and now it shows:

Organization A: 40 hours
Organization B: 25 hours
Organization C: 35 hours
Which is incorrect…

So I need help on how to do this.
In short: I need to know how I can filter my result-set by using a “in-statement” on a custom field checkbox with multi-values.

Thanks in advance for helping.

Kind regards,

1 Like

Hi Robin,

Multiple value fields are a bit tricky as each measure is counted to be true for each selection. On default dimension total level there is a logic that ‘subtracts’ the duplicates, but on custom aggregations, it is not as simple. For the count type measures there is the Distinct count section, see

To get the ‘distinct hours spent’ you would need to create a new calculated measure that filters issues with hours spent in the context and then sums the hours. Please try the following:

  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  [Measures].[Hours spent] > 0
), ([Measures].[Hours spent], [Organization].DefaultMember))

Lauma /