How to extract day, hours, minutes and seconds from duration in separate columns for sum

[eyelikedata2]

Hello,

I have a data set with many rows of seconds listed as text for openDuration. I have created a custom column called timeDuration from or =#duration(0,0,0,[openDuration) to convert to proper format. However, I cannot use filter slicers in visuals like sum, avg, min and max from these. I read the solution was to separate the data and then add it back together again to filter on a single value.

Would anyone have any suggestions on how to go about this with a single column I can get values from?

Thank you!

[eyelikedata2]

bump

[eyelikedata2]

How would I do this?

I made the change to duration and it shows the data type correctly in transforms, but when looking in data mode, it becomes a decimal number. If I change it to time, it will result in numbers with E5 in them. Here's what I have now which does let me adust in the visual, but doesn't make any sense.

Now in data view

And the visual

[eyelikedata2]

Here's a sample test data set.

The data

The transform to add seconds was done in the prior image and last column to translate seconds of duration to a time format. I have left the default of text data type as changing to duration looks fine in transform mode.

But again, when I attempt to use filters in a visual for min, max, average they are not present. I don't want a long answer but just to make this data visualize properly. I would prefer not to write out DAX or custom visuals, as this is easier to maintain for people who don't do this on a daily basis and need to understand or modify a visual.

[ppm1]

Not totally clear on your scenario and separate columns. In general, it is a good practice to have a duration value as a decimal in days, load that, and then do your aggregate and then just display in hh:mm:ss with FORMAT.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[eyelikedata2]

@ppm1Hopefully, my response clarifies the ask. I need to be able to calculate the min, max and average from that dataset and column shown called timeDuration in the first and second images for the visual on the third.

[ppm1]

In your query editor picture, the column still shows as ABC123 (the Any data type). Change that to duration. When you load it, it will show (like it does currently) as a decimal in days. That should show options for min/max/avg, but I would recommend writing explicit measures (MIN(table[timeDuration]) and using format strings or the FORMAT function like described in that article.

Pat

[eyelikedata2]

@ppm1I did this as requested. The data view messes it up again.

But I can work with the data min, max, etc. but doesn't make sense.