0

new to the board and have a frustrating issue blocking me from proceeding with a project. So I have a worksheet which documents times when warehouse pickers select items, and a formula that determines how long passed between picks, to monitor for excessive stops. The formula looks like this:

=IF([@[PICK TIME]]-MAXIFS([PICK TIME],[Last Picked By User (Username)],[@[Last Picked By User (Username)]],[PICK TIME],"<"&[@[PICK TIME]])>1/12,0,[@[PICK TIME]]-MAXIFS([PICK TIME],[Last Picked By User (Username)],[@[Last Picked By User (Username)]],[PICK TIME],"<"&[@[PICK TIME]]))

Here's a portion of the sheet it lives within (TIME FROM LAST is where the formula exists):

Now, I have a 1.6 million record Access file that feeds into Power BI, and I need to replicate this functionality in DAX. I'd like to do it all at once, but in the end I'm fine with just a column that produces the time of the previous pick for that specific picker, and a measure can do the subtraction. But here's what I end up with:

How would I modify this code to prevent a circular dependency error and produce the desired result? Thanks in advance for your help, and please let me know if you need anything else!

Prev Pick = 
VAR PT = 'Lucas Archive'[Date/Time]
VAR Pckr = 'Lucas Archive'[Picker]
    RETURN
    CALCULATE(MAX('Lucas Archive'[Date/Time]),
    'Lucas Archive'[Date/Time] < PT,
    'Lucas Archive'[Picker]=Pckr
)
vvvvv
  • 25,404
  • 19
  • 49
  • 81
AnalystMan
  • 13
  • 2
  • how do you calculate a difference between the last yesterday time and the first today time? – Mik Aug 10 '22 at 12:20
  • We would need to add something to ignore if [Date/Time]-Prev Pick was over a certain threshold, like 4 hours or so; as you can see in the second screenshot, [Date/Time] includes both date and time thanks to the serial number. – AnalystMan Aug 10 '22 at 16:46
  • I added the 3rd solution the the answer. Would be nice to have a feedback. As for me I'd prefer a measure for visual then calculated column, because you do not need to calculate all cases, but only a several selected cases and, I believe it's much faster. – Mik Aug 12 '22 at 07:48

1 Answers1

0

This works, but takes a lot of time to calculate your 1mln rows. I'll think about a better solution.

Solution 1:

Prevpick = 
VAR currentTime=[Date/Time]
VAR tbl = CALCULATETABLE(VALUES('Lucas Archive'[Date/Time]),ALLEXCEPT('Lucas Archive',Lucas Archive[Picker],Lucas Archive[Pick Day]))
VAR tbl2 = Filter(tbl,[Date/Time]<currentTime)
RETURN maxx(tbl2,[Date/Time])

Solution 2 (much faster):

Rank = -- It's the first column
VAR currentTime=[Date/Time]
VAR RankDayPicker = 
         CALCULATE(
              RANKX('Lucas Archive','Lucas Archive'[Date/Time],{currentTime},ASC,Dense)
             ,ALLEXCEPT('Lucas Archive','Lucas Archive'[Picker],'Lucas Archive'[Pick Day])
         )
RETURN RankDayPicker

PrevPick = -- It's The second column
VAR currentRank = [Rank]
RETURN 
    CALCULATE(
         SELECTEDVALUE('Lucas Archive'[Date/Time])
         ,ALLEXCEPT('Lucas Archive','Lucas Archive'[Picker],'Lucas Archive'[Pick Day])
         ,'Lucas Archive'[Rank]=currentRank-1)

Solution 3 : -- fast enough, but takes some time to calculate ~ 1 mln rows

Var currentTime=[Date/Time]
VAR timeValues=
    CALCULATETABLE(
       TOPN(
        1
        ,VALUES('Lucas Archive'[Date/Time])
        ,SWITCH(
            TRUE()
            ,[Date/Time]=currentTime,currentTime
            ,[Date/Time]<currentTime,currentTime-[Date/Time]
            ,100000
        )
        ,asc)
        ,ALLEXCEPT('Lucas Archive','Lucas Archive'[Picker],'Lucas Archive'[Pick Day])
    )

RETURN MAXX(timeValues,[Date/Time])
Mik
  • 2,099
  • 1
  • 5
  • 19