上个月偶然看到消息,说微软发布了一个新的函数XLOOKUP,作为VLOOKUP的接班人。当然VLOOKUP和HLOOKUP还会继续被Excel支持的。XLOOKUP和VLOOKUP语法上来说挺像的,实现的功能也类似,不过更简单更不容易出错。目前还没有开放给所有人,慢慢来。
所以我就想先写个DAX基础,一起来研究一下LOOKUPVALUE,应应景。应该挺有意思的。也很简单。
首先先简单说下EXCEL里的VLOOKUP。然后再重点讲下DAX里面的LOOKUPVALUE函数。
已经会EXCEL里的VLOOKUP和HLOOKUP的可以从下一部分DAX里面的VLOOKUPVALUE开始看。
因为做过Finance的技术支持,本着做一行爱一行的原则,略学过EXCEL,不算专家但也还凑合着够用。VLOOKUP就是在列范围内查找内容,HLOOKUP是在行的范围内查找内容。
简单举个例子如下,有一个数据源Data,里面有两个Sheet页面,一个是Sales(销量),一个是Product(产品).
销量表
产品表
在销量表里,根据相关的ProductID找到相应的Product名称。
公式如下:
=VLOOKUP($A2,Product!$A$2:$F$1246,2,FALSE)
第一个变量,是要查找的数据,在下面的例子里面,就是我要查找ProductID是560的ProductName。第二个变量,要查的字段在哪里查,即查找范围,就是在Product sheet页的表中,在EXCEL里的表达是Product!$A$2:$F$1246 第三个变量是我要显示第几个字段作为结果,在PRODUCT表中,我想显示PRODUCTNAME,在product表中第二个。最后一个我想精确匹配,所以FALSE。
现在开始说下DAX里面的LOOKUPVALUE函数。
LOOKUPVALUE函数返回来自于result_columnName的一个值,这个值是search_columnName里面满足search_value条件的值。
语法:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])
er,看完觉得和我本来想的那个EXCEL里面的VLOOKUP完全不相关啊。好吧,都写了这么多字了,还是继续吧。
我想了想,对于已经建好关系的星型模型,用LOOKUPVALUE这个函数实现一个很有趣的例子,真的想不出来。不过这里需要注意search_columnName和result_columnName不能是表达式。
随便举个例子吧。有一张表,显示如下信息:
随便新建一个Calculated Column,表达式如下:
Column = LOOKUPVALUE(Customer[Gender],Customer[Education],"Bac")
结果如下:
因为我的“Bachelors”没有写完整,所以没有找到。如果我把表达式修改一下:
Column = LOOKUPVALUE(Customer[Gender],Customer[Education],"Bachelors")
会报错,因为对于这样的筛选,出现了两个不一样的结果。
说点延展的东西吧。
我上面写的这个例子是上个月的时候,我随便一时兴起写的。总觉得写得不好,所以就只在公众号上发了,其他的都没有贴。
今天把这个继续拓展一下。
LOOKUPVALUE( 你想得到的目标表的结果列, 在目标表中的查询列, 需要查询的内容)这些不难理解。
怎么把LOOKUPVALUE在DAX中用的和Excel中的VLOOKUP一样呢。其实就是结合变量一起。
举个例子。我有如下几张表。其中AR Balance表是一个AR相关的数据事实表。其他几张都是维度属性描述表。
我需要通过AR Balance中的Cost Centre来找到BA Mapping里面的BA-Area,如果找不到,就用Customer number来找BAG Key Acct里面的BAG Key Account。
你可以想星型模型什么的,但是如果数据质量不太好的话,比如我当前的数据都是来自Excel,并且如果要是做关联,就会出现多对对的错误的时候,推荐使用LOOKUPVALUE。
不需要建立任何模型关系。直接使用LOOKUPVALUE这个函数就可以了。
具体的内容如下:
BA =
VAR c_ct = 'AR Balance 201909 Raw Data'[Cost Centre]
VAR cust = 'AR Balance 201909 Raw Data'[Customer number]
Return
if(
LOOKUPVALUE('BA Mapping'[BA-Area],'BA Mapping'[Old cost center],c_ct,"null") = "null",
LOOKUPVALUE('BAG Key Acct'[BAG Key Account],'BAG Key Acct'[Company ID],cust,"NA"),
LOOKUPVALUE('BA Mapping'[BA-need],'BA Mapping'[Old cost center],c_ct,"null")
)
定义两个变量,然后在表中逐项查找当前内容,然后返回结果。所以不会建模也没有关系啊。你会用LOOKUPVALUE就可以了。
很简单吧。
DAX这个东西,大家一看上就觉得好难啊。其实和Excel差不多的。你可以写的好Excel,这个东西也不会难多少的。很多东西都是相通的。
欢迎关注转发我的公众号内容。喜欢您来(话说突然想到这个了M)