第5章

迭 代 函 数





DAX中，所有带后缀X的聚合函数都是迭代器函数，例如COUNTX()、MAXX()、SUMX()、AVERAGEX()等。大多数带X后缀的聚合类迭代器函数的语法相似，大多为(<table>,<expression>)结构，第1个参数为表，第2个参数为表达式。与Excel通用的聚合函数相比，迭代类聚合函数是行上下文函数，它们能够识别当前行并实现逐行运算，最终聚合成一个值，迭代函数多用于度量值中。

5.1常见迭代聚合

聚合迭代函数的运算一般分以下三步来完成： 

(1) 对第1个参数的表进行运算(例如引用表、筛选表； 选择列、新增列等)。

(2) 依据行上下文条件，对运算后的参数表进行逐行迭代运算。

(3) 对运算结果进行聚合。

5.1.1COUNTX()

COUNTX()对表表达式的运算值进行条件计数(空值不计算)，语法如下： 



COUNTX(<table>,<expression>)





在DAX中，类似的函数有COUNTAX()。

对比FILTER()迭代函数及CALCULATETABLE()筛选器函数在计算列中的差别。创建两个计算列，表达式如下： 



库.B吨行数F:=COUNTX(FILTER(DK,DK［等级］="B"),DK［吨数］)



库.B吨行数C:=COUNTX(CALCULATETABLE(DK,DK［等级］="B"),DK［吨数］)





为了方便比较，隐藏了表中的其他列，返回的值如图51所示。



图51COUNTX()函数的应用(1)


FILTER()是迭代函数，是行上下文函数； 聚合函数在计算列中会忽略行上下文，而CALCULATETABLE()函数与CALCULATE()函数一样，具备上下文转换能力。唯一的区别是CALCULATE()返回的是标量值而CALCULATETABLE()返回的是表。

创建计算列库.月行数F，表达式如下： 



库.月行数F:=COUNTX(

FILTER(

DK,

RELATED(DT［月］)=9), 

DK［包装方式］

)





为了方便理解，隐藏了表中的其他列，返回的值如图52所示。



图52COUNTX()函数的应用(2)


创建计算列库.月行数C，表达式如下： 



库.月行数C:=COUNTX(

CALCULATETABLE(

DK,

RELATED(DT［月］)=9), 

DK［包装方式］

)





返回的值如图53所示。



图53COUNTX()函数的应用(3)


在DAX中，CALCULATETABLE()与CALCULATE()是仅有的两个能够创建筛选上下文的函数。在筛选上下文中，关系是自动传递的，不需要额外使用RELATED()或RELATEDTABLE()手动传递，且在计算列中涉及聚合函数的运算时，CALCULATETABLE()与CALCULATE()会将聚合函数的筛选上下文转换为计算列中的行上下文。

对上面的表达式进行修改，修改后的表达式如下： 



库.月行数C:=COUNTX(

CALCULATETABLE(DK,DT［月］=9),

DK［包装方式］

)





返回的值如图54所示。



图54COUNTX()函数的应用(4)


5.1.2MAXX()

MAXX()对表表达式的运算值按条件获取最大值(跳过空值)，语法如下： 



MAXX(<table>,<expression>)





在DAX中，类似的函数为MINX()，MINX()函数对表表达式的运算值按条件获取最小值(跳过空值)。MAXX()和MINX()只计算数字、文本、日期，不支持TRUE/FALSE值。

创建计算列库.乘积最大和库.各行乘积，表达式如下： 



库.乘积最大:=MAXX('DK','DK'［入库］*'DK'［出库］)



库.各行乘积:=CALCULATE(MAXX('DK','DK'［入库］*'DK'［出库］))





为了方便理解，隐藏了表中的其他列并对返回值进行排序，返回的值如图55所示。



图55MAXX()函数的应用


出于ABC分类管理的需要，以合同表中的产品及订单表中的数量为依据，设置A类产品的起点值(总订单数的前30%中的最小值，并且数量值大于8)。在合同表中创建计算列合.A类值，表达式如下： 



合.A类值 :=

VAR A =

MINX (

TOPN (

CALCULATE ( COUNTROWS ( '订单' ) ) * 0.3,

RELATEDTABLE ( '订单' ),

'订单'［数量］, DESC

),

'订单'［数量］

)

