Need help optimizing Filters in Calculation

[Adak]

Hello,

 I'm having an issue with performance with one of my Calculated Columns.

I have a table similar to example below with more than 6 million rows. I am trying to make a column that count the total Qty still in stock at a given time with this formula:

Total Qty = CALCULATE(SUM(Inventtrans[Qty]);FILTER(ALL(Inventtrans[ItemWithOrderID]);Inventtrans[ItemWithOrderID]=EARLIER(Inventtrans[ItemWithOrderID]));FILTER(Inventtrans;Inventtrans[TransactionDate]<=EARLIER(Inventtrans[TransactionDate])))

It does calculate correctly, but then I try to use this formula on production model it returns error that there is not enough memory to calculate. I suppose there should be a way to make this calculation more optimized.

Thanks in advance

[Oxenskiold · Accepted solution]

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.

[Adak]

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.

[Oxenskiold]

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

[v-shex-msft]

Hi @Adak,

For your dax formula, you can refer to below codes:

Measure :

Sum of total = 

var currentOrderID = MAX(Inventtrans[ItemWithOrderID])

var currentTransactionDate= MAX(Inventtrans[TransactionDate])

return

SUMX(FILTER(ALL(Inventtrans),AND(Inventtrans[ItemWithOrderID]=currentOrderID,

                Inventtrans[TransactionDate]<=currentTransactionDate)),Inventtrans[Qty])

In addition, you can use TopN function to choose the specify number of rows to a new table, then use above formula on new table to see if issue persists.

Regards,

Xiaoxin Sheng

[Adak]

Thanks @v-shex-msft

Such measure looks promising. One thing that gets error in my environment is:

var currentOrderID = MAX(Inventtrans[ItemWithOrderID])

Error says: The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.

I can't use Earlier here, in many cases I have used a bit more basic measures so maybe my understanding is a bit limited here, what should I use.

Would be grateful if you could explain how can I get rid of this error.

[v-shex-msft]

Hi @Adak,

You can convert "ItemWithOrderID" column to whole number format.

Regards,

Xiaoxin Sheng

[Adak]

Hi @v-shex-msft

I'm not sure I understood this correctly, that field already includes some letters so it doesn't allow me to convert that columnt to whole number

Error: Cannot convert value 'Hammer1' of type Text to type Integer.