cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

I am a Power BI PRO license user.

I have a complex measure that in MICROSOFT Service results in a bad error due to RAM overflow:

Resource Governing: The memory used by the query exceeded the configured limit. The query or calculations referenced by it might be too memory intensive. Please consider simplifying the query or calculations. If the dataset is hosted on a dedicated capacity/server, you may also reach out to your capacity/server administrator to see if the per-query memory limit can be increased. Additional information: Requested 1048577KB, Limit 1048576KB.

 

You can download the pbix file from here.

The page where the visual is located is the 2nd visible (Istogramma Centri - Voci).

Could someone help me optimizing the measure or telling me how solve the issue maybe in a commercial way, if the measure results to be correct?

Thanks everyone!

Giovanni 🙂

P.S. @

11 REPLIES 11
Highlighted
Super User VII
Super User VII

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

@GiovanniBracci Hey I can assist, I downloaded your file and looked at the importo measure. I think it will be easier if you explain what you are trying to achieve.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Prodigy
Post Prodigy

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

@GiovanniBracci 

Have you Tried to increase the Data Load

Data Load.PNG

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Highlighted
Super User VII
Super User VII

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

@aj1973 @ but why I would do that? I don't see that is an issue. The real problem is in the DAX, it is not fully optimized and that's probably causing the issue.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Post Prodigy
Post Prodigy

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

Yes the DAX might need to be optimized, but the data load of the Power bi desktop and the RAM of his machine can also make the difference.

I increased the data load to 8M and my machine is 32 of RAM and the report loads smoothly.

 

However i still agree that the DAX needs optmization as the model and data is pretty much big.

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

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

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

Hi guys and thank You.

I think the same as @parry2k that the problem could come from the measure.
I don't know if increasing the value suggested by @aj1973... the error comes out when I try to consult the report in the Cloud Service Microsoft Server. How can that parameter in PBI Desktop could affect the behaviour of the Service?

@parry2k, i am trying to visualize the measure "importo" sliced by Months.

If you want better understand what does the measure is calculating I suggest you to read this explanation in an older post:
https://community.powerbi.com/t5/Desktop/Complex-calc-of-values-in-a-difficult-data-model-with-hiera...
Here you can find an earlier version of the report, where someone help me to make the right measure... and then the report with the measure done.


Let me know if you can understand more from this. My Skype id is giovannibracci if you wanna communicate faster and need more information.

Thank you both!!!!!

Highlighted
Super User VII
Super User VII

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

@aj1973 It loaded for me without any problem, without any change, still not sure if loading has something to do with the setting you mentioned, or it could be, don't know but one thing for sure, measure is not very well written,






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

I could also load the visual in PBI Desktop without errors. The problem come when I load the report into the service.

Highlighted
Post Prodigy
Post Prodigy

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

Yes i got the error trying to publish it to the service.

 

Resources Exceeded
This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.
Please try again later or contact support. If you contact support, please provide these details.
More details: Resource Governing: The memory used by the query exceeded the configured limit. The query or calculations referenced by it might be too memory intensive. Please consider simplifying the query or calculations. If the dataset is hosted on a dedicated capacity/server, you may also reach out to your capacity/server administrator to see if the per-query memory limit can be increased. Additional information: Requested 1048577KB, Limit 1048576KB.
Activity ID: cd769258-8b75-41c6-8c9b-e6c3bb9c8ddb
Correlation ID: d40aec16-b210-631a-639d-5b543120afff
Request ID: 53b79242-f169-ee17-029d-65d6bfe02c03
Time: Thu Jul 02 2020 14:03:10 GMT-0400 (Eastern Daylight Time)
Service version: 13.0.13736.165
Client version: 2006.4.01715-train
Cluster URI: https://wabi-canada-central-redirect.analysis.windows.net/

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Highlighted
Super User VII
Super User VII

Re: Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

@GiovanniBracci it surely happens when DAX is not performing and taking all the resources. We can connect but I'm busy for the next few days. If you explain what you are trying to achieve with that importo measure, it might be quicker.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors