cancel
Showing results for
Did you mean:
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:

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"
//
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
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
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.

3 REPLIES 3
Highlighted
Super User III

Hi @Shem_R ,

Regards,

HN

Highlighted
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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors