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



## omaroses (Apr 13, 2016)

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 (Apr 18, 2016)

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 (Apr 18, 2016)

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.


----------

