|String Comparison in #DAX|
|Written by Marco Russo|
|Sunday, 08 January 2012 18:52|
In DAX you don't have the LIKE operator and you have to use SEARCH instead. However, performance are not very good and it is better to use LEFT and RIGHT if you just need to compare the initial (or ending) match of a string.
I just wrote an article about string comparison in DAX. During my exploration of LIKE replacement functions in DAX, I have found that documentation of SEARCH for PowerPivot v1 is wrong - in DAX the SEARCH function is always case-insensitive, whereas FIND is always case-sensitive. Moreover, in PowerPivot v1 you had to use IFERROR to catch the string not found condition. This was a big issue in performance and in PowerPivot v2 (and in BISM Tabular) the new SEARCH and FIND functions have a fourth parameter that specify the value that should be returned when a match is not found. Using FIND seems to be 10% faster than SEARCH, just because it is case-insensitive.Read more...
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of PowerPivot DAX functions with description
- How to install PowerPivot for Excel and list of know issues
- List of suggested datasets to test PowerPivot
- Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
- Learning PowerPivot and DAX
- Using Power Pivot and Power View for Profit Analysis
- DAX cheat sheet