The DAX Text Detective: Find and Search Functions

Find and Search, both functions are used to locate substrings within a larger text string. They return the position (as a number) where the substring is found. But both may result in different values. This is because, FIND is case-sensitive, while SEARCH is case-insensitive. Also remember that they both return the position of first substring found in the string.

FIND(Text, WithinText, [StartPosition], [NotFoundValue])

The Find function returns the position of the first occurrence of text in within_text, starting from start_pos. If text is not found, it returns 0. For example FIND("apple", "I have an apple", 1) will return 11. However FIND("APPLE", "I have an apple", 1) will return nothing in DAX results, and en error in Power BI visual if the source is a measure saying “error fetching data for this visual”. Whereas FIND("APPLE", "I have an apple and APPLE", 1) will return 21. And the last use of this function clearly tells us that while searching for “APPLE”, the function has ignored the “apple”.

SEARCH(Text, WithinText, [StartPosition], [NotFoundValue])

The Search function returns the position of the first occurrence of text in within_text, starting from start_pos, ignoring case. If text is not found, it returns 0. For example SEARCH("apple", "I have an apple", 1) and SEARCH("APPLE", "I have an apple", 1) both return 11 which means Search function is not case sensitive.

Comparing both functions

Put this code inside you DAX quer view and click on the run button to compare their result. The Search function will result in 11, while the Find function will result in 24.

EVALUATE
{
	Search("APPLE","I have an apple and an APPLE",,0),
	Find("APPLE","I have an apple and an APPLE",,0)
}

Or using more sophesticated way.

EVALUATE
var f = Search("APPLE","I have an apple and an APPLE",,0)
var s = Find("APPLE","I have an apple and an APPLE",,0)
return {f,s}

NotFoundValue

Providing a NotFoundValue is very important to avoid errors. For example both Find and Search functions will result in error when used in a new column without providing a value for NotFoundValue. Since the value returned is a number, it is better to provide ‘0’ for NotFoundValue.

Key Differences

Case Sensitivity: This is the most crucial difference. FIND is case-sensitive, meaning it will only find exact matches of the substring, including uppercase and lowercase letters. SEARCH is case-insensitive, so it will find matches regardless of case.

Performance: FIND can be slightly faster than SEARCH in some scenarios, especially when searching for short substrings in large text strings. However, the difference is usually negligible.