一、篩選單列和多列

通過(guò)前兩節(jié)課的學(xué)習(xí)，我們理解了下面兩個(gè)度量值是等價(jià)公式

本期銷量1 = CALCULATE([總銷量],'銷售表'[日期]="本期")

本期銷量2 = CALCULATE([總銷量],filter(all('銷售表'[日期]),'銷售表'[日期]="本期"))

回憶《DAX神功》第1卷第7回 基礎(chǔ)表函數(shù)之Filter函數(shù)

我們學(xué)習(xí)了filter單列和多列篩選，如果你是按順序聽(tīng)課，現(xiàn)在舉一反三沒(méi)有問(wèn)題

總分 = Sum(Sheet1[分?jǐn)?shù)])?

一班男生分?jǐn)?shù) = CALCULATE([總分],'Sheet1'[性別]="男",'Sheet1'[班級(jí)]="一班")

一班男生分?jǐn)?shù) = CALCULATE([總分],'Sheet1'[性別]="男" && 'Sheet1'[班級(jí)]="一班")

一班男生分?jǐn)?shù) = CALCULATE([總分],filter(all('Sheet1'[性別],'Sheet1'[班級(jí)]),'Sheet1'[性別]="男" &&'Sheet1'[班級(jí)]="一班"))

注：使用逗號(hào)或&&代表and（且）的關(guān)系，|| 代表or（或）的關(guān)系

現(xiàn)在性別和班級(jí)都無(wú)法篩選，因?yàn)閍ll(列)

我們可以通過(guò)姓名進(jìn)行篩選

以上舉例每列上只有一個(gè)條件，如果是單列上有多個(gè)條件呢？

一班男女生分?jǐn)?shù) = CALCULATE([總分],'Sheet1'[性別] in {"男","女"} , 'Sheet1'[班級(jí)]="一班")

一班男女生分?jǐn)?shù) = CALCULATE([總分],'Sheet1'[性別] in {"男","女"} && 'Sheet1'[班級(jí)]="一班")

二、權(quán)威書(shū)籍中對(duì)復(fù)雜篩選的建議

如果你的表就如同《The Definitive Guide to DAX》案例所講述的，所有列都來(lái)自一張表

需求：銷售金額大于1的商品賣了多少錢？

常識(shí)：銷售金額=數(shù)量*售價(jià)

PowerBI 2021年6月版親測(cè)可用，早期版本及其它支持DAX的軟件未經(jīng)測(cè)試

金額總和=sumx('Sheet2','Sheet2'[數(shù)量]*'Sheet2'[售價(jià)])

度量值 = CALCULATE([金額總和],'Sheet2'[數(shù)量] * 'Sheet2'[售價(jià)] >1)

如果你使用的是低版本的PowerBI，可以選擇升級(jí)到最新版。

如果你使用的是Excel中的PowerPivot，可以使用下面的等價(jià)公式：

等價(jià)度量值 = CALCULATE([金額總和],filter(all(Sheet2[數(shù)量],Sheet2[售價(jià)]),'Sheet2'[數(shù)量] * 'Sheet2'[售價(jià)] >1))

恢復(fù)篩選度量值 = CALCULATE([金額總和],keepfilters(filter(all(Sheet2[數(shù)量],Sheet2[售價(jià)]),'Sheet2'[數(shù)量] * 'Sheet2'[售價(jià)] >1)))

恢復(fù)篩選等價(jià)度量值= CALCULATE([金額總和],filter('Sheet2','Sheet2'[數(shù)量] * 'Sheet2'[售價(jià)] >1)))

理論上，直接使用表會(huì)比keepfilters慢，速度是個(gè)虛無(wú)縹緲的事情，具體情況具體分析，非戰(zhàn)斗人員你的重心應(yīng)該是如何寫(xiě)對(duì)公式算出來(lái)。

