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,BLANK())
RETURN runid_fim

 

... but I just get this error message bellow:

 

"An argument of the 'SEARCH' function has the wrong data type or an invalid value."

 

I found out that the [<start_num>] parameter (in my example, contains the value of the runid_inicio variable) is causing me trouble. So I tried to use CONVERT(runid_inicio,STRING) / CONVERT(runid_inicio,INTEGER) and both didn't work.

 

OBS.: I only used the formula SEARCH("RunID",Incidentes[Description3],,BLANK()) in a new column and it works normally.

 

Can anyone help?

Thanks in advance!

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().

     

11 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    Hello there thomas_raphael . You can try the following:

     

    Coluna2 =
VAR runid_inicio =
    SEARCH ( "RunID", Incidentes[Description3],, -1 )
VAR runid_fim =
    SEARCH ( ",", Incidentes[Description3], VALUE ( runid_inicio ), BLANK () )
RETURN
    runid_fim

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

  • Anonymous's avatar
    Anonymous
    Not applicable
    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().

     

    • thomas_raphael's avatar
      thomas_raphael
      Frequent Visitor
      4 years ago

      Hey Anonymous,

       

      I can't use Power Query 'cause I'm creating this table from a published dataset, buuut your tip about changing the BLANK() to 1 made it finally work!

      Thank you for that!

  • 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