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
Solution Sage
Solution Sage

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

7 REPLIES 7
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
Solution Sage
Solution Sage

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors