Count issues where text field contains values from a multi select list field

Questions & Answers
, , ,
1

hi there
I have a multi select list field and we choose values , and we also have a field of text and it may have the values from the list , seperated by commas.
for example the list has A B C D E. the user selected A,B
the text might have A,B,D as text

now I want to write
Count(
Filter(
[Issue.Epic].[Epic].Members,
(InStr([Measures].[Issue textfield],[Measures].[Issue listfield]) > 0 )))

so the question I have , is my syntax correct?

2

Hi Aya,
The direction is correct, however, there could be cases when it does not return true. For example if user selected A and C from the list and Issue listfield has value “A,C”, but the text field has “A,B,C” or even “A, C” with extra space, this would not return true.

Try the following code, adjusting for your real dimension names:

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issues created]>0
  AND
  Count(
    Filter(
      [Listfield].[Listfield].Members,
      [Measures].[Issues created]>0
    )
  )=Count(
    Filter(
      [Listfield].[Listfield].Members,
      [Measures].[Issues created]>0
      AND
      InStr([Measures].[Issue textfield],[Listfield].CurrentMember.Name)>0
    )
  )
  )
))

This formula checks if all [Listfield] members of the issue are also within the [Issue textfield] field.

I hope this helps.

Best,
Ilze