Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am try to convert a text type column to duration format as"h:mm:ss". I am using the DAX as below, however, I am getting correct output for value 1h 2m 3s as 1:02:03, however, for 11m 20s I get 0:00:20
Formatted AHT =
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText) - 1)), 0)
VAR MinutesText = IFERROR(
IF(
ISNUMBER(FIND("m", AHTText)),
VALUE(MID(AHTText, FIND("h", AHTText) + 2, FIND("m", AHTText) - FIND("h", AHTText) - 2)),
VALUE(MID(AHTText, FIND("h", AHTText) + 2, LEN(AHTText) - FIND("h", AHTText) - 1))
),
0
)
VAR SecondsText = IFERROR(
IF(
ISNUMBER(FIND("s", AHTText)),
VALUE(MID(AHTText, FIND("m", AHTText) + 2, FIND("s", AHTText) - FIND("m", AHTText) - 2)),
VALUE(MID(AHTText, FIND("m", AHTText) + 2, LEN(AHTText) - FIND("m", AHTText) - 1))
),
0
)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
Solved! Go to Solution.
FormattedAHT = //Try this one in Calculated column
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos))
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos))
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
FormattedAHT = // Analyze yourself please
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = IFERROR(VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos)), 0)
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = IFERROR(VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos)), 0)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
FormattedAHT = //Try this Calculated Column might help you
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText) - 1)), 0)
VAR MinutesStartPos = IF(ISNUMBER(FIND("h", AHTText)), FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText), LEN(AHTText))
VAR MinutesText = VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos))
VAR SecondsStartPos = IF(ISNUMBER(FIND("m", AHTText)), FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText), LEN(AHTText))
VAR SecondsText = VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos))
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Getting the below error
FormattedAHT = //Try this one in Calculated column
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos))
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos))
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Now this error
FormattedAHT = // Analyze yourself please
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = IFERROR(VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos)), 0)
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = IFERROR(VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos)), 0)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |