Using a formula to get a "lookup" value in PowerPivot

[BeverlyB]

I have a table in PowerPivot called ID which contains unique IDs for every combination in my financial data set of Profit Center and Cost Type. In Excel, in Column A, I have a list of each of these unique IDs. In Columns B & C, I want the Profit Center and Cost Types associated with each unique ID (there will be a lot more columns which are not coming from the ID table; this is just the first step in setting up a template for users to enter forecasts for each of their Profit Centers by Cost Type). In the old days, I'd just bring this data into a tab and do a vlookup based on the ID. But I am trying to keep this file as small as possible and cube formulas and PowerPivot seem to be the way to go.

My problem is that I can't get this to work and am about to give up and go back to a chisel and stone tablet!

Here are the formulas that I am using:

Cell C1
=CUBESET("PowerPivot Data","[ID].[ID].children","ID",5)

Cell A35 (ID)
=CUBERANKEDMEMBER("PowerPivot Data",C$1,ROW($A1))

Cell B35 (Profit Center)
=CUBEMEMBERPROPERTY("PowerPivot Data","[ID].[ID].["&A35&"]","Profit Center")

Any help would be greatly appreciated!

 

[patbarb]

OK, my experience with PowerPivot is extremely limited, but, are you saying that just typing in the DAX lookup function "Related()" in an empty column on the right of your PowerPivot sheet doesn't do a sophisticated enough lookup? I'm just wondering since that seems to be PowerPivot's version of Vlookup and you said you used to do it like that.

 

[Laurent C]

The main problem is you are trying to use PowerPivot to do a lookup instead of what it is intended for: defining calculations in a model and building pivot tables.

If you want to share a few example of how your final report should look like, then we might be able to provide some hints on you can solve your scenario the PowerPivot way.

This being said, PowerPivot does not support member properties. You should be able to define a calculated field (measure) as VALUES([Profit Center]) and use this measure with a CUBEVALUE function.

 

[Officeway]

Hi folks,

I am running into the same problem and cannot seem to find a solution for that. I was at a Microsoft big-data conference the other day asked their tech dude, who couldn't really answer it for me.

> The issue is:

· I have plugged in some transactional data into the power pivot via SQL server - let's call the tab a 'Query' tab

· Among with the standard columns, there is a column called ‘Product Code’. The input data brings all sorts of values in this column (Alpha numeric & Blanks/Null). Ideally it should only be numeric and moreover should be a 7 digit number starting with a ‘7’ – but clearly it is not

· I have also plugged a catalogue file which have all the product codes and descriptions etc in a different tab on power pivot – let’s call it ‘Catalogue table’. All products are purely numeric starting with a ‘7’ and are 7 digits in length

> The desired outcome:

· To do a standard lookup function (so a vlookup, index/match etc etc) to strike the query table’s product code with the catalogue table’s product code to get other relevant information – so in a very simplistic way, something like:

=Iferror(VLOOKUP(A2,Catalogue!C:D,2,0),”Non Catalogued”)

> Why am I not using the native relational model which power pivot is all about?

· Purely because in query table, the product codes are all over the place; they are numeric with various different character length, they are alphabets and also alpha numeric – On the contrary, catalogue table only has numeric as specified above. Powerpivot would fail the relationship due to that very fact

> Why can’t I do it in excel by pivoting both the tabs?

· Simply because it is very system intensive as I am using close to 10 Million rows at this stage and they will grow exponentially as the time proceeds. I want to utilize PowerPivot’s in-memory rapid processing

> Help required?

· Any DAX formulae to establish a calculated column in PowerPivot (with proper use case with the above mentioned example) that does something exactly like a normal lookup function in native excel

Regards,

 

[Laurent C]

From the formula you provide as an example (=Iferror(VLOOKUP(A2,Catalogue!C:D,2,0),”Non Catalogued”)), I assume you will ignore product codes that have no match in your catalogue, or at least assign them to a single common "unknown" category.

This is not an impediment to using the native PowerPivot relationships: If you relate your Query table to your Catalogue table, PowerPivot will add a BLANK member. Product codes that cannot be found in the Catalogue will be assigned to this BLANK member. This is, in essence, the same as what your IFERROR() formula does.

Also, creating calculated columns in the Query table to import columns from the Catalogue columns are usually not required. You can simply use the Catalog table to navigate your fact (Query) table in the pivot table.

Try it and if there seems to be something you cannot do with a regular PowerPivot relationship, just provide a sample of your data, and the expected results.