Forum Discussion

ritanoori's avatar
ritanoori
Resolver I
4 years ago
Solved

Search function is returning an error

Hi All 

I have this column created in DAX, However, it's returning an error. What can I do to avoid the error if the find text can not be found? Thank you so much in advance. 

 

Vendor1 =
/*Vendor from Hana thru DAX; To delete later, this is only to validate if GR55_Vendor data ties up with Hana*/

var _vim = IF(Fact_GLCost_Hana[Category] = "VIM Accrual" && Fact_GLCost_Hana[Vendor Name] = BLANK(), RIGHT(Fact_GLCost_Hana[Line Item Text], LEN(Fact_GLCost_Hana[Line Item Text]) - SEARCH(" ",Fact_GLCost_Hana[Line Item Text])), BLANK())
var _amex = IF(CONTAINSSTRING(Fact_GLCost_Hana[Category], "AMEX"), "AMERICAN EXPRESS", BLANK())
var _eclaims = IF(CONTAINSSTRING(Fact_GLCost_Hana[Category], "ECLAIMS"), "ECLAIMS", BLANK())
var _vendor = _vim & _amex & _eclaims

return
IF(ISBLANK(Fact_GLCost_Hana[Vendor Name]), _vendor, Fact_GLCost_Hana[Vendor Name])
 
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    ritanoori , Try to return 0 when no text found

     

    var _vim = IF(Fact_GLCost_Hana[Category] = "VIM Accrual" && Fact_GLCost_Hana[Vendor Name] = BLANK(), RIGHT(Fact_GLCost_Hana[Line Item Text], LEN(Fact_GLCost_Hana[Line Item Text]) - SEARCH(" ",Fact_GLCost_Hana[Line Item Text],,0)), BLANK())
    var _amex = IF(CONTAINSSTRING(Fact_GLCost_Hana[Category], "AMEX"), "AMERICAN EXPRESS", BLANK())
    var _eclaims = IF(CONTAINSSTRING(Fact_GLCost_Hana[Category], "ECLAIMS"), "ECLAIMS", BLANK())
    var _vendor = _vim & _amex & _eclaims

    return
    IF(ISBLANK(Fact_GLCost_Hana[Vendor Name]), _vendor, Fact_GLCost_Hana[Vendor Name])

     

    OR

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ritanoori , Try to return 0 when no text found

     

    var _vim = IF(Fact_GLCost_Hana[Category] = "VIM Accrual" && Fact_GLCost_Hana[Vendor Name] = BLANK(), RIGHT(Fact_GLCost_Hana[Line Item Text], LEN(Fact_GLCost_Hana[Line Item Text]) - SEARCH(" ",Fact_GLCost_Hana[Line Item Text],,0)), BLANK())
    var _amex = IF(CONTAINSSTRING(Fact_GLCost_Hana[Category], "AMEX"), "AMERICAN EXPRESS", BLANK())
    var _eclaims = IF(CONTAINSSTRING(Fact_GLCost_Hana[Category], "ECLAIMS"), "ECLAIMS", BLANK())
    var _vendor = _vim & _amex & _eclaims

    return
    IF(ISBLANK(Fact_GLCost_Hana[Vendor Name]), _vendor, Fact_GLCost_Hana[Vendor Name])

     

    OR

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.