In this article you will see how to fix CUBEMEMBER and CUBEVALUE formulas, which are not automatically updated.

The process is pretty simple and Excel asks you to upgrade the data model. Please, be careful that once the workbook will be saved in the new version, it will be no longer possible to navigate it with Excel 2010, so save a copy of the old 2010 version if you want to be able to still work with Excel 2010 (I will update this article in case a Service Pack for PowerPivot will make it possible to open it again with Excel 2010).

In the upgrade process, the formula used in the workbook are not updated. If you used CUBEMEMBER and CUBEVALUE functions with PowerPivot for Excel 2010, you need to change the first parameter used in these functions, because the name of the cube changed. In fact, the value displayed is #N/A for all the values returned by these functions, because a wrong reference is used.

Your existing formula should be something like:

= CUBEMEMBER( "PowerPivot Data", "[Customer].[Customer].&[SQLBI]" )
= CUBEVALUE( "PowerPivot Data", $A2$B1 )

You have to replace the string PowerPivot Data with ThisWorkbookDataModel:

= CUBEMEMBER( "ThisWorkbookDataModel", "[Customer].[Customer].&[SQLBI]" )
= CUBEVALUE( "ThisWorkbookDataModel", $A2$B1 )

Remember that this replace can be done by using the replace function in Excel specifying to work at the Formula level.