Search for distinct word DAX formula

daxpowerpivot

I am trying to create a columns which looks at another column called software name, if this column contains a certain word the new column will say what it is. so for example chrome is found in the software column, in the new column this will say Google and then if the software column contains firefox, the new column will have mozilla. Ive tried using the contain and search function and i come out with #ERROR all the time.
Anyone got a solution??

Pseudo code

=IF(CONTAINS([softwareName],"Chrome"),"Google", IF(CONTAINS([softwareName],"Firefox"),"Mozilla","Unknown"))

Best Answer

This should work: 

=IF(IFERROR(SEARCH("Chrome",[SoftwareName]),-1) <> -1, "Google",IF(IFERROR(SEARCH("Firefox",[SoftwareName]),-1)<>-1,"Mozilla", "Unknown"))

The Search function in DAX actually returns an error if it cannot find the string you provided. Otherwise it returns the starting position where the string can be found. See the DAX reference for more details. So I used iferror to catch the error returned when it can't find the string. If it doesn't find the string (and therefore Search returns an error) , it returns -1 instead, which cannot possibly be a valid start position in this context. If the search for "Chrome" is not -1, "Chrome" was found so the value is "Google". Otherwise, it moves on to the next if statement.

Related Topic