cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mixednuts Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Editing my dataset in the desktop takes a long time

@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.
5 REPLIES 5
Super User
Super User

Re: Editing my dataset in the desktop takes a long time

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.

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




Mixednuts Frequent Visitor
Frequent Visitor

Re: Editing my dataset in the desktop takes a long time

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!

Mixednuts Frequent Visitor
Frequent Visitor

Re: Editing my dataset in the desktop takes a long time

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!

 

 

 

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: Editing my dataset in the desktop takes a long time

@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.
Mixednuts Frequent Visitor
Frequent Visitor

Re: Editing my dataset in the desktop takes a long time

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.