Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate difference between consecutive rows - memory error

Hello,

 

I am trying to calculate the difference between chronological rows in my table but I'm getting a memory error whenever I try to execute the DAX command. The table (AAP - exits) is 45000 rows and the other table referenced (AAP) is 80000. AAP - exits is just a filtered version of AAP but if I change the references to all be to AAP - exits I get a circular reference error. My machine has 8 GB RAM.

 

Column 2 = CALCULATE(MAX(AAP[Timestamp]),AAP[IMO]=EARLIER('AAP - exits'[IMO]),AAP[Timestamp]<EARLIER('AAP - exits'[Timestamp]))
 
I've tried other variations of the formula, including DATESBETWEEN instead of the inequality because I read on here that can be more efficient. But EARLIER doesn't work within DATESBETWEEN and I cannot find another way to refer to the current row. 
 
Would be open to solutions in DAX or Power Query. 
 
Thanks for your time.
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to try this code to create a calcualted column,

    Column 2 =
CALCULATE (
    MAX ( AAP[Timestamp] ),
    FILTER (
        AAP,
        AAP[IMO] = EARLIER ( 'AAP - exits'[IMO] )
            && AAP[Timestamp] < EARLIER ( 'AAP - exits'[Timestamp] )
    )
)

    If you code will still return memory error, please refer to this offical blog to optmize your data model.

     

    Best Regards,
    Rico Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    I suggest you to try this code to create a calcualted column,

    Column 2 =
CALCULATE (
    MAX ( AAP[Timestamp] ),
    FILTER (
        AAP,
        AAP[IMO] = EARLIER ( 'AAP - exits'[IMO] )
            && AAP[Timestamp] < EARLIER ( 'AAP - exits'[Timestamp] )
    )
)

    If you code will still return memory error, please refer to this offical blog to optmize your data model.

     

    Best Regards,
    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you, this worked!