Counting number of options in a multiselect custom field

Hey Everyone!

So I am having an issue creating a new measure which will count the number of items selected in a multiselect field.

This image show currently [Measures].[Creative - Brand Selection(s)]. This is a multiselect field with a number of options. For some other measures we would like to make we need to know how many options were selected. I have tried using Count, but as it is just a string being returned the count is 1. Any help on this would be appreciated

1 Like

Hi @Matthew_Feldman,

Sometimes even in the string, you may find something to count. If you have a string with multiple values separated by commas, you can count the commas.

There is an example that counts how many values are selected in the label field. Given formula compares string length with commas and without commas and adds 1 for the first value in the string.

--counta commas only if field has any value
CASE WHEN NOT IsEmpty([Measures].[Issue labels]) THEN
Len([Measures].[Issue labels])
- Len(Replace([Measures].[Issue labels], ',', ''))
+ 1
END

Best,
Zane / support@eazybi.com

1 Like

Hi @zane.baranovska,

I have the project named INT: …, in which it has the history KN-1699, in turn, this history has 4 subtasks. Within those 4 subtasks, there is a subtask that has the name block (but that in Jira has the subtask format), that ticket would be the KN-1920.

What I am looking for is to be able to differentiate between subtasks as such and block subtasks by means of the measure you put in the comment above. That is to say, to have a column with the number 3 and another column with the number 1 (which in total gives 4 that gives me the measurement that you gave).

Hi @Marcelo_Ignacio_Cid1,

This use case is different as you woudl like to group data first by some attributes and then count them. Simple counting by commas without any further context of each item in the field won’t work.

First, you should import a field that distinguishes blocking subtasks from the rest of sub-taks. Then you can move forward to group them. If blocking information is hidden in a free format in other fields, like issue summary, then you might want to create a new JavaScript calcaulted custom field that check summary for the name pattern and divides all issues in “Blocking” and “Non-blocking” subtasks. And import this new field as a dimension so you could group data by it.
Check out this community post on how to create new dimension from Summary:

Check out this community posts on counting subtasks and grouping them by some attributes:

Hi @zane.baranovska

this jira configuration does not work?, I have separated both subtasks in different types of tickets

image

Hi @Marcelo_Ignacio_Cid1,

If “Block” is already a specific issue type for sub-tasks, then this information is already available in eayzBI. Check the “Issue Type” dimension.

In the report, use the “Issue” dimension Sub-task hierarchy on report rows and measure “Issue created” showing how many subtasks and issue itself are created for each row.

To get sub-task count by issue type (including sub-taks issue type), click on measure “Issues created” and chose the option Drill into >> Issue Type >> Issue Type (see documentation Create reports - Drill into measure by another dimension level).
Then you can adjust which issue types you would like to see on report columns:

Or you may create a calculated measure using tuple expression as described in this community post:

([Measures].[Issues created],
[Issue Type].[Block])

Hi @zane.baranovska

In this case KN-1699 is a story.

  • KN-1746 is a subtask
  • KN-1747 is a subtask
  • KN-1920 is a block
  • KN-2038 is a subtask

what I need is to be able to count how many of those 4 issues are blockages (following the measure you mentioned in the previous section)

--counta commas only if field has any value
CASE WHEN NOT IsEmpty([Measures].[Issue labels]) THEN
Len([Measures].[Issue labels])
- Len(Replace([Measures].[Issue labels], ',', ''))
+ 1
END

@Marcelo_Ignacio_Cid1 expression to count commas won’t work for this scenario.

Use a tuple expression of measures “Issues created” and subtask issue type “Block” to see how many block issues are related to each story:

([Measures].[Issues created],
[Issue Type].[Block])

Make sure to use Sub-task hierarchy to represent stories on report rows. Then the measures, like Issues created, Hours spent, etc. aggregate results.