cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Fastest time with milliseconds

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:

Shem_R_0-1593053922870.png

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Hi @Shem_R ,

 

Please share sample data.

 

Regards,

HN

Highlighted
Community Support
Community Support

HI @Shem_R,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Frequent Visitor

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors