Power BI之DAX神功：第3卷第3回 CALCULATETABLE与FILTER函数的区别

2021-11-28 13:31 作者:孙兴华zz 0人读过 | 我要投稿

一、Calculatetable与Calculate只是返回结果不同

【度量值】总销售 = SUM(Sheet1[销售])

【度量值】男生成绩 = CALCULATE([总销售],'Sheet1'[性别]="男")

【新建表】男生成绩的表 = CALCULATETABLE('Sheet1','Sheet1'[性别]="男")

总结：Calculate返回的是一个值，Calculatetable返回一张表。除此之外，Calculatetable继承了Calculate几乎所有能力，其中包含对上下文转换的功能。

例如：sumx生成行上下文，套上Calculate后被转换成筛选上下文。Calculatetable也可以进行上下文转换。

二、Calculatetable与filter的区别

我们为Sheet1这张表增加一行，与第1行重复的：张三，男，1

【新建表】男生成绩的表1 = CALCULATETABLE('Sheet1','Sheet1'[性别]="男")

【新建表】男生成绩的表2 = FILTER('Sheet1','Sheet1'[性别]="男")

表面上看二者在结果上是一样的，只是在这种情况下结果相同，我们看看不同情况

区别1：使用ALL表对Calculatetable的影响

我们以all函数为例，它既是表函数又是Calculate的调节器，刚才我们说了Calculatetable只是结果返回表，它继承了Calculate几乎所有的功能。

all函数在Filter中使用时，它是表函数，在Calculate或Calculatetable中使用时是调节器

【新建表】表1 = FILTER(all('Sheet1'),'Sheet1'[性别]="男")

// all(表)就是复制这张表， filter中使用all不会影响筛选，《DAX神功》第2卷第3回计算移动平均值再次理解Filter+ALL 已经得到了证明

【新建表】表2 = CALCULATETABLE(all('Sheet1'),'Sheet1'[性别]="男")

// 但是当ALL放在Calculate或Calculatetable引擎中，ALL是调节器的作用，表2的意思是筛选男生并返回Sheet1这张表的所有行。等于没筛选，结果返回的是Sheet1表中的所有行

区别2：使用ALL列注意去重和能否使用的问题

【新建表】表3 = FILTER(all(Sheet1[姓名]),'Sheet1'[性别]="男")

// all(Sheet1[姓名])是取姓名去重后的表，这张表中没有性别列，所以无法筛选。

但是列名相同时可以使用

【新建表】表3 = FILTER(all(Sheet1[姓名]),'Sheet1'[姓名] in {"张三","王五"})

【新建表】表4 = CALCULATETABLE(all('Sheet1'[姓名]),'Sheet1'[性别]="男")

// 使用Calculatetable就可以这样筛选（如果想得到正确结果去掉all函数改用Values或DISTINCT），原理：Calculate(计算器，筛选器)先执行筛选器再执行计算器，Calculatetable除了结果是表以外继承了Calculate几乎所有的功能，所以Calculatetable也是先执行筛选器，后执行计算器。先在Sheet1表中筛选所有男生的表，再取Sheet1表中姓名列去重后的表所有行。（等于筛选没生效，因为all返回去重后的所有行）

想实现对男生的筛选结果，换用VALUES或DISTINCT

// 《DAX神功》第1卷第9回详细讲解了Values或DISTINCT的区别

【新建表】表4 = CALCULATETABLE(VALUES('Sheet1'[姓名]),'Sheet1'[性别]="男")

【新建表】表4 = CALCULATETABLE(DISTINCT('Sheet1'[姓名]),'Sheet1'[性别]="男")

区别3：calculatetable与filter计值顺序与上下文转换

我们先使用ADDColumns对Sheet1添加列

《DAX神功》答网友问05 返回表的迭代函数你应该注意什么？我们讲解了ADDCOLUMNS与SELECTCOLUMNS的区别

ADDCOLUMNS相当于整容，例如你长了两只眼睛，整容可以让你变成3只，但你还是你。

SELECTCOLUMNS相当于克隆再整容，克隆你再整容成3只眼，长的一样，但是那不是你。

【新建表】测试 = ADDColumns('Sheet1',"数量",CountRows('Sheet1'))

// CountRows('Sheet1')返回4，在Sheet1中新建一个列，每一行都赋值为4

【新建表】表5 = FILTER(ADDColumns('Sheet1',"数量",CountRows('Sheet1')),'Sheet1'[性别]="男")

// filter生成的是行上下文，所以每一行显示的都是CountRows('Sheet1')生成的值：4

Filter计算顺序：

【新建表】表6 = CALCULATETABLE(ADDColumns('Sheet1',"数量",CountRows('Sheet1')),'Sheet1'[性别]="男")

// CALCULATETABLE除了结果返回表外，几乎继承了calculate所有功能，它具备将行上下文转换成筛选上下文的功能，所以每一行的CountRows('Sheet1')返回的是筛选后表中有多少行。

CALCULATETABLE计值顺序：

如果我们想实现内部的CountRows实现筛选功能：

根据《DAX神功》第1卷第19回独创的"母子关系"

我们讲了下面sumx创建行上下文，写在度量值中，就相当于外面套上了Calculate实现了上下文转换功能，但是他内层的sum还是行上下文，无法换转，如果想转换，就要在sum前面套上calculate实现内层的转换。

我们现在的案例可以改写成：

【新建表】表7 = CALCULATETABLE(ADDColumns('Sheet1',"数量",calculate(CountRows('Sheet1'))),'Sheet1'[性别]="男")

三、Filter代替Calculatetable

【新建表】表 8 = ADDCOLUMNS(FILTER('Sheet1','Sheet1'[性别]="男"),"数量",CALCULATE(COUNTROWS('Sheet1')))

运算过程：

四、Calculatetable缺点

当初讲calculate时，就详细说明了一个问题

【度量值】总销售 = sum('销售表'[销售])

【度量值】指定日期1 = Calculate([总销售],'销售表'[日期]=date(2021,1,1))

【度量值】指定日期2 = Calculate([总销售],FILTER(all('销售表'[日期]),'销售表'[日期]=date(2021,1,1)))

以上两个度量值，既然可以用第1个度量值完成，为什么要使用第2个度量值呢？

因为Filter是高级筛选器，《孙兴华讲PowerBI火力全开》第5课讲到

【新建表】表 9 = CALCULATETABLE('商品表',[总销售]>6)

// 与calculate一样，calculatetable不支持度量值与一个固定值进行比较

【新建表】表 10 = FILTER('商品表',[总销售]>6)

《DAX神功》第1卷第8回基础表函数之ALL与ALLEXCEPT函数 中，我重点讲解了filter第1参数为何使用一端表的问题。

还是那句话，没有什么时候就应该用什么的说法，一切取决你的业务，你需求用什么你就用什么。比如很多人问我，他有一个财务需求或是一个人力需求，自己不会做问我用PowerBI怎么做，甚至还有人这样问，他说自己的需求保密不能给我看，问我应该怎么办。我想告诉大家，即便你给我看了，我也不一定帮得了你。隔行如隔山，我都不懂你的业务，又何谈分析呢？业务都是一点点积累起来的，大家应该清楚，很多科学家、科研人员都是有常年实践经验的，没有谁能只坐在办公室里对着屏幕搞分析，醒一醒，想用PowerBI分析一件事，你必需自己先要会做这件事，DAX只是计算。就好比你不会做一道数学题，你买什么样的计算器也帮不了你。