Search text string within column

[webportal]

I'm trying to find the position (in nr. of characters) of the word "span" in column "order_product_name" of table "order_product".

For that, I'm using a calculated column with the formula:

Coluna 2 = SEARCH("span";'order_product'[order_product_name])

This syntax is exactly the same as used in the DAX reference so I have no idea why it is returning an error.

[Ramadevi]

Hi,

Can you try below. 

Created New column with below logic.

SearchColumn = SEARCH("Re",<Column Name>,1,0)

It gave me the position of the word 'Re' 

For example,

Column1     SearchColumnResult

----------      ----------------------

I am Ready     6

If the text is not found, i will get 0. Because i mentioned 0 as NotFoundValue parameter

[Anonymous]

Maybe a semicolon is the issue....

Try comma instead:

Coluna 2 = SEARCH("span",'order_product'[order_product_name])

[webportal]

Thanks, but no.

We use semicolons here to separate argument formulas.

I found that if I use:

Coluna 2 = IFERROR(SEARCH("span",'order_product'[order_product_name]); BLANK())

It works, although it doesn't make a lot of sense to me.

[Anonymous · Accepted solution]

Interesting...

I've found this in the DAX reference for SEARCH:

"If the find_text cannot be found in within_text, the formula returns an error. This behavior is like Excel, which returns #VALUE if the substring is not found. Nulls in within_text will be interpreted as an empty string in this context."

So your "IFERROR" takes care of these cases where the "span" is not found.

I think it's the way to do it.