none
formula works in powerpivot not standard excel

    Question

  • I have a formula in one of my columns in my PowerPivot table. That works fine...

    =IF(SEARCH("A TE",[SITE_DOC],,0),"A 1",IF(SEARCH("A TC",[SITE_DOC],,0),"A 2",

    IF(SEARCH("A MDC",[SITE_DOC],,0),"A 3", IF(SEARCH("A MSD",[SITE_DOC],,0),"A 4",

    IF(SEARCH("A MSO",[SITE_DOC],,0),"A 5",IF(SEARCH("A DM-L",[SITE_DOC],,0),"A 6",

    [SITE_DOC]))))))

    But for some reason I can't use the exact same formula when I copy it to Sheet1 on my standard excel page. The SITE_DOC Column exists on my Sheet1 as well so it's not an issue referencing the column.

    The error says...

    "You've entered too many arguments for this function."

    What am I doing wrong?

    Tuesday, July 23, 2013 3:08 PM

Answers

  • While similar, not all DAX functions are identical to regular Excel functions.

    SEARCH() in regular Excel only accepts 3 arguements.  There is no option to return a value if the search term is not found.

    That double comma you have in every SEARCH() is basically a placeholder for the optional 3rd arguement (start position) but that makes 0 the last (and 4th) arguement for every SEARCH().

    • Marked as answer by DCDeez Wednesday, July 24, 2013 1:23 PM
    Tuesday, July 23, 2013 6:36 PM
    Answerer
  • Thanks! used...

    =IF(ISNUMBER(SEARCH("A TE",[@[SITE_DOC]])),"A 1", "0")

    • Marked as answer by DCDeez Wednesday, July 24, 2013 1:26 PM
    Wednesday, July 24, 2013 1:26 PM

All replies

  • While similar, not all DAX functions are identical to regular Excel functions.

    SEARCH() in regular Excel only accepts 3 arguements.  There is no option to return a value if the search term is not found.

    That double comma you have in every SEARCH() is basically a placeholder for the optional 3rd arguement (start position) but that makes 0 the last (and 4th) arguement for every SEARCH().

    • Marked as answer by DCDeez Wednesday, July 24, 2013 1:23 PM
    Tuesday, July 23, 2013 6:36 PM
    Answerer
  • Thanks! used...

    =IF(ISNUMBER(SEARCH("A TE",[@[SITE_DOC]])),"A 1", "0")

    • Marked as answer by DCDeez Wednesday, July 24, 2013 1:26 PM
    Wednesday, July 24, 2013 1:26 PM