EXCEL OLAP reference to another cell

[dimiandre]

hi all,

i have this formula
=CUBEMEMBER("ThisWorkbookDataModel";{"[Statistiche vendite].[anno_data].&[2014]"\"[Statistiche vendite].[mese_data].&[1]"})


i would like to use a value of one cell (F1) instead of [1]

i tryed this but don't works:
=CUBEMEMBER("ThisWorkbookDataModel";{"[Statistiche vendite].[anno_data].&[2014]"\"[Statistiche vendite].[mese_data].&["&F1&"]"})


any ideas ?


thanks a lot

 

[scottsen]

I have done similar without issue, but the post just before yours had the same problem. Not sure what's up with that...

They solved, and said...

I figured out a work around by putting the expression in another cell and referencing it, seems a little silly that I cant nest the text in the formula itself though.

 

[dimiandre]

Solved with
=CUBEMEMBER("ThisWorkbookDataModel";{"[Statistiche vendite].[anno_data].&[2014]"\"[Statistiche vendite].[mese_data].&["&+F1&"]"})
(added the plus symbol)

now i have another question:

I have two tables in power pivot:

Table one; prod_data

prod_id | data1 | data2 |
1 | XX | YY
2 | ZZ | MM

Table two; prod_name
prod_id | name
1 | Orange
1 | Tomato

i referenced the two tables with "prod_id" in the powerpivot relationships

In excel worksheet now i have one cell (A1) containing:
=CUBEMEMBER("ThisWorkbookDataModel";"[prod_data].[id_prod].[&1]")

I want to get name of product in that cell
so something like in cell A2:
=select [prod_name].[name] where [prod_name].[id_prod] = $A$1


someone can help me with this simple issue ? i can't understand how it works

i tryed this
=CUBEVALUE("ThisWorkbookDataModel"; "[prod_name].[name]"; A1)

but i get "1" instead of the name :S

thanks

 

[scottsen]

For dimensions (as opposed to measures), typically you would use =CUBEMEMBER.

Though, the more common pattern is 1 cell using CUBESET, then a set of cells referencing that CUBESET with CUBERANKEDMEMBER.
Some info here: Introducing CUBESET() and CUBERANKEDMEMBER() « PowerPivotPro

 

[dimiandre]

For dimensions (as opposed to measures), typically you would use =CUBEMEMBER.

Though, the more common pattern is 1 cell using CUBESET, then a set of cells referencing that CUBESET with CUBERANKEDMEMBER.
Some info here: Introducing CUBESET() and CUBERANKEDMEMBER() « PowerPivotPro

already tryed that way,

i created 2 cubeset, but the problem is that in real prod_name table i have more than 3000 rows, and in prod_data table only 200 or less.
So ranks of two cubestes will never match