Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Services

[S_JO21rnr]

Hi All,

I am new to Power BI.
For one of my visuals, In Power BI desktop, I am getting an error as “There's Not Enough Memory To Complete This Operation” and
In Power BI Services, I am getting an error as "visual has exceeded the available resources".

I have 8 GB of RAM and the query that’s running this visual has around 9 million records and I am using couple of DAX Measures in this visual.

I did some research and I have found out that “Power BI services has a limit of 1 GB for any queries that it tries to run” and optimizing my measures might help to resolve the issue.
I have used 3 measures in my visual “fAvgPerMonth”, “fAvgForLast6Month” and “fPercentageChange”.

Could these measures be tuned ?

#Measure 1 
fAvgPerMonth = CALCULATE( AVERAGEX (‘Table’, [fValues] ),
			 DATESINPERIOD(‘Table’ [Period],MAX(‘Table’ [Period]),-30,DAY))

#Measure 2
fAvgForLast6Month = CALCULATE( AVERAGEX (‘Table’, [fValues] ),
			 DATESINPERIOD(‘Table’ [Period],MAX(‘Table’ [Period]),-6,MONTH))

# fValues is another measure used within the above 2 main measures
fValues = IF(HASONEVALUE(ParamMetric[Metric])  && ISFILTERED(ParamMetric[Metric]), 
               SWITCH (VALUES(ParamMetric[Metric]),
                    {"Net Sales"}, SUM(‘Table’[Net Sales]),
                    {"Redemptions"}, SUM(' Table’[Redemptions]),
                    {"Gross Sales"},SUM('Table’ [Gross Sales]),
                    {"Assets”}, SUM('Table’ [Gross Sales]),blank()))

#ParamMetric is a calculated table 
ParamMetric = {("Assets"),("Gross Sales"),("Net Sales"),("Redemptions")}
"Net Sales", "Redemptions", "Gross Sales" and "Assets” are 4 different columns in the table which are used as slicer with the help of the above calculated table to filter fValues.

#Measure 3 
fPercentageChange = 
            IF(    ([Percentage] >= min(ParamPercentage[PercentChange]))
                        &&
                   ([Percentage] <= max(ParamPercentage[PercentChange])) , 
                   [Percentage],blank())

#where:
Percentage = Divide( ([AvgPerMonth] - [AvgForLast6Month]), [AvgForLast6Month] )

#And ParamPercentage is a calculated table 
ParamPercentage = GENERATESERIES(-4,4,0.01)

#ParamPercentage[PercentChange] is used as a slicer ( Percentage range) to filter the fPercentageChange Values.

Where should I begin and How to resolve this issue?
Any feedback is appreciated.

[v-yulgu-msft]

Hi @S_JO21rnr,

Here are some links about performance Optimization and tips about reducing file size for your reference:

Topic: Optimization

Possible SWITCH Optimization in DAX

power bi performance tips and techniques

Best regards,

Yuliana Gu

[GilbertQ · Accepted solution]

Hi there

One thing you could do to get an average is take the SUM and DIVIDE it by the Count

My Average = DIVIDE(SUM('Table'[fValues]),COUNTROWS('Table'))

And then use that as a basis for your other calculations.

[S_JO21rnr]

Thanks a ton 🙂
This definitely helped to optimize DAX measures and they are running smoothly. Also visuals are working perfect both in Power BI Desktop & Services.

[GilbertQ]

Thanks for letting us know.

[Anonymous]

@S_JO21rnr While others may provide a more detailed answer specific to the calculations, here is a couple notes in the limited time I have to respond. First, AVERAGEX is a row by row calculation, this is going to take much longer. Explore just using AVERAGE and filtering the time period to the one you want. Variables will help you do this.

You are creating calculated tables (In Memory) from your base table - this is expensive.

The 1 GB limit only relates to the PBIX file size. Your overall file cannot be larger than that in order to publish to the Service.

The error you are getting is relative to your machine, it is maxing out your local machine memory. 8GB memory is really small if your using large datasets in power bi. The engine uses an all in memory technology that requires more when you scale up in data size.