Optimize DAX memory

Question

Im currenly having an issue with VAR data. When i try to run this with all that dax measurements i run out of memory. As you can see i only have 7 measurments in this because i couldnt figure out which measurement was causing me the issue. Unfortunately, its this one. Does anyone know of a diffrent way of attiving this? basically i want to subtract the previous dimension with the current dimension. these are based on time stamps. so in excell it would be =ABS ( A2-A3). Something like that but because power bi is not set up like this i had to make another equation basically to get the dates in order (which is the second picture). I cant imagine this is that big for power bi which leads me to believe that its how i wrote the DAX measurment.

Any help would be appreciated

greg_deckler · Answer

Sample data.&nbsp;Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

anonymous · Answer

Unfortunately, I cant post the actually data due to privacy issues nor can i give you the actual file that im working with.&nbsp; I undertand how that would make it eaiser to answer my question. Apologize for the little information to go off of.

anonymous · Answer

Could you clearly explain what you are trying to do in this measure? I'd like to know, for instance, why you are using ALLSELECTED and why there is no Date/Calendar table in your design.

Also, please go to www.daxformatter.com and format the measure properly so that people can read it.

Thanks.

Best
D

anonymous · Answer

Moving Range =IF (&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK ( 'eOVL Data - MySQL'[Earlier Time1] ),&nbsp;&nbsp;&nbsp;&nbsp;BLANK (),&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VAR EarlierTime =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX ( 'eOVL Data - MySQL'[TimeStamp] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'eOVL Data - MySQL'[TimeStamp] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'eOVL Data - MySQL'[TimeStamp]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt; SELECTEDVALUE ( 'eOVL Data - MySQL'[TimeStamp] )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VAR EarlierMeasureValue =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM ( 'eOVL Data - MySQL'[Measured Value] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'eOVL Data - MySQL'[TimeStamp] = EarlierTime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ABS ( EarlierMeasureValue - SUM ( 'eOVL Data - MySQL'[Measured Value] ) )&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;This should now be a proper dax equation. All selected is becasue i have slicers that need to be used. I know this will slow down Power bi also but i need the. Basically there is thousands of part numbers that this is looking through and the time in which they were measured. Its let putting them in order from oldest to newest. Its then getting the diffrence between measurments. If your&nbsp;familiar with stats, its a moving range chart. The equations that i use work when the data that is pulled isnt huge but when I pull from our SQL database instead of an excel workbook it takes 20 min.

anonymous · Answer

Oh, and there is a date table you just couldnt see it in the Snip-it that was sent.

Forum Discussion

Optimize DAX memory

5 Replies

Related content

DAX performance optimization

Dax code optimization

Optimize memory

DAX query with SUMMARIZECOLUMNS - Optimization

DAX optimization problem