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.
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:
Link to Power BI file: here.
- The search function is case insensitive. Searching for “N” will find the first occurrence of ‘N’ or ‘n’.
- The search function is accent sensitive. Searching for “á” will find the first occurrence of ‘á’ but no occurrences of ‘a’, ‘à’, or the capitalized versions ‘A’, ‘Á’.
- By using this function, you can locate one text string within a second text string, and return the position where the first string starts.
- 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.
- 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.