Power BI apply DAX to column AFTER aggregation Elapsed Time hh:mm:ss

Question

I have been struggling to get Elapsed Time values to work the way I'd like them to.

My data is coming from MYSQL and contains a start and end datetime

(format is 7/27/2018 12:16:12 AM)

I created a custom column using the Power Query Editor that contains this simple formula:

=[TimeCompleted]-[TimeStarted]

I then changed the type in the column to Duration which leaves the values in this format:

4.00:01:37 (which translates to 4 days, 1 minute, 37 seconds as I understand)

That all seems fine - when I view the values in a table however, the amounts are different. They display as decimal numbers (like 6.9444444)

I believe they're in the format of "days" which is basically like 6.9444444 or 0.0000324

When I put these values in a table to view, it might show that the elapsed time is 0.5 (which translates to 12 hours?)

I have a DAX formula that can convert a value to hh:mm:ss, but I would like to apply this after I have aggregated the data.

For example, my table with Elapsed Time looks like this:

I would like to leave it just like this, but format the column so that it is in the format of hh:mm:ss for display purposes.

Is it possible to do this?

Thanks for your help.

EDIT:

As suggested, I created a measure that summed the ElapsedTime and it seemed to work just fine (except that anything > 24 hours would wrap around).

The solution I went with is to just add the hours to the beginning yourself if it is greater than one day:

ElapsedTimeHMS = 
IF(
    SUM('checklists allqachecklists'[ElapsedTime]) >= 1,
    INT(SUM('checklists allqachecklists'[ElapsedTime]))*24 + INT(FORMAT(SUM('checklists allqachecklists'[ElapsedTime]),"hh")) & ":" & 
    FORMAT(SUM('checklists allqachecklists'[ElapsedTime]), "mm:ss"),
    FORMAT(SUM('checklists allqachecklists'[ElapsedTime]), "hh:mm:ss"
)

The result for 1.07 becomes 25:12:45 (before it was 01:12:45)

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-16T19:10:33

Sure. If your measure is something like SUM(Table1[Elapsed Time]), then you can just add a formatting wrapper to it.

Formatted Measure = FORMAT(SUM(Table1[Elapsed Time]), "hh:mm:ss")

Power BI apply DAX to column AFTER aggregation Elapsed Time hh:mm:ss

1 Answers