Using the excel function SEARCH in PowerPivot DAX

By | July 7, 2010

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.powerpivotblog.nl/using-iferroriserror-in-your-dax-formula

  • Hi Kasper…

    many thanks for your article and your time:-)

    IMHO your article confirms that the wildcards are working and that the feature seams to be undocumented in the PowerPivot documentation. But the most interesting thing you said is the similarity to the Excel functions and that the code of them was pasted and copied to PowerPivot.

    In fact, I did not have the idea to have a look on the Excel SEARCH() function. According to parameter help window in the function wizard and the online help in Excel 2010, wildcards * and ? are allowed in this function and if you are searching * or ? themselves, you may use ~? and ~*. And of course, this works.

    I also tested ~* and ~? in my PowerPivot table and, yes, this works. Then I checked the FIND() function in Excel and in PowerPivot. And, as expected they are similar. The Excel function does not support wildcards in this case and the PowerPivot FIND() has the same behaviour.

    I will also write an article about this in my german blog and of course refer to this article and your blog. Thanks again.

    Regards

  • m_okuda

    This article was very helpful!
    Thank you!