Using the excel function SEARCH in PowerPivot DAX

Last week I got a question about he search function in PowerPivot:

I have used the text function SEARCH() in my sample. While doing this I noticed that wildcards seems to be allowed. For example if I do something like this: SEARCH(“google.*/search”,[Referer],1), then google.de/search or google.com.au/search are found. The problem now is that I haved searche the documentation of the DAX functions (TechNet, …) for getting sure that wildcards are allowed. Unfortunately I did not find clear official statements about this. Do you perhaps know if wildcards are really supported or perhaps know some links where I can get more info.

Of course i started searching the internet for a DAX help file at for example the PowerPivot wiki. I unfortunately couldn’t find the answers asked on my questions page. But since most PowerPivot functions are not only similar to the Excel functions but in most cases is the code from these functions copied and pasted into PowerPivot. So we now can broaden our search to include excel sites as well (this won’t count for all the functions).

Here i found the following about wildcards and the SEARCH function:

Wildcards are generic characters used in the following ways:

? (question mark) is used as a placeholder for a single character. For example, to search for “bl?ck” would find “black” and “block.”

* (asterisk) serves as a placeholder for a series of characters within a word. A search for “*body” would return “anybody,” “somebody,” and “nobody.”

To test this i used the search function in my contoso dataset in a calculated column:

=IFERROR(SEARCH(“Product03?1*”,DimProduct[StyleName]),-1)

This function returns a 1 (first position) if the stylename contains Product03 + any character +1 + any other char

if nothing is found it will give an error, we don’t want that, so the catch this using the iferror and return a -1 if nothing is found. As you can see in this example:

So when you cannot find the information you need of a DAX function, broaden your search to include excel function as well, the bets are good that a function (especially text functions) is based on excel.

Update: please make sure you read this post: http://www.kasperonbi.com/using-iferroriserror-in-your-dax-formula