Power BI high memory consumption when creating measures

[ItAccounts]

When creating measures in Power BI it causes Power BI's memory consumption to increase dramatically. I have a fairly complex model with several hundred measures and after opening the file and creating a couple of simple measures, it causes my memory consumption to soar - I've seen Power BI use up to 11GB of memory (and start swapping) on a computer with 16GB of memory installed. The result is I need to enter a few measures, close and restart Power BI, and enter more measures until I have finished editing measures. The dataset comes from a web survey and there are several dozen questions in the questionnaire, and each page requires about 10 similar measures to be created for each question.

Is there any way to avoid this problem? Is there any way to edit measures outside Power BI and import them into Power BI, or edit them using an external tool? As far as I can tell, tools like DAX Studio can only view measures and run queries, but not edit the content of the PBIX file.

[Wilhelm]

Funny, I noticed the same thing happening. A small file of 2MB exploded into 11GB of RAM when opening Power BI Desktop (from App store) after what appeared the March update. I have been uninstalling and reinstalling the App with varying levels of success and improvement, but this morning it is back again in full force.

I should also mention that I have hardly any measures in this file and no visuals!

My only other idea is to install Power BI from the download and see what happens. Will post it here if I see a change in behaviour...

Here is a snap of the file on disk and what happens to RAM when I open it.

[v-shex-msft]

Hi @ItAccounts,

As d_gosbell said, normally this should more related to your data amount and calculate formulas that you apply on your data tables.

You can refer the following link to trace the dax formula(e.g. nested measure formula with iterator functions, multiple looping on whole table records...) that spends a huge amount of system resources and try to do some optimization on them to reduce the resource spend:

Use Performance Analyzer to examine report element performance 

Optimizing nested iterators in DAX 

In addition, you can also take a look at below document about tricks to improve power bi performance:

Optimization guide for Power BI 

Regards,

Xiaoxin Sheng

[ItAccounts]

I split a large report into 6 smaller reports and this problem went away, because each smaller report has fewer measures in it. The performance of the built-in Power BI measure editor is very poor, especially when a report contains a large number of measures. The built in measure editor still is not very fast after splitting up the report, but at least it does not use up so much memory that it causes Power BI to lock up.

I have started using Tabular Editor https://www.sqlbi.com/tools/tabular-editor/ to edit measures outside of Power BI because the built in measure editor is poor when editing large numbers of measures. Microsoft needs to improve its built in measures editor (one of the highest voted ideas for Power BI) and the Tabular Editor can provide some ideas for Microsoft. It should not be slow to edit measures or cause high memory consumption; is Power BI recalculating every measure in the report every time I add or edit a measure? There should be no need to do this.

[d_gosbell]

---

@ItAccounts wrote:

is Power BI recalculating every measure in the report every time I add or edit a measure? There should be no need to do this.

---

No, it's not. Measures are only evaluated when they are referenced in a query (and each visual can send one or more queries). Which is why I asked if you see this memory issue when editting measures while you have a blank page open as the visuals on the current report page will refresh after you change a measure in your model.

The number of measures will have some impact on the editor when it has to do the code completion prompts, but that should not have any real impact on memory usage. So it's likely that what you are seeing is related to some other issue, not directly an issue with editting a measure.  

[d_gosbell]

This is not normal behaviour for Power BI Desktop, measures should not consume any noticable amount of memory when you create them. They might consume a lot of memory when you query them (ie use them in a visual), but not otherwise.

Can you try the following:

1. Create a new blank page in your Power BI report

2. Save your report while the blank page is selected

3. Close and re-open Power BI Desktop

4. Now try creating a number of new measures (without switching off the blank page) and see if the memory issue re-occurs

If the issue does not occur while the blank page is set as the current view then you probably have an issue with one of the measures used in one of the visuals on your normal default page (you should see the spinning progress circle in the top left corner of the visual although depending on how complex your page layout is this can sometimes be hard to see)

If the issue does continue to occur while adding measures with the blank page active then you may need to look at raising a ticket with product support https://powerbi.microsoft.com/en-us/support/pro/