Forum Discussion
Need help optimizing Filters in Calculation
Hi Adak,
could you try this column code out, unfortunately I can't test it myself but it should use a lot less memory and do what you want:
Total Qty =
SUMX (
FILTER (
FILTER (
Inventtrans;
Inventtrans[ItemWithOrderID] = EARLIER ( Inventtrans[ItemWithOrderID] )
);
Inventtrans[TransactionDate] <= EARLIER ( Inventtrans[TransactionDate] )
);
Inventtrans[Qty]
)Sorry for interrupting if your problem has already been fixed by Xiaoxin Sheng.
Best regards
Oxenskiold.
Hi Adak,
could you try this column code out, unfortunately I can't test it myself but it should use a lot less memory and do what you want:
Total Qty =
SUMX (
FILTER (
FILTER (
Inventtrans;
Inventtrans[ItemWithOrderID] = EARLIER ( Inventtrans[ItemWithOrderID] )
);
Inventtrans[TransactionDate] <= EARLIER ( Inventtrans[TransactionDate] )
);
Inventtrans[Qty]
)
Sorry for interrupting if your problem has already been fixed by Xiaoxin Sheng.
Best regards
Oxenskiold.
Hi Oxenskiold
Thanks for your input, with this calculation structure all table calculated quite quick.
It would be nice to understand, how does this formula evaluates differently compared to what I wrote, it seems I don't understand evaluation process to well in this case.
Hi Adak,
actually I think you understand the evaluation process quite well. With a tiny modification to your DAX code you would probably have brought it home.
Modified DAX code:
Total Qty =
CALCULATE (
SUM ( Inventtrans[Qty] );
FILTER (
ALL ( Inventtrans[ItemWithOrderID] );
Inventtrans[ItemWithOrderID] = EARLIER ( Inventtrans[ItemWithOrderID] )
);
FILTER (
ALL ( Inventtrans[TransactionDate] );
Inventtrans[TransactionDate] <= EARLIER ( Inventtrans[TransactionDate] )
)
)
I have changed the 'Inventtrans' expression in the second filter condition to ALL ( Inventtrans[TransactionDate] ). That way the DAX engine doesn't have to materialize the entire 'Inventtrans' table in memory which accounts for the out of memory condition.
The above code is syntactically equivalent to:
Total Qty =
CALCULATE (
SUM ( Inventtrans[Qty] );
Inventtrans[ItemWithOrderID] = EARLIER ( Inventtrans[ItemWithOrderID] );
Inventtrans[TransactionDate] <= EARLIER ( Inventtrans[TransactionDate] )
)
My code tries to optimize the above DAX by first finding all the rows with the same ' ItemWithOrderID' as the current row and then among those rows finding those that are less than or equal to the ' TransactionDate' field of the current row. Since there are far less distinct ' ItemWithOrderID' values than Inventtrans[TransactionDate] values that should speed the processing up and keep the memory use down without any materialization. This also means that the order of the conditions in my code is important. If you reverse them more time will be spent by the DAX engine crunching the data set.
The DAX engine is optimized for every released version, it would therefore be interesting to see if what I write still stands. (I know it used to be so). If you have the time and opportunity to test your original DAX code with my small modification on a COPY of your production data I would love to hear if you can see any significant difference between the code I posted at first and your original code (with my modification).
Best regards
Oxenskiold