Forum Discussion

jameskeuning's avatar
jameskeuning
Frequent Visitor
2 years ago
Solved

Why does the DAX formula for a very simple LEFT using FIND notwork like it works in every other app?

I have this formula that works fine:

(find(",",'Enforcement%20Tracking'[Matter Name + Details],1,blank())-1)

 

It finds the position of the first comma and then subtracts one

 

And I have this formula that works fine:

left('Enforcement%20Tracking'[Matter Name + Details],12)
 
It returns the left 12 characters.
 
But when I replace the "12" in the second formula with the working formula that finds that actual number of chacters I need, it does not work: "An argument of function LEFT has the wrong data type or has an invalild value.
 
I created a column with the working "find" formula in it and called it Pos. And then I tried:
 
left('Enforcement%20Tracking'[Matter Name + Details],[Pos])
 
And it does not work either
Need Help
  • TomMartens's avatar
    TomMartens
    2 years ago

    Hey jameskeuning ,

     

    LEFT expects a numeric value, FIND returns BLANK (what is a not a numeric value) if you change the NOTFOUNDPARAMETER to a reasonable value it works as expected.

    If you can not provide a reasonable value, then you have to check the return value of FIND and depending on the result you branch to the LEFT algorithm or something different like another BLANK()

    DAX does not perfrom implicit type conversion, some people consider this great some don't.

     

    Hopefully, this provedes you ideas of how to tackle your challenge.

     

    Regards,

    Tom

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    2 years ago

    Hey jameskeuning ,

     

    LEFT expects a numeric value, FIND returns BLANK (what is a not a numeric value) if you change the NOTFOUNDPARAMETER to a reasonable value it works as expected.

    If you can not provide a reasonable value, then you have to check the return value of FIND and depending on the result you branch to the LEFT algorithm or something different like another BLANK()

    DAX does not perfrom implicit type conversion, some people consider this great some don't.

     

    Hopefully, this provedes you ideas of how to tackle your challenge.

     

    Regards,

    Tom

    • jameskeuning's avatar
      jameskeuning
      Frequent Visitor
      2 years ago

      Thanks Tom. I noticed that I get a -1 result if there is no comma in the field, so that gave me a hint as to the problem, and your response helped me connect the dots.