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

[thomas_raphael]

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!

[goncalogeraldes]

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

[thomas_raphael]

Hey @goncalogeraldes!,

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

Thanks anyway!

[goncalogeraldes]

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

[thomas_raphael]

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.

[goncalogeraldes]

Thanks for the feedback @thomas_raphael ! 🙂

[Daryl-Lynch-Bzy · Accepted solution]

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]

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!