Forum Discussion
Create Array lookup in Query
I am trying to measure TAT. To do so, I need to calculate how long an item was in each queue using only the time an item entered each queue. In excel, I used an array to return the min Entered Queue Date that is greater than the current row's Entered Queue Date where the Name matches. How do I do this in PowerBI Query?
Sample Data set: Name and Entered Queue exist. I am trying to create the Exit Queue or a Time in Queue column instead.
| Name | Entered Queue | Exit Queue |
| 321Z279_00JWH2BWP000B3S | 7/9/2018 10:54 | 7/11/2018 13:27 |
| 321Z279_00JWH2BWP000B3S | 7/11/2018 13:27 | 7/12/2018 14:58 |
| 321Z279_00JWH2BWP000B3S | 7/12/2018 14:58 | 7/17/2018 9:23 |
| 321Z279_00JWH2BWP000B3S | 7/17/2018 9:23 | 7/18/2018 9:00 |
| 321Z279_00JWH2BWP000B3S | 7/18/2018 9:00 | 8/15/2018 12:59 |
| 321Z279_00JWH2BWP000B3S | 8/15/2018 12:59 | 8/15/2018 13:25 |
| 321Z279_00JWH2BWP000B3S | 8/15/2018 13:25 | 8/31/2018 13:30 |
| 321Z279_00JWH2BWP000B3S | 8/31/2018 13:30 | 9/13/2018 10:34 |
| 321Z279_00JWH2BWP000B3S | 9/13/2018 10:34 | 9/19/2018 13:17 |
| 321Z279_00JWH2BWP000B3S | 9/19/2018 13:17 | |
| 321Z15Z_00ECD8YEZ001N3Y | 4/10/2018 11:32 | 4/10/2018 13:17 |
| 321Z15Z_00ECD8YEZ001N3Y | 4/10/2018 13:17 | |
| 321Z189_00F5DTK02000F2L | 1/13/2018 11:33 | 1/13/2018 11:36 |
| 321Z189_00F5DTK02000F2L | 1/5/2018 8:23 | 1/13/2018 11:31 |
| 321Z189_00F5DTK02000F2L | 1/13/2018 11:36 | |
| 321Z189_00F5DTK02000F2L | 1/13/2018 11:31 | 1/13/2018 11:33 |
| 321Z189_00F5DTK02000F2L | 1/3/2018 12:23 | 1/5/2018 8:23 |
Thanks.
You can use this calculated column:
= CALCULATE ( MIN ( Table1[Entered Queue] ), ALLEXCEPT ( Table1, Table1[Name] ), Table1[Entered Queue] > EARLIER ( Table1[Entered Queue] ) )
2 Replies
You can use this calculated column:
= CALCULATE ( MIN ( Table1[Entered Queue] ), ALLEXCEPT ( Table1, Table1[Name] ), Table1[Entered Queue] > EARLIER ( Table1[Entered Queue] ) )