Forum Discussion
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...
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 + SecondsBest Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
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!