Forum Discussion

nicoag98's avatar
nicoag98
New Member
3 years ago
Solved

Text to Minutes, Seconds, Milliseconds Format

Hi all, I have a text column with circuit lap times (Minutes, Seconds, Milliseconds) You know if it is possible to convert this data to a duration format with milliseconds in order to make averages,...
Need Help
Tutorial Requests
  • ppm1's avatar
    ppm1
    3 years ago

    First add a prefix of 00: as text to your column (Transform tab, Format button), then convert it to type Duration. When you load your data, it will convert to a decimal (in days). From there, do your measures/aggregations and then FORMAT it at the end, if desired. See this article for more details.

    Calculate and Format Durations in DAX – Hoosier BI

     

    Pat

therealfzoccara's avatar
therealfzoccara
New Member
2 years ago

From a duration column (displayed in visuals as a decimal) I'd create a measure, this measure aggregate the duration values and display it as a readable column, here the measure code:

 

 

 

Timing =
VAR dur = AVERAGE('Fact Tempi'[Durata])
VAR hours = INT ( MOD ( dur * 24 , 24 ) )
VAR minutes = INT ( MOD ( dur * 24 * 60, 60 ) )
VAR seconds = INT ( MOD( dur * 24 * 60 * 60, 60))
VAR millis  = INT ( MOD ( dur * 24 * 60 * 60 * 1000, 1000 ))

VAR hoursText =
    IF (
        LEN ( hours ) = 1,
        CONCATENATE ( "0", hours ),
        CONCATENATE ( "", hours )
    )
VAR minutesText =
    IF (
        LEN ( minutes ) = 1,
        CONCATENATE ( "0", minutes ),
        CONCATENATE ( "", minutes )
    )
VAR secondsText =
    IF (
        LEN ( seconds ) = 1,
        CONCATENATE ( "0", seconds ),
        CONCATENATE ( "", seconds )
    )
VAR millisText =
    IF (
        LEN ( millis ) = 3,
        CONCATENATE ( "", millis ),
        IF (
            LEN ( millis ) = 1,
            CONCATENATE ( "00", millis ),
            CONCATENATE ( "0", millis )
        )
    )
RETURN
    hoursText & ":" & minutesText & ":" & secondsText & "." & millisText