Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Adak
Frequent Visitor

Need help optimizing Filters in Calculation

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.

 

Capture.PNG

 

 

Thanks in advance

1 ACCEPTED SOLUTION
Oxenskiold
Advocate I
Advocate I

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.

View solution in original post

7 REPLIES 7
Oxenskiold
Advocate I
Advocate I

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

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Hi @Adak,

 

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

 

Capture3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

 

Capture2.PNG

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.