RETURN

IF ( A > 8, A, BLANK () )







图56MINX()函数的应用


为了方便理解，隐藏了表中的其他列并对返回值进行排序，返回的值(仅截取非空值)如图56所示。

5.1.3AVERAGEX()


平均数也称为均值，用于反映一组数据中心点所在的位置。AVERAGEX()用于对表表达式的运算值求算术平均值(空值不计算)，语法如下： 



AVERAGEX(<table>,<expression>)





在DAX中，类似的函数有MEDIANX()中位数、PERCENTILEX.EXC()分位数(不含0、1)、PERCENTILE.INC ()分位数(含0、1)、MAXX()最大值、MINX()最小值。中位数、分位数是指一组数据按升序或降序排列后，所排在中间位置的值(中位数，50%位置)或某百分位位置的值(如25%位置、75%位置)。DAX中没有众数对应的函数。

以上聚合方式的区别与联系说明： 平均数AVERAGE()/AVERAGEX()是全部数据的算术平均值，中位数MEDIAN()/MEDIANX()是处于一组数据中中间位置的值，如果数据是对称的，则平均值与中位数相等。如果数据中存在极小值MIN()/MINX()，则数据呈左偏态，中位数将大于平均值； 如果数据中存在极大值MAX()/MAXX()，则数据呈右偏态，中位数将小于平均值。中位数不受极值(极大值、极小值)影响，它是一组数据中间位置的值； 当数据呈左偏态或右偏态时，中位数的稳健性高于平均数，此种情况宜采用中位数进行分析。

在DAX中，可采用多种表达式灵活地实现同一需求。考虑到代码的优雅与易维护性，应尽量采用简洁、实用的表达式。以实现各类包装的平均入库为例，创建度量值M.均入库1、M.均入库2、M.均入库3，各表达式如下： 



M.均入库1 :=

ROUND (

AVERAGEX (

SUMMARIZE ( 'DK', 'DK'［包装方式］, 'DK'［入库］ ),

'DK'［入库］

),

2

)





或



M.均入库2 :=

ROUND (

AVERAGEX (

ADDCOLUMNS (

SELECTCOLUMNS (

DK,

"日期", ［日期］, "产品", ［产品］, "入库", ［入库］

),

"月", MONTH ( ［日期］ )

),

［入库］

),

2

) 





或



M.均入库3 :=

VAR A =

ADDCOLUMNS (

SELECTCOLUMNS (







DK,

"日期", ［日期］, "产品", ［产品］, "入库", ［入库］

),

"月", MONTH ( ［日期］ )

)

RETURN

ROUND ( AVERAGEX ( A, ［入库］ ), 2 )





创建透视表中，将包装方式拖入行标签，勾选度量值M.均入库1、M.均入库2、M.均入库3，返回的值如图57所示。



图57AVERAGEX()函数的应用


5.1.4PERCENTILEX.EXC()

PERCENTILEX.EXC()用于返回针对表中的每行计算的表达式的百分数。若要返回列中数字的百分数，应使用PERCENTILE.EXC()函数，语法如下：



PERCENTILEX.EXC(<table>, <expression>, k)





在DAX中，类似的函数有PERCENTILEX.INC ()，此函数用于返回针对表中的每行计算的表达式的百分数。函数PERCENTILE.EXC()与PERCENTILE.INC()中，EXC是exclude(不包含)单词的简写，排除k值为0和1的情况； INC是include(包含)单词的简写，包含k值为0和1的情况。

当数据呈正态分布(对称分布)时： 68%的数据处于±1Ω(标准差)的范围内，95%的数据处于±2Ω的范围内，99%的数据处于±3Ω的范围内。如果准备剔除±3Ω之外的数据，则PERCENTILE.INC()/PERCENTILEX.INC()、PERCENTILE.EXC()/PERCENTILEX.EXC()这几个函数就是很好的选择。

当数据呈左偏或右偏不对称分布时，可以采用切比雪夫不等式进行相关数据的筛选或剔除，该不等式的内容如下： 

(1) 至少有75%的数据在平均数±2Ω范围内。

