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

Questions & Answers
1

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:
Aggregate({
[Organization].[Organization A],
[Organization].[Organization B],
[Organization].[Organization C]
})

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

CALCULATED FIELD TOTAL: 100 hours
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,
Robin

1 Like
2

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 https://docs.eazybi.com/eazybijira/getting-started/measures-and-dimensions.

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:

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

Lauma / support@eazybi.com