Downloads
DAXTextIntermediate

SEARCH

Returns the position of one text value inside another, ignoring case.

Syntax
SEARCH(<find_text>, <within_text> [, <from_position>] [, <not_found_value>])

Returns: A whole number (a scalar). An error, or the not found value, when the text is absent.

What it does

SEARCH tells you where a piece of text sits inside another piece of text, and returns its position, counting from 1. It ignores case, so "reversal" and "REVERSAL" are the same. The most common use is a test: wrap SEARCH in ISNUMBER to ask "does this text contain that word?" Use it to flag journal memos, bank descriptions or invoice notes that mention a keyword.

Syntax and arguments

Example

The controller wants to list the journal lines whose memo mentions a reversal, because reversals should be reviewed at close.

Is Reversal =
IF(
    ISNUMBER(SEARCH("reversal", Journal[Memo])),
    1,
    0
)

SEARCH finds "reversal" whatever the case in the memo. If the word is present the position is a number, ISNUMBER returns TRUE, and the line is flagged. Lines without the word get 0.

Example: a second, different use

Find where the first hyphen sits in a reference, so the text before it can be taken with LEFT:

Hyphen Position = SEARCH("-", Invoices[Reference], 1, 0)

The not found value is 0, so a reference with no hyphen returns 0 rather than an error. Pair the position with LEFT to take the part before it.

Things to watch

In business terms

SEARCH finds the journal lines, bank descriptions and invoice notes that need attention: reversals, write-offs, manual adjustments, duplicate payments. The flags become the review list for a close or an internal audit sample.

Related functions

All DAX functions · View as Markdown · DAX fundamentals