Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

I need a function that search the value provided and count it

Hi all,

I'm trying to count the number of row that a value occures in a column.

The column is set like that : 

x+y+z

z+b+t

h+x+y+o

a+z+e+r

I need to get the number of time that one of these letters (Y for exemple) has been repeated.

I tried with COUNTA, COUNTX, COUNTAX and the result isn't correct, it always return the total number of rows.

I need a function that search the value provided and count it.

For exemple i need to know how many Y i have,  it would return 2.

ps : I used this function in excel and it works : =COUNTIF('table1M2:M1794,"*Value*")

Thanks in advance

Need Help
Tips and Tricks

8 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    You can use the function CONTAINSSTRING.

    For example, to count number of rows which contain "y", use:

    Count Y = CALCULATE(COUNTROWS(Data);
            FILTER(Data;
                CONTAINSSTRING(Data[String]; "y")))

    And you get:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Paul,

      Thank you for quick response.

      I get this error message when trying to do it : Unexpected expression FILTER, Unexpected expression CONTAINSSTRING

      Do you think that i need to install a package or something?

      Regards,

       

       

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        6 years ago

         Anonymous 

         

        What version of Power BI Desktop are you using? 

        It should work if you have the latest version installed.