手機(jī)站首頁散文詩歌雜文隨筆日記小小說

散文網(wǎng) » 科技 »學(xué)習(xí) » Power BI之DAX神功：第2卷第15回累計(jì)至今區(qū)間、Filter與CalculateTable區(qū)別

Power BI之DAX神功：第2卷第15回累計(jì)至今區(qū)間、Filter與CalculateTable區(qū)別

2021-11-09 13:15 作者:孫興華zz 0人讀過 | 我要投稿

一、理解累計(jì)至今的區(qū)間計(jì)算原理

《DAX神功》第2卷第11回我們學(xué)習(xí)了累計(jì)至今區(qū)間，現(xiàn)在我們研究一下它們的計(jì)算過程

【1】DatesYTD 年初至今

思路：

1.確定最大日期 2021/7/4

2.確定最大日期所在年：2021年

3.確定小于等于最大日期且等于2021年的數(shù)據(jù)

【新建表】

年初至今 = FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))

// 結(jié)果 2021/1/1~2021/7/4

【2】DatesMTD 月初至今

思路：

1.確定最大日期 2021/7/4

2.確定最大日期所在年：2021年

3.確定最大日期所在月：7月

4.確定小于等于最大日期且等于2021年7月的數(shù)據(jù)

【新建表】

月初至今 = FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && MONTH('Sheet1'[日期])=MONTH(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))

// 結(jié)果? 2021/7/1~2021/7/4

【3】DatesQTD 季初至今

思路：

1.確定最大日期 2021//7/4

2.確定最大日期所在年：2021年

3.確定最大日期所在季度：3季度

4.確定小于等于最大日期且等于2021年3季度的數(shù)據(jù)

【新建表】

季初至今 = FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && QUARTER('Sheet1'[日期])= QUARTER(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))

// 結(jié)果? 2021/7/1~2021/7/4

【4】DatesYTD第二參數(shù)計(jì)算規(guī)則

《DAX神功》第2卷第11集我們講過只有DatesYTD擁有第二參數(shù)（可選），MTD和QTD是沒有第二參數(shù)的。

【新建表】年初至今 = DATESYTD('Sheet1'[日期],"6-30")? // 返回2021/7/1~2021/7/4

思路：

1.找到最大日期：2021/7/4

2.指定6月30日：DATE(year(2021/7/4),6,30)

3.篩選日期大于DATE(year(2021/7/4),6,30)和日期小于等于最大日期的

年初至今 = filter(ALL(Sheet1[日期]),'Sheet1'[日期]>date(year(max('Sheet1'[日期])),6,30) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))

PS:本人沒有做過財(cái)務(wù)，也沒有在外企工作過，本人經(jīng)歷的機(jī)關(guān)單位均是1月1日和7月1日起計(jì)算。對其它機(jī)構(gòu)沒有工作經(jīng)驗(yàn)，很難顧及全面，請見諒。

二、為什么要用Filter+ALL('Sheet1'[日期])

ALL('Sheet1'[日期]) 將Sheet1中日期列所有行去重后返回成一張表

《DAX神功》第2卷第6回 RankX神秘的第三參數(shù)中有詳細(xì)

如果單純?yōu)榱撕Y選表，VALUES與DISTINCT函數(shù)也是可以的。

三、Filter是迭代函數(shù)，如何進(jìn)行上下文轉(zhuǎn)換？

【新建列】

列1 = countrows(DATESYTD('Sheet1'[日期]))

列2 = COUNTROWS(FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期])))

列1使用了時(shí)間智能日期函數(shù)DATESYTD，它可以轉(zhuǎn)換上下文，但是Filter不行，所以我們給Filter嵌套CalculateTable

列3 = COUNTROWS(CALCULATETABLE(FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))))

從上面的公式，我們推導(dǎo)出：

DatesYTD(表[日期])? 等價(jià)于 CalculateTable(Fliter(all(日期列),年=max年 && 日期<=Max日期?)

// 當(dāng)Filter套上了CalculateTable時(shí)，就具備了篩選功能，就好比我們給聚合函數(shù)加Calculate一樣，因?yàn)镕ilter返回的是表而不是一個(gè)值，所以從行上下文轉(zhuǎn)換成篩選上下文需要使用CalculateTable

【新建列】

列1 = sum(Sheet3[數(shù)量])

列2 = CALCULATE(SUM(Sheet3[數(shù)量]))

四、CalculateTable與Filter在篩選表上有什么區(qū)別

【1】使用all函數(shù)后的區(qū)別

【新建表】表1 = FILTER(all('Sheet4'),'Sheet4'[性別]="女")

// filter中使用all不會(huì)影響篩選，《DAX神功》第2卷第3回?計(jì)算移動(dòng)平均值再次理解Filter+ALL 已經(jīng)得到了證明

【新建表】表2 = CALCULATETABLE(all('Sheet4'),'Sheet4'[性別]="女")

// 但是當(dāng)ALL放在Calculate或CalculateTable引擎中，ALL是調(diào)節(jié)器的作用，表2的意思是篩選女生并返回Sheet4這張表的所有行。等于沒篩選

【2】不使用all函數(shù)時(shí)，篩選表從表面上看是一樣的

【新建表】表1 = FILTER('Sheet4','Sheet4'[性別]="女")

【新建表】表2 = CALCULATETABLE('Sheet4','Sheet4'[性別]="女")

【3】透過現(xiàn)像看本質(zhì)，二者區(qū)別在于計(jì)算順序

【修改新建表】

表1 = FILTER(ADDColumns('Sheet4',"數(shù)量",CountRows('Sheet4')),'Sheet4'[性別]="女")

【修改新建表】

表2 = CALCULATETABLE(ADDColumns('Sheet4',"數(shù)量",CountRows('Sheet4')),'Sheet4'[性別]="女")

為了弄清楚這件事，我們先看一下ADDColumns生成的結(jié)果：

【新建表】表3 = ADDColumns('Sheet4',"數(shù)量",CountRows('Sheet4'))

我們來模擬filter和CalculateTable的計(jì)算規(guī)則：

其實(shí)很好理解，F(xiàn)ilter先執(zhí)行第1參數(shù)表，再執(zhí)行第2參數(shù)篩選表。而CalculateTable同Calculate一樣，先執(zhí)行篩選器，再執(zhí)行計(jì)算器，只不過在CalculateTable中計(jì)算器是第1參數(shù)表。