SEARCH - Daxplained

SEARCH is a DAX function that returns the starting position of one text string within another, without considering case sensitivity.

Syntax

SEARCH(
   FindText,
   WithinText,
   StartPosition,
   NotFoundValue
)

Argument	Properties	Description
FindText		The text you want to find. You can use the ? and * wildcard characters; use ~? and ~* to find the ? and * characters.
WithinText		The text in which you want to search for FindText.
StartPosition	Optional	The character position in WithinText at which you want to start searching. If omitted, the default value is 1.
NotFoundValue	Optional	The numeric value to be returned if the text is not found; if omitted, an error is returned.

Return Values

The number of the starting position of the first text string from the first character of the second text string.

Remarks

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’, ‘Á’.
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 function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

The following DAX query finds the position of the first letter of “cycle”, in the string that contains the reseller name. If not found, Blank is returned.

SEARCH is case-insensitive. In this example, if “cycle” or “Cycle” is used in the find_text argument, results are returned for either case. Use FIND for case-sensitive.

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        TOPN ( 10, SUMMARIZE('Reseller', [Reseller], [Business Type])),
        "Position of cycle", SEARCH ( "cycle", 'Reseller'[Reseller], 1, BLANK () )
    ),
    'Reseller'[Business Type] IN { "Specialty Bike Shop", "Value Added Reseller", "Warehouse"}
)

Returns,

Reseller	Business Type	Position of cycle
Volume Bike Sellers	Warehouse
Mass Market Bikes	Value Added Reseller
Twin Cycles	Value Added Reseller	6
Rich Department Store	Warehouse
Rental Gallery	Specialty Bike Shop
Budget Toy Store	Warehouse
Global Sports Outlet	Warehouse
Online Bike Catalog	Warehouse
Helmets and Cycles	Value Added Reseller	13
Jumbo Bikes	Specialty Bike Shop

Other functions related to SEARCH are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/search-function-dax

Syntax

Return Values

Remarks

Example

Related functions