I’m running into an issue trying to display some data in a table.
I have an Excel Spreadsheet that has a column for time a truck was traveling in hours:minutes and is marked as Duration in Power Query. That works just fine.
When I try to include this duration column into my visuals it changes the column to a decimal number and doesn’t have any selections for marking the column as a time duration.
Thoughts on a fix for this??? We need to be able to visually look at the time a truck has been traveling.
I realize that I can format the column as Text and display the time traveled but Text won’t allow any measures to be used that would sum the column to see totals for the Month, Quarter, etc.
Hi @GuyJohnson. Perhaps you could keep the column as decimal and write a measure to format the time as text (or duplicate your column and keep one column as decimal for calculations and one column formatted as text for display)? If that won’t work, please upload your work-in-progress PBIX and a marked-up screenshot of the visual of interest, where your desired outcome is noted.
Greg
Duration represents a length of time. It’s converted into a Decimal Number Type when loaded into the model. As a Decimal Number type it can be added or subtracted from a Date/Time field with correct results and you can easily use it in a visualizations that shows magnitude.
You can use the DAX function TIMEVALUE to convert a text time to time OR use the FORMAT function to convert a duration into a text time (explore options here).
If all eslse fails consider keeping two columns, one text for display and one numberic for aggregation.
The FORMAT command seems to work and not work at the same time.
I run the following DAX measure - Test Time = FORMAT( 0, “dd:hh:mm:ss” ) and it returns the following value 30:00:00:00 I’m confused on where the entry for 30 days is coming from.
I don’t think that answers my question, unless I’m missing the boat here.
I’m trying to get a total of the Time a driver is out on the road that can be sliceable. I believe your DAX formula will need work for a single input as in your example Test Time 2
Hi @GuyJohnson. I just generated a datetime value and used it as an example. The FORMAT function works on a Date or DateTime value, so apply it wherever you want. Good luck.
Greg
Hi @GuyJohnson, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
I have solved the issue with a little help from @cmalone1112 and the link provided and a Google search leading to RADACAD
I created a new column using: Time in Minutes = HOUR( ‘Scorecard by Driver’[Total Road Time - Copy] )*60 + MINUTE( ‘Scorecard by Driver’[Total Road Time - Copy] )
This gives me my total traveled time in minutes which I summed as Total Driving Minutes
I then used the formula from RADACAD as follows
Total Driving Time =
var vMinues=[Total Driving Minutes]
var vHours=int( vMinues/60)
var vRemainingMinutes=MOD(vMinues, 60)
return
vHours&" Hours & “& vRemainingMinutes& " Minutes”
