Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I hope I am posting this to the right location as I am developing a new report. Anyway, this is coming out of our telephone DB system and one column represented is Duration, which is an int column and represents the call in the number of seconds. I would like to convert and format that to HH:MM:SS. Would something like this work, or is there a better way?
New Duration = FORMAT(TableName[Duration]/60, "HH:mm:ss")
Thanks
Solved! Go to Solution.
@Anonymous
Try a DAX as below.
fmtCol = RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )
Or deal with the format in query.
select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1
Hi, you are just returning the minutes, you must return the entire number.
New Duration = format(((TableName[Duration] / 60)/60)/24, "HH:mm:ss")
Regards
The column value from SQL is an integer in the number of seconds so wouldnt / 60 return how many minutes, seconds, etc (except now I know what you are saying whereas if the number of minutes is more than 60). However, I get an error when trying to use FORMAT for the column in that FORMAT cannot be used with a calculated column.
I also found this article: http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Hi, you must go to Options and then find the DirectQuery options and enable the checkbox that says "Allow unrestricted measures in DirectQuery mode" and try again.
Regards
Unfortunately that did not work.
Straight up SQL Server data table. Nothing special at all.
@Anonymous
Try a DAX as below.
fmtCol = RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )
Or deal with the format in query.
select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1
This is what I am looking for. What would the calculation be if instead of converting minutes it were hours?
Hi @Eric_Zhang
I kinda stuck with the similar error when converting the seconds to duration (HH : MM : SS) format using the following query.
Select TimeinSeconds, from_unixtime(TimeinSeconds,"HH:mm:ss") as Duration From Tablename
When I execute the above query in Impala, I'm getting the result as expected and the same query when I use to load the data into Power BI, I'm getting this "Token Comma Expected" error. Can you help me with this, please?
Hi Eric,
Your code for Power BI solution works well... the only issue Minutes displayed out of 100 min and not as standard 60 min =o?
Any advice?
Thanks
Abduvali
Had a question about D:HH:MM:SS, hopefully this helps.
Dtime (meas) = VAR vDur = <<enter object as seconds>> RETURN INT(vDur/86400) & ":" & //Days RIGHT("0" & INT(MOD(vDur/3600,24)),2) & ":" & //Hours RIGHT("0" & INT(MOD(vDur/60,60)),2) & ":" & //Minutes RIGHT("0" & INT(MOD(vDur,60)),2) //Seconds
Hi, this post is in spanish but will be helpful in this case.
http://blog.iwco.co/2018/03/28/formato-duracion-power-bi/
Regards
just a click on a translate button and this is so helpful thanks a lot.
PS: use @jbocachicasuggested website, if you want to convert from seconds to this format HH:mm:ss, I used the following expression using DAX. I just created a new measure using the existing measure in seconds, because my seconds rae in a measure not a column, well I am getting my Data from Analysis Services.
Measure := FORMAT(FactTable[MeasureName in seconds]/86400, "HH:mm:ss")
By magic you have it in one single line haha.
Read about Format Function here ==> https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax
Thank you!!! You are officially a legend =D
Worked really well to convert seconds into time format from a calculation in a measure!!!
To be honest, there is a big lack of time formatting options in Power BI, I would love to see a function that would allow us to display time as a total number of hours like in Excel that would make life so much easier.
but anyway rant is over =D thanks again!!!
I'm pretty new to DAX so it's possible that I'm doing something wrong, but your code was calculating the incorrect seconds for me and I made the following changes to get it to work:
fmtCol =
RIGHT ( "0" & INT ( TableName[Duration]/ 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( (TableName[Duration]- INT (TableName[Duration]/ 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & INT(MOD(MOD (TableName[Duration], 3600),60)), 2 )
@Eric_Zhang
Hello I was able to create the DAX as a calculated column not a measure when creating as a measure I get the following error:
"A single value for column duration in table dialingresults cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Either way when I use the code provided:
Time =
RIGHT ( "0" & INT ( DialingResults[Duration]/ 3600 ), 2 )
& ":"
& RIGHT (
"0"
& INT ( (DialingResults [Duration]- INT (DialingResults [Duration]/ 3600 ) * 3600 ) / 60 ),
2
)
& ":"
& RIGHT ( "0" & INT(MOD(MOD (DialingResults [Duration], 3600),60)), 2 )
It is giving me mis-calculations see attached image.
Hi
I am also experiencing this, have you found any solution for this?
Thanks
Guys I think I got it.
You need a field that is just seconds and use this code as a measure.
Time = FORMAT(INT(
IF(MOD([Seconds]|60)=60|0|MOD([Seconds]|60)) +
IF(MOD(INT([Seconds]/60)|60)=60|0|MOD(INT([Seconds]/60)|60)*100) +
INT([Seconds]/3600)*10000)| "00:00:00")
Seconds = the field that contains seconds.
Thanks you! I have just updated a little bit your code to use IT in SSAS :
=FORMAT(INT( IF(MOD(('WEBSITES KPI'[Temps_passé]/1000),60)=60,0,MOD(('WEBSITES KPI'[Temps_passé]/1000),60)) + IF(MOD(INT(('WEBSITES KPI'[Temps_passé]/1000)/60),60)=60,0,MOD(INT(('WEBSITES KPI'[Temps_passé]/1000)/60),60)*100) + INT(('WEBSITES KPI'[Temps_passé]/1000)/3600)*10000), "00:00:00")
It's working fine for me 😄 !
I have divided by 1000 because I have the duration in milisecondes.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
5 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
10 | |
4 | |
3 | |
2 | |
2 |