SEARCH

Syntax

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

Tutorial with example

In today’s DAX Fridays! tutorial we are going to go though examples on how to use two DAX functions: SEARCH and FIND SEARCH and FIND DAX functions are used to find the number of characters at which a specific word is found in a column.

Keynotes:

00:58 SEARCH function DAX Microsoft Documentation
02:20 DAX search function without options
04:00 DAX search function with options
04:43 DAX search function Error validation options
05:49 DAX search function with * wildcard
07:00 DAX search function with ? wildcard
08:52 Search and Switch functions
10:35 Find function DAX Microsoft Documentation
10:57 Find function explained
11:32 Find vs Search function

Download example file:

To get the files:
1. Go to Curbal Download Center > Dax Fridays
2. Get File #30′

Find multiple values using DAX:

In this video, I show you how to do a partial search using FIND, but you can replace FIND with SEARCH if needed:

Remarks

1. The search function is case insensitive. Searching for “N” will find the first occurrence of ‘N’ or ‘n’.


2. The search function is accent sensitive. Searching for “á” will find the first occurrence of ‘á’ but no occurrences of ‘a’, ‘à’, or the capitalized versions ‘A’, ‘Á’.


3. By using this function, you can locate one text string within a second text string, and return the position where the first string starts.


4. You can use the SEARCH function to determine the location of a character or text string within another text string, and then use the MID function to return the text, or use the REPLACE function to change the text.


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

This DAX function may return different results when used in a model that is deployed and then queried in DirectQuery mode. For more information about semantic differences in DirectQuery mode, see http://go.microsoft.com/fwlink/?LinkId=219171.