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

[webportal]

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

[EMP]

You could also use CONTAINSSTRING. 

IF(CONTAINSSTRING('Table[Colum], "FindMe"), "Found It", "Didn't Find It")

[Kinsey]

I find a better solution is to use Switch True and Search with zero as the 4th part of search and fin trus for anything greater than zero (tehn use the ELSE side of switch for anything else) like so:

=SWITCH(TRUE(),
                SEARCH("House",YetiData[Centre],,0)>0,"Building"
                ,SEARCH("Car",YetiData[Centre],,0)>0,"Vehicle"
                ,"Other"
                    )

[Phil_Seamark]

Please try this 

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

[amitpatil35]

Its used for me... Thanks 

[webportal]

Yes, that works.

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

[Phil_Seamark · Accepted solution]

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.

[FaizanKhan]

Can you please look into the problem in the link ? 
Question on StackOverflow

[FaizanKhan]

Can you please look into the problem in the link ? 
Question on StackOverflow

[Sean]

@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

Hope this helps!

[webportal]

Yep, that's great too!

[AltranPBI_123]

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

[dexterwoolfe]

Hi 

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

[webportal]

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.

[Phil_Seamark]

SWITCH statements are waaay cooler than nested IF statements 🙂