Forum Discussion
Convert decimal Duration to meaningful data
Hi
I have the below data, that in PQ is set to duration, and shows the number of minutes per interaction. However, in Dax it shows as a decimal.
That's fine when it is its own visualisation / query, and it displays on the clustered column chart as "Interactions" - a sum of minutes to show hours:
But, if I then include a second second query's "Duration", which has the same setup and similarly linked x axis slots, this happens:
I've determined that this univeral figure across all the slots is because it's treating it as a decimal, or so it seems. But how do I get this second set of values. The values should be slightly higher than "Interactions" because there are more staff providing support during any period than are required...it should show something like this (done by creating two visualisations and placing one over the other. I'm aware that this is a stacked and not clustered, but you can see the point I'm trying to illustrate. Both queries' "Duration" and "interactions" should be being measured against time, not a decimal value. But no matter which way round I try, it never looks correct.
I've asked this question on another topic, but I don't think I explained myself properly and the thread appears to have gone cold. Help!!
7 Replies
- AnonymousNot applicable
HI Anonymous ,
Have alook at this blog.
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Regards,
Harsh Nathani
- AnonymousNot applicable
Thanks, but this doesn't answer my question about why the 'host' query displays this properly in the visual, but the other one just interprets the strict decimal?
- AnonymousNot applicable
Also Anonymous, my columns in both queries for Duration are set to datatype Duration, which is why the 'home' query's version is summed into Hours. I need to know why the other query's isn't, when linked as the second Value of the clustered line and column chart. Forgive me if this is showing my lack of knowledge, but I've not been using PBI very long and this also is not my actual day job!
- AnonymousNot applicable
Hi Anonymous ,
What's the calculation logic of column "Duration"? Which time type need to be displayed as? Hour? Please try to create a calculated column or measure to get it instead of getting it in Power Query Editor, then check if the chart visuals can display correctly... You can refer the following documentations to do.
How to calculate duration in minutes between two columns
Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX
Or please try to change the data type of field Duration as Time type:
Best Regards
Rena