excel - 如何在power pivot excel中进行范围索引和匹配或vlookup？

Question

这些是 的各个数据点data model：

这些是员工的月薪（使用数据模型数据中的数据透视表获得）：

然后，每个单元格将用作查找值，该值将在表格中运行。

查找值将在下表中查找column A，column B如果匹配（在范围内），它将返回对应的值column C。

我无法在 excel 的 Power Pivot 功能中找到任何功能，这些功能index and match用于获取对报表对象（例如数据透视表）的聚合值的一些分析。vlookupmeasures

我发现LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)这是一个DAX函数，但是这里的问题是我正在进行范围查找，如下所示，我不知道您是否可以将数组作为函数的参数。

传统calculated fields也不允许在公式中使用数组。

Lookupvalue()仅适用于单列查找，因为如果找不到匹配项，它将返回一个空单元格，如下所示：

但是当它确实使用下表找到匹配项时：

它会工作得很好：

标签： excelpowerbipowerpivot

Answer 1

首先，您需要为 Pay 创建一个度量：

Total Pay = SUM(Table1[Pay])

重要的是把它作为一种衡量标准，而不是仅仅将“支付”放入数据透视表（这被称为“隐含衡量标准”，被认为是一种不好的做法）。

然后，假设您的薪酬范围表被命名为“薪酬范围”。创建另一个度量：

Returned Value = 
CALCULATE( 
VALUES('Pay Ranges'[Value To Return]), 
FILTER( 'Pay Ranges', 
[Total Pay] >= 'Pay Ranges'[Lower Bound] &&
[Total Pay] <  'Pay Ranges'[Upper Bound]
))

确保所有这些公式都是度量，而不是计算列。此外，该公式依赖于范围的正确构造。如果它们重叠，您将收到错误消息。