Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Duration: Change the format without changing to string

Hi, I've searched the forum but I haven't found a solution to this problem.

 

I have a large database with this structure:

ProductWorkerDateDuration
Product 1Worker A01/01/20210:17:34
Product 5Worker A01/01/20211:01:26
Product 1Worker A02/01/20211:43:13
Product 3Worker A02/01/20210:34:56
Product 1Worker B01/01/20210:47:34
Product 2Worker B01/01/20212:13:08
Product 2Worker B02/01/20211:03:59
Product 3Worker B02/01/20210:14:20

 

I want to show it in a dashboard with different graphs, such as a timeline of time invested by product, or a graph bar with total time dedicated to each product with a related filter by worker, etc... The problem is that, in each one of these graphs, on the "duration" axis the time is shown as a decimal number. I've tried the following solutions:

Is there a way to be able to play with this data in the dashboard while showing the data in a normal hh:mm:ss format?

 

Thanks!

2 REPLIES 2
AllisonKennedy
Super User
Super User

Can you please link to the solutions you have referenced so we know what you've already tried? You could potentially create a measure that returns string and/or removes the second component if I'm understanding your question correctly. What's the wrong result you're currently getting with the two solutions you've tried and what difference would you like? It sounds like you've only tried using columns up to this point? Or have you tried a measure?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Sure! I've edited the original post, but I've tried both of these solutions:

 

  1. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 
  2. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/td-p/793639 

I've tried using measures, but they don't give me the freedom to play with the data in the dashboard, or I'm using them incorrectly. It would be the perfect solution if I wanted to use cards, but I have three dynamic graphs: one with a timeline with the time inverted by a group of products by day, another where I see the % of time invested in the group of products by total period and a bar graph where I see the total time invested in each product by total period. The last one I use filtering by a worker and by a group of products. Using measures I can't establish this dashboard, as it gives me a pretty rigid result (or that is the only way I know how to use them).

 

The problems with the other two solutions used are:

  1. It returns the duration as strings, so I'm unable to do the dashboard I've described. I need them to be some kind of number that can be aggregated or disaggregated to be able to analyze the data with the graphs help.
  2. It returns numbers (awesome!!), but when it aggregates them, it adds seconds to seconds, minutes to minutes, and hours to hours. So, if I had for Product 1, Day 1, and two workers having spent 1:40:12 and 00:50:13, it shows 1:90:15, instead of 2:30:15. That wouldn't be a problem if the graphs were static, as I would the calculations and transformations. But as I want them to be dynamic (for example, being able to filter the total aggregated by product by month, worker, or group of products), it is not the optimal solution.

I hope I was more clear!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.