Introduction to Search and Find Functions
The search and find functions in Power BI DAX are used to search for a specific value within a text string and return its position or extract a substring based on a specified condition.
1. SEARCH Function
The SEARCH function is used to find the position of a specified text string within another text string. It returns the starting position of the first occurrence of the search text.
The syntax of the SEARCH function is as follows:
SEARCH(search_text, text, [start_num])
where:
- search_text: The text string to search for.
- text: The text string within which to search.
- start_num (optional): The starting position for the search. If omitted, the search starts from the beginning of the text.
Example:
SEARCH("Power", "Power BI is a powerful tool for data analysis.")
The above example will return 1, as the search text “Power” starts at the first position in the given text.
2. FIND Function
The FIND function is similar to the SEARCH function, but it is case-sensitive. It returns the starting position of the first occurrence of the search text.
The syntax of the FIND function is as follows:
FIND(find_text, within_text, [start_num])
where:
- find_text: The text string to find.
- within_text: The text string within which to search.
- start_num (optional): The starting position for the search. If omitted, the search starts from the beginning of the text.
Example:
FIND("Power", "Power BI is a powerful tool for data analysis.")
The above example will return 1, as the find text “Power” starts at the first position in the given text.
3. SEARCHB and FINDB Functions
The SEARCHB and FINDB functions are used to find the position of a specified text string within another text string, considering double-byte characters as two characters.
The syntax of the SEARCHB and FINDB functions is the same as the SEARCH and FIND functions, respectively.
4. SEARCHREPLACE Function
The SEARCHREPLACE function is used to replace a specific text string within a larger text string with a new text string.
The syntax of the SEARCHREPLACE function is as follows:
SEARCHREPLACE(old_text, start_num, num_chars, new_text)
where:
- old_text: The text string to be replaced.
- start_num: The starting position for the replacement.
- num_chars: The number of characters to be replaced.
- new_text: The new text string to replace the old text.
Example:
SEARCHREPLACE("Power BI is a powerful tool", 1, 5, "Data")
The above example will return “Data BI is a powerful tool”, as it replaces the first 5 characters (“Power”) with “Data” in the given text.
Conclusion
In this tutorial, we have explored the search and find functions in Power BI DAX and provided examples to illustrate their usage. These functions are useful for searching and extracting specific values or substrings within text strings, enabling users to perform advanced calculations and transformations in their Power BI reports and dashboards.
By understanding and utilizing the search and find functions, Power BI users can enhance their data analysis capabilities and gain valuable insights from their data.