Forum Discussion

Arti12's avatar
Arti12
Frequent Visitor
2 years ago
Solved

Time duration - help with DAX

Hi Guys. I would like to ask for your advice.


I have a table CallDetails that has a [Call duration] column. In the fields I have a call duration time. Unfortunately, the minutes and seconds are entered in one value, for example, 1 min, 32 sec, or 1 min, or 1s. So I have 3 different combinations (I also have blank fields).

I would like to use the Dax command which will add a calculated column and convert this time to seconds, for example.

1 min, 1 s = 61
1 min = 60
1 min, 15 = 75
5 s = 5
25 s = 25

etc

 

How can I achieve this? I was able to do something like this:

 

TimeInSeconds =

VAR TimeText = CallDetails[Call duration]

VAR CleanText = IF( CONTAINSSTRING(TimeText, "min") && CONTAINSSTRING(TimeText, ","), REPLACE(REPLACE(TimeText, FIND("min", TimeText) - 1, 5, ""), FIND(",", TimeText), 1, ""), "Invalid format" )

VAR SpacePosition = SEARCH(" ", CleanText, 1, 0) VAR MinutePart = VALUE(LEFT(CleanText, SpacePosition - 1))

VAR SecondText = MID(CleanText, SpacePosition + 1, LEN(CleanText) - SpacePosition)

VAR SecondPart = VALUE(REPLACE(SecondText, SEARCH(" ", SecondText, 1, 0), LEN(SecondText), ""))

 

RETURN IF(CleanText <> "Invalid format", MinutePart * 60 + SecondPart, BLANK())

 

 

But in my result appeared only (correct) values that have minutes and seconds. Individual ones like 1 s, 2 min, etc. were left blank. How to change them into seconds as well?

 

Need Help
Tips and Tricks
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi Arti12 ,

     

    Based on the example and description you provided, please try code as below.
    My Sample:

    TimeInSeconds = 
VAR TimeText = 'Table'[Call duration]
VAR CleanText =
    SUBSTITUTE ( SUBSTITUTE ( TimeText, "min", "" ), "s", "" )
VAR HasMinutes =
    CONTAINSSTRING ( 'Table'[Call duration], "min" )
VAR HasSeconds =
    CONTAINSSTRING ( 'Table'[Call duration], "s" )
VAR TextLength =
    LEN ( CleanText )
VAR MinutePart =
    IF (
        HasMinutes,
        VALUE (
            TRIM (
                LEFT ( CleanText, IF ( HasSeconds, FIND ( ",", CleanText ) - 1, TextLength ) )
            )
        ),
        0
    )
VAR SecondPart =
    IF (
        HasSeconds,
        VALUE (
            TRIM (
                RIGHT (
                    CleanText,
                    IF ( HasMinutes, TextLength - FIND ( ",", CleanText ), TextLength )
                )
            )
        ),
        0
    )
RETURN
    MinutePart * 60 + SecondPart

    Result is as below.


    Best Regards,
    Yulia Yan


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    Hi Arti12 ,

     

    Based on the example and description you provided, please try code as below.
    My Sample:

    TimeInSeconds = 
VAR TimeText = 'Table'[Call duration]
VAR CleanText =
    SUBSTITUTE ( SUBSTITUTE ( TimeText, "min", "" ), "s", "" )
VAR HasMinutes =
    CONTAINSSTRING ( 'Table'[Call duration], "min" )
VAR HasSeconds =
    CONTAINSSTRING ( 'Table'[Call duration], "s" )
VAR TextLength =
    LEN ( CleanText )
VAR MinutePart =
    IF (
        HasMinutes,
        VALUE (
            TRIM (
                LEFT ( CleanText, IF ( HasSeconds, FIND ( ",", CleanText ) - 1, TextLength ) )
            )
        ),
        0
    )
VAR SecondPart =
    IF (
        HasSeconds,
        VALUE (
            TRIM (
                RIGHT (
                    CleanText,
                    IF ( HasMinutes, TextLength - FIND ( ",", CleanText ), TextLength )
                )
            )
        ),
        0
    )
RETURN
    MinutePart * 60 + SecondPart

    Result is as below.


    Best Regards,
    Yulia Yan


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.