从数据到信息
从信息到洞察

理解 LOOKUPVALUE

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]

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

本文隐藏内容查看价格为3G币,请先
注:加入VIP会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

14
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
4 评论数
10 被回复的评论
4 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
xifeng

对于本文的最后一个公式:
1. Cal Column1 New =
2. CALCULATE (
3. DISTINCT ( Table1[C2] ),
4. FILTER ( ALLNOBLANKROW ( Table1[C1] ), Table1[C1] = Table2[C1] ),
5. ALL ( Table2 )
6. )
其中,编号为4的那一行的Table2[C1]的计值环境是什么呢?按照我的理解,首先Table2进行行上下文转换,然后被ALL调节器清除掉了Table2的所有筛选器,然后FILTER迭代ALLNOBLANKROW ( Table1[C1] )所生成的表,对其每一行都创建了一个行上下文,所以Table1[C1]能够取对应行的值。然后问题来了,Table2的行上下文进行转换变成了筛选上下文,然后又被ALL清除掉了,而且FILTER迭代的表所创建的行上下文也没有发生转换,那么Table2[C1]既无行上下文也无筛选上下文,它是怎样确定该取哪个值的?

还是说我的理解有误,请老师指正,谢谢~

游客
tim

我是个新手,我的俩个表是多对多的,我想返回第二章表第一个符合条件的值,但是lookupvalue会出错,那我该怎么办呢

成员
lyliuyouyang

DEFINE VAR table1 = ADDCOLUMNS ( ‘人员考核’, “籍贯”, CALCULATE ( IF ( HASONEVALUE ( ‘dim籍贯'[籍贯] ), VALUES ( ‘d… 阅读更多 »

成员
lyliuyouyang

我一直在想这个lookupvalue的使用场景,觉得在dax关系模型里,好像不太用得到这个函数,直到我遇到以下需求。 我有两个表,一个产品数量表,一个产品大类表,并且通过两个表里面的产品编号创建了一对多的关系。 在实际工作中,因为产品数量表里面的具体详细细目很多,有时候分析是以这… 阅读更多 »

11111.jpg
DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数