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

omaroses

New Member
Joined
Apr 13, 2016
Messages
1
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

Board Regular
Joined
Apr 18, 2016
Messages
180
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.

Code:
=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
Code:
CustomerName:=LASTNONBLANK(Customer[CustomerName], Customer[ID])
Let me know if this works out for you...
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
You could try this array formula
Code:
=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.
 

Forum statistics

Threads
1,081,657
Messages
5,360,291
Members
400,580
Latest member
Drexl88

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top