(2) 至少有89%的数据在平均数±3Ω范围内。

(3) 至少有94%的数据在平均数±4Ω范围内。

至于采用单侧还是双侧拒绝域则需结合数据分布的实际情况进行判断。

创建一个查询表达式，取DK表中位于15%和85%分位的入库数据，表达式如下： 



EVALUATE

VAR A =

PERCENTILEX.EXC ( DK, DK［入库］, 0.15 )

VAR B =

PERCENTILEX.EXC ( DK, DK［入库］, 0.85 )

RETURN

FILTER ( DK, DK［入库］ >= A && DK［入库］ <= B )





返回的值及相关说明如图58所示。



图58PERCENTILEX.EXC()函数的应用(1)


数据分析时可用PERCENTILEX.INC()、PERCENTILEX.EXC()或PERCENTILE.EXC()、PERCENTILE.EXC()函数对极大值、极小值进行剔除，以便确保数据分析的准确性。

5.1.5VARX.P()

总体(population)是包含所研究的全部个体或数据的集合，有有限总体和无限总体之分。VARX.P()对表表达式的运算值按条件计算总体的方差(跳过空值)，语法如下： 



VARX.P(<table>, <expression>)





样本(sample)是从总体中抽取的一部分元素的集合，抽样的目的是根据样本提供的信息来推断总体的特征。VARX.S()函数对表表达式的运算值按条件计算样本的方差(跳过空值)。

VARX.P()函数在不涉及第1个参数的额外表运算时，VAR.P()就是它的简写，以下两个表达式的返回值相同： 



M.入库方差1:=VAR.P('DK'［入库］)



M.入库方差2:=VARX.P(DK,'DK'［入库］)





将包装方式拖入透视表的行标签，勾选以上两个度量值，返回的值如图59所示。



图59VAR.P()函数的应用


5.1.6STDEVX.P()

在对实际问题进行分析时更多地使用标准差，因为标准差有量纲且它与原始数据的计量单位相同，这是方差所不能比拟的。STDEVX.P()对表表达式的运算值按条件计算总体的标准偏差(跳过空值)，语法如下： 



STDEVX.P(<table>, <expression>)





类似的函数有STDEVX.S()，此函数用于对表表达式的运算值按条件计算样本的标准偏差。

在数据分析过程中，标准差的参照对象是平均值。创建计算列库.异常值标识，标识入库数据中的异常值，表达式如下： 



库.异常值标识 :=

VAR A =

AVERAGE ( DK［入库］ )

VAR B =

STDEVX.P ( DK, DK［入库］ )

RETURN

IF (

DK［入库］ >= A + 1 * B

|| DK［入库］ <= A - 1 * B,

"异常值",

BLANK ()

) 







图510STDEVX.P()函数的应用


为了方便理解，隐藏了表中的其他列，如图510所示。

数据分析时可用STDEVX.P()、STDEVX.S()或STDEV.P()、STDEV.S()函数对指定标准差之外的数据进行剔除，确保数据分析的准确性。

5.1.7RANKX（）


RANKX()用于对某列表中的数字进行排名。第1个和第2个参数为必选参数，其他为可选参数，语法如下： 



RANKX(

<table>, 　　//要排序的静态物理表或动态虚拟表

<expression>//排序依据的表达式

［, <value>　　//修改需要排序的内容，通常情况下无须使用

［, <order>//默认为降序(DESC、0或FALSE);升序为ASC、1或TRUE

［, <ties>］］］//DENSE(稠密排名)或SKIP(稀疏排名)

)  





1. 计算列中排序

创建计算列库.入库降序和库.入库升序，表达式如下： 



库.入库降序:=RANKX(DK,DK［入库］)



库.入库升序:=RANKX(DK,DK［入库］,,ASC)





为了方便理解返回的值，隐藏了表中其他不必要的列，并对入库列进行了升序排列，返回的值如图511所示。



图511RANKX()函数的应用(1)


2. 多重排名分析

创建度量值M.产品组内排名、M.产品组间排名，表达式如下： 



M.产品组内排名:=

IF(

HASONEVALUE(DK［产品］),

RANKX(ALL(DK［产品］),［M.入库量］),//ALL()函数用于移除筛选，扩大上下文

BLANK()

)



