Cell References in Cube Formulae

**rab2a** · 09-17-2014, 10:45 AM

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

**Pauleyb** · 09-17-2014, 11:49 AM

Just some thoughts, as I don't see anything inherently wrong, but I have not tried to do what you are attempting. The fact that it works when 'hard coded' with Midlands and Border, and even works with a single reference, implies it should work with two references, so I understand your frustration.

1 - Use 'Evaluate Formula' to see how Excel is interpreting it.
2 - longshot - add spaces around your &s that are being used for concatenation

**rab2a** · 09-18-2014, 11:39 AM

Thanks Pauley.

Managed it in the end by converting each value to a CUBEMEMBER then taking a collected CUBESET of those CUBEMEMBERS. Doesn't exactly cut down the formula size but should work..

Not used Evaluate Formula before; looks useful, will keep that in mind in the future.