Entering the (Power BI) Matrix – Part 2: Time & Other Non-Metric Measures

What are you trying to tell me? That I can dodge bullets? – Neo
No, Neo. I’m trying to tell you that when you’re ready, you won’t have to. – Morpheus

Time moves differently in the Matrix. And by that, I mean that measuring the passage of time can be a little trickier than it should be in Power BI. But don’t worry, DAX can solve for that, too.

This post will build on the previous approach for text based output, to handle non-metric units. This includes imperial units of measure, but also things like degrees of angle, or really, anything that doesn’t scale on a consistent base. In particular, units of time, which we will focus on here.

The duration datatype in Power BI (and Power Pivot) is (more than) a little peculiar. It is possible to get a native column to display as a time value with the GUI, but if this is set, the column can no longer be aggregated on the values shelf of our Matrix.

It’s also possible to aggregate a time column via a calculated measure and then set that calculation to output time (HH:MM:SS) in the GUI. But this format discards the days portion of the aggregated value and will never exceed 23:59:59.99. Not particularly useful, unfortunately.

In order to get useful output of elapsed time, we are going to need a little more DAX. The key to working with elapsed time in Power BI is to store the duration as a number rather than a time value – either integer or decimal. This number can be any unit useful, but for our example we’ve stored duration as seconds.

The crews of our Hovercrafts are logging their data extracts already, but we are missing some useful data here. We know how much they got, but we don’t know how efficiently they retrieved it. To gauge efficiency of their efforts, we also need to log the time required to complete the extract, after which we can calculate the speed – or efficiency – of their efforts.

When it comes to time, seconds (or decimal fractions thereof) will do for short durations. But for longer periods, the management suite is likely to want to see time expressed in the units we are all used to digesting. HH:MM:SS is standard enough. We don’t use days as frequently, but we can add days to this hierarchy if the hours exceed whatever threshold our requirements set.

We can compute our efficiency metric easily enough using the raw number of seconds, with a function like:

Click to Copy

efficiency display = VAR efficiency = DIVIDE ( SUM ( fact_matrix[kbytes] ), SUM ( fact_matrix[duration value] ) ) RETURN SWITCH ( TRUE (), ABS ( efficiency ) > 2 ^ 50, FORMAT ( DIVIDE ( efficiency, 2 ^ 50 ), “###.00 Eb/s” ), ABS ( efficiency ) > 2 ^ 40, FORMAT ( DIVIDE ( efficiency, 2 ^ 40 ), “###.00 Pb/s” ), ABS ( efficiency ) > 2 ^ 30, FORMAT ( DIVIDE ( efficiency, 2 ^ 30 ), “###.00 Tb/s” ),
ABS ( efficiency ) > 2 ^ 20, FORMAT ( DIVIDE ( efficiency, 2 ^ 20 ), “###.00 Gb/s” ), ABS ( efficiency ) > 2 ^ 10, FORMAT ( DIVIDE ( efficiency, 2 ^ 10 ), “###.00 Mb/s” ), ABS ( efficiency ) <> BLANK (), FORMAT ( efficiency, “###.00 Kb/s” ), BLANK () )

But in order to display the hierarchy of time units, we’ll need to use the MOD() function. Modulo returns the remainder after a number is divided by a divisor. This allows us to return the number of units at each level of the scale. The logic looks like this:

Click to Copy

duration display = VAR duration = SUM ( fact_matrix[duration value] ) VAR DD = FORMAT ( INT ( DIVIDE ( duration, 86400 ) ), “##00” ) VAR HH = FORMAT ( MOD ( DIVIDE ( duration, 3600 ), 24 ), “00” ) VAR MM = FORMAT ( MOD ( DIVIDE ( duration, 60 ), 60 ), “00” ) VAR SS = FORMAT ( MOD ( duration, 60 ), “00” ) VAR DDHHMMSS = COMBINEVALUES ( “:”, dd, hh, mm, ss ) RETURN IF ( duration = BLANK (), BLANK (), DDHHMMSS )

This code performs the following:

• store the aggregated sum of our random duration column in the variable duration
• compute the integer result of total seconds divided by 86400 to compute number of days, and format as minimum of two digits, preserving zeros.
• compute total seconds divided by 3600, modulo 24 to compute number of hours, and format as two digits, preserving zeros.
• compute total seconds divided by 60, modulo 60 to compute number of minutes, and format as two digits, preserving zeros.
• compute total seconds modulo 60 to compute number of 60, and format as two digits, preserving zeros.
• Concatenate all 4 strings, using “:” as the delimiter between them.

I’m not the first to come up with this approach to handling duration. Credit to: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 for a fine previous post on this approach.

And here’s the example report page: