DAX：LOOKUPVALUE 函数

敬锋 2023-05-02 发布于广东

展开全文

LOOKUPVALUE函数用于根据一个或多个搜索条件，从另一个表中获取一个或0个值。LOOKUPVALUE运行在行上下文中，根据当前表中的当前行，从另一个表中查找条件相等的行，查找不需要两个表之间存在关系，搜索结果也不受过滤条件的影响。当两个表之间存在关系时，考虑使用RELATED函数，因为RELATED函数查询性能更快。

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

参数注释：

result_columnName：包含要返回的值的现有列的名称，它不能是一个表达式。
search_columnName：现有列的名称，它可以与 result_columnName 在同一个表中，也可以在相关表中，它不能是一个表达式。
search_value：要在 search_columnName 中搜索的值。
alternateResult：可选的参数，当 result_columnName 的上下文已过滤为零个或多个不同值时返回的值。如果未提供，则当 result_columnName 被过滤为零值时该函数返回空白，或者当多个不同值时返回错误。

注意：

如果两个表之间存在关系，那么LOOKUPVALUE函数所在的表是基表，通过关系来扩展表，Search_ColumnName 可以是 Result_ColumnName 引用的扩展表的任何列。

search_value 和 alternateResult 参数在函数循环访问搜索表的行之前进行计算。

返回值：

如果没有数据行能够满足所有的搜索条件，那么函数返回 BLANK。
如果存在唯一的数据行满足所有 Search_Column 和 Search_Value 对，那么返回该行中Result_Column的值。
如果存在多行与搜索值匹配，并且Result_Column 值都相同，那么返回该值。但是，如果 Result_Column 存在不同的值，那么会返回错误。

引擎内部在执行LOOKUPVALUE函数时会生成一个等效的查询：

VAR SearchValue = <Search_Value>
RETURN
    CALCULATE (
        SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
        FILTER (
            ALLNOBLANKROW ( <Search_ColumnName> ),
            <Search_ColumnName> == SearchValue      -- The == operator distinguishes between blank and 0/empty string
        ),
        ALL ( <table_of_Result_ColumnName> )        -- If Result_ColumnName is t[c], this is ALL ( t )
    )

一，用RELATED代替LOOKUPVALUE

如果结果表和搜索表之间存在关系，在大多数情况下，使用 RELATED 函数而不是 LOOKUPVALUE 会更有效并提供更好的性能。

举个例子：基于当前表Sales的SalesOrderLineKey列，通过匹配Sales Order表中SalesOrderLineKey列，获取Sales Order表Channel字段的值。

CHANNEL = LOOKUPVALUE('Sales Order'[Channel],'Sales Order'[SalesOrderLineKey],'Sales'[SalesOrderLineKey])

由于Sales和Sales Order之间存在关系多对一的关系，可以使用RELATED函数获得Sales Order表Channel字段的值。

CHANNEL = RELATED('Sales Order'[Channel])

二，LOOKUPVALUE的替代写法

举个例子，从table中获得result_column列的值：

LOOKUPVALUE (
    table[result_column],
    table[search_column_1], <expression_1>,
    table[search_column_2], <expression_2>,
    <alternate_result>
)

根据常规的替代方法，可以得到替代方案1：

CALCULATE (

SELECTEDVALUE ( table[result_column], <alternate_result> ),

FILTER (

ALLNOBLANKROW ( table[search_column_1] ),

table[search_column_1] == <expression_1>

),

FILTER (

ALLNOBLANKROW ( table[search_column_2] ),

table[search_column_2] == <expression_2>

),

REMOVEFILTERS ( )

)

当 <expression_1> 和 <expression_2> 是常量值时，不会有任何问题。但是，通常情况下这些表达式是动态的，这可能会生成更昂贵的查询计划。为了减少这个工作，您可以将表达式移到 CALCULATE 中的过滤谓词之外，得到替代方案2：

VAR filterValue1 = <expression_1>
VAR filterValue2 = <expression_2>
RETURN CALCULATE (
    DISTINCT ( table[result_column] ),
    table[search_column_1] = filterValue1,
    table[search_column_2] = filterValue2,
    REMOVEFILTERS ( )
)

使用TREATAS函数来实现搜索条件，得到解决方案3：

CALCULATE (
    DISTINCT ( table[result_column] ),
    TREATAS ( { <expression_1> }, table[search_column_1] ),
    TREATAS ( { <expression_2> }, table[search_column_2] ),
    REMOVEFILTERS ( )
)

出于可读性原因，最好将 TREATAS 结果存储在变量中，但从查询计划的角度来看，以下代码也与前面的代码相同：

VAR filter1 = TREATAS ( { <expression_1> }, table[search_column_1] )
VAR filter2 = TREATAS ( { <expression_2> }, table[search_column_2] )
RETURN CALCULATE (
    DISTINCT ( table[result_column] ),
    filter1,
    filter2,
    REMOVEFILTERS ( )
)

对于 LOOKUPVALUE 用例，可以创建单个多列过滤器而不是多个过滤器——可能会产生更好的查询计划，得到替代方案4：

VAR filterLookup =
    TREATAS (
        { ( <expression_1>, <expression_2> ) },
        table[search_column_1],
        table[search_column_2]
    )
RETURN CALCULATE (
    DISTINCT ( table[result_column] ),
    filterLookup,
    REMOVEFILTERS ( )
)

在复杂场景中，LOOKUPVALUE 的替代方法可以优化迭代器中 LOOKUPVALUE 的存在导致性能不佳的情况。

参考文档：

DAX guide - LOOKUPVALUE