Issues with string manipulation script

[Rimo72]

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,

[Anonymous · Accepted solution]

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.

[Rimo72]

Thanks it works. 

[lbendlin]

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

Is this a measure or a column?

[Rimo72]

I do :). Will follow you recomandation. 🙂