Search code examples
powerbidaxmaxifs

How can I replicate this MAXIFS formula in DAX?

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
)
Solution

  • 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])