Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

[Dpyr]

Hi,

I get the following error when trying to refresh my dataset either using "Refresh now" or scheduled refresh: 

I removed as many tables and columns it was possible and create calculations in the database instead of the query editor.

For some days the error dissapeared.

No change was made to the power bi file, neither in the power bi service, but the error re-apeared.

Can somebody give me an appropriate solution?

I also need to ensure if "Try simplifying or reducing the number of queries" refers to the number of the tables are imported to the power bi, or to the steps applied in the query editor.

Thanks a lot,

Dimitra

[lbendlin]

Needs more details - is this on Premium or on shared capacity?  If Premium - did your tenant admin maybe modify the memory allocation?

In any case you will want to use DAX Studio to re-evaluate all your calculated columns, and Power Query Diagnostics to check all the merges and other expensive steps.

[Dpyr]

Thanks for your answer and I'm sorry for my late reply. 

It is on shared capacity.

I used the DAX Studio and modified each expensive measure was possible.

Also redused query steps to the minimum. I kept just "Changed type" and "Trim text" steps.

There is one table where I use merge steps, which table is similar to other reports in the same workspace without a problem.

This table contains transaction types. It has not a lot of rows and columns. The merge steps applied are about 2 columns needed for the relationships in the model.

Any other step needed, was made to the data source, before importing data to power bi. 

The only data source used is an Azure database. 

The file contains only columns and rows needed. 

We started with one file holding all reports (sales, logistics...), but when this error first occured, file was splited to multiple files. The file keeps giving this error is holding 4 pages from the original file and conains the sales data.

There are 15 tables from database (which are made in views so to keep only needed columns), one calendar table (time intelligence is turned off to avoid multiple virtual hierarchy date tables) and 4 measure tables holding a total of 56 measures. Most of them use SUMX() trying to avoid multiple CALCULATE() functions, that caused to expensive measures before using DAX Studio. 

When original file splited the error dissapeared for 20 days. No change was made, but the error appeared again. I followed your suggestion and used DAX Studio, made changes and the error got fixed again from 15 days, but again no change was made during these 15 days.

I don't even understand what's the main cause of this error so that I will be able to avoid it in the future.

I hope you suggest me a solution that will totally fix this problem.

[lbendlin]

Do the trimming in the view.

See if you can avoid the merge and use the data model instead.

Reduce the number of measures. Consider switching to Calculation Groups.