Power Pivot Principles: Introducing the SEARCH Function

Welcome back to our Power Pivot blog.  Today, we introduce the SEARCH function.

The SEARCH function takes the input text character or text string and looks for it in a data value.  It returns with the character position number of where it found within the data value.  SEARCH is case sensitive and looks from left to right.

The SEARCH function uses the following syntax to operate:

• <find_text> is the text that we wish to find.  Note that we can use the ‘?’ and ‘*’ operators.  The question mark ‘?’ matches any single character to the string and the asterisk ‘*’ matches any sequence of characters.  If we wish to find an actual question mark or asterisk, we have to include the tilde ‘~’ before the question mark or asterisk

• <within_text> is the text that we wish to search for <find_text>, or a column containing text

• <start_num> is an optional input, is the character position in <within_text>, where we want to start searching

• <NotFoundValue> is an optional input; this has to be a number, or we can use the BLANK() function, and it will be returned when the operation does not find a matching value.

Let’s say we want to search for the text “Shoes” in the following data set: 

We can use the following DAX code to search for “Shoes”:

The SEARCH function returns with the text location of the first instance where it finds “Shoes” in the Product Type text string.  In row 2 it is 10, because there are eight characters in “Business”, space “ ” is the ninth character, therefore the 10^th character is where the text string “Shoes” begins.

The current formula has been set to return with zero (0) when no matches are found.

We can also use the BLANK() function in place of 0:

Notice that our column is not picking up rows 8 and 10.  That’s because the data values in those rows contain “Shoe” rather than “Shoes”.  We can adjust our DAX formula to account for this:

We included ‘?’ in our <find_text> term so that we will pick up rows 8 and 10 where it’s “Shoe ” with a space and not “Shoe(s)”.

That’s it for this week, until next time, happy pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.