Daily DAX : Day 155 SEARCH

 The SEARCH function in Power BI's Data Analysis Expressions (DAX) is a text function that allows you to find the starting position of a specific substring within a text string. It’s case-insensitive and useful for text manipulation, data cleansing, or extracting information based on patterns.

Syntax

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

    <find_text>: The substring you want to search for (required).

    <within_text>: The text string in which to search (required).

    <start_num>: (Optional) The position in <within_text> to start the search. Defaults to 1 if omitted.

    <NotFoundValue>: (Optional) The value returned if <find_text> is not found. Defaults to -1.

Return Value

    Returns the starting position (as an integer) of <find_text> within <within_text>.

    If the substring isn’t found, it returns <NotFoundValue> (or -1 by default).

    The position is 1-based (the first character is position 1).

Key Features

    Case-insensitive: It doesn’t distinguish between uppercase and lowercase letters.

    Supports wildcards: You can use ? (single character) and * (zero or more characters) in <find_text>.

    Error handling: If the substring isn’t found, it returns the specified <NotFoundValue> or -1.

Example Usage

    Basic Example:

    Suppose you have a column [ProductCode] with values like "ABC123XYZ". You want to find the position of "123":

    PositionOf123 = SEARCH("123", [ProductCode])

        For "ABC123XYZ", this returns 4 (since "123" starts at the 4th character).

    With Start Position:

    If you want to search starting from the 5th character:

    PositionFrom5 = SEARCH("XYZ", [ProductCode], 5)

        For "ABC123XYZ", this returns 7.

    Custom Not Found Value:

    If "XYZ" isn’t found, return 0 instead of -1:

    FindXYZ = SEARCH("XYZ", [ProductCode], , 0)

        For "ABC123", this returns 0; for "ABC123XYZ", it returns 7.

    Using Wildcards:

    To find a pattern like "A" followed by any two characters:

    WildcardSearch = SEARCH("A??", [ProductCode])

        For "ABC123", this returns 1 (matches "ABC").

Use Cases

    Text Extraction:

    Combine SEARCH with MID or LEFT to extract portions of text. For example, to extract everything after "123":

    TextAfter123 = MID([ProductCode], SEARCH("123", [ProductCode]) + 3, LEN([ProductCode]))

        For "ABC123XYZ", this returns "XYZ".

    Data Validation:

    Check if a specific substring exists in a column. For instance, flag rows containing "ERROR":

    HasError = IF(SEARCH("ERROR", [LogMessage], , -1) > 0, "Yes", "No")

    Conditional Formatting:

    Identify records based on text patterns (e.g., product codes with a specific prefix) for reporting purposes.

    Parsing Strings:

    When dealing with inconsistent data (e.g., email domains), use SEARCH to locate delimiters like "@" or ".".

Limitations

    It’s not case-sensitive (use FIND for case-sensitive searches).

    It doesn’t support regular expressions; wildcards are limited to ? and *.

    If <start_num> exceeds the length of <within_text>, it returns an error unless handled.

Practical Tip

Pair SEARCH with error-handling functions like IFERROR to manage cases where the substring isn’t found or the input is invalid:

SafeSearch = IFERROR(SEARCH("123", [ProductCode]), 0)

In summary, the SEARCH function is a versatile tool in DAX for locating text patterns, enabling dynamic text analysis and manipulation in Power BI reports.