Cell References in Cube Formulae

[ex_cowboy]

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]

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

 

[Tianbas]

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]

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:

REGION	HAS_KIDS	HAS_CAR	SEGMENT	AREA_CODE
Border	Yes	Yes	02	01
Midlands			03	03

<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]

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]

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]

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]

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

Thanks again.