理解 LOOKUPVALUE

显示全部楼层 · 2021-2-21 18:56:57

LOOKUPVALUE 是一个搜索函数，它在作为参数的列中搜索与提供的值相等的列值，并最终从表中检索一个可用值，整个过程忽略任何筛选上下文。

LOOKUPVALUE

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…[ <alternateResult>] )

复制代码

参数	属性	描述
Result_ColumnName		要返回的值所在的列名。列必须使用标准 DAX 语法命名，通常是完全限定的，不支持表达式。
Search_ColumnName	可重复	在与 Result_columnName 相同的表中或扩展表中，执行查找的现有列的名称，列必须使用标准 DAX 语法命名，通常是完全限定的。不支持表达式。
Search_Value	可重复	标量表达式（不引用正在搜索的同一表中的任何列）
Alternate_Result	可选	当第一参数结果为空或多个不重复值时的替代结果，如果省略此参数，为空时返回 BLANK，多值返回错误

函数返回匹配所有 search_column 和 search_value 组合的行上的 result_column 的值。

如果没有满足所有搜索值的匹配行，返回一个空白或 alternateResult(如果提供)。换句话说，如果只有部分条件匹配，函数将不会返回查找值。

报错情形

如果有多个行匹配搜索值，并且它们的 result_column 值都相同，则返回该值。但是，如果 result_column 返回不同的值，则函数返回错误或 alternateResult(如果提供)。也就是说，匹配到的结果可以在列中重复显示，但值必须唯一。

最高价时间:=LOOKUPVALUE(‘测试 2′[日期],’测试 2′[最高],’测试 2′[最高价])

复制代码

最高价对应多个不同日期的时候，LOOKUPVALUE 返回错误

等价表达式

LOOKUPVALUE 在引擎内部计算时使用以下语法：

VAR SearchValue = <Search_Value>
RETURN
CALCULATE (
SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
FILTER (
ALLNOBLANKROW ( <Search_ColumnName> ), //忽略外部上下文
<Search_ColumnName> == SearchValue //严格相等，BLANK 不等同于 0 或空字符串
),
ALL ( <table_of_Result_ColumnName> ) //忽略结果列所在表提供的所有筛选器
)

复制代码

从等价表达式的 6，7，9 行可以分别读出有价值的信息，如公式中的标注所示，通过第 6 和第 9 行你可以明白为什么 LOOKUPVALUE 忽略外部筛选器，其中第 9 行的写法在某些特殊情况下可以导致异常，本文最后将提供一个案例。

示例用法

以下查询将 CAD 货币代码转换为相应的货币名称：

EVALUATE
ROW (
"Currency", LOOKUPVALUE (
Currency[Currency],
Currency[Currency Code], "CAD"
)
)

复制代码

LOOKUPVALUE 函数忽略现有的筛选上下文，直接在目标表中执行查找。当表达式在发生上下文转换的迭代环境中执行时，这种行为特别有用。

你可以指定更多的列来执行匹配操作，还可以通过关系引用相关表的列。以下查询返回满足 Contoso 品牌、Deluxe 类别、颜色为银色的音频类别的产品：

EVALUATE
ROW (
"Product", LOOKUPVALUE (
Product[Product Name],
Product[Color], "Silver",
Product[Brand], "Contoso",
Product[Class], "Deluxe",
'Product Category'[Category], "Audio"
)
)

复制代码

如果有存在多行与搜索值匹配，结果将视情况而定：

如果<result_column_name>返回不同的值，函数将返回错误.
如果<result_column_name>返回唯一值，即使表中的多个行与搜索值匹配，LOOKUPVALUE 函数也只返回该唯一值。
如果没有匹配的行，函数返回空白值。

例如，以下查询返回月份编号为 3 对应的月份名称，即使返回的值可能有几百行，但它们有相同的唯一值：

EVALUATE
ROW (
"Month Name", LOOKUPVALUE (
'Date'[Month],
'Date'[Month Number], 3
)
)

复制代码

大多数情况下，当你有行上下文并且需要通过关系获取相关表某一列的值时，可以使用 RELATED 函数执行查找操作，它的性能更好。但是，如果两个表在数据模型中没有可用关系，或者需要实现基于特定逻辑的查找时，可以使用 LOOKUPVALUE 或 TREATAS 代替 RELATED。

某些情况下，你也可以使用 CALCULATE 函数重写 LOOKUPVALUE，但这并不是最佳实践。例如，可以按以下方式重写上面的查询：

EVALUATE
ROW (
"Month Name", CALCULATE (
VALUES ( 'Date'[Month] ),
'Date'[Month Number] = 3
)
)

复制代码

循环依赖的 Bug

LOOKUPVALUE 在大部分情况下可以正常工作，但某些特殊情况可能导致错误提示，一旦你理解 LOOKUPVALUE 内部使用的表达式，就可以解决这类问题。在这里举一个简单的例子。

两张表的数据和关系如下图所示，两表通过 C1 建立双向关系，我们在 Table2 新建计算列使用 LOOKUPVALUE 在 Table1 中查找对应的 C2 的值：

案例数据示意图

从示意图可以看出，Table2 的计算列 Cal Column1 得到了正确的结果，问题出现在 Cal Column2，这个计算列使用了简单的等式

Cal Column2 = 'Table2'[Cal Column1]

复制代码

却得到了循环依赖的错误提示。这是为什么？当你理解了等价表达式，就能回答这个问题。

让我们再重新研究一下 LOOKUPVALUE 的等价表达式：

VAR SearchValue = <Search_Value>
RETURN
CALCULATE (
SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
FILTER (
ALLNOBLANKROW ( <Search_ColumnName> ),
<Search_ColumnName> == SearchValue
),
ALL ( <table_of_Result_ColumnName> )
)

复制代码

这次让我们把注意力放在表达式的第 9 行，这里的 ALL 忽略了结果列所在的整张表(Table1)，在绝大部分情况下，它可以有效的忽略来自外部的筛选器。但是本例中两表的关系是双向筛选，这就意味着对 Table2 的筛选可以传递到 Table1，只忽略 Table1 的筛选条件将不起作用。此时新建的 Cal Column2 将在 Cal Column1 的等价表达式中通过 CALCULATE 执行上下文转换筛选 Table1，最终影响 Cal Column1 的结果，这意味着 Cal Column1 依赖于 Cal Column2。又因为 Cal Column2 = ‘Table2′[Cal Column1]，所以 Cal Column2 又依赖于 Cal Column1，循环依赖就发生了。

导致此问题的关键是双向筛选，而单向筛选不会遇到此问题。如果你需要维持筛选关系为双向，可以按如下方式改写表达式，忽略来自 Table2 的筛选，此时新建列将不再筛选 Table1：