0

I sometimes write a DAX formula which turns out to be to heavy for my machine. It can even take more than 10 GB of memory and block my work for quite a long time. Excel is totally unresponsive at this point.

Is there an option to pause calculation, so that I can rewrite the formula without closing the Excel file? So far the only solution I know is closing Excel from Task Manager, but then I lose the unsaved work.

Amade
  • 3,665
  • 2
  • 26
  • 54

2 Answers2

2

I would suggest trying DAX Studio to develop and troubleshoot the measure. DAX Studio is both a standalone tool and Excel plugin that allows you to query a Power Pivot (Or SSAS) model with syntax highlighting, server timings, query plans, etc. In this case, if the measure results in excessive resource usage and freezes your machine, you may be able to end the DAX Studio process without crashing Excel. If your measure is complex, you can break it down into smaller pieces and output the results in DAX Studio to visualize the intermediate steps.

Shown below is an example CALCULATE measure test with two filters. The measure is embedded in a DAX query, and the result will be a single value like what you see in the Power Pivot measure pane.

EVALUATE
ROW (
    "Measure Result", CALCULATE (
        COUNTROWS ( Registrations ),
        'Vehicle Model'[Vehicle Year] = "2015",
        'Vehicle Model'[Type] = "TRUCK"
    )
)

You can find resources on how to use DAX Studio at SQLBI.

Chris Koester
  • 492
  • 4
  • 9
  • Thank you Chris for trying to help, but this does not answer my question... I am specifically interested in an option to 'kill' the long running calculation without shutting down Excel and not the query optimization methods (this is a separate topic). BTW, I agree that DAX Studio is a great tool and use it as well :) – Amade May 12 '16 at 09:20
  • I'm not sure how to cancel a calculation in Power Pivot/Excel, which is why I suggested using DAX Studio as a way to avoid killing Excel. Think of it like sacrificing DAX Studio to spare Excel :) It's not the most elegant solution, and it would certainly be nicer to just press escape to cancel the measure calculation. – Chris Koester May 12 '16 at 18:52
0

The best idea is to reduce your data in the model through aggregation (with Power Query - Group By) or split the data into different workbooks (one workbook for each year for example).

Doing so will reduce the memory/cpu needed for calculations and your workbook hopefully won't crash...

Otherwise...

You can set calculation within Power Pivot to be Manual Mode (Design Ribbon > Calculation Options). With that you can build up your measures etc. without Power Pivot / Excel calculating them on each change.

If you've got a lot of big Pivot Tables that are refreshing after changing a measure, this can cause Excel to crash. Install the OLAP PivotTable Tool extension (http://olappivottableextend.codeplex.com/) - this allows you to disable the auto refresh which happens on Pivot Tables whenever you change something within Power Pivot.

Edit:

Powerpivot lets you cancel a calculation being made in a calculated field. Once you hit enter upon changing the formula, you can use the Esc key to stop the calculation.

The same is not possible in measures.

From your comments I would think that there's something else wrong with your model though and would recommend checking that first... 100k rows should be extremely easy for Powerpivot and should not result in 10gb of memory being eaten up.

dijksterhuis
  • 1,225
  • 11
  • 25
  • Thank you for your answer, but this won't solve my problem... To give you a specific example: I've been working on a dataset with roughly 100k records and used a CALCULATE function counting the number of values in a column meeting certain conditions. Everything was working fine with 2 filter conditions (calculating within 1-2 seconds), but when I was adding the 3rd one, the file was always crashing... Basically I'd need an equivalent of stopping the query execution on SQL databases, which wouldn't require me to close Excel through Task manager. – Amade Apr 28 '16 at 15:36
  • 100k rows? Interesting... Can you post an example of the 2 filter and 3 filter CALCULATE() functions you created? – dijksterhuis May 03 '16 at 14:21
  • I do not have the function anymore. I can only tell you that this was a column with only unique values (date/time). But again, this was not the point of my question. I know the workaround. I just wanted to know if there is an option to 'kill' the calculation if it is running to long. – Amade May 12 '16 at 09:17
  • The only way to kill a calculation within powerpivot itself is on calculated fields. Once you add / change the formula and hit enter, powerpivot will show a green progress bar in the bottom right. You can then hit Esc to stop the calculation. It's not great practice to use a complicated formula in a calculated column, but sounds like it's what you want. The same is not possible with measures.... Still, only 100k rows and everything crashing sounds like there is something else going seriously wrong.... I would revisit your code and see if there's a better way if doing it.... – dijksterhuis May 14 '16 at 11:52