M.产品组间排名:=IF(







HASONEVALUE(DK［产品］),

RANKX(ALL(DK［包装方式］,DK［产品］),［M.入库量］),

BLANK()

)





创建透视表，将DK表中的包装方式、产品分别拖入行区域，勾选度量值M.入库量、M.产品组内排名、M.产品组间排名，返回的值如图512所示。



图512RANKX()函数的应用(2)


3. 第2个参数为聚合函数时

创建度量值M.排序1和M.排序2，第2个参数为聚合函数的表达式，表达式如下： 



M.排序1:=RANKX(DK,SUM(DK［入库］))



M.排序2:=RANKX(FILTER(DK,DK［等级］="A"),SUM(DK［入库］))







图513RANKX()函数的应用(3)


创建透视表，将包装方式拖入行值，勾选M.排序1和M.排序2这两个度量值，返回的值如图513所示。

如图513所示，返回的值不是期望的值，继续新增度量值，表达式如下： 



M.排序3:=RANKX(DK,CALCULATE(SUM(DK［入库］)))



M.排序4:=RANKX(DK,CALCULATE(SUM(DK［入库］)),,ASC)



M.排序5:=RANKX(FILTER(DK,DK［等级］="A"),CALCULATE(SUM(DK［入库］)),,ASC)





在以上度量值M.排序3～5的聚合值外面嵌套CALCULATE()，将行上下文转换为筛选上下文。将新增的度量值继续拖入透视表中，如图514所示。



图514RANKX()函数的应用(4)


在图514中，M.排序3的显示结果仍不是期望值，而度量M.排序4～5中显示的值发生了变化。这是因为RANKX()函数的特点： 在(默认的)降序排序中，聚合列的排序序号永远是1，而在升序排序中，聚合列的排序序号则是当前排序列中最大排序号加1。

5.2SUMX()迭代函数

SUMX()是使用频率很高的一个函数，用于对表中的每行计算表达式进行求和运算，语法如下： 



SUMX(<table>, <expression>)





SUMX()函数仅对列中的数字进行计数。空白、逻辑值和文本会被忽略。函数中第1个参数的表可为静态物理表，也可为动态虚拟表。

5.2.1第1个参数(表)
1. 直接整表引用

在DK表中，新建计算列库.数量和，表达式如下： 



库.数量和:=SUMX('DK','DK'［入库］)





在DAX中，以上表达其实是SUM('DK'［入库］)的简写，返回的值如图515所示。



图515SUMX()函数的应用(1)


在计算列中，聚合函数SUMX()不受行上下文影响，每行的值都是整列的聚合值。

2. 通过FILTER()多条件筛选引用

在DK表中，新建计算列库.筛选，首先获取入库>70且入库>出库*1.5的动态虚拟表，然后对表中的入库与出库数据进行相乘相加，表达式如下： 



库.相乘相加F:=

SUMX (

FILTER (

'DK',

'DK'［入库］ >70

&& 'DK'［入库］> 'DK'［出库］ * 1.5

),

'DK'［入库］ * 'DK'［出库］

)





返回的值如图516所示。



图516SUMX()函数的应用(2)


通过对比图515及图516可看出： 在计算列中，聚合类迭代函数同样不受行上下文影响。在DAX中，CALCULATE()和CALCULATETABLE()可改变上下文。在DK表中，新建计算列库.乘加CF，表达式如下： 



库.乘加CF :=

CALCULATE (

SUMX (

FILTER (

'DK',

'DK'［入库］ > 70

&& 'DK'［入库］ > 'DK'［出库］ * 1.5

),

'DK'［入库］ * 'DK'［出库］

)

)





返回的值如图517所示。



图517SUMX()函数的应用(3)


图517中库.乘加CF列所返回的值由于发生了上下文转变，所以每行的值均为各行对应的入库*出库的值，所有符合条件的值相加(2660+2871+3906+2808)后的总值为12245。

创建度量值(M.相乘相加F)，表达式如下： 



M.相乘相加F:=

SUMX (

FILTER (

'DK',

'DK'［入库］ >70

&& 'DK'［入库］> 'DK'［出库］ * 1.5

),

'DK'［入库］ * 'DK'［出库］

)