我并不建議大家用這樣的方法處理，我沒(méi)有指責(zé)書(shū)中這么講不對(duì)，而是國(guó)人看完書(shū)籍之后跑出來(lái)抬扛是錯(cuò)的。

三、孫興華對(duì)復(fù)雜篩選的建議

方法千千萬(wàn)！我在《火力全開(kāi)》筆記05課，教給大家的方法:

恢復(fù)篩選等價(jià)度量值= CALCULATE([金額總和],filter('Sheet2','Sheet2'[數(shù)量] * 'Sheet2'[售價(jià)] >1)))

等價(jià)度量值2 = CALCULATE([金額總和],FILTER('Sheet2',[金額總和] >1))

還記得《火力全開(kāi)》中筆記原文嗎？

PS:《火力全開(kāi)》源于《乾坤大挪移》正面迎戰(zhàn)六大門派，各門各派懼怕《火力全開(kāi)》的流行，有雇傭水軍的、有開(kāi)著小號(hào)來(lái)?yè)v亂的、有托私人關(guān)系打壓的、還有學(xué)完《火力全開(kāi)》想講出來(lái)賣錢又覺(jué)得《火力全開(kāi)》影響它生意了故意來(lái)黑的，還有嚇鳥(niǎo)的...等等

切記：從國(guó)外文獻(xiàn)或書(shū)籍上看到的東西，需要?jiǎng)幽X子，否則沒(méi)有靈魂。

我為什么不建議你們使用書(shū)中的方法，因?yàn)槟阍趯?shí)際工作中，不可能是一張表

雖然，我建議你使用SQL從服務(wù)器上拿一張干凈的表（需要哪些字段就拿哪些字段）

但是，你有各種理由不這樣去做。

你的情況比我舉的例子還要復(fù)雜，你用書(shū)中的方法就不易實(shí)現(xiàn)了

需求：銷售金額大于1的商品賣了多少錢？

總金額 = SUMX('銷售表','銷售表'[數(shù)量]*RELATED('商品表'[售價(jià)]))

度量值1 = CALCULATE([總金額],'銷售表'[數(shù)量]*'商品表'[售價(jià)]>1)

錯(cuò)誤: 該表達(dá)式包含來(lái)自己多表的列，只有單個(gè)表中的列篩選器布爾表達(dá)式中

度量值2= CALCULATE([總金額],'銷售表'[數(shù)量]*RELATED('商品表'[售價(jià)])>1)

錯(cuò)誤：關(guān)系函數(shù)是用在行上下文中的，Calculate創(chuàng)建的是篩選上下文

度量值3 = CALCULATE([總金額],FILTER('商品表','商品表'[售價(jià)]*RELATEDTABLE('銷售表'[數(shù)量])>1)

錯(cuò)誤：filter第1參數(shù)使用1端表時(shí)，關(guān)系函數(shù)無(wú)法通過(guò)一端找多端的列,RELATEDTABLE參數(shù)只能是表

度量值4 = CALCULATE([總金額],FILTER('銷售表','銷售表'[數(shù)量]*RELATED('商品表'[售價(jià)])>1))

正確：因?yàn)閒ilter不僅是表函數(shù)還是迭代函數(shù)，上行上下文

從度量值3和度量值4我們可以推導(dǎo)出來(lái)：

度量值5 = CALCULATE([總金額],FILTER('商品表',[總金額]>1)

錯(cuò)誤：雖然沒(méi)有報(bào)錯(cuò)，但是返回結(jié)果不是我們需要的，因?yàn)槎攘恐?，關(guān)系沒(méi)有建立，所以篩選失效，返回的結(jié)果是沒(méi)有篩選的。

