Forum Discussion
Durations - Dynamic Formats or Calculation Groups? Or both?
I have a bunch of columns giving durations in seconds. I have a bunch of measures averaging those durations over a count of interactions, and a bunch more measures giving the sums of those durations as a proportion of total shift duration.
I need to display these values in numeric form in tables and matrices, with the average durations formatted as [hh]:mm:ss, and I need to chart them, so I need to keep the underlying numeric values rather than formatting them as text.
I've just learned about Dynamic Formats and Calculation Groups, and I'm tearing my hair out because I feel I'm so close to an elegant solution but I can't quite figure out how to use these features.
I want to enter some form of "[hh]:mm:ss" as a dynamic format string for the durations, and choose to use it when presenting the measure in numerical form, or to ignore it when dropping the measure onto a chart. That feels like it should be straightforward, but definitions like FORMAT(SELECTEDMEASURE()... don't work because the definition wants to yeild a format string not a formatted value, while simply entering a format string directly ends up just displaying the string instead of the formatted measure.
Calculation Groups feel like they should be a solution to formatting and avoiding creating lots of duplicate measures, but here I'm running into similar problems.
I've looked up lots of online guides and tutorial videos, but most don't quite seem to speak to my use case. I've come across a lot of fellow travellers bemoaning Power BI's inexplicable troubles with elegantly handling durations. Could some kind soul take pity on a poor beginner and show me the way?
The fix is a dynamic format string. Revert the measure to plain numeric:
ACD handling Av =
DIVIDE(SUM('Performance'[ACD handling time (days)]), SUM('Performance'[ACD calls handled]))Then select it, Measure tools ribbon, set Format to Dynamic, and paste this into the format expression:
VAR Seconds = INT ( SELECTEDMEASURE () * 86400 )
VAR Hrs = INT ( Seconds / 3600 )
VAR Mins = INT ( MOD ( Seconds, 3600 ) / 60 )
VAR Secs = MOD ( Seconds, 60 )
RETURN
"""" & FORMAT ( Hrs, "00" ) & ":" & FORMAT ( Mins, "00" ) & ":" & FORMAT ( Secs, "00" ) & """"The `""""` wraps the time as literal text while keeping the real number underneath. Now it works everywhere: tables, charts with formatted labels, conditional formatting, and over-24h shows as 25:30:00 with no rollover.
For your seven measures, paste it into each one, or set up one calculation group with this as the Format String Expression and SELECTEDMEASURE() as the expression. I'd do per-measure first to get it working, then tidy up with a calc group later if you want.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
16 Replies
Hey,I'll try to help 🙂
the trick nobody tells you: Power BI's [hh]:mm:ss format treats the underlying number as days, just like Excel.Your FORMAT(SELECTEDMEASURE()...) attempt failed because a dynamic format string expression needs to return the format pattern itself (the literal "[hh]:mm:ss"), not a pre-formatted string. You were handing the engine "01:30:00" when it was asking "what pattern should I apply?"
Just do this:
Avg Handle Time = DIVIDE(SUM(fct_interactions[duration_seconds]), [Interaction Count]) / 86400
Set the format string on the measure to [hh]:mm:ss (type it in directly, it's not in the dropdown). Tables show 01:23:45, charts plot the numeric value, axes also render as durations. Everything just works.
That's it. Calculation groups are a great tool but you don't need one here. Reach for them only if you genuinely need the raw seconds preserved somewhere (like a "Raw Seconds" vs "As Duration" toggle), and even then watch out: calc groups apply to every measure in scope, so non-duration measures pick up the formatting too unless you guard with SELECTEDMEASURENAME().
Your proportion measures are a separate concern. They're unitless 0 to 1, format them to 0.0% and move on.
divide by 86400 and set the format string.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai KarmaniThank you for the quick and helpful response! Sorry I'm missing something obvious - where do I enter the format string [hh]:mm:ss? I tried entering it as a dynamic format in quotation marks, "[hh]:mm:ss", and as a custom format without quotation marks in the "Data format" menu on the visual, but predictably that just showed the format string as text. Where does it go?
No worries, this bit is genuinely hidden. You want the measure's format string, not the visual's format setting, and not the dynamic format expression.
In Model view (or the Properties pane in Report view), click the measure itself and look for the Format property. Set it to Custome, and a Custom format text box appears below it. Type [hh]:mm:ss in there with no quotes. That's the spot.
Once it's set there, the format follows the measure everywhere it gets used. Type it raw, no quotes.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai KarmaniThanks. I found that and tried entering it there, along with variations such as [HH]:mm:ss and [HH]:nn:ss, but all of them resulting in displaying the format string.
To be clear, I had already reset the format on the visual to default and set the measure format back to General before trying this.ok so a good way to debug: remove the calculation group from whatever slicer or filter is applying it to that visual, so it's out of the picture entirely. Then with just the plain measure:
- Edit the measure so it divides the seconds total by 86400
- Format property: Custom, value [hh]:mm:ss
You should immediately see proper 01:23:45 style values. Once that's working, you can decide whether you even need the calc group. For your original use case (always render as duration in tables, raw number on charts), you honestly don't.
If you do want to keep the calc group around for some reason, open up the "S to HHMMSS" item and check those two fields. My bet is the format string is sitting in the wrong one.
Hi DAST ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank  Shai_Karmani , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.Best Regards,
Community Support Team
