Forum Discussion

ukare1996's avatar
ukare1996
Helper I
3 years ago
Solved

Countrows containing

Hi,

I will try and explain this in the best way possible.

I have some columns that within each row they might have different options which are semicoloun separated. 

Take for example the below coloumn - Row one contains two select options Private drains cleaned and GM UPGDARED....

within the hightlight row, I only just want to count "GM UPGRADED following....

My question is, how can I count item semicolon separted within a row? If that makes any sense

 

I have tried the below but it' not returning me the correct result

 

Monitor 1 -  TEST =
COUNTROWS(
    FILTER('Network (Test)',
    'Network (Test)'[Monitor 1 - Action] IN({"GM installed following NPO visit/advice", "GM (ADDITIONAL) installed following NPO visit/advice", "GM UPGRADED following NPO visit/advice"}
    )))
 
The above formula is only returning me 1 - not taking into account the row with multiple options separated with semicolons
Any help will be much appreciated 🙂

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    ukare1996 , try like

     

    Monitor 1 - TEST =
    COUNTROWS(
    FILTER('Network (Test)',
    Containsstring('Network (Test)'[Monitor 1 - Action] , "GM installed following NPO visit/advice")
    || Containsstring('Network (Test)'[Monitor 1 - Action] , "GM (ADDITIONAL) installed following NPO visit/advice")
    || Containsstring('Network (Test)'[Monitor 1 - Action] ,"GM UPGRADED following NPO visit/advice")
    ))

     

     

    CONTAINSSTRING and CONTAINSSTRINGEXACT: https://www.youtube.com/watch?v=XbgLGDvWdWQ&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=44

  • ukare1996's avatar
    ukare1996
    Helper I
    3 years ago

    Thank you, I honestly dont know why I didnt think of doing it this way 🙂

    Much appreciated