Fastest time with milliseconds

[Anonymous]

Hi there,

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 =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// MilliSeconds with leading zeros
VAR MS =
    IF (
        LEN ( Milli ) = 1,
        CONCATENATE ( "00", Milli ),
        IF (
           LEN (Milli) = 2,
           CONCATENATE("0", Milli),
           CONCATENATE ( "", Milli )
        )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
   CONCATENATE ( M, CONCATENATE ( ":", CONCATENATE(S, CONCATENATE(".", MS ) ) ) ) 

Any help would be greatly appreciated.

Thanks Shem

@harshnathani , @v-shex-msft  I have updated with example data in the figure.

[Anonymous]

Got it! sorry, just had to change from SUM to MIN..... 🙈 . Easy to fix.

[v-shex-msft]

HI @Anonymous,

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.

Regards,

Xiaoxin Sheng

[harshnathani]

Hi @Anonymous ,

Please share sample data.

Regards,

HN