cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bolabuga Member
Member

Using count with search text in column.

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])
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Using count with search text in column.

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))

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: Using count with search text in column.

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))

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

bolabuga Member
Member

Re: Using count with search text in column.

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.

 

Capturar.PNG

Highlighted
bolabuga Member
Member

Re: Using count with search text in column.

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??

bolabuga Member
Member

Re: Using count with search text in column.

Thks smoupre,

 

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

Super User IV
Super User IV

Re: Using count with search text in column.

Awesome! Glad you got it working!

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors