cancel
Showing results for
Did you mean:
Post Prodigy

DAX to Calculate Opening and Closing Stock, Exceed memory limit

I have a large data table in Data Model from 2000 year

Why simple measure exceed memory limits? And how to get rid of that?

Opening Stock=
CALCULATE (
SUM ( Table1[value] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] < MIN ( 'Date'[Date] ) )
)

Closing Stock=
CALCULATE (
SUM ( Table1[value] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

7 REPLIES 7
Community Support

Hi, @Analitika

I'd like to suggest you refer to the following guidence to improve Power BI performance by optimizing DAX.

Best Regards

Allan

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

Super User IV

@Analitika Couple thoughts on this. First, incredibly difficult to troubleshoot performance related DAX without the model so all I can do is kind of guess. I wrote a two part series on DAX performance optimization that might help.

---------------------------------------

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

@Analitika - Would have to understand the data model better to understand what is going on. Do you really need the ALL in there? With the ALL in there you are overriding context forcing the measure to load the entire date table into memory and then pare it back down versus just taking what it has in context and filtering it. How big is your Date table? Have you turned off Auto date/time?

---------------------------------------

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Post Prodigy

I have aa slicer

it filled with auto date/time  'Date'[Date]  but sliced to last 3 years to reduce slicer dates (just need show dates for last 3 years)

but MIN(Table1[date]) is 2000.01.01

So for Opening Stock i need to sum all in Table1[value] from 2000.01.01 until MIN(Date[Date]) selected by slicer

So for Closing Stock i need to sum all in Table1[value] from 2000.01.01 until MAX(Date[Date]) selected by slicer

Super User IV

@parry2k , @Greg_Deckler , what is best way to this in performance efficient way?

Proud to be a Super User!

Super User IV

@Analitika , See if this blog on the same topic can help

https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/

Proud to be a Super User!

Post Prodigy

inefficient DAX calculations can cause a query to try to grab a lot of memory

so how to write efficient calculation for that purpose?

Announcements