cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Serv

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Serv

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
Microsoft
Microsoft

Re: Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Serv

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User IV
Super User IV

Re: Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Serv

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Highlighted
New Member

Re: Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Serv

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.

Highlighted
Super User IV
Super User IV

Re: Optimizing DAX measures to avoid "Exceeded available Resources" error in Power BI Serv

Thanks for letting us know.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Kudoed Authors