Dax Function: SEARCH

Category: Text Functions

The SEARCH function in Power BI is a DAX (Data Analysis Expressions) function used to find the position of a substring within a larger text string. It is case-insensitive and provides the starting position of the first occurrence of the substring.

Purpose

• Substring Identification: Locate specific patterns or characters within a text.

• Dynamic Parsing: Use in conjunction with other DAX functions to extract or manipulate text based on the substring’s position.

• Data Transformation: Helps in splitting, categorizing, or cleaning text fields for analysis.

Type of Calculations

The SEARCH function performs scalar text calculations, returning the position of a substring as an integer.

Practical Use Cases

1. Email Parsing: Find the position of @ in email addresses to extract domain names.

2. Error Detection: Identify the position of error codes or patterns in log entries.

3. Custom Splitting: Locate delimiters like commas or hyphens for splitting strings dynamically.

How Does SEARCH Dax Function Works

• Case-Insensitive Search: The SEARCH function is not case-sensitive, so A and a are treated as identical.

• Position Counting: Positions are counted starting from 1. If a start_position is provided, the search begins from that index.

• Error Handling: If the substring is not found and no not_found_value is provided, an error is returned.

What Does It Return?

The function returns an integer indicating the starting position of the first occurrence of find_text within within_text. If the substring is not found and not_found_value is specified, that value is returned instead.

When Should We Use It?

• Dynamic Text Extraction: To locate specific characters or words for slicing text.

• Conditional Parsing: In scenarios where the substring’s existence or position determines the next action.

• Custom Error Handling: Using not_found_value for robust handling of missing substrings.

Examples

Basic Usage – Find a Character:

Result: 8

Start Position – Skip Initial Characters:

Result: 9 (skips the o in “Power”)

Column Usage – Extract Domain:

Extracts the domain name from email addresses.

Advanced Usage – Combine with FIND:

Extracts the subdomain from an email address like info.domain.com.

Tips and Tricks

• Use not_found_value to avoid errors when find_text does not exist in within_text.

• Combine with MID, LEFT, and RIGHT for advanced text manipulation.

• For case-sensitive searches, consider using the FIND function.

Performance Impact of SEARCH DAX Function:

• Efficient for scalar operations but may slow down in calculated columns for large datasets.

• Ensure appropriate use of not_found_value to avoid unnecessary error handling.

Related Functions You Might Need

• FIND: Similar to SEARCH but case-sensitive.

• MID: Extracts a substring from a specific position.

• LEN: Returns the length of a string.

• REPLACE: Replaces part of a text string.

Want to Learn More?
For more information, check out the official Microsoft documentation for SEARCH You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.