Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have PBI pulling in an excel spreadsheet and one of the colums is in h:mm:ss format. The column is used to show how long someone is doing something.
It works when the time is under 1 hour but when it's over an hour, it starts to show date/time instead. I've tried transforming it to several formats and nothing worked.
Any ideas on how to show just the time?
Looking at the below picture, the first one with the date, I want to to look like the others below it - 1:19:44 With no date in front
Hi,
Please change this column data type to 'time' and then create a calculated column:
column = Format('Table'[TimeTaken],"h:mm:ss")
Best Regards,
Giotto Zhi
Did not work the way I need it to
Anything under 1 hour, it's showing as Error. Everything over 1 hour, it's showing as time.
For example, 1 hour and 15 minutes, which is how long it took someone to work on their project is now showing as 1:15AM
I need it to stay as h:mm:ss So if they worked 4 hours and 15 minutes, I need 4:15:00, not 4:15AM or PM
Hey @bryanc78
Check this thread out and let me know if it works: https://community.powerbi.com/t5/Developer/formatting-time-value-to-hh-mm-ss/td-p/85249
No as that column is in seconds but it did lead me to another post where I found this:
Custom = #duration(0, Time.Hour([Time in Project]), Time.Minute([Time in Project]), Time.Second([Time in Project]))
However, this fixes the ones that are over an hour (though I don't like that leading 0) but errors for those under an hour are now occurring
Hey @bryanc78
I agree the leading 0's are not ideal. To solve the Errors, just embed an IF statement to make the hours 0 or leave out the hour.
Also, have you tried using FORMAT in DAX? https://docs.microsoft.com/en-us/dax/format-function-dax
Hey @bryanc78
Check out this link for IFERROR function: https://docs.microsoft.com/en-us/dax/iferror-function-dax
Put the IFERROR around your time.hour to return 0 if time.hour errors.
It's not working unless my sytax is the issue
#duration(0, IFERROR(Time.Hour([Time in Project],0)), Time.Minute([Time in Project]), Time.Second([Time in Project]))
Hey @bryanc78
Check out the link below: https://community.powerbi.com/t5/Desktop/Duration-format/td-p/447543
I'm not sure if what you are trying to accomplish is possible. The general workaround is to convert your times to seconds and then convert to HH:MM:SS. Aside from this and the article in the thread above I don't think there is another solution.
Select the column or measure. Go to the "Modelling" menu at top of Power BI desktop. Here there should be options to choose the format of the selected column or measure. One of the options (along with "Text", "Whole Number" etc.) should be "Time".
If you select this option it should format the whole column or measure as a time, excluding the date from your view.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |