Forum Discussion

crispybc's avatar
crispybc
Frequent Visitor
3 years ago

Lookup MAX value from other table using power query

I have 2 tables.

One is a table of all timestamps per ID where a change has occured. [Lookup]

The other is high resolution timestamp table (30 min intervals) between the start and end dates of an ID. [History]

 

I have a solution in DAX where I lookup  the last change in [Lookup] for each value of [History]

 

 

LAST CHANGE = CALCULATE(MAXX(LOOKUP, LOOKUP[UPDATED_AT]), FILTER(LOOKUP,LOOKUP[ID] = HISTORY[ID] && LOOKUP[UPDATED_AT] <= HISTORY[Time Active]))

 

 

 

 

This works fine, until the datset grows too large (around 50 mil rows) . Then there is not enough memory to perform the DAX  calculation. I'm wondering if I can achieve the same within power query which is maybe not so memory hungry

Need Help

1 Reply

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi crispybc  try using variable and see results

    If this post helps, please consider accept as solution to help other members find it more quickly.

    Test LAST CHANGE=
    VAR _filteredtable =
    FILTER(LOOKUP,LOOKUP[ID] = HISTORY[ID] && LOOKUP[UPDATED_AT] <= HISTORY[Time Active])
    VAR _result=
    CALCULATE(MAXX(LOOKUP, LOOKUP[UPDATED_AT]),
    _filteredtable
    )
    RETURN
    _result