The DMC 2: using UDF to rule all format strings

DMC stands for Dunder Mifflin Contest, not Devil May Cry, yet I hope this series hits a jackpot.

Personally I have a love-hate relationship with DAX UDF… Specially because it’s one of those features that is easy to abuse without adding real value to the development, but when it’s well implemented, they save up a lot of time.

For me, as a super duper lazy developer. I try to be aware of repetitive patterns and complex or dense logic. When something triggers one of those feelings, I change the mindset and try to implement DAX UDF (even tho they are on preview).

Coming back to the Dunder Mifflin contest, I found one of these use cases where DAX UDF hits like a train: dynamic format strings.

Format all the time results like HH:MM

The naive approach with a measure is straightforward: use variables to break the decimal into hours and minutes, concatenate them into a string, done. And for worked hours, it looks exactly right.

But returning text kills the measure. No aggregations, no calculations on top of it. Sorting breaks too: 9 h comes after 10 h alphabetically. And that’s before we even get to balance hours.

Now the format itself fights back. A negative balance, a zero, a result under an hour… Each one needs different treatment. Maybe you don’t want to show minutes when the result is a round hour. Or maybe you want special treatment for nulls and zeros. What about hiding the hours entirely for sub-hour results? Or that leading zero when the value is less than 10?

The displayed result should react to the input, and a static format string can’t do that.

Why dynamic format string shines here?

This is the DAX format string I ended up with:

VAR AbsoluteValue = ABS ( SELECTEDMEASURE() )
// Break down hours and minutes
VAR Hours = INT ( AbsoluteValue )
VAR Minutes = ROUND ( ( AbsoluteValue - Hours ) * 60, 0 )
// Rounding corrections
VAR FinalHours = IF ( Minutes = 60, Hours + 1, Hours )
VAR FinalMinutes = IF ( Minutes = 60, 0, Minutes )
// Conditional: if minutes are 0, return hours-only string
RETURN
    IF (
        FinalMinutes = 0,
        UNICHAR ( 34 ) & FORMAT ( FinalHours, "#,##0" ) & " h" & UNICHAR ( 34 ),
        UNICHAR ( 34 ) & FORMAT ( FinalHours, "#,##0" ) & " h " & FORMAT ( FinalMinutes, "00" ) & "m" & UNICHAR ( 34 )
    )

And that’s it. The results were exactly what I expected:

Ok but what about the UDF?

We did the heavy lifting creating the dynamic format string, now we have to apply it to… all the time related measures. That implies copy-pasting the DAX format string into every single one of them… And what if we want to make changes in the future?

When I ask these questions my mind activates the DAX UDF mode. I would say that even before that, the first thing I think oh hell naw…

To me, the key is that the format string has no dependency on the measure itself. It only cares about one thing: a decimal number. It doesn’t matter if that number comes from worked hours, planned hours, or balance hours. The logic is always the same.

That’s exactly what a UDF is for. One function, one input type, applied to every measure that returns decimal time.

DEFINE
/// Converts decimals to "HH h MMm" format with thousands separator. Hides minutes if 00.
FUNCTION UDF_Format_Hours_Minutes =
    ( MetricToFormat: NUMERIC ) =>
    VAR AbsoluteValue = ABS ( MetricToFormat )
    // Break down hours and minutes
    VAR Hours = INT ( AbsoluteValue )
    VAR Minutes = ROUND ( ( AbsoluteValue - Hours ) * 60, 0 )
    // Rounding corrections
    VAR FinalHours = IF ( Minutes = 60, Hours + 1, Hours )
    VAR FinalMinutes = IF ( Minutes = 60, 0, Minutes )
    // Conditional: if minutes are 0, return hours-only string
    RETURN
        IF (
            FinalMinutes = 0,
            UNICHAR ( 34 ) & FORMAT ( FinalHours, "#,##0" ) & " h" & UNICHAR ( 34 ),
            UNICHAR ( 34 ) & FORMAT ( FinalHours, "#,##0" ) & " h " & FORMAT ( FinalMinutes, "00" ) & "m" & UNICHAR ( 34 )
        )
// EVALUATE to test several cases
EVALUATE
{
    ( "Normal test:",                   UDF_Format_Hours_Minutes ( 2.5 ) ),
    ( "Exact hours (0 minutes):",       UDF_Format_Hours_Minutes ( 5 ) ),
    ( "1000+ hours:",                   UDF_Format_Hours_Minutes ( 10405.34 ) ),
    ( "Edge case (rounding up to 60):", UDF_Format_Hours_Minutes ( 1.999 ) ),
    ( "Absolute zero:",                 UDF_Format_Hours_Minutes ( 0 ) ),
    ( "Negative value:",                UDF_Format_Hours_Minutes ( -30 ) )
}

Once we have our UDF, we go to the format string and type: UDF_Format_Hours_Minutes(SELECTEDMEASURE()). All the edge cases, handled.

Balance Hours =
VAR H = INT( ABS([Balance]) )
VAR M = ROUND((ABS([Balance])
    - H) * 60, 0)
RETURN
    IF([Balance] < 0, "-", "")
    & H & " h "
    & FORMAT(M,"00") & "m"

Balance Hours =
    SUM( Table[Balance] )

// Format string:
UDF_Format_Hours_Minutes(
    SELECTEDMEASURE()
)

What if I want to change something?

This is when things get interesting, let’s just imagine our manager loves capital letters and asks to change all the hh:mm to HH:MM. Or maybe they want to set h for hours and ‘ for minutes. Even worse, they want to add seconds.

Simply go to the DAX function and make these edits. All 20 format strings update at once. One change. Twenty measures updated. That’s it.

Give it a try, share feedback and start UDF’ing all these cool cases!