Optimizing a column calculation

[aaelansr]

Hi, I have a dataset thats has 11m rows and I am trying to add a column to run a sumX that will then be used in a different column but this calcualation keeps running out of memory. The calculation won't even run on a third of the dataset.

sumX (

            FILTER (

                ALL ( 'Master' ),

                Master[Observe] <= _CurrentYear

                    && Master[Observe] > _MinYear
                    && _Asset =Master[Building-Asset] 

            ),

            ([Amount to be Depreciated]*Master[Percentage]*Master[Period of Depreciation])/Master[Total Number of Months]

        ))

Are there ways to optimize or rewrite this calculation to reduce the memory usage and have it work?

Thank you!

[Greg_Deckler]

@aaelansr Try something like this:

VAR _Table = 
  SELECTCOLUMNS(
    FILTER( 
      'Master', 
      [Observe] <= _CurrentYear && [Observer] > _MinYear && [Building-Asset] = _Asset
    ),
    "Amount to be Depreciated", [Amount to be Depreciated],
    "Percentage", [Percentage],
    "Period of Depreciation", [Period of Depreciation],
    "Total Number of Months", [Total Number of Months]
  )
VAR _Return = 
  SUMX( 
    _Table, 
    DIVIDE( 
      [Amount to be Depreciated] * [Percentage] * [Period of Depreciation], 
      [Total Number of Months] 
    )
  )
RETURN
  _Return

Another trick you can do is to utilize SUMMARIZE. SUMMARIZE can make things crazy fast.

[aaelansr]

@Greg_Deckler Great! That worked beautifully and enabled me to process more than twice as many rows, but still didn't get me all the way. Can you help me with what this would look like using SUMMERIZE? Would SUMMERIZE also use up less memory?

Thank you!

ER