Report refresh and calculated column changes takes long time to apply

[dilkushpatel]

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.

[edhans · Accepted solution]

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 · Accepted solution]

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-msft · Accepted solution]

@dilkushpatel ,

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

[mwegener · Accepted solution]

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.

[edhans · Accepted solution]

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.