# Cell References in Cube Formulae



## ex_cowboy (Sep 17, 2014)

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


----------



## Tianbas (Sep 17, 2014)

try =CUBESET("PowerPivot Data";I15:I16)


----------



## Tianbas (Sep 17, 2014)

maybe better use =CUBESET("PowerPivot Data";I15:I16;"My Region Set") otherwise you might not "see" the cell even if its working in a Cubevalue formula


----------



## ex_cowboy (Sep 17, 2014)

Tianbas said:


> try =CUBESET("PowerPivot Data";I15:I16)



Thanks for this. This does work for that example actually, however I'm looking to include a number of other fields on top of REGION. A number of these fields will share values in particular alot of them would be Yes/No or numbers, so i need to still reference the header somehow. i.e:


REGIONHAS_KIDSHAS_CARSEGMENTAREA_CODEBorderYesYes0201Midlands0303

<tbody>

</tbody>
Ideally i'd be looking at a CUBESET formula that could capture - REGION: Border or Midlands, HAS_KIDS: Yes, HAS_CAR: No, SEGMENT: 02, 03, AREA_CODE, 01,03 and based on what is entered in cells on a table like above.


----------



## Tianbas (Sep 17, 2014)

not in one set but you can use more than one set in a CUBEVALUE so just build your 5 sets and include the Cells in your CUBEVALUE formula =CUBEVALUE("Powerpivot Data";A1;A2;A3,...) if A1 includes the CUBESET for region, A2 for Has-Kids and so on


----------



## ex_cowboy (Sep 17, 2014)

Tianbas said:


> not in one set but you can use more than one set in a CUBEVALUE so just build your 5 sets and include the Cells in your CUBEVALUE formula =CUBEVALUE("Powerpivot Data";A1;A2;A3,...) if A1 includes the CUBESET for region, A2 for Has-Kids and so on



Thanks for your help again. Just without the header referenced in some way in the CUBESET, the CUBEVALUE would essentially be referencing identical CUBESETs i.e

CUBEVALUE("Powerpivot Data"; CUBESET or Border, Midlands, CUBESET of 'Yes', CUBESET of 'Yes'...)

If that makes sense?


----------



## Tianbas (Sep 17, 2014)

If the name is uniqe like border or midlands the set is working just with the name. For the other Sets the different reference cells might need to be CUBEMEMBER formulas.


----------



## ex_cowboy (Sep 18, 2014)

Brilliant, got it working now using the CUBEMEMBERS in the CUBESET as you recommended.

Thanks again.


----------

