I was recently doing some consulting work for a customer and they had a column which contained seconds and whilst this is useful it is better to have it shown as Minutes and Seconds, which is easier to relate and compare.

Below I will show you how I came up with the DAX measure which converts it from seconds to minutes, and I will be using my Parkrun sample dataset. As shown below I have a table which shows the total seconds

Below is my DAX measure with an explanation on how I got to the measure

  • Line 1
    • This is where I created the measure with a name
  • Lines 2 – 3
    • This is where I am summing up all the Total Seconds
  • Lines 4 – 5
    • I am creating a variable called TimeMins and then dividing the seconds by 60 to get the minutes.
  • Line 6 – 7
    • I am creating a variable called JustSeconds, and I am using the TRUN DAX function, so that this returns the remainder of the variable TimeMins, which is currently stored as a decimal.
    • NOTE: The above means that the remainder is stored as decimal point out of 100. So if the remainder is 0.50 that translates to 30 seconds. Which is shown below.
  • Line 8 – 9
    • I am creating a variable called JustMinutes, where all that I want is the Minutes, so I do this by subtracting the TimeMins from JustSeconds
  • Line 10
    • I am creating a variable called JustSecondsInTime, and what I am doing is converting the seconds from a decimal value back to time.
    • What this means is that if the decimal value was 0.50, the variable JustSecondsInTime will be converted to 0.30 which represents 30 seconds.
  • Line 11 – 12
    • I am creating a variable called MySeconds2, and what I am doing here is to ensure that if it is less than 10 seconds to add in a zero before the second number. This is so that it will be displayed as expected.
    • As shown below you can see where the seconds are less than 10 it is displaying correctly.
    • When looking at the image above it does appear that the seconds is not being displayed correctly. This will be resolved in the next step below.
  • Line 13 – 14
    • Now I am closing off the variable section to return the measure.
    • I begin with the DAX function called VALUE, this is because I want my DAX to be a measure in my model.
    • I then put in my variable called JustMinutes which is the minutes from the seconds that I defined in lines 8 & 9
    • Next, I put in a dot “.” This is to ensure that the VALUE will still work because it expects a value in the format of 99.22
      • If I had to change it from a dot to the colon the VALUE function would return an error.
    • I get the seconds to display correctly by using the DAX function LEFT ( MySeconds2, 2 )
      • What I am doing here is using the LEFT DAX Function to get the first 2 values starting from the left hand side. Which results in displaying the seconds

Now when I take my measure and put it into a table I get the Minutes and seconds displaying as expected.

I found that I really had to use the DAX variables to get this pattern to work as expected. With all the screenshots above, I changed the return function to the particular DAX variable that I was working on to ensure that it was returning the value that I was expecting.

If you have any questions or comments please leave them in the section below. I do hope that you found this useful.

7 thoughts on “Convert from Seconds to Minutes with a DAX Measure”

  1. Thanks for sharing this information!

    How can we use “HH:MM:SS” as an axis value for a line/bar/area chart?

    1. Hi there,

      The challenge is that I wanted the result to be a data type of number so that it could be used as a measure.

      Would you be looking to rather use the output with hours, mins, secs?

        1. @Michael
          you can use a calculated column:

          Secs to Time =
          VAR Hours = QUOTIENT ( ‘ParkRun'[Total Seconds], 3600 )
          VAR Rest = MOD ( ‘ParkRun'[Total Seconds], 3600 )
          VAR Minutes = QUOTIENT ( Rest, 60 )
          VAR Seconds = MOD( Rest, 60 )
          TIME ( Hours, Minutes, Seconds )

          1. Thanks Frank, that would most certainly work.

            The challenge would be when you want to use it as a measure.

            You could do it with the following code below, due to a number value only having one point.

            Sec-Min-Hour =
            VAR TimeSecs =
            SUM ( 'ParkRun'[Total Seconds] ) + 1
            VAR TimeMins =
            DIVIDE ( TimeSecs, 60 )
            VAR JustSeconds =
            TimeMins - TRUNC ( TimeMins )
            VAR JustMinutes = TimeMins - JustSeconds
            VAR JustSecondsInTime = JustSeconds * 60
            VAR MySeconds2 =
            IF (
            JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) VAR JustHours = VAR HourTest = DIVIDE ( JustMinutes, 60 ) RETURN IF ( HourTest < 1, 0, HourTest ) VAR JustHours2 = DIVIDE ( JustMinutes, 60 ) VAR MoreThan1Hour = IF ( JustMinutes < 60, VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) ), VALUE ( LEFT ( JustHours, 2 ) & "." & JustMinutes ) ) RETURN MoreThan1Hour

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.