Log In
Register
Home
New Member
February 17, 2022
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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]")
