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 = ...
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.

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.

  • ritanoori's avatar
    ritanoori
    Resolver I
    4 years ago

    Thank you. It works! 🙂