Forum Discussion

makarama's avatar
makarama
Frequent Visitor
1 year ago
Solved

Convert String that represents duration to a Numeric Value (number of seconds) in DAX

Assuming that I have a string that contains any of the following words:   Day, Days, Hour, Hours, Minute, Minutes, Second, Seconds   I wanna grab the number in front of every appearing word and d...
"DAX "
"Expression"
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi makarama 

     

    You can try this DAX formula:

    TotalSeconds = 
VAR dd = SEARCH ( " Day", [TimeString],, 0 )
VAR Days = IF ( dd = 0, 0, VALUE ( MID ( [TimeString], 1, dd - 1 ) ) )
VAR hh = SEARCH ( " Hour", [TimeString],, 0 )
VAR Hours = IF ( hh = 0, 0, VALUE ( MID ( [TimeString], MAX ( hh - 2, 1 ), 2 ) ) )
VAR mm = SEARCH ( " Minute", [TimeString],, 0 )
VAR Minutes = IF ( mm = 0, 0, VALUE ( MID ( [TimeString], MAX ( mm - 2, 1 ), 2 ) ) )
VAR ss = SEARCH ( " Second", [TimeString],, 0 )
VAR Seconds = IF ( ss = 0, 0, VALUE ( MID ( [TimeString], MAX ( ss - 2, 1 ), 2 ) ) )
RETURN
    Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi makarama ,

 

The best approach to convert a duration string into total seconds in DAX is to systematically extract numeric values preceding time unit words (Day, Hour, Minute, Second) and multiply them by their corresponding values in seconds. The solution must account for both singular and plural forms of the time units while handling missing values gracefully. The optimal DAX formula for this is:

TotalSeconds =
VAR DurationString = 'Table'[Duration]

VAR Days = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Day", DurationString & " Day") - 1)), 
            TRUE, 
            TRUE
        ) * 86400, 
        0
    )

VAR Hours = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Hour", DurationString & " Hour") - 1)), 
            TRUE, 
            TRUE
        ) * 3600, 
        0
    )

VAR Minutes = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Minute", DurationString & " Minute") - 1)), 
            TRUE, 
            TRUE
        ) * 60, 
        0
    )

VAR Seconds = 
    IFERROR(
        LOOKUPVALUE( 
            VALUE(LEFT(DurationString, FIND(" Second", DurationString & " Second") - 1)), 
            TRUE, 
            TRUE
        ) * 1, 
        0
    )

RETURN
    Days + Hours + Minutes + Seconds

This formula ensures that each time unit is correctly identified, the number in front of it is extracted, and the result is multiplied by the appropriate conversion factor. The IFERROR function prevents calculation errors when a particular time unit is missing. This method efficiently handles different input variations, such as "2 Days 3 Minutes 1 Second" producing 172981, "1 Hour" returning 3600, and "1 Day 3 Hours 1 Minute 10 Seconds" calculating to 97270. This approach is concise, robust, and flexible for various input formats.

 

Best regards,