Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Optimize DAX memory

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 whi...
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Moving Range =
        IF (
            ISBLANK ( 'eOVL Data - MySQL'[Earlier Time1] ),
            BLANK (),
            (
                VAR EarlierTime =
                    CALCULATE (
                        MAX ( 'eOVL Data - MySQL'[TimeStamp] ),
                        FILTER (
                            ALLSELECTED ( 'eOVL Data - MySQL'[TimeStamp] ),
                            'eOVL Data - MySQL'[TimeStamp]
                                < SELECTEDVALUE ( 'eOVL Data - MySQL'[TimeStamp] )
                        )
                    )
                VAR EarlierMeasureValue =
                    CALCULATE (
                        SUM ( 'eOVL Data - MySQL'[Measured Value] ),
                        'eOVL Data - MySQL'[TimeStamp] = EarlierTime
                    )
                RETURN
                    ABS ( EarlierMeasureValue - SUM ( 'eOVL Data - MySQL'[Measured Value] ) )
            )
        )

         

        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 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.