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?
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.)
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.