Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
S_JO21rnr
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

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
v-yulgu-msft
Employee
Employee

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.

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

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.

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors