Dax Function: FIND

Category: Text Functions

The FIND function in Power BI is a DAX function that locates the starting position of a specific substring within a text string. It performs a case-sensitive search and returns the numeric position of the first occurrence of the substring.

Purpose

To determine the position of a substring in a larger text string for further text manipulation or validation.

Calculations Performed

The function calculates the numeric position of a substring within a text string, enabling targeted operations like extracting, replacing, or validating text data.

Practical Use Cases

• Locating delimiters or specific keywords in text strings.
• Splitting or extracting parts of a text string based on the position of substrings.
• Validating whether a specific word or character exists in a given text.

How Does FIND Dax Function Works

The FIND function operates as follows:

1. Searches for the find_text substring in within_text starting at the specified start_position.
2. If the substring is found, it returns the position (1-based index) of the first character in the substring.
3. If the substring is not found, the function throws an error.

What Does It Return?

The function returns an integer:

• The numeric position of the first occurrence of find_text in within_text.
• Returns an error if find_text is not found or if the starting position exceeds the string length.

When Should We Use It?

• Text Parsing: Extracting specific parts of a text based on delimiter positions.
• Validation: Checking the existence and position of certain keywords or characters in a string.
• Conditional Logic: Using the position result to implement conditional processing or formatting.

Examples

Basic Usage

Locating the position of a substring in a text string.

Result: 1 (substring starts at position 1).

Column Usage:

Finding the position of a specific character in a column of text.

Result: A column containing the position of the “@” symbol in each email address.

Advanced Usage

Combining with other functions for text extraction.

Result: Extracts the domain part of email addresses.

Tips and Tricks

• Error Handling: Use IFERROR to handle scenarios where find_text is not found. IFERROR(FIND("Power", "BI Tools"), 0)
  This returns 0 instead of an error if the substring is not found.
• Case Sensitivity: If a case-insensitive search is required, consider using SEARCH instead of FIND.
• Optimize Start Position: When searching for multiple substrings, adjusting the start_position can improve performance.

Performance Impact of FIND DAX Function:

• On large datasets, use the start_position parameter to minimize the search range and improve performance.
• Avoid excessive nesting of text functions for complex transformations to maintain readability and efficiency.

Related Functions You Might Need

• SEARCH: Similar to FIND but performs a case-insensitive search.
• MID: Extracts a substring starting at a specific position.
• LEN: Returns the length of a text string.
• SUBSTITUTE: Replaces occurrences of a substring in a text string.

Want to Learn More?
For more information, check out the official Microsoft documentation for FIND 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.