Forum Discussion

makarama

Frequent Visitor

1 year ago

Solved

Calculate duration from String in Dax

Edit to make my question simpler:

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 depending on the type of measure multiply it with the right number to get the total number of seconds.

For example if I have "2 Days 3 Minutes" then the code will calculate: 2 x 86400 + 3 x 60 = 172980

----------------------------------------------

I need to calculate the duration in hours of a string that contains text in the format: "1 Day 20 Hours 34 Minutes" OR any combination really with both plural and singular endings (Day or Days etc) and also some metric could be missing e.g. "1 Day 24 Minutes" etc. Screenshot provided.

I managed to do that with this dax measure:

Sum Reassignment Duration Hours p1 =
SUMX(
'prm_AllIncidentMetricData',

VAR FullText = [Duration]
VAR Days = IFERROR(VALUE(LEFT(FullText, SEARCH(" Days", FullText) - 1)), 0)
VAR Hours = IFERROR(VALUE(MID(FullText, SEARCH(" Days", FullText) + 5, SEARCH(" Hours", FullText) - SEARCH(" Days", FullText) - 5)), 0)
VAR Minutes = IFERROR(VALUE(MID(FullText, SEARCH(" Hours", FullText) + 6, SEARCH(" Minutes", FullText) - SEARCH(" Hours", FullText) - 6)), 0)
VAR Seconds = IFERROR(VALUE(MID(FullText, SEARCH(" Minutes", FullText) + 8, SEARCH(" Seconds", FullText) - SEARCH(" Minutes", FullText) - 8)), 0)
var result = (Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds)/3600
RETURN result

)

This code works IF the words are in a plural format e.g. 1 Days 1 Minutes etc. But in this case my data is in both singular or plural.

Example of data:

The result I want is simply the number of hours in decimal format. For example 30 Minutes would be 0.5

Thanks

Anonymous

Hi,lbendlin ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,makarama .I am glad to help you.
Like this?
Using IFERROR() allows the entire judgment logic to be more streamlined

ResultsDuration = 
VAR FullText = 'Table02'[Duration]
-- theDayNum
VAR PositionDays = SEARCH(" Days", FullText, 1, LEN(FullText) + 1)
VAR PositionDay = SEARCH(" Day", FullText, 1, LEN(FullText) + 1)
VAR PositionDayResult = IF(PositionDays <> LEN(FullText) + 1, PositionDays, PositionDay)
VAR Days = IF(PositionDayResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionDayResult - 1), 2)), 0)
-- theHourNum
VAR PositionHours = SEARCH(" Hours", FullText, 1, LEN(FullText) + 1)
VAR PositionHour = SEARCH(" Hour", FullText, 1, LEN(FullText) + 1)
VAR PositionHourResult = IF(PositionHours <> LEN(FullText) + 1, PositionHours, PositionHour)
VAR Hours = IF(PositionHourResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionHourResult - 1), 2)), 0)
-- theMinuteNum
VAR PositionMinutes = SEARCH(" Minutes", FullText, 1, LEN(FullText) + 1)
VAR PositionMinute = SEARCH(" Minute", FullText, 1, LEN(FullText) + 1)
VAR PositionMinuteResult = IF(PositionMinutes <> LEN(FullText) + 1, PositionMinutes, PositionMinute)
VAR Minutes = IF(PositionMinuteResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionMinuteResult - 1), 2)), 0)
-- theSecondNum
VAR PositionSeconds = SEARCH(" Seconds", FullText, 1, LEN(FullText) + 1)
VAR PositionSecond = SEARCH(" Second", FullText, 1, LEN(FullText) + 1)
VAR PositionSecondResult = IF(PositionSeconds <> LEN(FullText) + 1, PositionSeconds, PositionSecond)
VAR Seconds = IF(PositionSecondResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionSecondResult - 1), 2)), 0)
RETURN (Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds) / 3600

You can also use the CONTAINSSTRING() function
The function does the following
Get whether the target field exists or not from the text type data

The logic of the calculated column I created is as follows
Find the location of “ Hours” and “ Hour” in FullText.

Use the first position found (preferably “ Hours”).

Intercept the string and extract the last two characters.

Use the IFERROR function to handle potential error cases, ensuring that the default value of “0” is returned if “Hours” or “Hour” is not found.

This is actually the optimized version, but if you want to see the calculation more visually, you can refer to this calculation column

