topic Re: Issues with string manipulation script in DAX Commands and Tips

Issues with string manipulation script

Rimo72 — Thu, 18 May 2023 22:00:30 GMT

Hi,

I don't understand what i'm doing wrong. I'm appling this code to a new column called TW

TW =

IF(ISBLANK('Tickets'[TimeWorked]), BLANK(),

IF (SEARCH("(", 'Tickets'[TimeWorked],1,BLANK()),

MID('Tickets'[TimeWorked], SEARCH("(", 'Tickets'[TimeWorked], 1, BLANK()) + 1, SEARCH("min", 'Tickets'[TimeWorked], 1, BLANK()) - SEARCH("(", 'Tickets'[TimeWorked], 1, BLANK()) - 2),

LEFT('Tickets'[TimeWorked], SEARCH("min",'Tickets'[TimeWorked],1,BLANK()) - 2)

)

in the field 'Tickets'[TimeWorked] have 2 value format "15 minutes" or "2.08 hours (125 minutes)"

The error message is "LEFT has the wrong data type or has invalid value" regardless what I tryed, changing with static value in the LEFT statement, nothing works.

I'm trying to get the minutes value of each field.

Would it be possible to get some help with this.

Thanks,

Re: Issues with string manipulation script

lbendlin — Sun, 21 May 2023 01:46:09 GMT

You are using 'Tickets'[TimeWorked] a lot. Might want to put it in a variable.

Is this a measure or a column?

Re: Issues with string manipulation script

Anonymous — Tue, 23 May 2023 06:28:51 GMT

Hi Rimo72 ,

The issue is likely with your SEARCH function. When SEARCH can't find the value (e.g. if the row value is blank) this kind of error can appear within LEFT.

You just need adjust your dax formula, please try below dax formula:

TW = IF ( ISBLANK ( 'Table'[Time Worked] ), BLANK (), IF ( SEARCH ( "(", 'Table'[Time Worked], 1, BLANK () ), MID ( 'Table'[Time Worked], SEARCH ( "(", 'Table'[Time Worked], 1, BLANK () ) + 1, SEARCH ( "min", 'Table'[Time Worked], 1, BLANK () ) - SEARCH ( "(", 'Table'[Time Worked], 1, BLANK () ) - 2 ), IF ( ISBLANK ( SEARCH ( "min", 'Table'[Time Worked], 1, BLANK () ) ), BLANK (), LEFT ( 'Table'[Time Worked], SEARCH ( "min", 'Table'[Time Worked], 1, BLANK () ) - 2 ) ) ) )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Issues with string manipulation script

Rimo72 — Tue, 23 May 2023 16:43:18 GMT

Thanks it works.

Re: Issues with string manipulation script

Rimo72 — Tue, 23 May 2023 16:44:08 GMT

I do :). Will follow you recomandation. 🙂