topic Re: SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000 in DAX Commands and Tips

SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000

AFuente7 — Tue, 10 Oct 2023 20:44:11 GMT

Hi,

I have an excel base data with many laptimes (from a car in a circuit), and the format used there is: mm:ss,000. For example: 1:35,577

(which means 1 min, 35 sec, 577 dec)

If I import this data in BI, it takes also a date, so i've tried to use a different format in Excel and use seconds, so in the example above, would be 95,577s.

Then I've created a new column and used the formula =FORMAT(LapTime, "mm:ss,000) and it doesn't work. It gives me sth like 04:58:000, which doesnt make sense for me.

So anyone knows how to achive to have this format in BI?

Thanks!!

Re: SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000

DallasBaba — Tue, 10 Oct 2023 21:08:40 GMT

AFuente7

Can you convert the lap times to seconds in Excel by using the formula =TIMEVALUE("00:"&A1)*86400, where A1 is the cell containing the lap time in the format mm:ss,000. This will convert the lap time to seconds.

Then import the data into Power BI, and create a new column with the formula =TIME(0,0,[LapTimeInSeconds]), where [LapTimeInSeconds] is the name of the column containing the lap times in seconds.

Change the data type of this new column to “Time”. This should give you a column with lap times in the format mm:ss.000.

Let me know if this helps.

Thanks

Re: SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000

AFuente7 — Tue, 10 Oct 2023 21:40:52 GMT

Definetely it works!!

It's just missing the info of the tenths.

So now the result is:

0:01:37

So it's missing the 0:01:37,577 for example.

Re: SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000

DallasBaba — Tue, 10 Oct 2023 21:52:04 GMT

AFuente7 you can use the following DAX formula to correct the missing data.

LapTimeFormatted = FORMAT(TIME(0, 0, [LapTimeInSeconds]), "mm:ss.000")

Please consider Accept it as the solution to help the other members find it more quickly.

Regards,
Babatunde

Re: SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000

AFuente7 — Mon, 16 Oct 2023 11:03:33 GMT

Hi,

It's not working 100%. It's missing the tenths, and it looks like PowerBI just does not have this format available on his software (as Ive read in some foros)

Re: SEE IN POWERBI A LAPTIME IN FORMAT mm:ss,000

DallasBaba — Mon, 16 Oct 2023 18:17:15 GMT

AFuente7 you can convert your lap times in Excel to seconds using the formula =TIMEVALUE("00:"&A1)*86400, where A1 is the cell containing the lap time in the format mm: ss,000.

Than, import your data into Power BI and create a new column with the formula
LapTimeFormatted = TIME(0,0,[LapTimeInSeconds])
where [LapTimeInSeconds] is the column's name containing the lap times in seconds.

Change the data type of this new column to Time and then change the format to hh:mm:ss.000. This should display your lap times with milliseconds.

If you want to display your lap times in a different format, such as mm: ss.fff or mm: ss,fff, you can create another column or measure with the formula

LapTimeFormatted = FORMAT([LapTime], "mm: ss.fff") OR
LapTimeFormatted = FORMAT([LapTime], "mm: ss,fff")
where [LapTime] is the column's name containing the lap times in Time format.

You can also refer to the blog below for more details about the million-seconds workaround:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Milliseconds-Duration/td-p/406698

Let me know if this helps.
Thanks