Counting number of options in a multiselect custom field

[Matthew_Feldman]

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

[zane.baranovska]

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.

--count 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

[Marcelo_Ignacio_Cid1]

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).

[zane.baranovska]

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:

Filter open issue by summary

Hi @karim , I don’t recommend defining such calculated members in the Issue dimension. They are a performance risk. If you don’t have the option to create a new custom field in Jira, define a new JavaScript calculated custom field in eazyBI. See more details on how to do that here - JavaScript calculated custom fields - eazyBI for Jira. Add parameters similar to the one below to your eazyBI advanced settings: [jira.customfield_developer_issue] name = "Issue summary Developer" data_type = "st…

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

Measure to count number of subtasks of specific issue type per parent issue

Hi @bar368, The calculated measure “Dependency Subtask” looks for issues with type Dependency Subtask and that also has some subtasks under it. As Dependency subtasks are sub-tasks themselves and can not have any other sub-tasks, the calculated measure returns no data. To make this work, you might want to use the Sub-task hierarchy in the Issue dimension. And then you may use measure Issues create for the calculation to represent subtasks with a particular issue type. When using a hierarchy, m…

[Marcelo_Ignacio_Cid1]

Hi @zane.baranovska

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

[zane.baranovska]

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])

Measure to count number of subtasks of specific issue type per parent issue

Hi @bar368, The calculated measure “Dependency Subtask” looks for issues with type Dependency Subtask and that also has some subtasks under it. As Dependency subtasks are sub-tasks themselves and can not have any other sub-tasks, the calculated measure returns no data. To make this work, you might want to use the Sub-task hierarchy in the Issue dimension. And then you may use measure Issues create for the calculation to represent subtasks with a particular issue type. When using a hierarchy, m…

[Marcelo_Ignacio_Cid1]

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

[zane.baranovska]

@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.

[Marcelo_Ignacio_Cid1]

Hi @zane.baranovska I have another question .

Is it possible to filter the type of linked problem? for example, it only counts entries linked under the Issue labels parameter that are of type Task & Bug

[zane.baranovska]

Hi @Marcelo_Ignacio_Cid1,

Yes, you can group and filter linked issues by their fields, if that information is imported in eazyBI.
Here are several options.

1. You can build calculated measures to count linked issues by linked issue fields if the linked issues project is also selected for data import in eazyBI (check if you can find the linked issue in “Issue” dimension).
   See those Community posts for similar use cases:

   • Linked issue count
   • Count issues with linked "analyzes" Issue type Problem Record
   • Import issue links as dimension - #18 by gerda.grantina
   • training video: Advanced MDX: Recipe to Change Report Context.

2. You can precalculate the count of linked issues for each issue during data import and import the number as an issue property if you want to group data by linked Issue type, priority, latest status, or project.
   See this community post for more details: Stories without feature link - #2 by zane.baranovska.

Best,
Zane / support@eazyBI.com