Cell References in Cube Formulae

ex_cowboy

New Member
Joined
Feb 17, 2009
Messages
9
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]REGION[/TD]
[TD]HAS_KIDS[/TD]
[TD]HAS_CAR[/TD]
[TD]SEGMENT[/TD]
[TD]AREA_CODE[/TD]
[/TR]
[TR]
[TD]Border[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]02[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]Midlands[/TD]
[TD][/TD]
[TD][/TD]
[TD]03[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,416
Messages
6,178,509
Members
452,853
Latest member
philipnjk64

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top