I have an Excel workbook that has 30 plus pivot tables and 10 plus slicers connected to PowerPivot. When we filter data based on these slicers the performance is sub par, 30 sec to 2 minutes. Is there a strategy for improving this performance?
Things I did to improve the performance of my PowerPivot files:
- check for unecessary calculated columns on table-level
- check and re-check measures (is every measure necessary? are they calculated in an efficient way?)
- are the slices only connected to the relevant tables and don't interfere with each other?
Another strategy could be to save a backup of the original file and delete some of those tables or slices step-by-step to narrow down if there is a specific set of slicers which are especially time-consuming and focuse on those first.
For more general statements than that it would be helpful to see some examples.