Forum Discussion

aaelansr's avatar
aaelansr
Frequent Visitor
2 years ago

Optimizing a column calculation

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!
Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      aaelansr
      Frequent Visitor
      2 years ago

      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