“查找"Excel中PowerPivot Model-FactTables中的数据 [英] "Lookup" Data in PowerPivot Model-FactTables from Excel

问题描述

我在PowerPivot中有一个事实表，其中有两个唯一的列.

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

我想知道是否可以通过Excel从该表中查找"数据.

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

作为示例，请参见下表(位于PowerPivot中).

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

我的问题是，是否可以在UniqueID_1列中进行搜索，并在UniqueID_2中返回值，反之亦然?

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.

作为背景，该表通过PowerQuery进入PowerPivot，我想检索PowerQuery提供的表而不将表写入电子表格(这是我现在正在解决的方法).

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).

有什么聪明的主意吗?

最好

本

感谢您的建议&邮政.我不确定这是否能解决我的问题.

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

请澄清一下，我不是在寻找用于查找数据的解决方案:-(a)通过从另一个PowerPivot表中查询来驻留在PowerPivot表中(可以通过PowerPivot中的​​相关"或"LookupValue"函数来完成)-(b)通过从另一个Excel单元查询来驻留在Excel表中(可以通过Excel中的查找"或索引"/匹配"功能来完成)

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)

我正在寻找一种查找数据的方法:-(c)通过从Excel单元查询来驻留在PowerPivot表中.

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

下面，我添加了一个示例以使其更加清楚.我尝试通过将日期值与Excel-Match功能与PowerPivot表中的日期列进行匹配来进行(c).

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.

我正在寻找的解决方案可以返回任何数据类型，而不仅仅是数字(可以通过excel公式"CubeValue"完成)

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

我添加了屏幕截图:i)从PowerPivot表中显示表结构&内容ii)从我尝试用于连接到PowerPivot表的Excel公式中(不起作用！)

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!)

期待任何建议！

最好，本

推荐答案

这可以使用CUBEMEMBER函数来完成.如果为成员表达式提供适当的元组，则应该能够找到所需的内容.

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)")

因此，在您的示例中:

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

这篇关于“查找"Excel中PowerPivot Model-FactTables中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！