Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Got it! sorry, just had to change from SUM to MIN..... 🙈 . Easy to fix.
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
Hi @Anonymous ,
Please share sample data.
Regards,
HN