Forum Discussion
Helper IIIReport refresh and calculated column changes takes long time to apply
We have a report which is approx 600 MB big
It has quite a lot of calculated columns and some calculated columns depend on other calculated columns
This report was refreshing fine till couple of weeks back but not it takes almost 15 min on working on stage if I make any changes to any calculated columns and sometimes on measures as well
if I refresh report there also it takes much longer time even when all rows are loaded, so it takes some 5 min to load all rows and then stays there for another 15-20 minutes
How can we improve this?
We are in process of migrating all calculated columns to source itself, however initials testings were not that satisfactory.
Convert as many calculated columns to custom columns in Power Query. Depending on your data source, you may get much faster results of the calculations are sent back to a relational database, like SQL.
Data coming in from Power Query also gets treated as imported data in the DAX model, so it is compressed and needs no additional RAM or CPU to process, as it is pre-processed before it is imported.
Calculated columns should be used sparingly and in specific circumstances. It isn't impossible, but it is rare to get better peformance from them than from custom columns in Power Query.
Hi dilkushpatel ,
check this videos...
VertiPaq Analyzer
https://www.youtube.com/watch?v=1A7gFFvlRFI
https://www.youtube.com/watch?v=frTcFzdxHH4
Debugging a slow Power BI report
https://www.youtube.com/watch?v=eABg872TAJU
...and this blog post.
https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Please check if there're any useless DAX formula in your report. Make sure each column will be used, remove the useless columns. For more details about how to improve the query in power bi, I would suggest you refer to doc below:
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- edhans
Community Champion
Convert as many calculated columns to custom columns in Power Query. Depending on your data source, you may get much faster results of the calculations are sent back to a relational database, like SQL.
Data coming in from Power Query also gets treated as imported data in the DAX model, so it is compressed and needs no additional RAM or CPU to process, as it is pre-processed before it is imported.
Calculated columns should be used sparingly and in specific circumstances. It isn't impossible, but it is rare to get better peformance from them than from custom columns in Power Query.
- mwegener
Most Valuable Professional
Hi dilkushpatel ,
check this videos...
VertiPaq Analyzer
https://www.youtube.com/watch?v=1A7gFFvlRFI
https://www.youtube.com/watch?v=frTcFzdxHH4
Debugging a slow Power BI report
https://www.youtube.com/watch?v=eABg872TAJU
...and this blog post.
https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
v-yuta-msftCommunity Support
Please check if there're any useless DAX formula in your report. Make sure each column will be used, remove the useless columns. For more details about how to improve the query in power bi, I would suggest you refer to doc below:
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- dilkushpatel
We are in process of doing everything mentioned here
i.e
1) remove extra calculated columns which are not used anywhere
2) move what all we can to data lake analytics