Counting responses in rows - multiple text strings needs to be ignored

[ddw]

Hi Forum

I need to count rows with a measure where there are some "regular", and some "mixed" responses to a survey all stuck in one column. I need to count the columns where "other" responses where provided.

For simplicity, lets call the regular responses A, B and C.

All regular responses A's, B's and C's are always the same, so A might be = "yes", B = "no", C = "don't know", as an example.
And lets call all other responses X. X can be anything. Like - X = "this is a stupid survey" or X = "why are we doing this"

So the data might be as follows in my Survey_Column:
row 1 : A; B; C
row 2 : A; B
row 3 : X
row 4 : X; A; B

I've tried:

CALCULATE(
COUNTROWS('Table'),
SEARCH("A",'Table'[Survey_Column],1,-1)=-1,
SEARCH("B",'Table'[Survey_Column],1,-1)=-1,
SEARCH("C",'Table'[Survey_Column],1,-1)=-1
)

This returns a count of 1, since all the rows with A, B and C has been left out. But I need the count to be 2, since there are two rows where other responses were provided?

[MattAllington]

I suggest you split the responses into rows. You can do this in power query using the split columns by delimiter feature. Just make sure you select “to rows”. Then you table will have one row for each answer.

[rwfigtree]

yes power query is the way to go.

[c1] is the column with your ";" delimited list.
add a custom column which splits the answers into a list, removes entries of A B c, then counts the remaining entries.

= Table.AddColumn(#"Changed Type", "Custom", each List.Count(List.RemoveItems( Text.Split([c1],";"),{"A","B","C"})))

returns number > 0 if answer list has any entries other than A B C

in dax you can then count rows with the custom column value > 0

eg calculate(countrows(table),filter(table,customcolumn>0))