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

**URL:** <https://community.eazybi.com/t/how-to-filter-on-a-multi-value-custom-field-checkbox/2302>\
**Category:** Questions & Answers\
**Created:** [June 3, 2019, 3:36pm UTC](https://community.eazybi.com/t/how-to-filter-on-a-multi-value-custom-field-checkbox/2302 "2019-06-03T15:36:51Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sliekerr](https://community.eazybi.com/user_avatar/community.eazybi.com/sliekerr/32/1518_2.png) [@sliekerr](https://community.eazybi.com/u/sliekerr)\
**Post date:** [June 3, 2019, 3:36pm UTC](https://community.eazybi.com/t/how-to-filter-on-a-multi-value-custom-field-checkbox/2302/1 "2019-06-03T15:36:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lauma.cirule](https://community.eazybi.com/user_avatar/community.eazybi.com/lauma.cirule/32/8830_2.png) [@lauma.cirule](https://community.eazybi.com/u/lauma.cirule)\
**Post date:** [June 25, 2019, 11:50am UTC](https://community.eazybi.com/t/how-to-filter-on-a-multi-value-custom-field-checkbox/2302/2 "2019-06-25T11:50:11Z")

</div>

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](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](mailto:support@eazybi.com)
