I want to display the fastest duration with milliseconds from a dataset where time is currently stored in seconds (i.e 65.69 seconds)
I would like to display the time in mm:ss.00 format.
The format for duration is as below:
I have found the measure to show time with milliseconds as shown below, however, i need to display the fastest time so I am looking for something like a 'Min' function.
Duration from Milliseconds =
// Duration formatting
// * Based on @konstatinos and blog post
// https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 1/25/2016
// * Given a number of milliseconds, returns a format of "hh:mm:ss:000 format"
// We start with a duration in number of milliseconds
VAR Duration = SUM('Goldmine data'[Milliseconds])
// There are 3,600,000 milliseconds in an hour
VAR Hours = INT ( Duration / 3600000)
// There are 60,000 milliseconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600000 ),3600000 ) / 60000)
// There are 1000 milliseconds in a second
VAR Seconds = INT (MOD ( MOD( Duration - ( Hours * 3600000 ) - (Minutes * 60000),60000 ), 60000 ) / 1000)
VAR Milli = ROUNDUP(MOD(MOD ( MOD( Duration - ( Hours * 3600000 ),3600000 ), 60000 ), 1000),0)
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
// Minutes with leading zeros
VAR M =
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
// Seconds with leading zeros
VAR S =
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
// MilliSeconds with leading zeros
VAR MS =
LEN ( Milli ) = 1,
CONCATENATE ( "00", Milli ),
LEN (Milli) = 2,
CONCATENATE ( "", Milli )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
CONCATENATE ( M, CONCATENATE ( ":", CONCATENATE(S, CONCATENATE(".", MS ) ) ) )
AFAIK, power bi time type not existed millisecond and related functions. How did you data stored? If your records store with a common time format, it will remove milliseconds when you getting data and convert them to power bi data model tables.
I'd like to suggest you extract it to a new column with a whole number format then you can use this DAX formula to calculate.
Community Support Team _ Xiaoxin If this post helps, please consider accept as solution to help other members find it more quickly.