DAX Text - SEARCH function


Advertisements

Description

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.

Syntax

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

Parameters

Sr.No. Parameter & Description
1

find_text

The text that you want to find.

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

A question mark matches any single character and 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 for find_text, or a column containing text.

3

start_num

Optional.

The character position in within_text at which you want to start searching.

If omitted, default is 1.

4

NotFoundValue

The value that should be returned when find_text is not found in within_text.

This can be any specific integer or BLANK ().

Return Value

An integer, or Blank if specified as NotFoundValue.

Remarks

  • DAX SEARCH function is case insensitive. Searching for "N" will find the first occurrence of 'N' or 'n'.

  • DAX SEARCH function is accent sensitive. Searching for "á" will find the first occurrence of 'á' but not any of the occurrences of 'a', 'à', or the capitalized versions 'A', 'Á'.

  • You can use the SEARCH function to determine the location of a 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 find_text cannot be found in within_text, DAX SEARCH function returns NotFoundValue, if given. If omitted, returns #ERROR.

  • Nulls in within_text will be interpreted as empty strings.

Example

= SEARCH ("Yes", "Yesterday",, BLANK()) returns 1. 
= SEARCH("yes","Yesterday") returns 1. 
= SEARCH ("no", "Yesterday",, BLANK()) returns (blank). 
= SEARCH("no","Yesterday") returns #ERROR. 
= MID("Yesterday",SEARCH("day","Yesterday"),2) returns da. 
= REPLACE("Yesterday",SEARCH("day","Yesterday"),3,"fff") returns Yesterfff. 
dax_functions_text.htm

Useful Video Courses


Video

Mastering DAX and Data Models in Power BI Desktop

53 Lectures 5.5 hours

Abhay Gadiya

Video

Mastering DAX Studio

Featured

24 Lectures 2 hours

Randy Minder

Video

DAX / Power BI - Customer and Sales Analysis Deep Dive

26 Lectures 4.5 hours

Randy Minder

Advertisements