Forum Discussion

thomas_raphael's avatar
thomas_raphael
Frequent Visitor
4 years ago
Solved

Search Function in DAX (wrong data type or an invalid value error)

Hi,   I'm trying to use this function ...   Coluna2 = VAR runid_inicio = SEARCH("RunID",Incidentes[Description3],,BLANK()) VAR runid_fim = SEARCH(",",Incidentes[Description3],runid_inicio,BLAN...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi thomas_raphael , if you are trying to add a new column, you could consider using Power Query to perform this function.  It may be easier to see the results.

     

    Please note that SEARCH("RunID",Incidentes[Description3],,BLANK()) will return a number representing the starting position of the text.   "Find Text Value"  - if I use search to find "Text" the result will be 6.  If the search fails, the result is BLANK().  You may need to change the BLANK() to either 1 or 1000.

    This result could be passed to the second VAR function.  But it will only find position of the first "," after this start positon.   1, 6 or 1000.  It there is no "," is returns BLANK().

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi i have same issue

I'm trying to use this function ...

_Gr_Numeric = CONVERT(LEFT(TExportDataMS_Daily[_Gr],LEN(TExportDataMS_Daily[_Gr])-1),INTEGER)
 
but I just get this error message bellow:
 
An argument of function 'LEFT' has the wrong data type or has an invalid value

Can anyone help?

Thanks in advance!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous - i difficult with example data, but I think you might be trying to convert a text string e.g. "A" to INTEGER, or it appears you are trying to find the second to last character, but what happens if there is only 1 character?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
      Yes I want to convert from string to integer
       
      This is query TExportDataMS_Daily[_Gr]
      _Gr = LOOKUPVALUE(MProduct[Size],MProduct[IDProduct],TExportDataMS_Daily[ProductID])
       
       
      And when I tried this query I got this isue
      _Gr_Numeric = CONVERT(LEFT(TExportDataMS_Daily[_Gr],LEN(TExportDataMS_Daily[_Gr])-1),INTEGER)
       
      An argument of function 'LEFT' has the wrong data type or has an invalid value
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous - 
        The LOOKUPVALUE function can return any data type (https://dax.guide/lookupvalue/) 

        LEFT is a Text function - https://dax.guide/left/.

        This will not work when the Product Size if the size is Number.  You need to convert this Number to string https://dax.guide/convert/ before using LEFT. 

        But at this stage the function is become complex so it not clear what you are trying.  Can you provide example of the data and expected result?