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

[Anonymous]

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

[PaulDBrown · Accepted solution]

@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:

[PaulDBrown · Accepted solution]

@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]

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]

 @Anonymous 

What version of Power BI Desktop are you using? 

It should work if you have the latest version installed.

[Anonymous]

I use this version : 2.76.5682.0

Is it the latest version?

[PaulDBrown]

@Anonymous 

Latest version is: 2.76.5678.782 64-bit (December 2019)

It is important to use the latest versions for a number of reasons, including the fact that functions are added to DAX periodically. (You may be using a version which does not have CONSTAINSSTRING included).

I cannot recall when the function was added, so I can't pinpoint the exact version which started including this function.

EDIT: download the latest version from the link on this page:

https://powerbi.microsoft.com/en-us/desktop/ 

[Anonymous]

I've installed the new version and here's the error message that I get

It tells that the syntaxe is incorrect : 

Do you think it's related to the fact that i'm using free power BI?

Do i need a pro license?

Regards,

[Anonymous · Accepted solution]

Hi 

It was a syntax error,

I replaced ";" by "," and it worked.

Thankyou very much for your help

[PaulDBrown]

@Anonymous 

Oh, sorry about that. My version pf Power BI uses ";" as a separator instead of a ",". I should have warned you ealier.

I'm glad you worked it out,

PS. Can you please mark the post which Solved your question as the solution? It will help others looking for similar solutions.Thanks.