I have been setting up an Excel 2010 sheet with 1000+ CUBEVALUE formulas to populate data from PowerPivot.
The PowerPivot tab contains the following tabs:
- Data 1 (25k rows)
- Data 2 (40k rows)
There is no relationship between the 2 data sets.
When I initially loaded the data and started to write the formulas, everything worked perfectly fine. The values were being displayed exactly in the way expected.
After 1000+ cells with formulas were created I tried to refresh the data connection between Excel and PowerPivot. First of all I noticed that the connection was much slower than before. But unfortunately it only gives me random (or at least I don't see the pattern) cells with the correct values, and other cells #N/A.
I have tried to search if there are any limitations in terms of number of cells when using this formula, but with no success. Also, my dataset in PowerPivot is nothing special in terms of size.
I am not sure if I have provided enough information to get an idea of what's going on, but any feedback would be highly appreciated. Did anyone ever have the same experience?
Thanks in advance!
To my understanding, I suppose the issue may be caused by some reasons.
CUBEVALUE returns a #N/A error value when:
• The member_expression syntax is incorrect.
• The member specified by member_expression doesn't exist in the cube.
• The tuple is invalid because there is no intersection for the specified values. (This can occur with multiple elements from the same hierarchy.)
• The set contains at least one member with a different dimension than the other members.
• CUBEVALUE may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
I recommend you check the data source if it has the above scenario.
For more detail information, please refer to the following link:
TechNet Community Support
Hi, I think I'm getting the same problem. Cubevalue formulas were working fine then suddenly I'm getting random cells with correct values and others with #N/A. It seems to have happened after I added a bunch more cells with cubevalue formulas so maybe it is some sort of limitation? Did you end up finding out what the issue/solution is?