2.2 Power BI計算精要

2.2.1 Power BI與Excel

Power BI中的分析公式分為兩大類：DAX（表格型）公式和MDX（多維分析）公式。相比之下，DAX公式更為簡化，易于理解，因此，近年來DAX公式漸漸成為主流趨勢，而且DAX公式可用于Analysis Services、Power BI和Excel中。有人戲稱，學會了DAX，就等于同時會了三種工具，本書內容也是圍繞DAX公式展開的。

DAX與Excel函數同屬于分析語言，許多Excel函數在DAX環境下也是通用的。例如，LEFT、IF、SUM等函數。但是，DAX和Excel函數又存在明顯的不同。以下將從三個角度比較它們的差異。

DAX度量（Measure）：依存于內存的列計算。Excel公式的計算依據為“單元格”，但在DAX中，“單元格”是不存在的。這樣做極大地節約了計算資源，度量只有在被使用時才會占用內存，也不占用硬盤空間。由于度量計算是基于列的計算，其計算引擎十分高效，幾十萬行數值的聚合計算在4GB內存的電腦中運行是很輕松的事情。同時，DAX還會優化壓縮源文件數據，PBIX文件往往比原有的Excel/文本文件更小。而Excel中的單元格計算不僅消耗內存資源，而且需要硬盤空間，其計算效能遠不如DAX，復雜的Excel工作簿在運行時還會導致文件崩潰，強制關閉應用程序。

表關聯：DAX的另一個特點是允許表與表依據一定的規則進行關聯，這樣避免了使用Excel工作表的必要。眾所周知，Excel的工作表是導致Excel性能問題的一個重要因素，因為如果在Excel工作表中使用VLOOKUP公式則會產生大量的數據冗余，資源消耗會隨著表的復雜程度的增長呈幾何級增長，而DAX的表關聯很好地彌補了Excel在此方面的局限。

引用變量：在Excel公式中經常使用單元格引用，這種方式雖然很方便，但也有極大的隱患，一旦單元格位置發生變化，則可能導致公式錯誤。因為DAX度量是列計算，不存在引用單一單元格的情況，除非整列字段被刪除，否則DAX公式不受表格字段變動的影響。DAX公式還可以相互嵌套使用，實現許多復雜的業務邏輯。DAX公式有“寫一次，永久運行”的優點。

說了這么多DAX的好處，那么DAX就沒有缺點嗎?并不是，DAX不是編程語言，DAX不僅沒有單元格，也沒有像While、Loop語句的循環邏輯。因此，當遇到非使用單元格不可或計算復雜循環邏輯的場景時，仍然需要通過VBA、SQL等編程語言解決，這不屬于商業智能的范疇。

2.2.2 度量與計算列

DAX的計算公式可以分為兩大類：度量（Measure）與計算列（Calculated Columns），表2.2.1中列舉了二者的主要區別。

另外，由于數量龐大的計算列會拖慢模型的性能，因此，在既可以使用度量又可以使用計算列的情況下，一般優先使用度量。如果需要使用計算列，則必須清楚是什么原因不能使用度量替代。

注意：度量為列計算，而計算列為行計算，列計算與計算列不是同一個事物。計算列公式與Excel公式都是相通的，運算方式也與Excel中的表運算相同，如圖2.2.1所示，“@”表示其為Excel表（Table）。

2.2.3 行上下文與篩選上下文

什么是上下文?比如朋友說今晚吃雞。如果此刻你們在餐廳，那么你會理解為他想吃雞肉。如果此刻你們在玩手機，那么你會理解為他想玩《荒野生存》游戲。這就是對上下文的通俗比喻：根據當前不同語境下所指的不同事物。DAX中的上下文分為兩種：行上下文（Row Context）和篩選上下文（Filter Context）。

1.行上下文（Row Context）

行上下文比較容易理解，即進行“當前”行的操作，圖2.2.1所示的是一個行上下文的例子，公式中雖然沒有標明具體的行，但Excel只對“當前”行進行求和運算。在本質上，Excel表與Power BI中的計算列的運算原理都是依據行上下文操作的。

2.篩選上下文（Filter Context）

篩選上下文是指所有作用于DAX度量的篩選。這里將其篩選邏輯分為三個篩選層次（見圖2.2.2所示），更好地幫助讀者理解。

（1）外部篩選:任何存在于可視化層級的上下文篩選，包括任何圖表本身、視覺級、頁面級和報表級篩選器。外部篩選通過外部可視化操作對度量進行篩選操作。外部篩選也被稱為隱性篩選，篩選設置不依存于度量中。

