前面已经分两次介绍了DAX中的calculate函数,
这应该是DAX中最重要的函数了:Calculate - Power Excel
DAX中的Calcualte函数虽然复杂,但是还是有章可循的 - Power Excel
今天我们继续说一说calculate函数的筛选器参数。
CALCULATE函数的语法如下:
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
其中的filter1,filter2,......,都是筛选器参数。
我们上次介绍过,这个参数尽管可以写作条件的形式,比如:
'Product'[BrandName] = "Contoso"
但是实际上,DAX引擎会将这个语句转换为一个FILTER公式,从而返回一个表,
FILTER(
ALL('Product'[BrandName]),
'Product'[BrandName] = "Contoso"
)
这个参数中可以使用多个条件,比如,
Contoso And Litware Sales Amount =
CALCULATE(
[Sales Amount],
'Product'[BrandName] = "Contoso" || 'Product'[BrandName] = "Litware"
)
这个度量值中,计算品牌为Contoso和Litware的产品销售额的合计。
这个语法等价的复杂形式与之前介绍的一样,
FILTER(
ALL('Product'[BrandName]),
'Product'[BrandName] = "Contoso" || 'Product'[BrandName] = "Litware"
)
如果要对不同列进行筛选,比如,品牌名称是Contoso,类别是Deluxe,也可以这么写,
Contoso Or Deluxe Sales Amount =
CALCULATE(
[Sales Amount],
'Product'[BrandName] = "Contoso" || 'Product'[ClassName] = "Deluxe"
)
注意,在以前的DAX版本中,这是无效的语法。但是经过多次迭代后,现在语法允许这么写的。
这种语法等价的复杂语法是这样的:
Contoso Or Deluxe Sales Amount 1 =
CALCULATE(
[Sales Amount],
FILTER(
ALL('Product'[BrandName], 'Product'[ClassName]),
'Product'[BrandName] = "Contoso" || 'Product'[ClassName] = "Deluxe"
)
)
要想在计算度量值时不覆盖外部筛选器上下文,可以使用KEEPFILTERS函数,
Contoso Or Deluxe Sales Amount 3 =
CALCULATE(
[Sales Amount],
KEEPFILTERS('Product'[BrandName] = "Contoso" || 'Product'[ClassName] = "Deluxe")
)
三种写法的对比如下:
后一种写法也可以不使用用KEEPFILTERS,而是筛选Product表,
Contoso Or Deluxe Sales Amount 4 =
CALCULATE(
[Sales Amount],
FILTER(
'Product',
'Product'[BrandName] = "Contoso" || 'Product'[ClassName] = "Deluxe"
)
)
顺便提一下,上一次讲完后,有人留言问了个问题:后一种筛选product的写法和keepfilter的写法哪一个效率高?
当然是keepfilter。因为涉及的数据行数更少,而筛选整个表涉及的行数更多。
在使用多个筛选条件(无论是同一列还是不同列)时,都有一个逻辑运算的问题。我们前面的例子中始终都是“或”,使用的运算符是“||”,比如,
'Product'[BrandName] = "Contoso" || 'Product'[ClassName] = "Deluxe"
当然,也有很多场景需要使用“且”的逻辑,即两个条件同时成立。比如,要计算品牌为Contoso同时类别为Deluxe的销售额,只要将“||”修改为“&&”,
'Product'[BrandName] = "Contoso" && 'Product'[ClassName] = "Deluxe"
这个度量值的完整定义如下,
Contoso And Deluxe Sales Amount =
CALCULATE(
[Sales Amount],
'Product'[BrandName] = "Contoso" && 'Product'[ClassName] = "Deluxe"
)
当然,在实际应用中,强烈建议写成下面的语法,
Contoso And Deluxe Sales Amount 1 =
CALCULATE(
[Sales Amount],
'Product'[BrandName] = "Contoso",
'Product'[ClassName] = "Deluxe"
)
没错!就是将这一个复合的筛选器参数拆成两个筛选器参数。
在CALCULATE函数中,如果有多个筛选器参数,它们之间的运算是“且”,即必须同时成立。
后一种写法的好处是计算效率更高。
当然,筛选条件也可以需要使用不同表的列,比如,我们希望计算要么品牌是Contoso,要么地区在Asia的销售额,(品牌在Product表中,地区在Geographu表中)按照前面的介绍,我们需要这么定义,
Contoso Or Asia Sales Amount 1 =
CALCULATE(
[Sales Amount],
'Product'[BrandName] = "Contoso" || 'Geography'[ContinentName] = "Asia"
)
但是,这么写是错误的!
DAX不支持这种语法!
返回的出错信息是这样的,
很明显,这里只能使用同一个表中的列。
这个需求我们需要采用下面的写法:
Contoso Or Asia Sales Amount =
CALCULATE(
[Sales Amount],
FILTER(
CROSSJOIN(ALL('Product'[BrandName]), ALL('Geography'[ContinentName])),
'Product'[BrandName] = "Contoso" || 'Geography'[ContinentName] = "Asia"
)
)
首先,必须使用FILTER函数来筛选。
其次,筛选的是一个表,是crossjoin函数的返回值。这个函数生成品牌列和洲名称列的所有值的笛卡尔积(可以理解为是所有的不同组合)。
本文涉及的DAX函数及其他DAX函数,在E学会课程中有详细介绍!
详情咨询客服(底部菜单-知识库-客服)
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
Excel企业应用 底部菜单:企业应用
联系客服