DAX基础7：常用的Filter functions

发表: 2019-05-17 浏览: 1417

更新慢，不定期更新，强迫症表示不舒服。想做得更好一点，不过其实慢也不一定会更好。要固定个时间，是每周四一次还是周二周五两次呢？纠结。嗯，如果周二没有更新，那就是周四更新，如果周二更新了，那周五就还有一更，哈哈哈...完美~~下周要在公司内部做一个DAX分享，还可以吧。越努力越自信。加油！

今天基础分享的主要内容是Filter function里面的ALL，ALLEXCEPT，ALLSELECTED并相关延展。

首先看看今天要研究的数据。先拉出来一个Color和一个Quantity。得到的结果如下。Quantity的总数是17498.

而对于filter context的每一个Color值，都有一个对应的Quantity的数据。如果我们想计算每一个Color的Quantity在总的Quantity的占比呢？

如果，我们新建一个度量值（measure），定义如下：

Quantity_test = SUM(Sales[Quantity])

得到的结果如下：

为什么不对呢？因为新建的度量值，在这个计算中，其实有一个隐藏CALCULATE的函数功能，他其实计算的是CALCULATE(SUM(Sales[Quantity]) )（注意我说的是新建的度量值。）在没有需要修改的filter context的情况下，他会根据当前filter context的内容来计算结果。好了，原理明白了，那我们就修改filter context，来得到我们想要的结果。我们可以考虑使用ALL函数。

ALL函数的定义是，返回一个表中的所有行，或者一个列中的所有数据，忽略里面的filter。这个函数主要就是用作去掉filter，基于全部数据做计算的。语法如下：

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

新建一个度量值来计算汇总，如下：

Quantity_All = CALCULATE(SUM(Sales[Quantity]),ALL('Product'))

哈哈哈，看来我没有胡说八道吧~~

继续算百分比哈。

Quality_Ratio = DIVIDE([Quantity_test],[Quantity_All])

（DIVDE函数：第一个参数是分子，第二个参数是分母。帮你考虑到分母为0的情况，第三个参数选填，如果填写了，就在分母为0的时候返回这个填写的内容，如果没有填写第三个参数，当分母为0的时候就返回空）

第一个[Quantity_test]考虑filter context，刚好可以做分子，会得到不同Color的汇总Quantity，第二个[Quantity_All]改写了filter context，得到全表的Quantity，刚好可以做分母。结果如下：

很不错吧。hmmm...如果我们加入了一个Brand在Table表里面呢？我还想要计算各种Color对总Color的Quantity占比。现在我们看看加入Brand之后的结果。

同样因为[Quantity_test]考虑filter context，所以可以看到[Quantity_test]的数据根据filter context的变化得到了不一样的结果。也因为这个变化，结果不是我们想要的。这时，我们可以考虑函数ALLEXCEPT。

ALLEXCEPT的定义是不考虑所有的filter context，只考虑指定的filter。

语法如下：

ALLEXCEPT(<table>,<column>[,<column>[,…]])

根据这个定义，我们重写[Quantity_test]，定义如下：

Quantity_test = CALCULATE(SUM(Sales[Quantity]),ALLEXCEPT('Product','Product'[Color]))

结果如下：（按照Color排序）

hmmm...很不错哦。

那那那，那如果我还想加入一个Slicer呢？这次我想选择不同的Manufacturer的时候，想看到考虑Brand和Color的，在不同选择下Quantity的占比。还是注意Quantity_test，我改回到了Quantity_test = SUM(Sales[Quantity]) 。结果如下：

如果什么都不选，好像也没啥问题，都挺对的。如果我选择了不同的Manufacturer呢？随便选一个，结果如下：

结果明显不对啊。因为Quantity_All 忽略了所有的filter所以，他也同样忽略了来自Slicer的filter。这个时候，我们可以选择用ALLSELECTED来改写Quantity_All。ALLSELECTED函数的定义是不考虑行列的filter context，但是保留外部filter影响。语法如下：

ALLSELECTED([<tableName> | <columnName>])

感觉和ALLEXCEPT函数很像的，一个是除了写出来的，都忽略（ALLEXCEPT），一个是其他都忽略，只保留写出来的。但是，语法上来说还是有区别的。如果，像之前我们的假设，只考虑一个列字段Color，不考虑其他的字段，我们用ALLSELECTED和用ALLEXCEPT都可以。（自己替换一下吧。）但是如果我们考虑两个以上的列字段，不考虑其他字段的话，只能用ALLEXCEPT。如果不加Slicer的时候，计算全部的Quantity的和，你选择用ALL或者ALLSELECTED也是一样的效果。（可以自己尝试下）

唉，既然是基础，我还是唠叨下吧。

在没有Slicer的情况下：（ALL和ALLSELECTED替换）
Quantity_All = CALCULATE(SUM(Sales[Quantity]),
ALL('Product'))
可以替换成
Quantity_All = CALCULATE(SUM(Sales[Quantity]),
ALLSELECTED('Product'))

2. 在考虑一个列的情况下：（ALLSELECTED和ALLEXCEPT替换）

Quantity_test = CALCULATE(SUM(Sales[Quantity]),

ALLEXCEPT ('Product','Product'[Color]))

可以替换成

Quantity_test = CALCULATE(SUM(Sales[Quantity]),

ALLSELECTED('Product'[Color]))

你看ALLSELECTED语法结构也能大概看出他的局限性。

继续继续，我们的需求，在选择了Manufacturer的时候，Quantity_All 的结果不对，他忽略掉了全部的filter，但是我们要保留Manufacturer的filter。所以，此时此刻，我们需要

Quantity_All = CALCULATE(SUM(Sales[Quantity]),

ALLSELECTED('Product'))

结果如下：

很不错啊。到此为止，我们基本上就完成了ALL，ALLEXCEPT以及ALLSELECTED函数的基本介绍。

那那那那，那如果我想在有Manufacturer的Slicer的情况下，只考虑Color的占比，不考虑Brand呢？为了更好的体现结果，在table中继续拉入一个Product Name字段，只考虑Color的占比，不考虑Brand和Product Name。目前的结果，Quantity_Ratio对于同一个Color，他的占比根据不同的Brand，Product Name而不同。

这个时候，我们来考虑下这么写这个计算。