cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Analitika
Post Prodigy
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] ) )
)

 

Analitika_0-1609234285042.png

Loading only if one month selected

 

 

 
 
 
 
 

 

7 REPLIES 7
v-alq-msft
Community Support
Community Support

Hi, @Analitika 

 

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

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.

Greg_Deckler
Super User IV
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. 

Unsure if it will help, but perhaps try using a separate date table for your slicer that is not related to your main date table. You would then be able to ditch your ALL statement. Also, if you Date table is related to your fact table, you could also try filtering the table directly versus going through the Date table once you have a separate disconnected date table for your slicer. My concern with the ALL statement is that it looks like you are bringing the entirety of your fact table into context and I can't imagine that is optimal. Also, I would definitely turn off Auto date/time and the reason is that this blows up the size of your date table to huge proportions, usually unnecessarily. Then, using ALL to bring in your entire date table you have to load this behemonth into memory. Getting rid of Auto date/time should drastically reduce the size of your date table in your data model and then it may fit into memory.

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
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?


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 

 

I have aa slicer 

Analitika_0-1609252983141.png

 

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

 

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

so how to write efficient calculation for that purpose?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors