Calculated field to filter single and multiple values from custom field

Questions & Answers
1

Hi,

I have custom field with name “Company Initiative” (multi select) with options

Arena
Hilton
Jungle
Klover
Marriott
NeMo

Users have selected multiple values for some issues (ex: Arena, Hilton, Jungle) and I would like create calculated field with below requirement:

  1. Calculated field for Arena (should exclude if selected multiple values as above).
  2. Calculated field for more than one value selected (ex: Arena, Hilton, Jungle).

To explain in JQL, it will look somthing like this for first one:
project = TCAP AND “Company Initiative” = Jungle and “Company Initiative” not in (Arena,Klover,Marriott,NeMo,Pluto,“Priority Program”,“Select”)

Please let me know if any questions.

Thanks

2

Hi Vamakam,
You can use MATCHES function for selecting specific text from a string. It uses regular expressions.

For example, to select those issues that have “Arena” substring in the property you can use:

Aggregate(
Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    CoalesceEmpty([Issue].CurrentHierarchyMember.get('Company Initiative'), "") MATCHES ".*Arena.*"
)
)

This should work for strings like “Arena, Hilton, Jungle” or just “Arena”. Note that I added CoalesceEmpty() function because if there is no property for the issue then it will return an error.

Another example: if you would like to select issues where the property is just “Arena” then you can use a simpler method:

Aggregate(
Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Issue].CurrentHierarchyMember.get('Company Initiative') = "Arena"
)
)

This will, however, will only match a string “Arena” but not a string like “Arena, Hilton, Jungle”

I hope that helps!
Gvido Neilands, flex.bi

1 Like
3

Thank you very much :slight_smile:

4

Hi,

The approach given to you by Gvido is correct and we would recommend pretty much the same.
Just it was not emphasised that this calculated member should be created in “Issue” dimension.

Martins / eazyBI team