Power Pivot Performance

[c_wag03]

I recently obtained the MCSA for BI Reporting, and have been doing some heavy lifting in Excel building a fairly complex data model pulling from several data sources, the largest being a folder of CSV files with about 2 million total rows of data. I am analyzing the data using DAX measures and a pivot table. After several different manipulations of the pivot table (adding fields, re-ordering fields, doing differenet sorts and filters, etc), the performance eventually degrades so much, that one change like adding another column to the Rows pane hangs at "reading data" for 30 minutes plus. I figured it was a memory issue or something, so I thought maybe closing out of Excel altogether and reopening might give me a fresh start, but this doesn't help. The only thing I have found that seems to "reset" and get me back to decent performance is to delete the pivot table and build a new one, which is obviously not ideal. I have also recently updated Excel to the most recent version available from Office 365 to see if that helps. Any ideas or suggestions?

 

[macfuller]

Just to be sure, are you deleting the pivot table from a worksheet or deleting the data model and rerunning your M queries?
And does the spreadsheet hang when you're creating a measure or when you're adding the measure to the pivot table?

Perhaps check the syntax of your measures. FILTER is often a culprit. If you are doing a FILTER ( MyTable ), try doing a FILTER on only those columns that are involved (e.g. FILTER ( ALL ( MyTable[Column1], MyTable[Column2] ), <condition> ) where <condition> only bumps against columns 1 and 2.

Other than that I'm out of ideas!

 

[c_wag03]

I'm just deleting the pivot (actually the whole tab that contains the pivot), not the whole data model. My most recent experience is it hanging just trying to add one more field to the rows section of my pivot table, not adding a measure. I do have quite a few calculated measures in the model, but only a a handful of them actually displayed in the pivot. I am using FILTER a decent amount, so I guess I'll see if changing some of those helps at all.