[DAX] 新手搞不懂，但卻一定要搞懂的CALCULATE() 與 ALL() — Measure基礎1
更新:
本篇文章如果覺得太複雜看不懂，屬於正常。
歡迎到 台灣最大 Power BI 臉書討論社群(?) 學習討論
我就不推薦我自己的 LinkedIn 了 😆
太久沒用 Power BI 了~
前言
鑒於中文的DAX網路上分享文章太少了，所以就以中文整理近來所學，以及學通的部分做分篇撰寫。
網路上已有多篇文章說明CALCULATE的用法(知乎、SQLBI )，但我還是想以自己工作時較常用的實務邏輯脈絡來做整理。
囿於我自用的Excel屬家用版，沒有power pivot的功能，所以文中是以power bi 來建模、表達的。但實際上與power pivot無異。
而本篇是希望能寫給非IT、資料庫專業，卻想要以Excel做分析的人看的。我大概就是想仿電腦玩物，或學不完．教不停．用不盡。
首先，DAX使用最基礎的便是拉出樞紐表的列(column)來篩選(filter)，再丟入以計算好的總計(費用)到值(value)便可。
再進一步便是利用如SUMX，來計算出自己所需的值。
小計+稅+運費(SUMX) =
SUMX(‘Sales SalesOrderHeader’,
’Sales SalesOrderHeader’[SubTotal]+
’Sales SalesOrderHeader’[TaxAmt]+
’Sales SalesOrderHeader’[Freight]
)
CALCULATE() & FILTER() & ALL()
而本段就是本篇的重點了。
首先，如果你的目的是只想顯示值時，僅顯示特定目標(Linda)，那你可以在你的CALCULATE()中帶上條件。
不論是CALCUTALE(sum(col), FirstName=”Linda”)，
或是CALCUTALE(sum(col), FILTER(table,FirstName=”Linda”)，
兩者都可以。(這點SQLBI 有篇說明。)
(注意 : 要達到這樣僅顯示特定目標的效果，你的樞紐列(columnfilter : FirstName) 必須與篩選條件欄位(FirstName=) 相同)
如果不相同，例如將篩選條件改為國家(CountryCode)=”CA”(Canada)，便會變成如下圖所示。於表格，篩選僅來自CA的費用。這便可看到有些人費用全來自CA、有些人沒有、有些人部分來自CA。
(這部分如果沒有用視覺圖表解釋，還真的很難說明。)
另外，如果想將篩選過後的結果，不僅限顯示於特定欄位的話，便可帶上ALL()。
同樣是放在篩選條件的位置，只不過再帶上ALL()，讓結果顯示到其他空格。
CALCUTALE(sum(col), FirstName=”Linda”, ALL(FirstName))，
或是CALCUTALE(sum(col), FILTER(ALL(FirstName),FirstName=”Linda”)。
(這邊例子在實務使用上，並不make sense。因此待下篇，再詳細說明常見的ALL()所使用的機會。)
CalculateTable()
這部分算是比較進階的部分。
通常CalculateTable()我會與VAR、NATURALLEFTJOIN、CROSSFILTER等進階函數使用。
同樣是篩選條件，但這邊先用Variable建立出篩選表格，然後再帶入SUMX()做計算。
VAR CaculateTable =
CALCULATETABLE(‘Sales SalesOrderHeader’,
FILTER(‘Person Person’,’Person Person’[FirstName]=”Linda”)
)
RETURN
SUMX(CaculateTable,’Sales SalesOrderHeader’[總計費用 (TotalDue)])
同樣也是帶上ALL()，CalculateTable()的運算邏輯和Calculate()一樣，再將ALL()帶到篩選的部分。
VAR CaculateTable =
CALCULATETABLE(‘Sales SalesOrderHeader’,
‘Person Person’[FirstName]=”Linda”,
ALL(‘Person Person’[FirstName])
)
或
VAR CaculateTable =
CALCULATETABLE(‘Sales SalesOrderHeader’,
FILTER(ALL( ‘Person Person’[FirstName]),
‘Person Person’[FirstName]=”Linda”
)
結論
1.如果你想在表中顯示特定目標的數據，那請務必左側列(column)的欄位，必須與篩選條件(filter)相同。
2.如果你想將篩選後特定目標的數據，顯示到所有空白欄位，便可針對該欄位上ALL()。
3.如果你想篩選出移去數值中特定元素的數據(如CA以外的國家)，請注意左側列的欄位，要與篩選條件不相同。
後記
寫到最後發現，這篇文章有些要加強的地方，與困難點。
1.文中的"費用"應該是指Sales的銷售金額，也就是Purchase Order(PO)金額，而非費用(Cost)。
2.如果一口氣要把ALL()說清楚，那真的會搞得很複雜，範例欄位也會變得很多。
3.CalculateTable以本篇寫給新手的目標來說，顯得沒必要加進來。應該再待有機會，放到與VAR、CROSSFILTER一起寫。
4.到最後，新手時最大的問題也還沒寫到。"為什麼我的Measure丟進去了，樞紐表卻沒有幫我篩選(filter)，全部都顯示相同的數字(總數)呢? 明明我有建立表格關聯性。"
(同樣是去除特定元素(如國家)為什麼不會成功，也屬此例。)
我想，這些就下次再補充好了OTZ。
