Forum Discussion

smillward's avatar
smillward
Frequent Visitor
9 years ago
Solved

Searching/Counting mutiple values from one cell

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...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi smillward,

     

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

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi smillward,

 

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

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's avatar
smillward
Frequent Visitor
9 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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. :smileyhappy:

     

    Regards