cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate durations for each product category

Hi everybody!

I'm not very good in DAX (yet!) so every help will be very appreciated.

I am sorry if this is an obvious question, however I red a lot on the community but I didn't find the solution I need.

I need this output, for each id:

 id STEP DURATIONS 1 A 30d 5 h 8 m 1 BB 0d 0 h 1 m 1 CCC 8d 1 h 13 m 2 A days, hours 2 BB days, hours 2 CCC days, hours 3 ... ...

From this input table:

 id step status date duration (to be calculated for step and for EACH ID) 1 A in 11/10/2020 10:04 0:00:00 1 A out 11/10/2020 10:06 0:02:09 1 BB in 11/10/2020 10:06 0:00:00 1 BB out 11/10/2020 10:07 0:01:00 1 A in 11/10/2020 10:07 0:00:00 1 A out 11/10/2020 10:08 0:01:03 1 CCC in 11/10/2020 10:08 0:00:00 1 CCC out 18/11/2020 11:12 913:03:50 1 A in 18/11/2020 11:12 0:00:00 1 A out 18/12/2020 16:18 725:05:30 1 CCC in 18/12/2020 16:18 0:00:00 1 CCC out 19/12/2020 16:28 24:10:03 2 A in ... ...

I tried to calculate duration as a calculated column. To do this I've added two index columns and then combining FILTER and EARLIER I calculated the durations, but I am not able to do it separately for each id...

How can I do it? Which may be the best approach, DAX formulas, calculated columns, M?

Thanks a lot for any advice 🙂

1 ACCEPTED SOLUTION
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

Hi,

Based on the information that you have shared in the second table, show the exact figures that you are expecting in the first table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish! Thank you for your help.

I've updated the output table, and also the input, with the figures.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish, your approach is different from mine: you've pivoted the date column. This is very clever!

So I canceled my calc columns and measures and got into M query to pivot and have two columns for in and out date.

However, when trying to pivot columns, I see the error: There were too many elements in the enumeration to complete the operation.

Couldn't find out, then I used your magic steps and finally I got my output table!

THANK YOU!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com