locked
Using DAX - Find Formula RRS feed

  • Question

  • HI , I'm new to PowerPivot and I need to add a calculated Column in the PowerPivot window.

    the formula is  =if(find("13", [CodeList],1)>1, 1, 0)         

    where [CodeList] is a column from my datasource which is a varchar field containing a string like "13, 14, 20, 24".

    Pretty easy, isn't it.

    I have an error : "La chaine de recherche fournie à la fonction "FIND" est introuvable dans le texte indiqué."  (sorry about the french version, it should translate as "The provided search string supplied at the "FIND" function doesn't match the indicated text")

    If I switch my formula to =if(find("13", "13, 14, 20, 24",1)>1, 1, 0) 

    it works !!!

    Can someone help me ?

    How can I make the find function work with a column ?

    Thanks

     

     

     

    Friday, March 25, 2011 6:41 PM

Answers

  • Hi there,

    =if(find("13", [CodeList],1)>1, 1, 0) returns error when you use column reference because it couldn't find 13 in *every* row. And if there's one row returning error, the whole expression returns error.

    You can work around this by wrapping an ISERROR around your FIND. For example,

    =if(ISERROR(FIND("13", [CodeList],1)), 0, 1)


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Danielp38 Monday, March 28, 2011 6:26 PM
    Friday, March 25, 2011 9:32 PM
  • Thanks a lot

    It worked fine !

     


    DanielP38
    • Marked as answer by Danielp38 Monday, March 28, 2011 6:27 PM
    Monday, March 28, 2011 6:16 PM

All replies

  • Hi there,

    =if(find("13", [CodeList],1)>1, 1, 0) returns error when you use column reference because it couldn't find 13 in *every* row. And if there's one row returning error, the whole expression returns error.

    You can work around this by wrapping an ISERROR around your FIND. For example,

    =if(ISERROR(FIND("13", [CodeList],1)), 0, 1)


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Danielp38 Monday, March 28, 2011 6:26 PM
    Friday, March 25, 2011 9:32 PM
  • Thanks a lot

    It worked fine !

     


    DanielP38
    • Marked as answer by Danielp38 Monday, March 28, 2011 6:27 PM
    Monday, March 28, 2011 6:16 PM
  • Alternative for iserror is iferror:

    iferror(if(find("13", [CodeList],1)>1,1,0),0)

    Friday, September 9, 2011 10:54 AM