度量值6 = CALCULATE([總金額],FILTER('銷售表',[總金額]>1)

正確：同理于度量值4

我上述講的兩個(gè)正確的度量值4、度量值6?都沒(méi)有限制篩選

如果想限制某個(gè)列或某幾個(gè)列不能篩選，filter第1參數(shù)使用all(列)

如果想限制所有列不能篩選，filter第1參數(shù)使用all(表)

四、現(xiàn)在打臉開(kāi)始

很多人看《火力全開(kāi)》時(shí)問(wèn)我，你在第5節(jié)講Filter時(shí)舉的這個(gè)例子我看不懂。

你先回答我一個(gè)問(wèn)題：filter是表函數(shù)，是表函數(shù)的我們都可以放到新建表中做測(cè)試

這句話我在《火力全開(kāi)》中是不是經(jīng)常說(shuō)？你有測(cè)試嗎？如果你測(cè)試了，你就秒懂！

總數(shù)量 = sum('銷售表'[數(shù)量])

度量值7 = CALCULATE([總數(shù)量],FILTER('商品表',[總數(shù)量]>2))

現(xiàn)在我們將FILTER('商品表',[總數(shù)量]>2)拿出來(lái)放到新建表中

度量值8= CALCULATE([總數(shù)量],FILTER('銷售表',[總數(shù)量]>2))? ?//錯(cuò)誤

為什么說(shuō)度量值8是錯(cuò)誤的？其實(shí)不是錯(cuò)誤，而是它得到的數(shù)據(jù)不是你的需求

五、嵌套篩選

加拿大安大略省城市：倫敦

它位于加拿大安大略省的西南部，距離多倫多不到200km。

倫敦人數(shù) =?

CALCULATE(

? ? CALCULATE(SUM(Sheet1[人數(shù)]),'Sheet1'[地區(qū)]="倫敦"),

? ? 'Sheet1'[國(guó)家]="英國(guó)"

)

PS:篩選時(shí)要注意篩選條件是不是限制了某個(gè)列的篩選？

假設(shè)你內(nèi)層限制了某個(gè)列的篩選，外層又篩選這個(gè)列，那是無(wú)效的。

六、Calculate公式計(jì)算順序

你有沒(méi)有發(fā)現(xiàn)上一個(gè)案例我是不是寫(xiě)反了，沒(méi)有！

倫敦人數(shù)=?

CALCULATE(

? ? CALCULATE(SUM(Sheet1[人數(shù)]),'Sheet1'[國(guó)家]="英國(guó)"),

? ? 'Sheet1'[地區(qū)]="倫敦"

)

你會(huì)發(fā)現(xiàn)這樣寫(xiě)答案也是對(duì)的，你真聰明：

我先篩選英國(guó)，再篩選倫敦；或先篩選倫敦再篩選英國(guó)；本來(lái)就是一回事

像這種篩選不同列的問(wèn)題，誰(shuí)在里面誰(shuí)在外面，不會(huì)影響最終結(jié)果

但是，你必須要知道他的正確順序。

DAX函數(shù)中，只有Calculate和CalculateTable是從外向內(nèi)計(jì)算，其它函數(shù)都是從內(nèi)向外計(jì)算。

Calculate和CalculateTable都是先計(jì)算第2參數(shù)，再計(jì)算第1參數(shù)。

美國(guó)人數(shù) =?

CALCULATE(

? ? CALCULATE(SUM(Sheet1[人數(shù)]),'Sheet1'[國(guó)家]="美國(guó)"),

? ? 'Sheet1'[國(guó)家]="英國(guó)"

)

英國(guó)人數(shù) =?

CALCULATE(

? ? CALCULATE(SUM(Sheet1[人數(shù)]),'Sheet1'[國(guó)家]="英國(guó)"),

? ? 'Sheet1'[國(guó)家]="美國(guó)"

)

權(quán)威書(shū)籍和收費(fèi)講師，又是使用相同的圖來(lái)講這個(gè)問(wèn)題：

上邊寫(xiě)上一個(gè)公式，左邊畫(huà)一條龍，右邊畫(huà)一道彩虹，中間一個(gè)大箭頭，指向下面一個(gè)不知道是龍還是彩虹。

DAX圣經(jīng)想法

順序是外層篩選器先生效，由外向內(nèi)，當(dāng)遇到內(nèi)層篩選器時(shí)，內(nèi)層篩選器覆蓋外層篩選器。

我孫興華一介文盲，讓我給你講講，你以后，就記著，所有DAX函數(shù)都是從內(nèi)向外的計(jì)算順序。我這是顛覆式的說(shuō)法：你先別著急噴我，且聽(tīng)我慢慢道來(lái)

【1】沒(méi)學(xué)過(guò)DAX，但是正常人的想法

例如：【美國(guó)人數(shù)】的度量值，我先從所人數(shù)總和中，篩選出美國(guó)的人數(shù)，我在美國(guó)的人數(shù)中篩選英國(guó)，能找到嗎？找不到，所以篩選失敗，它最終結(jié)果還是美國(guó)人數(shù)。

同理：英國(guó)人數(shù)度量值也是相同道理。

【2】DAX神功想法

以英國(guó)人數(shù)度量值為例，內(nèi)層的Calculate是不是取消了對(duì)國(guó)家列的篩選，那外層還篩個(gè)毛線呀？

各位講師，你們肯定不服氣，我們繼續(xù)：

DAX圣經(jīng)講下面這個(gè)度量值是這樣講的，它說(shuō)：使用Keepfilters后，calculate不會(huì)覆蓋之前的篩選上下文。下面這個(gè)度量值取兩個(gè)篩選器的交集。

美國(guó)人數(shù) =?

CALCULATE(

? ? CALCULATE(SUM(Sheet1[人數(shù)]),KEEPFILTERS('Sheet1'[國(guó)家] in {"美國(guó)","英國(guó)"})),

? ? 'Sheet1'[國(guó)家] in {"美國(guó)","加拿大"}

)

DAX神功解釋：DAX函數(shù)全部都是由內(nèi)向外，現(xiàn)在證明給你看

度量值【美國(guó)人數(shù)】?jī)?nèi)層Calculate篩選器使用了keepfilters后，恢復(fù)了篩選功能。

