Dynamic Format not working with Durations

[nchr]

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):

https://we.tl/t-gdqaD1nf3d

Thank you,
Nikos

[OwenAuger · Accepted solution]

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?

[nchr]

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. 

[OwenAuger]

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
    MetricFormat

Regards

[nchr]

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.