Forum Discussion
Helper IIIPlotting Duration as continuous line graph
Hello All,
I am have been stuck on this for a while and i would really appreciate help on this. I have got experimental data where i have got several readings for each experiment and the duration of when the reading were taken (Duration is calculated in DAX from time stamps https://community.powerbi.com/t5/Desktop/Calculate-time-between-dates-for-each-unique-identifier/m-p/1243388#M549580)
here is a small snippet of my data in dax. A single experiment can contain 80,000 readings which is very difficult to visualise without a continuous line graph.
| ID | Date | duration | reading |
| 1 | 16/07/2020 14:11:12 | 00:00:00 | 1 |
| 1 | 17/07/2020 18:32:23 | 28:21:11 | 2 |
| 1 | 16/07/2020 18:32:44 | 04:21:32 | 3 |
| 1 | 16/07/2020 17:11:24 | 00:00:12 | 4 |
| 2 | 19/07/2020 06:11:12 | 00:00:00 | 1 |
| 2 | 19/07/2020 12:12:12 | 06:01:00 | 2 |
| 2 | 20/07/2020 14:32:44 | 26:20:32 | 3 |
I am trying to compare the different experiments by plotting the duration on the x-axis and the readings on the y-axis. The problem is if i plot the duration as 'text' the line graph will be plotted as categorical and i will not be able to plot it as continuous.
I then tried to change duration to 'time' and i started getting weird dates on the x-axis when the duration is greater than 24 hours (It shows weird dates in powerbi service even if duration is less than 24 hours.)
My main question is, how can i plot duration on the axis and just see the duration without dates, and also have this as continuous so that i dont have to scroll? I would like to have duration on the axis as 28:21:11 or in any other format such as 1.04:21:11.
5 Replies
- v-easonf-msft
Community Support
Hi, Adham
It's so strange .When I use your sample data for testing, I cannot convert the column type.
How do you transform the data type?
Best Regards,
Community Support Team _ Eason
Adhamv-easonf-msft I forgot to mention that i was able to convert the column to 'time' by removing the rows with a duration greater than 24 hours. This is not ideal ofcourse but i did it for the sake of experimenting
- AllisonKennedy
Community Champion
Duration data type is only supported in Power Query Editor, not in Power BI report, so you should try making it decimal number to achieve the continuous result you're looking for.
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#:~:text=Power%20BI%20Desktop%20supports%20three,it%20also%20handles%20whole%20numbers.- Adham
How can i convert hh:mm:ss into decimal? I think it would work if it was in hh:mm or mm:ss but in my case it is slightly different
- AllisonKennedyI think Power BI handles this in a similar way to Excel - assuming that 1=1day=24hours=1440minutes = 86400seconds
So for converting hh:mm:ss into decimal you would basically take
(24/hh)+(1440/mm)+(86400/ss)
If you wanted to you could customize this and set 1 hour = 1 and update the above formula, depending on what units you want on your axis.
