PowerPivot, have ID, want to display name - CUBEMEMBER & CUBEMEMBERPROPERTY & GETPIVOTDATA won't work.

[omaroses]

This should be simple, but I've fought it for DAYS. HELP!

Excel 2010. PowerPivot with 3 tables. A few PowerPivot pivot's are in my Excel sheet.

On a regular sheet, in a regular cell, in A1 I want to enter a customer ID. In A2, I want to display the customer's name. I can get measures & calculated values to display, but not the name.


Here is an example of my Customer powerpivot table:
ID Name SalesRep EffectiveDate CreditLimit
001 ABC Company Ed 1/1/2015 $10,000
002 XYZ Company Fred 3/1/2015 $20,000


=CUBEMEMBER("Cube_name",{"[Customer].[ID].&[12345]","[Customer].[Name].&[ABC Company]"})


The CUBEMEMBER function is unhelpful because I'm required to know the Customer Name of "ABC Company" in order to get the value returned. I've tried the CUBEMEMBERVALUE function, but get #N/A results. I've made a PowerPivot table in my workbook and then tried GETPIVOTDATA, but since it's in a value(row) area, and not a calculated column, it refers to a cell, and isn't dynamic. I've tried measures with FIRSTNONBLANK, and a few other things I saw in google searches, but can't get anything to work.

What am I doing wrong? Any help is greatly appreciated.

 

[akice]

I don't have 2010, only 2013 but think CUBE functions are the same. If i understand what you are trying to do, you need to use the CUBEVALUE function. CUBEVALUE returns a value from the data model give the dimensions (aka CUBEMEMBERS) you want to reference.

=CUBEVALUE("Cube_Name",[COLOR=#333333]CUBEMEMBER("Cube_name",{"[Customer].[ID].&["& A1 & "]"[/COLOR] ), [COLOR=#333333]CUBEMEMBER("Cube_name",[/COLOR]"[Measures].[CustomerName]") )

where [CustomerName] is a measure that returns the name give the customer id. For that you should be able to use

CustomerName:=LASTNONBLANK(Customer[CustomerName], Customer[ID])

Let me know if this works out for you...

 

[theBardd]

You could try this array formula

=CUBERANKEDMEMBER("[COLOR=#333333]Cube_name[/COLOR]",SET.NAMES,MATCH(A1,--CUBERANKEDMEMBER("[COLOR=#333333]Cube_name[/COLOR]",SET.IDS,ROW(INDIRECT("1:"&CUBESETCOUNT(SET.IDS))))))

where SET.IDS is a CUBESET call for the customer ids and SET.NAMES is a CUBESET call for customer names.