Search for " (quote) in string

[Maxim ZelenskyParticipant Started: 1Replies: 0Total: 1]

Hi All.

Could you please explain a strange behavior of SEARCH function?

Let I have a table with [text_column]. Text in this column can/cannot contain quotes, like:

asdf

as”df

“asdf”

I need a calculated column to mark string as “with quotes” / “no quotes”

=SEARCH(“”””,[text_column])

result is… all errors.

but if we envelope it in ISERROR:

=ISERROR(SEARCH(“”””,[text_column]))

then we’ll get TRUE, FALSE, FALSE

Also, if we add 4th argument to SEARCH, result is correct:

=SEARCH(“”””,[text_column],,0)

// result is 0, 3, 1

 

What is wrong with SEARCH without 4th arg?

 

Thanks,

Maxim

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Maxim:

Thanks for reaching out with this question.  From where I sit (having had a similar feeling of exasperation while struggling with my first attempts with SEARCH), I see this as part of the DAX learning curve we all go through.  This is how I came to terms with the matter:

The #ERROR  seen in all cells of a column is more an indicator of a failure of a formula, not so much for an error in a single cell.  Although it may seem like splitting hairs, the important point is the perspective: in software that is made to influence decisions, there should be a low acceptance of any error returned by a formula (1 avoidable error = failure).

Regarding an example expression =SEARCH(“-“,[PostalCode])  the online documentation says “The FORMULA in the preceding example will FAIL if the search string is not found in every row of the source column.”

The “optional” fourth parameter (the “not found value”) is only optional when one can assure Power Pivot that it will always find a match for the search expression in the expression searched, otherwise (as the documentation explains) the formula will fail when no match is found.  In other words, if one does not specify the fourth argument, they are implicitly declaring their intent that they choose to have the formula fail (see #ERROR in every cell of a column) when any cell does not have a matching value.

These thoughts helped me get a handle on SEARCH and continue my climb up the sometimes slippery slope of DAX.  Hopefully they will help you also to get a handle on SEARCH and then move on to the next challenge.

Tom

[Author]

Posts