DurationInHours = 
VAR FullText = 'Table02'[Duration]
VAR Hours = IFERROR(
    VALUE(LEFT(FullText, SEARCH(" Hours", FullText, 1, LEN(FullText) + 1) - 1)),
    IFERROR(
        VALUE(LEFT(FullText, SEARCH(" Hour", FullText, 1, LEN(FullText) + 1) - 1)),
        0
    )
)
VAR result = 
    VAR positionHours = SEARCH(" Hours", FullText, 1, LEN(FullText) + 1)
    VAR positionHour = SEARCH(" Hour", FullText, 1, LEN(FullText) + 1)
    VAR position = IF(positionHours <> LEN(FullText) + 1, positionHours, positionHour)
    RETURN RIGHT(IFERROR(LEFT(FullText, position - 1), "error"), 2)
RETURN IFERROR(VALUE(result), 0)

I provided the test pbix, hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Convert time text to seconds.pbix32 KB

3 Replies

lbendlin
Super User
1 year ago
Please provide sample data that fully covers your issue. All combinations you can think of.
Please show the expected outcome based on the sample data you provided.
- makarama
  Frequent Visitor
  1 year ago
  Thanks for you reply. I edited my original question.

Anonymous

Not applicable

1 year ago

Hi,lbendlin ,thanks for your concern about this issue.

ResultsDuration = 
VAR FullText = 'Table02'[Duration]
-- theDayNum
VAR PositionDays = SEARCH(" Days", FullText, 1, LEN(FullText) + 1)
VAR PositionDay = SEARCH(" Day", FullText, 1, LEN(FullText) + 1)
VAR PositionDayResult = IF(PositionDays <> LEN(FullText) + 1, PositionDays, PositionDay)
VAR Days = IF(PositionDayResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionDayResult - 1), 2)), 0)
-- theHourNum
VAR PositionHours = SEARCH(" Hours", FullText, 1, LEN(FullText) + 1)
VAR PositionHour = SEARCH(" Hour", FullText, 1, LEN(FullText) + 1)
VAR PositionHourResult = IF(PositionHours <> LEN(FullText) + 1, PositionHours, PositionHour)
VAR Hours = IF(PositionHourResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionHourResult - 1), 2)), 0)
-- theMinuteNum
VAR PositionMinutes = SEARCH(" Minutes", FullText, 1, LEN(FullText) + 1)
VAR PositionMinute = SEARCH(" Minute", FullText, 1, LEN(FullText) + 1)
VAR PositionMinuteResult = IF(PositionMinutes <> LEN(FullText) + 1, PositionMinutes, PositionMinute)
VAR Minutes = IF(PositionMinuteResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionMinuteResult - 1), 2)), 0)
-- theSecondNum
VAR PositionSeconds = SEARCH(" Seconds", FullText, 1, LEN(FullText) + 1)
VAR PositionSecond = SEARCH(" Second", FullText, 1, LEN(FullText) + 1)
VAR PositionSecondResult = IF(PositionSeconds <> LEN(FullText) + 1, PositionSeconds, PositionSecond)
VAR Seconds = IF(PositionSecondResult <> LEN(FullText) + 1, VALUE(RIGHT(LEFT(FullText, PositionSecondResult - 1), 2)), 0)
RETURN (Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds) / 3600

You can also use the CONTAINSSTRING() function
The function does the following
Get whether the target field exists or not from the text type data

The logic of the calculated column I created is as follows
Find the location of “ Hours” and “ Hour” in FullText.

Use the first position found (preferably “ Hours”).

Intercept the string and extract the last two characters.

Use the IFERROR function to handle potential error cases, ensuring that the default value of “0” is returned if “Hours” or “Hour” is not found.

This is actually the optimized version, but if you want to see the calculation more visually, you can refer to this calculation column

DurationInHours = 
VAR FullText = 'Table02'[Duration]
VAR Hours = IFERROR(
    VALUE(LEFT(FullText, SEARCH(" Hours", FullText, 1, LEN(FullText) + 1) - 1)),
    IFERROR(
        VALUE(LEFT(FullText, SEARCH(" Hour", FullText, 1, LEN(FullText) + 1) - 1)),
        0
    )
)
VAR result = 
    VAR positionHours = SEARCH(" Hours", FullText, 1, LEN(FullText) + 1)
    VAR positionHour = SEARCH(" Hour", FullText, 1, LEN(FullText) + 1)
    VAR position = IF(positionHours <> LEN(FullText) + 1, positionHours, positionHour)
    RETURN RIGHT(IFERROR(LEFT(FullText, position - 1), "error"), 2)
RETURN IFERROR(VALUE(result), 0)

I provided the test pbix, hope it helps.

Convert time text to seconds.pbix32 KB

Forum Discussion

Calculate duration from String in Dax

3 Replies

Related content

Durations - Dynamic Formats or Calculation Groups? Or both?

calculating duration

Calculating Stage Duration

Convert String to Duration

calculate duration