cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User

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
Super User

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

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
Super User

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)