Colin Phillips (Alumni)
I’ve found that when using the DAX search function that I often need to include the two optional parameters (“start num” and “not found value”).
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
I’ll explain the reasoning behind my statement. What I’ve found when not using the optional parameters, if that if you have no matching value from your search, you end up with an “#ERROR” cell in Excel, and that just makes the field “dirty” with lots of “error” cells hanging around (and often causes issues with using the column later on).
For example the following formula
=SEARCH(“BOB”,[Client])
when searching on the data below returns these results
| Source | Result |
| BOBBY | 1 |
| SUE | #ERROR |
| JOE | #ERROR |
| SAM | #ERROR |
| SALLY | #ERROR |
However, when using the optional parameters
=SEARCH(“BOB”,[Client], 1, 0)
when searching on the same data returns these (much more easily digestible) results
| Source | Result |
| BOBBY | 1 |
| SUE | 0 |
| JOE | 0 |
| SAM | 0 |
| SALLY | 0 |
Alternatively, you can use this formula below without the optional parameters, but I prefer to account for my errors as much as possible within the given statement, instead of catching them outside a statement.
=IFERROR(SEARCH(“BOB”,[Client]), 0)
Which will return the same results as the second table above.
Note, don’t confuse the DAX Search function this with the Excel Search function. The two are similar, but have different parameters, and are intended to work in different places (one works within the PowerPivot window in Excel, the other is designed to work outside the PowerPivot window in Excel.