Hi,
I'm looking to run a CUBEVALUE formula with member expressions based on the contents of multiple different cells. For this example i have 2 cells with a value for REGION in:
I15: Border
I16: Midlands
I can reference one cell successfully using a cube value formula:
=CUBEVALUE("PowerPivot Data","[Measures].[Sum of WEIGHTED_IMPRESSIONS]","[pvtBASE].[REGION].&["&I$15&"]")
Couldn't find a way within CUBEVALUE alone to replicate this result to reference both I15 and I16 so tried with a CUBESET then referencing the CUBESET in a later CUBEVALUE formula:
FOr the CUBESET, this formula works:
=CUBESET("PowerPivot Data",{"[pvtBASE].[REGION].&[Midlands]","[pvtBASE].[REGION].&[Border]"})
This formula works:
=CUBESET("PowerPivot Data","[pvtBASE].[REGION].&["&I15&"]")
But for some reason this doesn't:
=CUBESET("PowerPivot Data",{"[pvtBASE].[REGION].&["&I15&"]","[pvtBASE].[REGION].&["&I16&"]"})
Does anyone know how to fix the final CUBESET formula or if perhaps there is another way of fitting multiple members and cell references into a CUBEVALUE formula.
Feels like i'm close but then again I might not be!
Cheers
Rab
I'm looking to run a CUBEVALUE formula with member expressions based on the contents of multiple different cells. For this example i have 2 cells with a value for REGION in:
I15: Border
I16: Midlands
I can reference one cell successfully using a cube value formula:
=CUBEVALUE("PowerPivot Data","[Measures].[Sum of WEIGHTED_IMPRESSIONS]","[pvtBASE].[REGION].&["&I$15&"]")
Couldn't find a way within CUBEVALUE alone to replicate this result to reference both I15 and I16 so tried with a CUBESET then referencing the CUBESET in a later CUBEVALUE formula:
FOr the CUBESET, this formula works:
=CUBESET("PowerPivot Data",{"[pvtBASE].[REGION].&[Midlands]","[pvtBASE].[REGION].&[Border]"})
This formula works:
=CUBESET("PowerPivot Data","[pvtBASE].[REGION].&["&I15&"]")
But for some reason this doesn't:
=CUBESET("PowerPivot Data",{"[pvtBASE].[REGION].&["&I15&"]","[pvtBASE].[REGION].&["&I16&"]"})
Does anyone know how to fix the final CUBESET formula or if perhaps there is another way of fitting multiple members and cell references into a CUBEVALUE formula.
Feels like i'm close but then again I might not be!
Cheers
Rab