图518SUMX()函数的应用(4)


创建透视表，将产品列拖入行标签，勾选M.相乘相加F度量值，如图518所示。

图518中的值是图517中库.乘加CF列的值分类汇总，能对应上。度量值的应用原理与说明将在第6章详细讲解。本章后续各迭代函数的表达式将主要采用度量值方式进行讲解。

5.2.2第2个参数(条件表达式)


在SUMX()中，第1个参数的表可为动态虚拟表，第2个参数可以为复杂的条件表达式。在上面表达式的基础上，对于入库量大于70的将按0.9的倍数与出库量相乘相加，表达式如下： 



M.乘加FI :=

SUMX (

FILTER (

'DK',

'DK'［入库］ > 70

&& 'DK'［入库］ > 'DK'［出库］ * 1.5

),

IF (

'DK'［入库］ >= 70,

'DK'［入库］ * 0.9,

'DK'［入库］

) * 'DK'［出库］

)







图519SUMX()函数的应用(5)


在图518的透视表中，勾选新建的M.乘加FI度量值，如图519所示。

图519中，M.乘加FI=M.相乘相加F*0.9。通过图518及图519不难发现，DAX很容易实现各类个性化的数据分析。

5.2.3SUMX()综合应用

以下是SUMX()+FILTER()的一些条件运算的拓展应用举例，FILTER()的条件可以来自单一的表，也可以来自数据模型中的关联表，甚至可以是两个多对多的表。

1. 同一表内数据的条件求和

创建度量值M.入库F，统计DK表中包装方式为箱装和桶装的入库量，表达式如下： 



M.入库F :=

SUMX (

FILTER (

'DK',

'DK'［包装方式］ IN { "箱装", "桶装" }

),

'DK'［入库］

) 





在图519的透视表中，勾选新建的M.入库F，如图520所示。



图520SUMX()函数的应用(6)


在上面表达式的基础上，在FILTER()原有条件表达式的基础上增加指定产品的要求，表达式如下： 



M.入库 F&:=SUMX (

FILTER ( 

'DK', 

'DK'［包装方式］ IN { "箱装", "桶装" }

&& 'DK'［产品］ IN { "蛋糕纸", "钢化膜" , "净化剂" }

),

'DK'［入库］

)





在图520的透视表中，勾选新建的M.入库F&，返回的值如图521所示。



图521SUMX()函数的应用(7)


2. 表间关联值条件求和

从运单表中获取指定的包装方式和产品，匹配到装货表中的包装单位，对克与吨为单位的产品进行单位转换(转换为千克)，然后对产品求积求和，表达式如下： 



M.质量FV :=

SUMX (

FILTER (

'运单',

'运单'［包装方式］

IN { "箱装", "桶装", "散装", "扎" }

&& '运单'［产品］ IN { "蛋糕纸", "钢化膜", "净化剂", "包装绳" }

),

VAR A =

RELATED ( '装货'［单位］ )

VAR B =

SWITCH ( TRUE (), A = "克", 0.001, A = "千克", 1, A = "吨", 1000 )

RETURN

B * '运单'［数量］* RELATED ( '装货'［质量］ )

)





返回的值如图522所示。



图522SUMX()函数的应用(8)


3. 无关系数据的条件求和

现有DK、DT、DQ三张表，完整的数据如图523所示。



图523SUMX()函数的应用(9)


在DOCK.xlsx工作簿的DK与DT表之间创建数据模型及表间关系，其中DT表位于一端，DK表位置多端，如图524所示。



图524数据模型说明


在DQ表中创建计算列季.入库量，引用DK表中的入库数据进行求和，表达式如下： 



季.入库量 :=

SUMX (

FILTER (

DK,

'DQ'［月］= RELATED ( DT［月］ )

),

'DK'［入库］

)





在上述表达式中，通过SUMX()+FILTER()实现无关系数据的获取与求和，返回的值如图525所示。



图525SUMX()函数的应用(10)


5.3当前行
5.3.1EARLIER()

EARLIER()可理解为当前行，按照当前行进行逐行扫描运算。在Excel 2016及以后版本的Power BI中，EARLIER()函数已逐渐被VAR变量所取代，语法如下： 



