Out of memory

[DouweMeer]

 Table2 is a table with unique values of which I need the sum of sales. Table1 contains of 800k rows and Table2 of 130k. I can't slim down the selection of 130k, as it would require me to this over and over again till I have done it 4000 times. 

I'm trying to calculate the sum of sales, with a filter on product and an additional field (rank of product based on a parameter). Table1 is the sales table. Roughly it is a running total for a ranking based on something else than sales, with a filter on product id. 

I'm currently trying the following in Table2 as a custom column. 

calculate (

   sum ( 'table1'[USD Conversion] )

   , filter ( 'table1' , 'table1'[Rank 1] <= 'table2'[Rank 1] )

   , filter ( 'table1' , 'table1'[PRODUCT_ID] = 'table2'[Product_ID] )

   )

Sadly enough I receive the message 'not enough memory'. I'm thinking of creating a measure instead and perhaps exporting the results and re-entering them as a separate table later, or just create a new report.

So, I'm stuck with 2 questions. 

1. Does someone how I can pass the issue of 'not enough memory'. Doing the same trick in excel does hits the same error. 

2. What would be the best solution for my attempts? Willing to look out of the box to link table via access (if possible) and reimport the query from access to power bi. 

[v-chuncz-msft]

@DouweMeer,

Check if the DAX below helps.

SUMX (
    FILTER (
        'table1',
        'table1'[PRODUCT_ID] = 'table2'[Product_ID]
            && 'table1'[Rank 1] <= 'table2'[Rank 1]
    ),
    'table1'[USD Conversion]
)

[DouweMeer · Accepted solution]

@v-chuncz-msft

Sadly enough I can't test it anymore. I nicked someone else his pc with more memory and expanded the file. My 8GB laptop now can't even open the file anymore because it returns into the error 'out of memory' :). I will add your suggestion to my notes.

[zoloturu]

@DouweMeer,

Did you try the same calculation but with smaller data volumes? Is it correct one? (you can limit rows amount in PowerQuery) 

Please post some sample data for Table1 and Table2. If you can provide a PBIX file then it would be better.

Regards,
Ruslan

[DouweMeer]

@zoloturu

Yea, it is supposed to work. I've tested it and the final expression which will use these values given by the expression, gives the correct data. Now as I want to use it on the 'real data', it gives me problems. 

As I can't share the real data of course, hereby my test data with dummy values. Also, I can't remove any data by power query. 

'Raw data'Rank 2 is unique value for a custom tableAnd the expression used for running total