Tuesday, March 13, 2012 11:00 PM
I've used the technique from the Russo/Ferrari book (p235) to create a table of period measures that enables the user to select a measure in the list from a slicer or drop down. It's worked successfully in the past with PowerPivot for 2008 R2.
In PowerPivot 2012 the technique doesn't work reliably. The failure appears to be memory related. Here are a couple of scenarios:
1) add the measure of measures in the Excel interface. The measure will work until a) more than 1 period is chosen, or 2) the PowerPivot Window interface is launched.
2) add the measure of measures in the PowerPivot Window. The measure may or may not be successfully added. If successful, when trying to add the measure to a PivotTable various errors including "measure not valid" are triggered.
3) in the PowerPivot Window on the table where the measure is stored you can see calculations occurring for all measures in the Calculation Area (the numbers to the right of the measure names). When this composite measure is added the recalculation takes a long time or hangs. This calculation appears not to be triggered in sceanrio 1) above until the PowerPivot Window is launched or more than 1 period is chosen.
The only change is the PowerPivot version. I'm using Windows 7 32 bit. In my 'IF' statement the measure is set to return BLANK() if more than 1 period is chosen. My fact table is about 400k rows. The Excel file size is about 8 mb.
Friday, March 16, 2012 9:03 AMModerator
For performance issue, I would recommend you follow Marco Russo's blog Memory Considerations about PowerPivot for Excel http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx to get some help.
TechNet Community Support
- Marked As Answer by Challen FuModerator Wednesday, March 21, 2012 10:27 AM