While I was writing my part (PowerPivot) of an article about Microsoft Office 2010 for the German computer magazine “c’t” (issue 16/2010), I noticed that the DAX function “Search” apparently supports wildcards. However I did not find any explanations for the use of wildcards in this function on the online help website for PowerPivot.
The PowerPivot specialist Kaspar de Jonge not only writes very interesting articles about PowerPivot in his blog, but also provides an online form, where anyone who wants can ask questions about PowerPivot. So, I asked him, if he could help and if he can confirm that wildcards are allowed in the DAX function. Kaspar put me on the right track: most PowerPivot functions are not only similar to the Excel functions but in many cases the code from these functions has been copied and pasted from Excel to PowerPivot. So, as the Excel “Search” function allows the use of wildcards, the corresponding PowerPivot function will also allow wildcards. At this point, thanks again to Kaspar.
The online help for the Excel “Search” functions states that characters “?” and “*” can be used as wildcards in the “find_text” argument of the function.
A question mark matches any single character and an asterisk matches any sequence of characters. The screenshot above shows some samples. On the left side, you can see the text in which the terms (on the top) are searched. The function returns the number of the starting position of the first occurrence found in the text. If the searched term is not found, the error value #VALUE is returned. So it makes sense to check the result for errors when using the “Search” function:
C5 = IF(ISERROR(SEARCH(C$3;$B5;1));0;SEARCH(C$3;$B5;1))
Please note, that the “Search” function is not case sensitive. If you like to perform a case sensitive search, you may use the function “Find” instead of “Search”. However, the function “Find” does not support wildcards.
The screenshot above also shows some samples for wildcard searches. For example, when searching for “.co*/url”, the “Search” function returns a positive value “goggle.co.in/url?”, “google.co.uk/url?”, “google.co.za/url?”, “google.com.au/url?” and “google.com/url?”. Another example is to search for all domains containing only “google.co”; in this case I can use “co.??” as search term.
The PowerPivot function “Search” works similar to the Excel function, for example, if I would like to filter out all google domains, I can use:
The search term is the “google.*/url” and – as PowerPivot is column based and not cell based – the name of my searched column is “[Referer]”. The result will be either zero, if the search term was not found, or a positive value, if the search term is found.
Finally, the idea behind this article was to use PowerPivot for analyzing my web server log files; and being able to use wildcards in the “Search” function makes it relatively simple to filter out the referrers.
- Blog from Kaspar de Jonge, Online form for questions, English
- Blog from Kaspar de Jonge, Article about the search function, English
- Excerpt from the c’t article about Office 2010, unfortunately without our part, German
- PowerPivot for Excel and Sharepoint, English
This article has also been published on the German Version of the Excel Ticker blog.