Forum Discussion
Helper ICalculate Delta in time between 2 different events within the same table
I'm trying to get some general direction on which way to go. I'm not even sure what direction to search
- Is there a Dax formula that will flatten a table
In the scenario below I feel like I need to flatten a table to get a result. Any point in the right direction would be helpful.
** edit 11/7 Changed Picture. While practicing Pivot / Unpivot I realized my Job type column was not correct in original picture.
Hi briguin , the one question I have for you - you call out two job positions, Delivery and Shelf Stocker. Are these two always "fixed" - i.e. do you know for sure these are the two job positions you care about, and you won't need any more? The options really are:
1. Yes, these are the only two you're interested in
2. No, there are more that I'm interested in....but it's a fixed list and doesn't change
3. No, there are more that I'm interested in...and the things are constantly changing, new ones being added and old ones deleted
The easiest way to solve your problem is to have a single table with 4 columns for timestamps, "Delivery Time Start", "Delivery Time End", "Shelf Stocker Time Start", and "Shelf Stocker Time End". Then it's super easy to calculate total minutes, average minutes, etc. between any of those points. This works perfectly for option #1 I listed above. If you have more categories but they are relatively fixed (option #2), I'd recommend the same thing - start/stop times for each "job type".
But if you're in option #3...things will be more difficult.
p.s. Probably best to do this in Power Query. Pivot/Unpivot will be your friends here.
Hope this helps! Let me know which option you're in, and I can help write a quick PBI file with a few rows of sample data to give you an idea how to handle this.
Thanks,
Scott
2 Replies
Hi briguin , the one question I have for you - you call out two job positions, Delivery and Shelf Stocker. Are these two always "fixed" - i.e. do you know for sure these are the two job positions you care about, and you won't need any more? The options really are:
1. Yes, these are the only two you're interested in
2. No, there are more that I'm interested in....but it's a fixed list and doesn't change
3. No, there are more that I'm interested in...and the things are constantly changing, new ones being added and old ones deleted
The easiest way to solve your problem is to have a single table with 4 columns for timestamps, "Delivery Time Start", "Delivery Time End", "Shelf Stocker Time Start", and "Shelf Stocker Time End". Then it's super easy to calculate total minutes, average minutes, etc. between any of those points. This works perfectly for option #1 I listed above. If you have more categories but they are relatively fixed (option #2), I'd recommend the same thing - start/stop times for each "job type".
But if you're in option #3...things will be more difficult.
p.s. Probably best to do this in Power Query. Pivot/Unpivot will be your friends here.
Hope this helps! Let me know which option you're in, and I can help write a quick PBI file with a few rows of sample data to give you an idea how to handle this.
Thanks,
Scott
briguin#2 is more along the lines of what it's like.
In the power query side I go into the model using the table as I described. In that format I'm doing data visualizations against that original table.
I see the Pivot/Unpivot comment but I'm unclear if I can do that on the original table and still leverage it for the original visualizations. (I'll go do some reading on Pivot/Unpivot.. I'm assuming that is a permanent change to a final table.)
I think I need to almost make a copy of that Original table into some type of summary table? I that something that can be done.
**** edit** Maybe Duplicate the table and then tranform it into a pivot. Basically giving me one version of a flat table and keeping my original