Forum Discussion

nchr's avatar
nchr
Frequent Visitor
1 year ago
Solved

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 numbe...
duration
dynamic format
Issue
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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?

     

     

OwenAuger's avatar
OwenAuger
Super User
1 year ago

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's avatar
nchr
Frequent Visitor
1 year ago

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.