Using count with search text in column.

[bolabuga]

Hello everyone, 

I would like helping for counting a specific amount of services in a table. i will list the sample.

SERVICE   	CODE	OBS
2652016000002	102023	XXXXXXXXXXXXXXXXXXX Alt.TL: de A p/ B
2652016000003	102023	YYYYYYYYYYYYYYYY Alt.TL: de B p/ A
2652016000004	102023	ADFD FDFD FDF  Alt.TL: de A p/ D
2652016000005	102023	ADSF JKDFDSÇLKFSJKL Alt.TL: de E p/ B
2652016000006	102023	ASDFBALKDJKLSJK Alt.TL: de A p/ B
2652016000007	102023	ASKDJFLÇASDJFASDDJ Alt.TL: de A p/ B
2652016000008	102023	AKDFJKLASJFASDJK Alt.TL: de A p/ B
2652016000009	102023	FLAKSJDFLÇKASJFD Alt.TL: de D p/ B
2652016000010	102023	UUUUUUUUUUUUUUU Alt.TL: de C p/ B
2652016000011	102023	XXXXXXXXXXXXX Alt.TL: de A p/F
2652016000012	102023	ASAAADDDDDDDDDD Alt.TL: de A p/ B
2652016000013	102023	AAAAAAAAAADDDDDDD Alt.TL: de C p/ B
2652016000014	102023	AAAAAAAAAASSSS Alt.TL: de A p/ B
2652016000015	102023	AAAAAAAAADDDDDD Alt.TL: de C p/ B

I would like to Count [SERVICE] if [CODE]=102023 and [obs]="Alt.TL: de A p/ B". The problem for me is that on the "OBS" column can have different kind of texts before the portion that is important.

im Trying the following thats not working:

COUNTAX(
          FILTER(TABLE; 
               TABLE[CODE]=102023
               &&
               ISNUMBER(FIND("Alt.TL: de A p/ B";TABLE[OBS]))
          );
          TABLE[SERVICE])

[Greg_Deckler · Accepted solution]

You could do it like this:

Custom Column:

ContainsText = SEARCH("Alt.TL: de A p/ B",[OBS],1,0)

Measure:

MyCount = CALCULATE(COUNTROWS(Codes),FILTER(Codes,Codes[CODE]=102023 && Codes[ContainsText]<>0))

[bolabuga]

Thks smoupre,

My main table had 1 blank space more than it should, after that everything worked fine.

[Greg_Deckler]

Awesome! Glad you got it working!

[bolabuga]

Hi smoupre, im getting this error:

"The search text provided to the 'SEARCH' function could not be found in the indicated text."

Strange, because i can see the text is there, i even searched for just "A p/ B" and its returning the same error.

[bolabuga]

Its working on my example table, i forgot the semicolon on the calculated column. "TEXT = SEARCH("ALT.TL: DE A P/ B";Tabela1[OBS];1;0)"

But its strange, in my main table, the search function is not finding the text, and it is filling everything with zeros.....Any ideas??