我們?cè)趦?nèi)層Calculate篩選后的表上再篩選外層的 "美國(guó)"和"加拿大"，你說(shuō)是不是只能篩選出美國(guó)人數(shù)？因?yàn)槭褂昧薻eepfilters后這個(gè)允許我繼續(xù)篩選！是不是由內(nèi)向外！我英語(yǔ)不如各位收費(fèi)講師，口語(yǔ)不如小學(xué)生，但是我看書(shū)不止看文字，我要?jiǎng)幽X子，你們可能說(shuō)我炫理解，就跟編程一樣，沒(méi)有誰(shuí)一上來(lái)就寫(xiě)出正確的代碼，都是在解決BUG中不斷成長(zhǎng)。

我們?cè)偻卣挂幌拢簽槭裁聪旅娴拇a就只能看到美英人數(shù)

美英人數(shù) =?

CALCULATE(

? ? CALCULATE(SUM(Sheet1[人數(shù)]),'Sheet1'[國(guó)家] in {"美國(guó)","英國(guó)"}),

? ?KEEPFILTERS('Sheet1'[國(guó)家] in {"美國(guó)","加拿大"})

)

內(nèi)層篩選使用的是布爾值，就相當(dāng)于國(guó)家列禁篩選，你外層只要篩選國(guó)家列，統(tǒng)統(tǒng)不可以，最終的答案是不是 “英國(guó)” 和 “美國(guó)” 的人數(shù)？

《DAX神功》文字版合集：

https://www.bilibili.com/read/readlist/rl442274

《DAX神功》視頻版合集：

https://www.bilibili.com/video/BV1YE411E7p3

PowerBI(DAX函數(shù))、PowerQuery(M函數(shù))、Python辦公自動(dòng)化、Python爬蟲(chóng)、Python數(shù)據(jù)分析、ExcelVBA、WordVBA、AccessVBA、MySQL等等

https://www.bilibili.com/read/cv10222110