Forum Discussion
Dynamic Format not working with Durations
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?
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
Hey OwenAuger
That is how I did things before dynamic formatting, with literal values as format strings, but it has its drawbacks as you mentioned.
For tables, it is ok; for graphs, what I am doing is to create another measure that returns the formatted value and use that as data label. It increases the model complexity though, I hoped that dynamic format would save the day!
Anyway, your help is much appreciated as usual, marking this as solved for the CONVERT tip.