Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mixednuts
Helper II
Helper II

Editing my dataset in the desktop takes a long time

Hi everyone,

 

I have built a large data set to act as a published data model for other dasboards and reports to draw on using the PowerBI Service.

 

This process works very well for our users, however editing this model now takes an incredibly long time.

 

I am the sole user working with the latest PowerBI desktop client on a virtual machine with an allocated 64gig of memory and 2ghz of CPU.

 

However when I try to add a column or measure using DAX through the desktop client it can take over 3 minutes before it prompts me to enter the DAX code. After editing it can take a further 3 minutes from when I press ENTER before it will be ready for the next action. Hopefully without syntax or coding mistakes.

 

Simply changing a field format can take several minutes to complete.

 

My model is large with 60+ tables  totalling 400+ columns so I can understand it might be a little burdened...

 

The Dataset is in its own PBIX file (>455mb) and uses import queries from SQLServer & Excel sources to build its data model.

 

Once loaded and published it works well, however editing it is becoming a  huge exercise in patience and delay.

 

Any advice on ways I could hasten the editing process (without breaking the model up)?

 

Thanks!

1 ACCEPTED SOLUTION

@Mixednuts,

In my opinion, this issue could be due to that if you have complex data model and have created multiple measures and calculated columns.

There is a discussion in the following similar thread, the community member works around this issue by cleaning up his data model and creating data warehouse.
http://community.powerbi.com/t5/Desktop/The-PBI-file-gets-extremely-slow-after-creating-a-few-calcul...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

You can use this method: http://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

 

It filters your source data via one central switch. So reduce the amount of data (sensibly) for the design process and turn off the switch/filter before deploying to production.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thank you Imke.

 

Just to clarify though, does this apply to the DAX editing as well as the Query Edit or Refresh processes?

 

My data model is already fully loaded with data when I begin editing the calculated columns and measures.

 

Certainly it may help with the Data Refresh which on the desk top can take 20+ minutes, but  I am unclear as to how this solution will help otherwise?

 

Appreciate the rapid response, though!

Sorry, Imke. On re-reading, I now see your suggestion is to reduce the volume of data in the data model.

 

I aleady have a built-in limit on the number of years worth of data my model imports so as a test I reduced this down to 15% of the original volume.

 

This reduced the memory requirement for the model but there was no significant change in delays editing the DAX.

 

Thanks!

 

 

 

 

 

 

@Mixednuts,

In my opinion, this issue could be due to that if you have complex data model and have created multiple measures and calculated columns.

There is a discussion in the following similar thread, the community member works around this issue by cleaning up his data model and creating data warehouse.
http://community.powerbi.com/t5/Desktop/The-PBI-file-gets-extremely-slow-after-creating-a-few-calcul...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Lydia.

 

I suspect you are correct.

 

Thankfully we are in the process of moving the sources (including calculated columns) to our SQL Server based data warehouse so I will anticipate some improvement when this is complete.

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.