Hi @Jetuke_ES, please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Thanks!
Glad to help – that was fun. FYI – I revised the Elapsed Days measure and the solution file posted. Still returns the same results, but is more robust if you end up putting more information into the table visual.
Just give a shout if you have any questions when you try these out with your data.
- Brian
Hi both,
all is working out perfectly up until the calculating the A and B step per Person duration to complete. Think i missing something, so sorry to be bothering again. Did you do the new Group By by Step, Overall Process and in Operation you did Max completed on and Min Created on for AllRows? Think im really close just missing something.
Okay that’s sounds like the part with the nested table functions I illustrated yesterday. Please see if you can fix it by examining the M code for the each of the Applied Steps in the provided PBIX.
If you can’t resolve it, post another sample that matches your column headers and structure exactly. Then I’ll set it up for you.
Melissa, that worked out for me! 
Could i possibly identify if there are weekend in between the Closed on and Created in between Steps? So the Duration would only cover week days and no weekends?
In excel i was just doing the Networkdays function. In this case would you use Calendar function between the MIN and MAX dates that are used in the duration calculation?
Thank you so much.
It is absolutely inexplicable to me that DAX still does not include a NETWORKDAYS function. Until it does, check out the following thread for how to mimic that capability using a custom function within your date table:
- Brian
1 Like
Hi both,
Does this seem odd to you, i get midst way through the transformations and power desktop starts “hanging”. cant even complete the transformations. With a small file i could. With this one - the full master file, its not even thaaaat huge (3mb) i get to M Magic point and seems it turns to like huge file and is just hanging. (it’s says now its 1.46GB and still keeps going). any tips or am i doing something wrong?
I even tried to just amend the Advanced editor, but didnt work.
Ok so let me try and tweek the M code a bit, will get back to you!
Amazing Melissa!! do you think its the code? i attached your pbix as well.
Thank you so so much!! eDNA - Identify data sets to keep.pbix (36.4 KB)
Just curious - How much RAM do you have on the computer that’s running this report?
- Brian
16GB ram. That should be okay, right?
I know that Table functions are generally more optimized, so I changed the M code for that step into:
Table.AddColumn(#"Grouped Rows for Duration", "TempTable", each Table.AddColumn(Table.AddColumn([AllRows], "Min", (x)=> Record.Field(Table.Min([AllRows], "Created On"), "Created On")), "Max", (y)=> Record.Field(Table.Min([AllRows], "Completed On"), "Completed On")))
Which does exactly the same as the previously used List function, I was hoping however to also be able to add a Buffer step but I just learned that is not possible for nested tables 
Fingers crossed
eDNA - Identify data sets to keep (2).pbix (37.0 KB)
1 Like
Thank you so much, giving it a go now.
Ok here are some other variations to try…
eDNA - Identify data sets to keep (3).pbix (40.2 KB)
eDNA - Identify data sets to keep (4).pbix (40.5 KB)
Now I’m all out of ideas, so let’s hope one of these will do the trick. 
1 Like
Somewhere between start and to remove duplicates and M Magic from 3MB it turns into 1.67GB. Do you think its because of group by or something? When i did the “Remove Duplicates” it reduced it significantly. I did manage to finish it this time though - Yaaay!! i wonder though what will happen when i will start adding data on a monthly basis
Check out this thread hopefully you can implement some other optimizations.
1 Like
Ok so I’ve been exploring ways to avoid the second Group By operation and succeded 
So would you be so kind to test these variatons as well and let me know if performance improved?
Thanks @Jetuke_ES
Now for future reference, this is what I’ve done:
Next I extracted the Min value for [Created On] in two different ways:
Did the same for the Max value for [Completed on] also two approaches:
Here are the updates files. I hope this is helpful.
eDNA - Identify data sets to keep (3b).pbix (44.7 KB)
eDNA - Identify data sets to keep (4b).pbix (40.6 KB)
1 Like
Thank you Melissa!! Made such a difference. Thank you so much for your patience and help! 
Stay safe!
Excellent. That’s great to hear.
Thanks for letting me know Erika.