DAX Text - FIND function

Description

Returns the starting position of one text string within another text string.

DAX FIND function is case sensitive.

Syntax

FIND (<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])

Parameters

Sr.No. Parameter & Description
1

find_text

The text you want to find.

Use double quotes (empty text) to match the first character in within_text.

You can use wildcard characters — the question mark (?) and asterisk (*) — in find_text.

  • A question mark matches any single character.
  • An asterisk matches any sequence of characters.

If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
2

within_text

The text in which you want to search.
3

start_num

Optional.

The character at which to start the search.

If omitted, start_num = 1. The first character in within_text is character number 1.
4

NotFoundValue

Optional.

The value that should be returned when the DAX FIND function does not find find_text in within_text.

It should be an Integer or BLANK ().

Return Value

  • Number (Integer) that shows the starting position of the find_text in within_text, if it is found.

  • If find_text is not found in within_text and NotFoundValue is specified, then that value (an Integer or BLANK ()).

Remarks

  • If you provide the argument find_text as a text string, it should be enclosed in double quotation marks.

  • If find_text is not found in within_text and NotFoundValue is omitted, DAX FIND function returns #ERROR.

  • NotFoundValue should be an Integer or BLANK (). It should not be any other value.

  • If you specify start_num that is greater than the start position of the first instance of find_text in within_text, then FIND function returns a number only if a second instance of find_text exists in within_text. Otherwise, it returns NotFoundValue. You can use this to find the duplicated text within a text string.

Example

= FIND ([ProductName], [Product Description],, BLANK ())

This returns a blank, if the product name is not mentioned in the product description.

You can use such verification to ensure that the product description contains the product name at least once.

= FIND (“Powder”, [ProductName],, BLANK ())

This returns an integer only if the product name contains the text – Powder. Otherwise, it returns blank.

You can use such verification to find different types of products.

dax_functions_text.htm
Advertisements