Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
thomas_raphael
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

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
Frequent 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
Resident Rockstar
Resident Rockstar

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.