cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thomas_raphael
New Member

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!

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Memorable Member
Memorable Member

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

 

View solution in original post

11 REPLIES 11
miftah_02
Regular Visitor

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!

Hi @miftah_02 - 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?

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

Hi @miftah_02 - 
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?

goncalogeraldes
Super User
Super User

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

Hey @goncalogeraldes!,

 

I've tried this VALUE() function and the error persists.

 

Thanks anyway!

Please note that I have also changed the first variable, maybe that would help... 🙂

Oh, sorry for missing that part 😅

I didn't mentioned it but I've already tried -1 and 0 as <NotFoundValue> before posting this.

 

Considering @Daryl-Lynch-Bzy answer, it seems that <NotFoundValue> can't be < 0 in the first variable.

Thanks for the feedback @thomas_raphael ! 🙂

Daryl-Lynch-Bzy
Memorable Member
Memorable Member

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

 

Hey @Daryl-Lynch-Bzy,

 

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!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors