cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DouweMeer Regular Visitor
Regular Visitor

Out of memory

 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. 
1 ACCEPTED SOLUTION

Accepted Solutions
DouweMeer Regular Visitor
Regular Visitor

Re: Out of memory

@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' Smiley Happy. I will add your suggestion to my notes.

4 REPLIES 4
zoloturu
Advisor

Re: Out of memory

@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 Regular Visitor
Regular Visitor

Re: Out of memory

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

 

image.png'Raw data'image.pngRank 2 is unique value for a custom tableimage.pngAnd the expression used for running total

 

Community Support Team
Community Support Team

Re: Out of memory

@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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DouweMeer Regular Visitor
Regular Visitor

Re: Out of memory

@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' Smiley Happy. I will add your suggestion to my notes.