DAX连接表系列 (四)VAR变量(第1部分)
(一) 迟到的说明
在本系列之前,先啰嗦几句,其实在编写这个系列前,就应该先需要一个说明:
DAX所在数据模型里存在三大引擎(也不确定,但已足够):压缩引擎、存储引擎、公式引擎。一般我们先不关心压缩引擎(不可控,只能做数据优化等来适应它)。
另外两个引擎的顺序是先由存储引擎依据定义,在内存中缓存出存储数据(有一个专门的名称叫做“物化”),然后公式引擎引用该存储数据进行计算。换一种方式(DAX总是可以使用各种方式来理解)来说,所有“物化”后由公式请求引用的存储数据(也对应于DAX的两大步骤:筛选+计算的最简单有力、最恰当的解释)可能有两种情况:
(1)只针对同一个表(无论一列或多列);
(2)针对关系连接的多个列表(两个或多个关系列表)。
实际上,99.9%的情况是这样。就这么简单!
第一种情况是我们最乐于见到的、也是最熟悉的,因为它不需要考虑关系,就像在Excel里的一个Sheet表数据区域里工作一样。如果还需要其他sheet表的内容,那么,就需要使用VLOOKUP引用(该引用其实就存在共同的“关系列”)。
我们对于DAX关系列表的最好理解方式是:参考最简单的VLOOKUP函数,而不必是复杂的关系型数据库理论。问题来了,既然前面第一种情况是我们最乐于见到的。那么,无论是创建数据模型,还是定义DAX,所有这些都在有意无意的趋向于用一个表的思维方式方法来理解,这包括:
我们前面所说的扩展表、“合纵连横”的宽表、列表集合函数定义的列表子集、以及添加参数表、变量、Power BI的新表等等。
它们都有一个共同的目标:通过这些列表方式,构建一个计算列表。所以,我们把这些列表“物化”的方式,统统可以使用一个定义:列表的连接或连接表。当然,仅仅只是一种方式而已。
我们接下来要说的是列表方式之一的变量。
(二) 何为VAR变量
什么是VAR变量?
VAR变量的语法非常简单,命名一个DAX代码的表达式,给个名称即可。
依据DAX引擎原理,变量的实际是一个存储数据(内存虚拟数据),存在于公式引擎之前,并能被公式引用的数据。
当变量被赋值后,即在被公式引用后的执行过程中,该值不会改变。基于此,用传统的编程语言环境来理解它,变量的作用更像是常量,而不是正则变量。
注意:该部分内容主要参考官方的内容,并做了进一步的简体、整理,集中做汇总性专题介绍。
能使用的版本
在DAX中,VAR变量(Variables)还是一个相对较新的特征,它可以在以下版本中使用。
• Power BI Desktop
• Excel 2016
• SSAS Tabular 2016
注意:Variables变量不能在Excel 2013或Excel 2010中使用。
基本语法
(1)我们通常将变量称为 “VAR语法”。它总是与两个新的关键字 VAR 和RETURN一起使用,这也是将VAR变量用于DAX公式的典型写法。 你必须始终先使用VAR定义,然后使用RETURN--返回来编写一个有效的、完整的、包含变量的DAX公式:
myFormula = VAR = VariableName1 = 变量1 // 一个有效的DAX公式
VAR = VariableName2 = 变量2 // 其他有效的DAX公式
RETURN < 包含variablename 1…variablename 2> --另一个可以使用variablename 1和variablename 2作为表达式的一部分的有效DAX公式。
(2)可以定义为变量(VAR)的有:标量值、值列表、定义列表的函数式等,例如:
VAR myConstantValue = 5 -- 标量值
VAR myFunction = MAX(Customer[Sales]) -- 值列表
VAR myTable = FILTER(Sales,Customer[Sales] >50) - -列表
(3)以下是准官方关于VAR的解释:
(三) VAR变量在DAX里的运用
使用VAR
虽然VAR变量是 DAX 2015 中的一个新功能,而且2015版本的 DAX 语言也有许多新的功能,但没有一个像变量VAR那样能改变DAX规则。
变量由关键字VAR来引入要定义的变量。你可以在单个表达式中放置所需的任意多个变量,并且每一个都可以有它自己的 VAR 定义。然后,使用RETURN关键字定义要作为结果返回的表达式。在RETURN表达式中,可以使用由变量替换的计算值。然而,还有很多关于变量的知识。
首先,变量的主要功能是使 DAX 代码更容易编写; 以及变量能大大提高代码的可读性和可重复使用性,语法也非常简单。如下面的示例所示:
[Growth%]:= VAR CurrentSales: = SUM(Sales[Quantity])
VAR SalesLastYear = CALCULATE ( SUM ( Sales[Quantity] ),
SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
IF ( AND ( CurrentSales <> 0, SalesLastYear <> 0 ),
DIVIDE ( CurrentSales - SalesLastYear, SalesLastYear))
从上一个示例中,你已经可以理解到代码可读性方面的优势。通过将表达式分配一个名称,以便代码可以更简单地读取和维护。
其次,可以各种方式来定义变量,并将其用于度量值、计算列或查询中。
例如:以下方式可以定义为变量:
(1)标量值-变量
VAR myScalarValue = SUM(Sales[Extended Amount])
(2)列表--变量
VAR myTable = FILTER(Customer,Customer[Post Code] = 50210)
上面的变量myTable返回了一个虚拟表,它可以在DAX的任何位置使用,例如在DAX公式中使用的表,以及CALCULATE函数中的筛选器等。
(3)其他要点
• 每一个变量在公式的RETURN 部分开始之前被评估一次;
• 变量里可以包含其他变量;
• 变量在初始计算筛选(列表筛选和行筛选)中执行;
• 变量计算的结果将被缓存(作为存储数据),以便在公式中重复调用;
• 变量被赋值后,在被公式引用的执行过程中,该值不能改变。这时,它更像一个常量。
(4)优势(好处)
被赋予名称的变量其实可以理解为存储数据里添加了“新增列表”(物化的列表),既然这样,何不直接使用度量值、计算字段名称?因为这些也都是可以再次被DAX公式引用,且与定义变量是一样的方式。那么,为何还要定义VAR变量的名称列表?
答案是:除了度量值、计算列或查询,使用VAR语法来构建DAX,还有其他很多好处,特别是当你需要:定义“当前行”时。我们总结以下:
1)公式可以更容易编写。一开始可能需要花更多的时间来写这个公式,但是最终可能会节省时间,通过更快的找到公式的工作版本,减少重复工作并节省时间;
2)公式可以更容易读懂。读者可以一步一步地理解公式的逻辑,从而更容易确定公式的设计目的。当使用注释、新行和空格时更是如此;
3)公式需要更改时,可以更容易维护;
4)公式中的变量可能更利于提升性能。变量可以将重复子字符串一次性写入,然后可多次使用。这对公式编写和性能都有好处(尽管在此语法之前,DAX已经有一些好的缓存特性);
5)公式中的变量可以替代EARLIER函数(当在复杂公式中嵌套行筛选时)。因为定义的变量始终在初始筛选器中执行,这种语法可以更容易的理解DAX当前行的概念。
实际运用中,比如:
1)使用VAR变量语法,替换DAX的某些筛选条件,来简化DAX代码;
2)使用VAR变量命名,定义一张虚拟的列表或表(指定某个物化的一个或多个列表);
3)使用VAR变量命名,定义需要的值列表,并分别放置于不同的计算筛选环境里来构建DAX的一个个逻辑条件(比如类似于EARLIER()函数的“当前行”),然后将这些条件按条件步骤组装成一个复杂的计算逻辑条件。
VAR定义方式
我们将前面提到的实际运用中的三点分别举例:
1)使用VAR变量语法,替换DAX的某些筛选条件,来简化DAX代码;
这与引用中间度量值作用是一样的。使用变量的目的是:由于其中的中间度量值很长,或者使用很频繁。比如,假如在一个DAX计算式中,SUM('Sales'[销售额]) 这个表达式多处需要引用,这时候:可以使用度量值名称定义它:比如“当前销售额”:
使用VAR变量来命名它,之后在其后的公式中引用该变量名称。比如使用该VAR参与计算同比、环比、累计比等。
依据 VAR语法,首先用 VAR定义该DAX表达式,然后使用RETURN 取出来,在后续公式中引用这段VAR。
=VAR Sales_Fist = SUM('Sales'[销售额]) // 定义一个函数计算式变量
RETURN // 取出变量
年初至今销售=TOTALYTD(Sales_Fist,Dates[Date]) // 作为度量计算的参数。
一般情况下,很少将一个度量值名直接指定为VAR变量,因为度量值字段也可以直接被公式重复引用。注意:最好不要直接将度量定义为变量。后面会进一步说明。
如果已经先定义了一个:当前销售 = SUM('Sales'[销售额]) 的[当前销售]度量,可以将筛选条件定义为VAR变量:
=VAR YTD_T= TOTALYTD ('Dates'[Date]) //定义一个筛选列表
RETURN // 取出变量
年初至今销售=CALCULATE([当前销售], YTD_T) //作为度量的列表筛选条件。
2)使用VAR变量命名,定义一张虚拟的列表或表(指定某个物化的列表);即变量可以定义为表(一个或多个列表的表)。
我们知道,列表间的并、交、补集合方式,在DAX里计算比较频繁,它需要CALCULATETABLE、FILTER等表函数或者UNION、CROSSJOIN等集合表函数定义的列表,并将其结果表格作为复杂计算的参数。
同理,依据 VAR语法,首先用 VAR定义该DAX表达式(表),然后使用RETURN 取出来,并在后续公式中引用这段VAR。
比如,有一个包含客户表、销售表、时期表的数据模型,要计算最近时期有销售的老客户数(条件:当前最近时期前都有销售),它涉及三个关系表中的客户列、销售列以及时期列三列。
= VAR Cur_Table =CALCULATETABLE( ‘客户表’ ,’销售表‘) // 所有时期所有客户表
= VAR Per_Table =CALCULATETABLE( ‘客户表’ ,
CALCULATETABLE( ’销售表‘ ,
FILTER( ‘时期表’, ‘时期表’[时期]<=MIN( ‘时期表’[时期]))) // 最初时期前有销售的
RETURN
CALCULATE( COUNTROWS(EXCEPT( Cur_Table, Per_Table))) // 差集部分
我们用VAR对客户表中的客户表、老客户子集表分别使用VAR变量进行定义。然后利用两个表的补集(EXCEPT)计算。
注:可以参考之前的关于新、老客户的VAR变量结合交、并、补集函数 的公式。
再例如,你还可以定义一个类似如下的表达式:
[RedSalesLastYear] :=
VAR RedProducts = FILTER (ALL ( Product[Color] ), Product[Color] = "Red" )
VAR LastYear = SAMEPERIODLASTYEAR ( 'Date'[Date] )
RETURN
CALCULATE ( SUM ( Sales[Quantity] ),RedProducts,LastYear )
在此示例中,RedProducts 以及LastYear是表,而不是标量值。
3)使用VAR变量命名,定义需要的值列表,并分别放置于不同的计算筛选环境里来构建DAX的逻辑条件(比如类似于EARLIER()函数的“当前行”)。即也可以使用标量值来定义变量,其语法或用法与定义为表没有区别:
例如之前的:VAR myScalarValue = SUM(Sales[Extended Amount])
4)其他注意以及方式运用
另一个可能不明显的一点是:变量可以是表达式的一部分;而且无论在表达式的任何位置,都可以定义变量;
补充:你可以使用以下语法在 DAX 表达式的任意位置定义一个变量:
VAR NAME = RETURNED VALUE
还有,变量可以是任何数据类型(数值、布尔值、时期、文本),也包括整个表。
请记住,每次在 DAX 表达式中引用变量时,Power BI 必须根据定义重新计算它的值。 因此,在函数中尽量避免使用重复的变量。
此外,DAX在变量定义的筛选中定义变量,而不是在使用变量的筛选中定义变量。例如,假设你要检索Sales--销售表中的所有products--产品的列表,它们分别占总销售额的1% 以上的产品列表。在标准 DAX,变量之前,你必须写:
EVALUATE
ADDCOLUMNS ( FILTER ( VALUES ( Product[Product Name] ),
[SalesAmount]>= CALCULATE ( [SalesAmount],ALL ( Product ) ) * 0.01 ),
"SalesOfProduct",[SalesAmount])
通过利用内部CALCULATE中ALL ( Product ),可以将当前产品的销售额与所有产品的销售额的1% 进行比较。结果是四种不同颜色的产品以及度量结果:
这个公式以及所有遵循类似模式的公式的问题是,一旦在产品外部放置任何筛选器,例如通过CALCULATETABLE定义的外部表,它就会停止工作。例如,以下查询将返回销售超过1% 的所有产品中的黑色产品,而不是销售超过1% 的产品。
EVALUATE
CALCULATETABLE ( ADDCOLUMNS (
FILTER (VALUES ( Product[Product Name] ),
[SalesAmount] >= CALCULATE ( [SalesAmount],ALL ( Product ) ) * 0.01 ),
"SalesOfProduct",[SalesAmount] ), Product[Color] = "Black")
结果是单行,前一个图中的一行产品:
通过使用变量,无论外部筛选器如何,查询都将变得更容易编写和工作:
EVALUATE
CALCULATETABLE ( ADDCOLUMNS (
VAR OnePercentOfSales = [SalesAmount] * 0.01
RETURN
FILTER (VALUES ( Product[Product Name] ),
[SalesAmount] >= OnePercentOfSales ), "SalesOfProduct",[SalesAmount]),
Product[Color] = "Black")
现在的结果是预期的一个值:即第5 行的黑色产品。
这是值得花上几分钟阅读的公式,因为它显示了变量的独特功能。变量 OnePercentOfSales 在 ADDCOLUMNS 中被定义。因此,它在ADDCOLUMNS的计算筛选 ( 即黑色产品的筛选器)中评估的。因此,1%的销售额也就是黑色产品的价值。
一旦变量被评估,开始迭代产品表,在迭代循环中,计算 [SalesAmount],并返回当前产品的销售额,而 OnePercentOfSales 则返回1% 销售的黑色产品。因为避免了在迭代中使用ALL和修改当前筛选器,所以,不管使用 CALCULATETABLE 设置了什么外部筛选器,后一种公式都有效。换句话说,变量能访问外部筛选器,这可能是一个经验丰富的 DAX 程序员最需要的功能。
补充说明:因为变量实际是一个存储数据,既可以关联外部压缩数据,也可以被公式引用。即压缩数据-存储数据-公式计算数据。
当然,你应该不限于在度量值或查询中使用变量,它们也能很好的用于计算列。事实上,一旦你开始使用变量,就可以完全摆脱EARLIER函数的约束。例如,要计算比当前价格高的产品数量,以前必须编写如下所示的计算列:
Product[ListPriceRankDense] =COUNTROWS (
FILTER (VALUES ( Product[Unit Price] ),
Product[Unit Price] > EARLIER ( Product[Unit Price] ) )) + 1
EARLIER可能是 DAX 中最令人费解的函数功能,许多人觉得很难使用它,也许是因为其名字含义更像是OUTER --“外部的”,而不是EARLIER --“更早的”。通过使用变量,相同的表达式变得更加清晰和易于编写:
Product[ListPriceRankDense] = VAR CurrentPrice = Product[Unit Price]
RETURN
COUNTROWS (
FILTER (VALUES ( Product[Unit Price] ), Product[Unit Price] > CurrentPrice ) ) + 1
即使在这个表达式中,你也可以理解为:变量是在筛选器外部单独计算的。因此,它评估当前产品的价格后,再在筛选器里面使用它时,它已经具有一个定义的当前值。所以,在此表达式中不再需要EARLIER版本。
最后,变量使得复杂子表达式被单个计算替代,只要存在需要多次评估的相同子表达式时,使用变量作为DAX 优化器,来保证计算只发生一次,从而产生更快的代码。
再次强调:因为变量实际是一个存储数据,所以只需要存储一次结果值,就可以被公式引擎重复利用。
基于此,同时也需要注意:最好不要将度量或基表直接定义为变量,因为,可能造成变量定义的存储表与公式引擎引用的原表存储表重复,而出现错误。这不绝对,关键是检查物化的存储数据否冲突。
例如考虑之前定义的:VAR myScalarValue = SUM(Sales[Extended Amount])
不能放置在CALCULATE的第一个参数里(计算列表):CALCULATE(myScalarValue),因为如果将变量作为CALCULATE的第一参数--计算式。那么,公式引擎只能引用该变量数据计算(存储数据),计算的总是一个固定的、由变量定义的值,而无论后面是怎样的筛选器(因为第一参数的任何筛选器,其筛选指向的列表都有可能是变量定义的计算列,也因为计算列表始终需要最后回到原列表计算)。