Identify non duplicates, to remove, and then calculate time spend on two steps, and average as well

@Jetuke_ES,

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)

@Jetuke_ES,

Just curious - How much RAM do you have on the computer that’s running this report?

  • Brian

16GB ram. That should be okay, right?

@Jetuke_ES,

Yes, that should be fine. I thought if it was 8MB, that might be an issue.

  • Brian

@Jetuke_ES

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 :slightly_frowning_face:

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. :crossed_fingers:

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 :slight_smile:

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 :wink: 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! :smile: Stay safe!

Excellent. That’s great to hear.
Thanks for letting me know Erika.

Melissa, i wanted to calculate average duration in Hours per month, would you use the Quick Measures for that? : )

Hey Brian,
Sorry to be bothering again. If i wanted to amend measure for Average per month, could you help amend the above dax for that possibly? So to see how many Hours (not days) does it take to complete both steps. I tried to Change to elapsed Hours instead, and removed the *60 in your measure. Something didn’t work though. Any help would be really appreciated. : )

@BrianJ made it work… :slight_smile: please disregard!!

@BrianJ is there way to exclude weekends for the Min and the Max dates for the duration/ elapsed calculation?

@Jetuke_ES,

Funny you should mention that - @Melissa were talking about that yesterday in another context. There are lots of ways to do this, but I think the easiest way is to include the NETWORKDAYS custom function within your date table. See this forum post for details:

Then what you will do, is subtract the NETWORKDAYS column values associated with your beginning and end date values to give you the duration not including weekend days (you can also exclude holidays if you want).

Take a look at this, and if you have any questions about implementing this within your calculations, I’m happy to work through the details with you.

  • Brian