Forum Discussion

Rimo72's avatar
Rimo72
New Member
3 years ago
Solved

Issues with string manipulation script

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's avatar
    Anonymous
    3 years ago

    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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

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

     

    Is this a measure or a column?

    • Rimo72's avatar
      Rimo72
      New Member
      3 years ago

      I do :). Will follow you recomandation. 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.