Our tasks have 12 Resource fields and I need to count how many of those 12 are not empty. I know I have the measure wrong because I believe it’s counting the number of Issues. How can I rewrite this for a count of the Resource fields?
NonZero(
Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
NOT IsEmpty([Measures].[Resource #1]) AND
NOT IsEmpty([Measures].[Resource #2]) AND
NOT IsEmpty([Measures].[Resource #3]) AND
NOT IsEmpty([Measures].[Resource #4]) AND
NOT IsEmpty([Measures].[Resource #5]) AND
NOT IsEmpty([Measures].[Resource #6]) AND
NOT IsEmpty([Measures].[Resource #7]) AND
NOT IsEmpty([Measures].[Resource #8]) AND
NOT IsEmpty([Measures].[Resource #9]) AND
NOT IsEmpty([Measures].[Resource #10]) AND
NOT IsEmpty([Measures].[Resource #11]) AND
NOT IsEmpty([Measures].[Resource #12])
)
))
Hi @Alyssa_A,
We can explore an option with calculated measures. Still, it significantly differs depending on the type of information in the 12 resource fields. Are the fields strings or numeric?
Nevertheless, I recommend a JavaScript calculated custom field as a better option. Please see the suggested parameters below:
[jira.customfield_emptycount]
name = "DA Empty field count"
data_type = "integer"
measure = true
javascript_code = '''
if (issue.fields.project.key = 'PROJECT_KEY') {
let allFields = [
issue.fields.customfield_NNNNN,
issue.fields.customfield_NNNNN,
issue.fields.customfield_NNNNN,
issue.fields.customfield_NNNNN,
issue.fields.customfield_NNNNN
];
let emptyCount = 0;
allFields.forEach(field => {
if(!field) {
emptyCount += 1;
}
})
issue.fields.customfield_emptycount = emptyCount;
}
'''
In the JavaScript variable, I define an array of five fields I want to consider. In your case, it is twelve fields. Update NNNNNN with the IDs of those fields. The JavaScript code also checks each issue’s Project and applies the calculation to a single Project, defined by PROJECT_KEY. Update it or remove the condition if you require the calculation for all Projects. Add the adapted parameters to the eazyBI advanced settings and update them.
See the eazyBI documentation page for more details about JavaScript calculated custom fields - JavaScript calculated custom fields.
Finally, head to the eazyBI import options, select the newly defined JavaScript calculated custom field for import, and make sure all the referenced custom fields are imported as well. See a screenshot below with the count of empty fields for each issue and the considered fields.
Best,
Roberts // support@eazybi.com
Thank you. Is there a way to do this without the JavaScript custom field?
Hi @Alyssa_A,
As mentioned in my original response, the approach for a calculated measure differs depending on the field types. Are the fields multi-value? What type does the field have in Jira? Please provide more details.
Best,
Roberts // support@eazybi.com
Sorry for my misunderstanding. The fields are a single select dropdown list.
Hi @Alyssa_A ,
I am sorry for keeping you waiting for an update. In the case of select list fields, eazyBI assigns the value “(none)” for issues that don’t have a value upon data import. With the Issue dimension in rows, you can define a new calculated measure with the formula similar to the one below:
Count(
Filter(
{
[Measures].[Issue T-shirt size],
[Measures].[Issue Color],
[Measures].[Issue Score]
},
Cast([Measures].CurrentMember.Value as string) <> '(none)'
)
)
For each issue in the report rows, it will consider three single select list fields and count the number of fields with any other value than “(none)”. Update the set of issue properties to consider in your use case. See the example below:
Best,
Roberts // support@eazybi.com
Thank you very much for this help