（2）DAX篩選:DAX篩選指DAX公式內部自身的篩選設置。例如，CALCULATE函數中的FILTER參數就是典型的DAX篩選。通過FILTER定義的篩選條件，可覆蓋外部篩選的結果。DAX篩選也被稱為顯性篩選，因為篩選條件直接依存于公式自身，后文有具體介紹。

（3）關聯篩選：通過表之間的關聯關系進行查詢傳遞，DAX中的USERELATIONSHIP語句就是一個很好的例子，關聯方式會改變外部篩選和DAX篩選的結果。

相比Tableau，DAX中沒有類似表計算和LOD這樣的專門術語，但DAX專有的函數可以實現相同的功能。例如，LOD中的FIXED、INCLUDE和EXCLUDE函數功能與DAX中的ALL、ALLEXCEPT、ALLSELECTED函數的功能對應。DAX度量的核心能力之一在于通過篩選上下文轉換，將查詢范圍縮小至滿足篩選條件的子集表中，在其內完成指定的聚合計算。

2.2.4 DAX精華公式

此處對DAX公式進行延伸，為讀者介紹幾款功能強大又有趣的公式。以下公式大部分來自微軟官網，讀者還可通過關鍵字在網上查找更詳細的解釋。

1.CALCULATETABLE——CALCULATE的姐妹

公式：CALCULATETABLE(<表達式>,<篩選器1>,<篩選器2>,…)

解釋：“眾所周知，CALCULATE是DAX的核心函數，可將計算列和度量進行上下文轉換。通過DAX篩選返回滿足條件的子數據集，再進行聚合運算并返回結果度量。作為CALCULATE的姐妹公式，CALCULATETABLE的工作原理與CALCULATE相似，只是CALCULATETABLE返回的是表，而非單一的度量。

示例：=SUMX(CALCULATETABLE('InternetSales_USD','DateTime'[Calendar Year]=2006),[SalesAmount_USD])

在上述例子中，CALCUCLATETABLE公式返回了一張日期為2006年的'InternetSales_USD'表中的子表。

2.CROSSJOIN——DAX中的笛卡兒積的表

公式：CROSSJOIN(<表格>,<表格>[,<表格>]…)

解釋：CROSSJOIN用于返回使用表格中所有字段的所有行的笛卡兒積的表。

示例：CROSSJOIN(Colors,Stationery)

3.SUMMARIZE——強大的依組分類摘要表

公式：SUMMARIZE(<表格>,<分類字段>[,<分類字段>]…[,<命名列名>,<表達式>]…)

解釋：SUMMARIZE用于對表進行依據字段信息進行分類聚合的計算分析，返回的結果為信息摘要表。

在上述例子中，SUMMARIZE函數依據【CalendarYear】和【ProductCategoryName】維度對字段【SalesAmount_USD】進行求和。最終返回摘要表。熟悉SQL語句的讀者對此不會感到陌生，SUMMARIZE函數的作用相當于SQL語句的GROUP BY的作用。

4.SUMMARIZECOLUMNS——跨表的摘要表

公式：SUMMARIZECOLUMNS（＜分類字段＞ [，＜分類字段＞]…，[＜篩選器表格＞]…[，＜命名列名＞，＜表達式＞]…）

解釋：與SUMMARIZE相似，SUMMARIZECOLUMNS也是對數據進行摘要，但是不同的地方在于，SUMMARIZECOLUMNS所依據的維度字段可以不局限于一張表。

示例：SUMMARIZECOLUMNS （'Sales Territory'[Category]，'Customer'[Education]，FILTER（'Customer'，'Customer'[First Name]=“Alicia”））

在上述公式中，公式依據源自不同表中的字段的【Category】、【Education】維度對滿足條件'Customer'【First Name】=“Alicia”的表子集進行摘要分析。

5.ADDCOLUMNS——為表添加字段列

公式：ADDCOLUMNS(<表格>,<命名列名>,<表達式>[,<命名列名>,<表達式>]…)

解釋：將表達式以計算列的形式添加到指定的表中。

在上述公式中，將字段列【Internet Sales】和【Reseller Sales】分別添加至表Product Category中。

6.LOOKUPVALUE——DAX中的VLOOKUP函數

公式：LOOKUPVALUE(<結果欄字段>,<匹配欄字段>,<匹配值>[,<匹配欄字段>,<匹配值>]…)

解釋：為滿足由<匹配欄字段>和 <匹配值>指定的所有標準的行返回 <結果欄字段>中的值。需要注意的是，匹配欄字段可以是多個字段選項。

公式：LOOKUPVALUE（Product[SafetyStockLevel]，[ProductName]，"Mountain-400-W Silver，46"）

在上述例子中，通過字段【ProductName】的值“Mountain-400-W Silver,46”匹配所對應的【Product】表中的【SafetyStockLevel】（安全庫存）字段。