How to get issue count based on a custom field value

We have 2 custom fileds one is a checbox which is to define whether the issue is reported by customer or not other is a text box to have support issueID
Want to get the number of issues reported by customer during a sprint based on the checkbox value set to Yes or custmer issueID field is not empty

Hi @Chinmay

Welcome to the Community! :slight_smile:

You can build a report like this by adding the Sprint dimension in the Rows section and defining a custom calculated measure “Customer issue counter” with the following formula:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween(
      [Issue].CurrentMember.GetDate('Created at'),
      [Sprint].CurrentHierarchyMember.GetDate('Start date'),
      [Sprint].CurrentHierarchyMember.GetDate('End date')
    )
    AND
    (
      [Issue].CurrentMember.Get('checkbox') MATCHES "Yes"
      OR
      NOT IsEmpty([Issue].CurrentMember.Get('customer issueID'))
    )
  ),
  (
    [Measures].[Issues created],
    [Sprint].DefaultMember
  )
)

This formula will iterate through all of the issues and count how many of them match the filter criteria. Be sure to use the correct Issue property names instead of the “checkbox” and “customer issueID”.

Best regards,
Nauris / eazyBI support

Thank you @nauris.malitis :+1:

@nauris.malitis The updated solutions is giving the correct result but takes huge amount of time if apply some sprint filter in pages or applying a new calculated data columnusing the data from these columns.
Many of a time I get the timeout error as well as shown below,
Any help on the same will be of great value.

Hi @Chinmay

This is somewhat expected as the Descendants function is resource-heavy as it has to iterate through all of the issues for each of the rows that you have in your report.

To achieve faster report execution, you can bring some of the logic to the import process by defining a new custom field in Advanced settings.

Add the following lines to your settings, but switch out the NNNNN for the ID of the customer id custom field and MMMMM for the ID of the checkbox custom field (you can find them in Custom fields section by hovering your mouse over the name):

# Is Customer Issue
[jira.customfield_iscustomerissue]
name = "Is Customer issue"
data_type = "string"
dimension = true
javascript_code = '''
if (issue.fields.customfield_NNNNN || issue.fields.MMMMM.includes("Yes")) {
  issue.fields.customfield_iscustomerissue = "Is Customer Issue";
} else {
  issue.fields.customfield_iscustomerissue = "Not Customer Issue";
}
'''

After the settings are updated, select this new “Is Customer Issue” field for import as a dimension, and you should be able to use it as a filter in the Pages section. Use the default “Issues created” measure with the Sprint dimension in Pages as a filter, and you should be able to achieve the same output with faster performance.

Best regards,
Nauris