I have a table that contains 75 million rows of data and the dax formula has out of memory

[Pandadev]

Hi have this formula in Dax , where I want to get just all the rows of data that has 7 or more days between each delivery

7 Plus Days =

CALCULATE(

max('del_Feed'[del_date_utc]),

FILTER(

'del_Feed',

'del_Feed'[del_id] = EARLIER('del_Feed'[del_id]) &&

'del_Feed'[del_date_utc] < EARLIER('del_Feed'[del_date_utc])

)

)

It works for data up to 3 million rows using dax , but my data table contains 57 million rows , and returns an out of memory error. Is it possible to do this in the query editor please.

[PhilipTreacy]

Hi @Pandadev 

Add a Custom Column in Power Query which is the result of one delivery date minus the other.  Then filter out the rows you don't need.

If you give us some idea of what the data looks like (column structure) I can write the code.

Thanks

Phil

[Pandadev]

del_date_utc   ,  del_date_time             , del_id     , date diff prev date

31/12/2019       31/12/2019 02:30:00    -19675       0

31/12/2019       31/12/2019 04:30:00    -19675       0

31/12/2019       31/12/2019 06:30:00    -19675       0

30/12/2019       30/12/2019 02:30:00    -19675       2

30/12/2019       30/12/2019 04:30:00    -19675       2

30/12/2019       30/12/2019 06:30:00    -19675       2

28/12/2019       28/12/2019 02:30:00    -19675       10

28/12/2019       28/12/2019 04:30:00    -19675       10

28/12/2019       28/12/2019 06:30:00    -19675       10

18/12/2019       18/12/2019 02:30:00    -19675       1

18/12/2019       18/12/2019 04:30:00    -19675       1

18/12/2019       18/12/2019 06:30:00    -19675       1

17/12/2019       17/12/2019 02:30:00    -19675       1

17/12/2019       17/12/2019 04:30:00    -19675       1

17/12/2019       17/12/2019 06:30:00    -19675       1

31/12/2019       31/12/2019 02:30:00    -10001       0

31/12/2019       31/12/2019 04:30:00    -10001      0

31/12/2019       31/12/2019 06:30:00    --10001      0

30/12/2019       30/12/2019 02:30:00    -10001       8

30/12/2019       30/12/2019 04:30:00    -10001       8

30/12/2019       30/12/2019 06:30:00    -10001       8

22/12/2019       22/12/2019 02:30:00    -10001      14

22/12/2019       22/12/2019 04:30:00    -10001      14

22/12/2019       22/12/2019 06:30:00    -10001      14

08/12/2019       08/12/2019 02:30:00    -19675       1

08/12/2019       08/12/2019 04:30:00    -10001      1

08/12/2019       08/12/2019 06:30:00    -10001      1

07/12/2019       07/12/2019 02:30:00    -10001      1

07/12/2019       07/12/2019 04:30:00    -10001      1

07/12/2019       07/12/2019 06:30:00    -10001       1

I would like to keep the earliest date time with a date diff of 7 or more  , so from the above , i would like to keep the following rows

28/12/2019       28/12/2019 02:30:00    -19675       10

30/12/2019       30/12/2019 02:30:00    -10001       8

22/12/2019       22/12/2019 02:30:00    -10001      14

[Fowmy]

@Pandadev 

Can you try this measure?

7 Plus Days =

CALCULATE(
    max('del_Feed'[del_date_utc]),
    FILTER(
        SUMMARIZE('del_Feed','del_Feed'[del_id],'del_Feed'[del_date_utc]) 
        'del_Feed'[del_id] = EARLIER('del_Feed'[del_id]) &&
        'del_Feed'[del_date_utc] < EARLIER('del_Feed'[del_date_utc])
    )
)

Check your data model for potential performance improvements.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Pandadev]

Thanks I tried adding the measure , but get parameter not correct type