Calculated Column not enough memory on refresh

[Anonymous]

Hi,

I have some calculated columns that are working albeit very slow due to the row numbers. however when I go to refresh the report at the Apply Query Changes part it pulls the data from the SQL query but gives an error saying there is not enough memory and cancels the refresh. I have 16gb of memory.

Unfortunately as I use an excel spreadsheet for some of the data I cannot carry out the aggregations in SQL.

Below is one the calculated columns (they are all the same but just sum different columns)

AIPRollUp600 is a calculated table based on AIP Demand600.

WeekOffset is a past week number (e.g -1) and MaxAip (again a number such as -4) is the leadtime for the SKU.

What the calculation is doing is summing the all the demand for weeks greater than the lead but up to the weekoffdate date.

I have limited DAX knowledge and how Power Bi processes data and calculations but i have tried to split the data over multiple tables and then creating a union in the hope that it would have been able to process the calculation over smaller tables upon refresh but it didn't work. I also tried to add variables but it hasn't made much difference, then I tried using Filter for each criteria instead on && but that didn't seem to make much difference either.

DemandOverLT = 

VAR ROLLUPWKOFFSET = AIPRollUp600[WeekOffset]

VAR ROLLUPAIP = AIPRollUp600[MaxAip]

RETURN

 CALCULATE(SUM('AIP Demand600'[DEMAND]),FILTER('AIP Demand600','AIP Demand600'[SKU]=AIPRollUp600[SKU]&& ('AIP Demand600'[WeekOffset])<=(ROLLUPWKOFFSET) && 'AIP Demand600'[AIPWEEKOFFSET]=(ROLLUPAIP)))

If anyone could help or at least point me in the right direction to make the calculated columns more efficient that would be great.

Thanks,

Mark.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Possible causes:

1. "Filter" will create a row context. In other words, there will be a new virtual table. The conditions of "Filter" will be compared row by row in that table.

2. You can choose to use DQ connection mode instead of import mode, and preprocess data in advanced options when connecting data

Possible solutions.

1.

 CALCULATE(SUM('AIP Demand600'[DEMAND]),'AIP Demand600'[SKU]=AIPRollUp600[SKU]&& ('AIP Demand600'[WeekOffset])<=(ROLLUPWKOFFSET) && 'AIP Demand600'[AIPWEEKOFFSET]=(ROLLUPAIP))

You can also learn more about capturing-power-bi-queries-using-dax-studio.

2.Please refer to the similar thread:https://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables/td-p/23901 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.