Hello,
I am working on rebuilding a rather heavy Excel file, which relies mostly on pivot tables and the GETPIVOTDATA() function.
I would like to modernize my approach to work with the data model and CUBE functions.
Currently, I am trying to use the CUBEVALUE function while integrating an OR filter on data (similar to how one would fetch data in a pivot table previously filtered).
My formula looks like this: =CUBEVALUE("ThisWorkbookDataModel";"[Measures].[Sum of Investments]";"([Invest CF].[CP].&[31]+[Invest CF].[CP].&[35])";"[Invest CF].[Year].&[2019]";"[Invest CF].[Month].&[1.]";"[Invest CF].[New CF].&[V1]";"[Invest CF].[Pilot].&[ROMEO]")
But it doesn't work; I also tried with a comma, within brackets...
I am not sure how to use CUBEVALUE to replace GETPIVOTDATA().
Could you help me understand how to use VALEURCUBE with filters?
Thank you
Francois
You should be able to use CUBESET:
=CUBEVALUE("ThisWorkbookDataModel";"[Measures].[Sum of Investments]";"CUBESET("ThisWorkbookDataModel";"[Invest CF].[CP].&[31];[Invest CF].[CP].&[35]");"[Invest CF].[Year].&[2019]";"[Invest CF].[Month].&[1.]";"[Invest CF].[New CF].&[V1]";"[Invest CF].[Pilot].&[ROMEO]")