Memory issues in PowerBI -having issues not sure how to fix it?

[shreep1]

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

Urgently needed-please help 

In the query editor, I merged 2 tables in my existing PowerBI desktop file. After that, I created reference table out of those merged file and did some data modeling for 1-Many relationship. I previously filtered for only 1 month of data and made all those data modeling changes. Now, that my data modeling issues are fixed, I filtered for whole 1 year of data and walked down every steps under the Applied Steps. It took some time but I was able to get all the way to the bottom.

My dataset is in millions of rows! 

When I tried to apply those changes in the Query Editor-Close & Apply, my PowerBI started showing issues- Memory error- Memory Allocation failure- Try simplifying or reducing the number of queries. Error happened while loading the table.

PowerBI other time crashes.

Other errors it mentions are Evaluation ran out of memory and can’t continue.

 I have 77 GB available out of 235 GB available capacity in my computer. My computer is 16.0 GB installed RAM of which 15.7GB is usable. I have 64 bit operating system. x64-based processor.

I restarted my computer multiple times and re-running the changes in the Query Editor multiple times but it still shows memory issues.

Not sure, how to fix this running out of memory issue??

[shreep1]

@tamerj1 

You can't just drag & drop the column fields from the 2nd data table in a table/matrix for coressponding stations in the 1st data table unless these 2 tables are interlinked-connected through a common key. Here, DAX doesn't help and you need to do the PowerBI data modeling. 

Why is it so diffcult to resolve the memory issues in PowerBI. It hasn't caused issue for other people. 

Maybe I should clear cache under Data Cache Management Options and increase the Maximum allowed(MB) options to more than 4096? What other ideas are here to resolve this issue?

[tamerj1]

@shreep1 

Can you please provide at least a dummy sample of both tables?

[shreep1]

@tamerj1 

@amitchandak @olgad @Greg_Deckler 

Maybe change the maximum number of simultaneous evaluations from 16 to something else?

Maybe change the maximum memory used per simultaneous evaluation from 432 to something else?

[shreep1]

@tamerj1 

@amitchandak @olgad @Greg_Deckler 

I have to do data modeling by connecting common keys between 2-tables so that I get the relevant data from the 2nd table that is missing in the 1st table. When I tried to form a relationship between Table 1 & Table 2 no other relationship was valid except Many-Many. That Many-Many cardinality relationship produced wrong data in the table later on in the report due to which I have to redo the data modeling.   So, I created 3 different reference table for each of key I want from the merged Master table and connect those 3 different tables through 1-Many relationship in the master merged table. It solved my data modeling issues and I started getting the right data in the table visualization!

To fix this issue, I tried under Options and Settings of the PowerBI desktop file and cleared the cache and tried to reload the changed data in the Query Editor which did not worked either. The data got stuck at 2.5 million rows of loading. 

Not sure how to solve this issue?? Do I need to install a seperate DAX studio-different software and clear cache through there and rerun my local PowerBI desktop file to fix the memory issue?

Do I need to get the latest version of PowerBI desktop file Feb 2023 version?

What other things I can do to solve the memory issue that is preventing me from loading the data?

[tamerj1]

Hi @shreep1 

Apparently this merge is producing a huge number of rows that results in data size larger than the allocated RAM's. 
In most cases such kind of transformations won't be necessary while you can use DAX measures to perform extra calculations. 
In general DAX requires less memory than M when comparing power query transformations against power bi calculated tables or columns. 
can you please provide more details of what are trying to achieve?