Forum Discussion
Dynamic Format not working with Durations
In my reports, I use durations in seconds for various metrics. It is handy to create visuals and make calculations, but when displaying the values, users prefer to see "hh:mm:ss" instead of the number of seconds.
I convert the durations to part-of-day decimal and use FORMAT() to display it as "hh:mm:ss" or "nn:ss" if less than an hour.
But this does not work with dynamic formating: although i return a text string containing the appropriate format, it is handled as text instead of being applied to the decimal value as format stirng.
So, how can i use dynamic formatting to apply the appropriate format string to a duration?
The .pbix demonstrates the issue with a simple example (it's in weTransfer, can't upload here yet):
Thank you,
Nikos
Hi nchr
(PBIX attached)
Try changing the measure to:
Dynamic Formatted = CONVERT ( [Duration] / ( 24 * 60 * 60 ), DATETIME )and the format string expression to:
IF ( [Dynamic Formatted] > 1 / 24, "hh:mm:ss", "mm:ss" )(it appears "mm" is required here).
When Power BI formats a measure in a visual it will only apply datetime format codes to a datetime value.
Does this work at your end?
4 Replies
Hi nchr
(PBIX attached)
Try changing the measure to:
Dynamic Formatted = CONVERT ( [Duration] / ( 24 * 60 * 60 ), DATETIME )and the format string expression to:
IF ( [Dynamic Formatted] > 1 / 24, "hh:mm:ss", "mm:ss" )(it appears "mm" is required here).
When Power BI formats a measure in a visual it will only apply datetime format codes to a datetime value.
Does this work at your end?
Hey OwenAuger , great to hear from you!
Yes, it works for that example, indeed.
But my original scenario is more complex. I have a list of several metrics that I track (consider a Dim table with metric name and other attributes) and I use SWITCH() to retrieve the value for each metric's measure. Some are durations, but others are decimals or integers.
When using diverse data types, the SWITCH() measure is cast into decimal anyway, losing any type I impose with CONVERT. In that case, the dynamic format does not work.I created an extra SWITCH measure that returns a value only for duration metrics, CONVERTing to datetime. In this case, i.e. when only blanks and datetimes are returned, it works.
So, I guess the issue is now that SWITCH measures returning diverse datatypes are cast into decimals and lose any datatype.
Thanks for the update nchr
I see, that's a bit of thorny issue!
As you've pointed out, it seems that a variant-typed measure returning decimal/time values doesn't allow switching between decimal/time format strings!
The best option I can suggest is to use the formatted-value itself as a literal value (enclosed in double quotes) as the format string in the case of time values.
There are some situations where this doesn't work (e.g. on the axis of a chart) but I think it should be acceptable in table visuals or similar.
PBIX with dummy example attached.
This is the format string expression:
VAR MetricType = SELECTEDVALUE ( Metric[Metric Type] ) VAR MetricFormat = SWITCH ( MetricType, "Percentage", "#,0.00%", "Decimal", "0.0000", "Duration", VAR MetricValue = [Metric Value] RETURN IF ( MetricValue > 1 / 24, """" & FORMAT ( MetricValue, "hh:mm:ss" ) & """", """" & FORMAT ( MetricValue, "mm:ss" ) & """" ) ) RETURN MetricFormatRegards
