DAX SEARCH Function

The SEARCH DAX function returns the number of the character at which a specific character or text string is first found, reading left to right.

Search is case-insensitive and accent sensitive. It comes under TEXT functions DAX category.

Syntax:

SEARCH(<find_text>, <within_text>, <start_num>, <NotFoundValue>)

Parameters:

find_text: The text you want to find, use double quote.

• 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.

within_text: The text in which you want to search for find_text, or a column containing text.

start_num: (optional) The character position in within_text at which you want to start searching. If omitted, 1.

NotFoundValue: (optional) The value that should be returned when the operation does not find a matching substring, typically 0, -1, or BLANK().

Note:

• The search function is case insensitive.
• This function does not support Direct Query mode
• This function support wildcards.

Let’s get started:

Import the following sample dataset into Power BI Desktop:

Id	Statement	Rating
1	Service	5
2	Food Quality	5
3	Atmosphere	7
4	Value for money	5
5	Cleanness	7
6	Coffe Quality	6
7	Help 24 * 7	8
8	AC / Lights	8
9	Help – Desk	10

Now follow these steps-

Step-1: Add one new column , Right click on dataset and select “New Column” and write below DAX function.

Search char position = SEARCH("R",Survey[Statement], , -1)

Step-2: Here we want to find “R” character position for all values of Statement column.




Now, navigate to the Data tab, and you will find the position number displayed. If the character is not found within the string, it will return -1.

Step-3: Support wildcard character, only for question mark(?) & asterisk (*) use tilde (~) before the character.

Step-4: For other wildcard character- no need to use tilde (~) before the character.

Refer other DAX post – DAX Tutorials

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.