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

DAX: FIND or SEARCH part of a string in a column?

I want a custom column to search a part of a text within another column.

For example, I want to find out if "TRF" is in column Descrição do Movimento

 

Data Mov.   Data Valor  Descrição do Movimento  Valor em EUR
17-02-2017  17-02-2017  CONSTITUICAO 123/004    -2.000
17-02-2017  17-02-2017  TRF 0000082 CUSTOMER A  368
17-02-2017  17-02-2017  TRF 0000082 CUSTOMER B  66
17-02-2017  17-02-2017  TRF 0000082 CUSTOMER C  98
17-02-2017  17-02-2017  TRF 0000082 CUSTOMER D  861

This is the code I'm trying to the calculated column:

Column = FIND("TRF";'ContaBancoX'[Descrição do Movimento])

And also:

Column = SEARCH("TRF";'ContaBancoX'[Descrição do Movimento])

Both returning the error:

The search Text provided to function 'SEARCH' could not be found in the given text.

And all the rows return #ERROR

How to fix this? Thanks in advance!

PS: Here's a link for the table: https://docs.google.com/spreadsheets/d/1vc6c4rV7-NpE1ClbF0dYcWa0PhH4hIMr38QU4EFKuxc/edit?usp=sharing

1 ACCEPTED SOLUTION

Accepted Solutions

Re: DAX: FIND or SEARCH part of a string in a column?

They are optional.  The problem with the function is that if it can't find your text in the string it throws an error (unless you specify the 4th argument), and Power BI errors the whole column.

 

The first row in your posted dataset doesn't contain the text 'TRF' and that is what is causing the errors for all the other rows.  Seems a bit silly to me, but if you use the 4th argument then it doesn't effect the other rows.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

13 REPLIES 13

Re: DAX: FIND or SEARCH part of a string in a column?

Please try this 

 

Column = SEARCH("TRF";'ContaBancoX'[Descrição do Movimento],,0)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

webportal Member
Member

Re: DAX: FIND or SEARCH part of a string in a column?

Yes, that works.

But aren't the 3rd and 4th argument optional?

Re: DAX: FIND or SEARCH part of a string in a column?

They are optional.  The problem with the function is that if it can't find your text in the string it throws an error (unless you specify the 4th argument), and Power BI errors the whole column.

 

The first row in your posted dataset doesn't contain the text 'TRF' and that is what is causing the errors for all the other rows.  Seems a bit silly to me, but if you use the 4th argument then it doesn't effect the other rows.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Sean Super Contributor
Super Contributor

Re: DAX: FIND or SEARCH part of a string in a column?

@webportal

You have another option to handle the error and also to return Text instead of a Number

 

TRF Check (DAX Number) =
SEARCH ( "TRF"; 'ContaBancoX'[Descrição do Movimento];; 0 )

TRF Check (DAX Text) =
IF (
    ISERROR ( SEARCH ( "TRF"; 'ContaBancoX'[Descrição do Movimento] ) );
    "No";
    "Yes"
)

Plus you can also very easily handle this in the Query Editor by creating a Conditional column using the User Interface

 

QE - Conditonal Column (DAX).gif

 

Hope this helps! Smiley Happy

webportal Member
Member

Re: DAX: FIND or SEARCH part of a string in a column?

This is cool, thanks a lot!

 

In fact, I need to categorize each row according to the text in column "Descrição do Movimento", so I guess I'll need nested IF statements or a SWITCH function.

Re: DAX: FIND or SEARCH part of a string in a column?

SWITCH statements are waaay cooler than nested IF statements 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

dexterwoolfe Frequent Visitor
Frequent Visitor

Re: DAX: FIND or SEARCH part of a string in a column?

Hi 

 

How would this work with nesting multiple ORs for the text searching?

webportal Member
Member

Re: DAX: FIND or SEARCH part of a string in a column?

Yep, that's great too!
AltranPBI_123 Frequent Visitor
Frequent Visitor

Re: DAX: FIND or SEARCH part of a string in a column?

Hello!

 

Can I use this formula and how do I apply it if I want to search "yes or no" and that I only show the values of "Descrição do Movimento" according to my search?

I do not know if I explain, but... if I have a filter: yes/no, and I want when I select "Yes"  only get the values that contain "yes" in a list.

 

Thanks for the support

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)