cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Tips on Peformance - any to give

I need to give someone advice on improving performance on Power BI desktop.  This is what comes to mind, is there anything you would add to this?

 

1. Only using data that you need rather than bringing all the data in

2.  Applying appropriate data types

3. Cleaning the data as much as you can removing anything that is redundant or repeated.

4. If you are using an imported model, conforming the data as much as possible to get as much as compression as you can

5.  Having a minimum of 8gb memory, 64bit and i5 machine.

6. Clearing the cache in Power BI

7. If you pulling from SQL make sure you are using query folding.

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Tips on Peformance - any to give

Two other tips (mostly) unrelated to the relationship thing:

 

  1. If you can do a CALCULATE(..., FILTER(...)) instead of a SUMX/AVERAGEX/WHATEVERX, do it. And God help you if you nest a SUMX inside another SUMX. Iterator functions can slow you down, so only use them when they are the only/best solution to a problem, not just when they're the first idea you had.
  2. This is more about performance in the desktop editor than end user performance. The number of measures seems to matter, but only at the very high end. Model complexity also matters and really complicated models tend to also have lots of measures so I haven't figured out quite where the tipping point is. But I do know that if you have a couple hundred measures in a single file you may have to wait a few minutes every time you want to edit or add a formula.

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
9 REPLIES 9
Super User
Super User

Re: Tips on Peformance - any to give

I would add optimizing your data model relationships to prevent unnecessary cross-filtering.


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

Proud to be a Datanaut!


Super User
Super User

Re: Tips on Peformance - any to give

in other words leave it on single directon vs both direction?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User
Super User

Re: Tips on Peformance - any to give

Leave it on single unless you need both. You can always use the CROSSFILTER function to force a "both" directionality. But also make sure that you only create active relationships where you have to, and try to avoid making relationships that require multi-table jumps to complete a measure.

 

Basically the more your relationship view looks like a simple printed circuitboard, and the less it looks like a large plate of spaghetti, the better.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Super User
Super User

Re: Tips on Peformance - any to give

Two other tips (mostly) unrelated to the relationship thing:

 

  1. If you can do a CALCULATE(..., FILTER(...)) instead of a SUMX/AVERAGEX/WHATEVERX, do it. And God help you if you nest a SUMX inside another SUMX. Iterator functions can slow you down, so only use them when they are the only/best solution to a problem, not just when they're the first idea you had.
  2. This is more about performance in the desktop editor than end user performance. The number of measures seems to matter, but only at the very high end. Model complexity also matters and really complicated models tend to also have lots of measures so I haven't figured out quite where the tipping point is. But I do know that if you have a couple hundred measures in a single file you may have to wait a few minutes every time you want to edit or add a formula.

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Super User
Super User

Re: Tips on Peformance - any to give

thanks those are great.


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User
Super User

Re: Tips on Peformance - any to give

Correct, if appropriate obviously.


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

Proud to be a Datanaut!


Super User
Super User

Re: Tips on Peformance - any to give

Good topic :-)

 

With regards to load-time, disabling background refresh and appropriate buffering will also improve performance significantly: https://blog.crossjoin.co.uk/2016/12/05/power-query-power-bi-and-the-allow-data-preview-to-download-...

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Tips on Peformance - any to give

Also if you're working with large amounts of data, you can save precious waiting time during your design-process by using this technique: http://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
Super User
Super User

Re: Tips on Peformance - any to give

those are great, thank you!


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!