问题描述
我在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 & contentii) 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中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！