DAX SEARCH statement nested in an IF statement

I need some help with a fairly easy DAX statement.  I'm trying to calculate a column the produces 1, 0 based on a SEARCH function nested in an IF statement.  The text I'm searching for in my original column is "LSB."  However, "LSB" is a substring, not the full string.  And "LSB" has different starting positions in different values throughout the column (not sure if this matters, but just pointing that out).

I keep getting the error message:  "Calculation error in column 'table'[]: The search Text provided to function 'SEARCH' could not be found in the given text."

= IF(SEARCH("LSB", [Column]) > 0, 1, 0)

Any suggestions???  Where's my mistake?  Thanks much.

September 23rd, 2013 11:14pm

Hello,

If not all rows contains the search value LSB, then you have to provide a value for the NotFoundValue parameter ; see SEARCH Function

= IF(SEARCH("LSB", [Column],,0) > 0, 1, 0)

Free Windows Admin Tool Kit Click here and download it now
September 24th, 2013 3:40am

That's it!   It worked!

Thank you so much!!!!

  • Marked as answer by Undergrads Tuesday, September 24, 2013 1:45 PM
  • Unmarked as answer by Undergrads Tuesday, September 24, 2013 1:45 PM
September 24th, 2013 4:44pm

Hi Olaf,

I am working with Excel 2010 (Not 2013), but I could not match two things from your input: (You opinion is highly appropriated since your are the BI expert :-) )

1- SEARCH function is only taking 3 parameters in my case (but here you are using 4, it would be great if you tell us why?)

2- The parameters appears in your excel file intellisense is totally different from my side:


3- You are using Semicolons while I am using comas.

I managed to solve my issue with this formula:

=IF(IFERROR(SEARCH("2000938",[dialString]),-1)=-1,"Video","Voice")

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2014 2:01am

Hello Jamil,

Seems you are using an old Version of PowerPivot, you should update it to the most recent Version. See MSDN Search Function for the different Versions:
http://msdn.microsoft.com/en-us/library/ee634235(v=sql.105).aspx  only 3 paramter
http://msdn.microsoft.com/en-us/library/ee634235(v=sql.110).aspx  with 4 parameter

I have a German Excel and here I have to use semicolon as parameter separator, not the comma.

August 20th, 2014 2:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics