Count if contains part of text

[Anonymous]

Hi all!

I wonder if is there any function in DAX if I can count how many cells in a column contain a certain part of text.

Imagine tha I have a list of names and I want to count how many names contains the letter "a" for example. 

In excel we can do it by just puting "*" like this: countif(table[names];"*a*")

Right now I am using an auxiliar column to do it in my excel file, but I would like to do it without that auxiliar column.

I've also used the function search in power bi in a column, but I am really looking for a measure that can give me this result

Thank you so much 

Best regards

[Floriankx · Accepted solution]

Pleae try this:

Countif:=CALCULATE(
COUNTROWS(Table);
SEARCH("a";Table[names];;0))

[Anonymous]

I tried this formula out but it did not accept the semicolon, only commas. Do comma's work as well?

[Anonymous]

Another question, imagine now that I have another column with values and I want to sum all the values that whose name has the letter "a".

How would I change the formula that you gave me?

Thanks in advance

[Floriankx]

It should work if you build two measures and simply add them.

[Anonymous]

Sorry, but I didn't get it.  Imagine this example the final result would be 90 (sum of aanthony adam and agatha). 

names	values
anthony	40
adam	30
phill	50
john	30
agatha	20

What do you mean by adding two measure? One would be the countrows and the other one the value of each row? 

But how would I do it?

[Floriankx]

Hello,

you just have to exchange the expression in your CALCULATE statement.

Sumif:=CALCULATE(
SUM(Name_search[values]);
SEARCH("a";Name_search[names];;0))

[jochendecraene]

Hello,

I'm using this solution. I want to count 3 different textstrings in a colum.

I get an error cause of circular dependency when I write the function for the second string a want to count. 

[Anonymous]

Great, it works perfectly!

Thank you so much