cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnthonyDF
Helper III
Helper III

Super slow powerBI report when creating a measure or calculated column

Hi all, 

I have read a few topics about the same issue here but none found the solution so far. Maybe some people here are new with new ideas... let's give it a try.

 

First of all, I'm not talking about DAX queries. They seem to be well optimized so far. I'm talking about the model and the time it takes to create a new measure, new column, change the data type etc... For me it takes about 5min for each step. Today it's almost impossible to work on my model.

Here are some specs of my model:
size: 170Mb, approx. 5 fact tables with more than 5 Millions rows each, one table with more than 10 Millions rows. A lot of dim tables (1 to many with one way filtering). They are all connected via a calendar table created in powerquery not a CALENDAR() function. A lot of measures. Almost no calculated colums, except for ragged hierarchy purpose on only one fact_table.

 

I tried almost everything I could:
No calculated columns
Relationship based on integer, not string
Reduced cardinality as much as possible
Reduced table size as much as possible

Do you have any idea? Is the number of measures the reason for bad report performance? two way filtering maybe? 

Help much appreciated.

11 REPLIES 11
AnthonyDF
Helper III
Helper III

Hi all,
Is there a correlation between the report performance and the home table of a measure?
Said in an other way: Is it better to associate a measure to an empty table, a dim table or a fact table?

This would be a question to ask somebody on the PBI team. Try to ask on their blogs. Or ask people like Marco Russo, Alberto Ferrari, Matt Allington, Casper de Jonge...
daxer
Solution Sage
Solution Sage

You can try to locate the DAX Gurus on their website www.sqlbi.com. Matt has his own website and Casper has his own blog. Just google for it...

Hi @daxer 
Is there a specific channel or forum where I can contact them?
Thanks,

sirlanceohlott
Advocate II
Advocate II

Hey @AnthonyDF ,

 

How many pages, visuals per page & measures do you think you've got in this report?

 

I find these things usually have a bearing on the report itself when editing. 

 

Also, not sure the specs of your workstation, that could have an effect as well.

Hi @sirlanceohlott 
Approximatly 40 pages and 500 measures in the report. Zero calculated columns, removed all of them for a test.
DO you thin it's linked to the count of measures?

Hi @AnthonyDF,

 

How does the model perform if you filter your dataset to a smaller set?

you could always develop with a subset of data and then remove the filters when published.

 

see this link from Chris Webb

https://blog.crossjoin.co.uk/2020/05/24/limit-the-amount-of-data-you-work-with-in-power-bi-desktop-u...

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!





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

Proud to be a Super User!




@richbenmintz 

I tried to reduce the history of the Fact tables to one year instead of 25 years and it has no impact at all. Seems like th eissue is from something else.

AnthonyDF
Helper III
Helper III

I tried to removed all the reamining Calculated columns and it's not improving anything.

nvprasad
Resolver V
Resolver V

Hi,

 

My understanding from your question is there could not be any issue with PBIX file in terms of size & number of rows. Kindly check the CPU & Memory utilization of your system. If your use more memory and CPU PBIX work slow. 

 

If the above one is not an issue means, kindly re-install PowerBI and try.

 

 

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

Hi @nvprasad , thanks for your reply.

Already checked this, CPU stable at 35% and Memory stable at 54%. Took this measure when "working on it" is displayed.

My concern is not really about the number of rows but more about the number of measures in a report. Could this lead to a poor performance of the report?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.