Power Pivot lookup function

[NewPowerUser]

I'm brand new to using Power Pivot and I've come across a problem that I can't find find a solution to.

I'm trying to have excel pull back information(column B) from the PowerPivot data source based on an entry field(column A). I've tried researching the CubeMember funtion but I can't seem to get it to work.

My formula(Column B) is as follows:
=CUBEMEMBER("ThisWorkbookDataModel",{"[Building_Permits_Issued_-_Official].[Permit #].&A2","[Building_Permits_Issued_-_Official].[Permit Type Description].&A2"})

Hopefully, I'm just overlooking something very easy.

Thanks for you help.

 

[Matt Allington]

Are you able to do this in a pivot table? Set up a pivot first that has your data. Then convert the pivot table to cube formulas OLAP TOOLS menu, convert to formulas. You can then look at the formulas and build a cell based alternative from there.

 

[NewPowerUser]

Matt,

Thanks for the reply. I actually walked through these steps before I posted the forum question. I got to the point where I was tweaking the formula(column B) and got stuck.

Thanks again.

 

[SimonNU]

What you're asking sounds eerily similar to a post I made yesterday: https://social.technet.microsoft.co...ering-a-cubeset?forum=sqlkjpowerpivotforexcel

If it is, then no one has come up with a solution for it yet (that I'm aware of).

 

[Matt Allington]

What is in cell A2? It seems like you may be missing the square brackets (unless they are in cell A2). Also your double quotes are in the wrong place. I would assume the following will work. The cell A2 should have the child member without square brackets

=CUBEMEMBER("ThisWorkbookDataModel",{"[Building_Permits_Issued_-_Official].[Permit #].["&A2&"]","[Building_Permits_Issued_-_Official].[Permit Type Description].["&A2&"]"})

 

[NewPowerUser]

Cell A2 is the Permit # that the user enters.

 

[Matt Allington]

OK, so did you try my formula?

 

[NewPowerUser]

I tried it but it's giving me errors. I also tried to tweak it but I keep getting the #N/A.

 

[Matt Allington]

well #NA normally means the item is not available as a child in the data model. Also the formula looks to me like it is pointing to 2 different columns. I can't see how that is possible. Try deleting one of the columns