更新慢,不定期更新,强迫症表示不舒服。想做得更好一点,不过其实慢也不一定会更好。要固定个时间,是每周四一次还是周二周五两次呢?纠结。嗯,如果周二没有更新,那就是周四更新,如果周二更新了,那周五就还有一更,哈哈哈...完美~~下周要在公司内部做一个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而不同。
这个时候,我们来考虑下这么写这个计算。
er,对的,分母肯定是要取得外部的filter,所以不能用ALL,用ALLEXCEPT还是ALLSELECTED呢?都可以,但是如果用ALLEXCEPT,我要罗列需要考虑的Silcer字段列,你可能知道的,我是一个懒胖子。。所以我不要写那么多,所以我用ALLSELECTED函数。
Quantity_All = CALCULATE(SUM(Sales[Quantity]),
ALLSELECTED('Product'))
然后分子呢?就用ALLEXCEPT了。虽然有点懒,但是也没有办法。因为ALLSELECTED不行~~
Quantity_test = CALCULATE(SUM(Sales[Quantity]),ALLEXCEPT('Product','Product'[Color],'Product'[Manufacturer]))
结果如下:
是不是还是很有趣的。我觉得这次写的真的是很详细啊。不错不错~
那来一个更难一点的需求吧,如果我想计算汇总在不同的Color下,有多少个Product 呢?这个有点难。可以认真思考下,如果还是做不出来,请参考DAX分享9:DAX中用变量来计算动态filter context中数值
欢迎关注分享DAX Share的内容。