cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

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
Highlighted
Microsoft
Microsoft

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
Highlighted
Microsoft
Microsoft

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!

Highlighted
Post Prodigy
Post Prodigy

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

Yes, that works.

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

Highlighted
Microsoft
Microsoft

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
Community Champion
Community Champion

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

Highlighted
Post Prodigy
Post Prodigy

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.

Highlighted
Microsoft
Microsoft

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!

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

Highlighted
Post Prodigy
Post Prodigy

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

Yep, that's great too!
Highlighted
Regular 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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors