Search code examples
excellookupdashboardpowerpivotpowerquery

"Lookup" Data in PowerPivot Model-FactTables from Excel

I have a fact table with two unique columns in PowerPivot.

I was wondering if it is possible somehow to "Lookup" data from this table from excel.

As an example, please see table below (which is in PowerPivot).

My question is would it be possible to search in column UniqueID_1 and return value in UniqueID_2, and vice versa?

I am familiar how to use cube functions for measures, but I do not know how to return strings.

As background, this table comes into PowerPivot through PowerQuery, and I want to retrieve the table delivered by the PowerQuery without writing the table onto a spreadsheet (which is the workaround I am doing right now).

Any clever ideas?

Best,

Ben

Screen Shot

EDIT:

Thank you for your suggestion & post. I'm not sure though this exactly solves my problem.

Just to clarify, I am NOT looking for a solution to lookup data: - (a) residing in PowerPivot table by querying it from another PowerPivot table (can be done with "Related" or "LookupValue" function in PowerPivot) - (b) residing in Excel table by querying it from another Excel cell (can be done with "Lookup", or "Index"/"Match" function in Excel)

What I am looking for is a way to lookup data: - (c) residing in PowerPivot table by querying it from an Excel cell.

Below, I added an example to make this more clear. I tried (c) by matching a date value with the Excel-Match functionality with a date-column from a PowerPivot Table.

The solution I am looking for can return ANY data type, not just numbers (which could be done through excel formula "CubeValue")

I added screenshots: i) from the PowerPivot Table to show the table structure & content ii) from the Excel formula I tried to use to connect to the PowerPivot table (does not work!)

Looking forward to any suggestions!

Best, Ben

i) Diagram View/Content in PowerPivot

iii) Match function in excel (NOT WORKING)

Solution

  • This can be done using a CUBEMEMBER function. If you provide the appropriate tuple for the member expression, you should be able to get what you're looking for.

    =CUBEMEMBER("ThisWorkbookDataModel","([Table].[FilterColumn].&[FilterValue],[Table].[ResultColumn].Children)")

    So, in your example:

    =CUBEMEMBER("ThisWorkbookDataModel","([Table].[UniqueID_1].&[CWTT],[Table].[UniqueID_2].Children)")