EARLIER(<column>, <number>)





新建计算列，对DK表中入库列的数据进行累加，表达式如下： 



库.入库累加 :=

SUMX (

FILTER (

DK,

DK［入库］<= EARLIER ( DK［入库］ )

),

DK［入库］

)





为了方便观察，先对入库列数据进行升序排列并隐藏其他不相关的数据列，返回的值如图526所示。



图526EARLIER()函数的应用(1)


创建计算列库.入库排名，表达式如下： 



库.入库排名:=

COUNTROWS (

FILTER (

'DK',

'DK'［入库］ < EARLIER ( 'DK'［入库］)

)

)+1





返回的值如图527所示。



图527EARLIER()函数的应用(2)


创建计算列库.包装累加，表达式如下： 



库.包装累加:=

SUMX (

FILTER (

'DK',

'DK'［包装方式］ <= EARLIER ( 'DK'［包装方式］)



),

'DK'［入库］

)





为了方便观察，先对包装方式列数据进行升序排列，返回的值如图528所示。



图528EARLIER()函数的应用(3)


创建计算列库.包装等级累加，表达式如下： 



库.包装等级累加 :=

SUMX (

FILTER (

'DK',

'DK'［包装方式］ <= EARLIER ( 'DK'［包装方式］ )

&& 'DK'［等级］ = EARLIER ( 'DK'［等级］ )

),

'DK'［入库］

)





返回的值如图529所示。



图529EARLIER()函数的应用(4)


5.3.2VAR变量

用VAR变量来嵌套两个不同的上下文，具有比EARLIER()更灵活直观的上下文嵌套能力。

在DK表中，创建计算列库.入库累加A，表达式如下： 



库.入库累加A:=

VAR A= DK［入库］ RETURN

SUMX (

FILTER (

DK,

DK［入库］<= A ),

DK［入库］

)





在DK表中，创建计算列库.入库排名A，表达式如下： 



库.入库排名A:=

VAR A= 'DK'［入库］ RETURN

COUNTROWS (

FILTER (

'DK',

'DK'［入库］ < A)

)+1





在表中继续创建计算列库.包装累加A，表达式如下： 



库.包装累加A:=

VAR A= 'DK'［包装方式］ RETURN

SUMX (

FILTER (

'DK',

'DK'［包装方式］ <= A  ),

'DK'［入库］

)





继续新增计算列库.包装等级累加A，表达式如下： 



库.包装等级累加A :=

VAR A= 'DK'［包装方式］

VAR B= 'DK'［等级］

RETURN

SUMX (

FILTER (

'DK',

'DK'［包装方式］ <= A

&& 'DK'［等级］ = B

),

'DK'［入库］

)





返回的值如图530所示。



图530VAR变量的应用


5.4CONCATENATEX（）

CONCATENATEX()函数用于文本迭代，该函数有两个必选参数，语法如下： 



CONCATENATEX(

<table>, 

<expression>

［, <delimiter>

［, <orderBy_expression>

［, <order>］］...］

)  





利用CONCATENATEX()函数创建度量值，表达式如下： 



M.文本串接:=

CONCATENATEX (

DISTINCT ( 'DK'［包装方式］ ),







'DK'［包装方式］,

"、"

)





创建透视表，将产品拖入行标签，勾选M.文本串接度量值，返回的值如图531所示。



图531CONCATENATEX ()函数的应用(1)


结合HASONEVALUE()函数，创建度量值M.产品名串接，表达式如下： 



M.产品名串接:=IF(HASONEVALUE('DK'［包装方式］),

CONCATENATEX(VALUES('DK'［产品］),

'DK'［产品］,

" 、 ")

 )





在DAX中，HASONEVALUE()返回的值为TRUE或FALSE，常用于IF()表达式的第1个参数。创建透视表，将包装方式拖入行标签，勾选M.产品名串接度量值，返回的值如图532所示。



图532CONCATENATEX ()函数的应用(2)


5.5本章回顾

本章主要对SUMX()、RANKX()、AVERAGEX()、MAXX()、CONCATENATX()等迭代聚合函数的用法及其背后的数理统计知识进行了深入介绍。











第三篇






强化篇