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?
SUM ( Table1[value] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] < MIN ( 'Date'[Date] ) )
SUM ( Table1[value] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
Loading only if one month selected
I'd like to suggest you refer to the following guidence to improve Power BI performance by optimizing DAX.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@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.
@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 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
@Analitika , See if this blog on the same topic can help
inefficient DAX calculations can cause a query to try to grab a lot of memory
so how to write efficient calculation for that purpose?
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.