Searching/Counting mutiple values from one cell

[smillward]

So currently i have a column storing multiple values in one cell (it captures answers from a multiple select survey),

So the column contains values such as

1.   Alpha 1

2.   Alpha 5

3.   Alpha 1, Alpha 5

4.   Alpha 1, Alpha 10

5.   Alpha 10

I want to count the number of rows with each value, so the desired output would be:

Alpha 1: 3

Alpha 5: 2

Alpha 10: 2

however I can't get it to count Alpha 1 properly.

I've been trying to use the SEARCH function combined with calculate to get at these, however my attempts so far have Failed to produce the desired result

My current formula is

Measure 1 : = CALCULATE ( COUNTROWS (Table), SEARCH ( "*Alpha 1,*", Table[Column],,0 ) > 0 )+ CALCULATE ( COUNTROWS ( Table), SEARCH ( "*Alpha 1", Table[Column],,0 ) > 0)

In my head, the first calculate function should count the rows where it finds the string "Alpha 1," which would be 3 and 4, then add it to the rows where it finds "Alpha 1" and immediately terminates, which would be row 1.

The second calculate function seems to be returning all rows where it finds "*Alpha 1*", and not "*Alpha 1%END OF STRING%, so it returns 1, 3, 4 and 5. so my end results is:

Alpha 1: 6

Alpha 5: 2

Alpha 10: 2

If anyone can help me understand what I'm missing, I would be most grateful

[v-ljerr-msft · Accepted solution]

Hi @smillward,

Based on my test, the formula below should work in your scenario. 

Measure =
CALCULATE ( COUNTROWS ( Table1 ), SEARCH ( "Alpha 1", Table1[Column],, 0 ) > 0 )
    - CALCULATE (
        COUNTROWS ( Table1 ),
        SEARCH ( "Alpha 1?", Table1[Column],, 0 ) > 0
            && SEARCH ( "Alpha 1,", Table1[Column],, 0 ) = 0
    )

Regards

[smillward]

Thanks that does seem to be working perfectly.

So given that * isn't needed as a wildcard, can you explain what the ? represents in the string. is it an END charachter?

[v-ljerr-msft]

Hi @smillward,

A question mark(?) matches any single character. For example, "Alpha 1?" can represent both "Alpha 10" and "Alpha 1," in your sample data. 

Regards

[jthomson]

That the string "Alpha 10" contains "Alpha 1" as part of it is probably going to throw it off in some way?