Power Pivot 中的 DAX 模型：公式、上下文和最佳實踐

如果你在Excel中處理數據， 動力樞軸你遲早會接觸DAX指數。 雖然「數據分析表達式」這個名稱聽起來很專業，有點嚇人事實上，只要充分理解其基本原理，它就能成為非常方便、功能強大的資訊建模工具。

在 Power Pivot 中，DAX 是表格模型的核心： 它用於建立計算列和度量值，為資料透視表和圖表提供資料。它允許您調整篩選條件、處理日期、處理錯誤以及進行比較。 El Temppo 還有更多內容。讓我們仔細看看這一切是如何融入 Power Pivot 的 DAX 模型中的，以及如何在不陷入複雜操作的情況下充分利用它們。

什麼是 DAX？為什麼它在 Power Pivot 模型中至關重要？

DAX（資料分析表達式）首先是 一種專為資料模型設計的公式語言不是一種語言 程序設計 經典模式。它的功能是定義應用於 Power Pivot 模型中的表格和列的自訂計算。

雖然從視覺上看它可能類似於 excel公式, DAX 旨在處理關係資料和動態聚合。這意味著它可以與透過關係連接的表、透視表和複雜模型無縫集成，其中篩選上下文會根據您在行、列或切片器中使用的欄位而變化。

在 Power Pivot 模型中，您可以使用 DAX 來 兩種主要類型的物件：計算列和度量值兩者在同一模型中共存，但各自有不同的用途，評估方式也不同，這一點應該非常清楚，以免混淆概念。

此外，DAX還包括 文字、日期和時間、邏輯、數學、過濾和「時間智能」函數以及一些能夠傳回完整表格的函數。這些函數是它與 Excel 的主要區別之一，因為 Excel 中的公式不會傳回表格，而最多只會傳回儲存格區域內的陣列。

DAX 公式和公式欄概述

DAX 公式的結構與 Excel 公式的結構非常相似： 它們以等號 (=) 開頭，然後寫出帶有參數的表達式或函數。您可以像在電子表格中建立複雜公式一樣，組合運算子、函數、列和表格引用等。

然而，其中存在一些重要的細微差別。 DAX 從不使用 A1:C10 類型的參考。相反，這裡指的是整個列或表，例如 '銷售量' o '日曆'這在表格模型中很有道理，但如果你來自 Excel 那種純粹的「逐個單元格」的世界，它會迫使你改變思維方式。

Power Pivot 具有 類似於 Excel 的公式欄 這大大簡化了表達式的建立。它具有函數、表和列的自動完成功能：當您開始輸入表或列的名稱時，會出現一個包含有效選項的下拉列表，從而減少語法錯誤並節省時間。

要編寫表名，只需開始輸入並讓它運行即可。 自動完成功能會建議符合的名稱對於列，您可以開啟一個方括號並選擇目前表中的列，或鍵入表名，後面跟著一個方括號，然後從清單中選擇。

然而，與Excel不同的是， Power Pivot 不會自動閉合括號 它無法為您匹配。您有責任確保函數格式正確，參數數量正確且括號完整，否則公式將無法儲存或使用。

DAX 公式的應用場景：計算列和度量值

在 Power Pivot 模型中，您可以撰寫 DAX 公式。 計算列 和 措施 （在資料透視表中也稱為計算欄位）。雖然它們使用相同的術語，但它們的行為方式卻截然不同。

Power Pivot 中的計算列

計算列是 在模型中，向現有表格中新增一個欄位。您無需從資料來源匯入該值，而是定義一個 DAX 公式，該公式逐行計算。計算結果儲存在表中每一行的對應列中。

計算列統一應用於所有行： 每一行不能使用不同的公式。與 Excel 中可以手動拖放元件不同，在 Power Pivot 中，您定義的表達式會自動針對整列進行評估，並在資料更新或強制重新計算模型時重新計算。

這種類型的列可以基於 其他計算列或度量但是，為了避免引用時產生混淆，建議不要對度量值和列使用相同的名稱。最好始終使用完整的列引用（表），以避免意外引用同名的度量值。

當您需要時，計算列是理想的選擇。 您希望在行、列、篩選器或切片器中使用的其他屬性 可用於資料透視表，或作為關係鍵。例如，可以考慮計算「邊際」列，然後使用該列進行分組或篩選。

度量或計算字段

這些措施本身就是： 在資料透視表或視覺化圖表的上下文中評估的計算它們不是按行儲存的，而是根據報表中啟動的每個篩選器、行和列的組合動態重新計算的。

一 典型測量 可能很簡單，例如：

總銷售額 = SUM(銷售額)

這項措施被放置在…區域 值 在資料透視表中，每個單元格都根據上下文進行評估（例如，按年份、按產品、按地區…）。 同樣的計算方法，根據應用的篩選條件不同，會得到不同的結果。 以及樞軸桌的設計。

這些措施只有在報告中使用時才起作用。 它們與資料模型一起存儲，並出現在透視表的欄位清單中。 這樣，本書的任何使用者都可以使用它們。它們對於靈活的總和計算至關重要，例如比率、貢獻百分比、累積總額、不同時期之間的比較等等。

DAX 函數和 Excel 函數的主要區別

儘管許多 DAX 函數在名稱和一般行為上與 Excel 函數相似， 它們並非可以簡單地互換。在 Power Pivot 模型中，公式的建構方式存在一些重要的差異。

首先， DAX 無法處理單一儲存格或儲存格區域。整個列或表始終用作引用。這迫使你更多地從資料集的角度而不是單一專案的角度思考問題，這更符合關聯式資料模型的概念。

在日期範圍內，DAX 返回 實際日期時間類型值雖然 Excel 通常將日期表示為序號，但在大多數情況下，這種差異並不明顯，但在合併模型或從其他系統匯入資料時，請務必牢記這一點。

另一個關鍵點是 許多新的 DAX 函數會傳回完整的表。 （例如，FILTER、ALL、VALUES 等函數）或它們接受表格作為參數。另一方面，Excel 沒有類似意義上的「返回表格」的函數概念，儘管它支援陣列公式。

最後，DAX 假設 列中的所有值都具有相同的資料類型如果資料類型混雜，資料引擎會強制將整列轉換為最適合所有記錄的類型，如果資料來源沒有經過徹底檢查，有時可能會導致意外情況。

DAX 中的資料類型和表格類型

當您將資訊匯入 Power Pivot 模型時， 數據被轉換為引擎支援的數據類型之一。 （數字、文字、布林值、日期和時間、貨幣等）。這類資料決定了哪些運算有效以及公式的計算方式。

與經典版Excel相比，一個重要的新功能是： 表資料類型許多 DAX 函數接受整個表作為參數，並傳回另一個表作為結果。例如，FILTER 函數接受一個表和一個條件，並傳回一個僅包含滿足該條件的行的表。

將返回表格的函數與 聚合函數，例如 SUMX、AVERAGEX 或 MINX可以建立高度複雜的計算模型，這些模型能夠處理動態定義的資料子集。這樣就能產生自訂聚合結果，使其能夠適應任何給定時間點啟動的篩選條件。

Power Pivot 中的關係、背景和關係模型

Power Pivot 視窗用於建立關係資料模型。 您可以在這裡匯入多個表並建立它們之間的關係。 （例如，銷售與產品、銷售與行事曆、銷售與客戶等）。這些關係是 DAX 公式能夠在不同表格之間跳轉的基礎。

當表格之間存在關聯時， 您可以編寫公式，將相關表中的值相加。 並在編寫表達式的表格中使用這些列。您還可以透過對特定列套用篩選器來控制哪些行參與計算。

重要的是要注意 Power Pivot 表格中的所有行必須具有相同數量的欄位。每一列的所有行都必須保持一致的資料類型。如果關係鍵的值不符（例如空白值、孤立值等），則尋找公式和資料透視表可能會傳回意外結果。

另一個基本概念是 語境在 DAX 中，主要使用術語「行上下文」和「篩選上下文」。行上下文是指計算列或迭代器正在計算的「目前」行；篩選上下文是指一組活動篩選器（來自透視表、切片器、關係、CALCULATE 等函數等）。

透過使用 CALCULATE、ALL、ALLEXCEPT 或 FILTER 等函數，您可以 修改篩選器上下文以更改度量值的評估方式。例如，這樣就可以計算某產品的銷售額佔總銷售額的百分比，或是在不進行篩選的情況下，將某部門的績效與整個公司的績效進行比較。

資料更新和DAX公式重新計算

在使用複雜公式或大量資料的模型中，了解更新是如何進行的至關重要。 區分刷新資料和重新計算公式非常重要。它們是相關但獨立的過程。

數據更新包括： 從外部來源將新記錄納入本書 (數據庫（文件、線上服務等）。您可以根據需要手動啟動此更新，或者如果書籍已出版，則可以安排更新。 的SharePoint 或其他相容環境。通常，此過程使用以下方式執行： Excel 中的 Power Query 在將資料載入到模型之前，對其進行準備和轉換。

另一方面，重新計算是指…的過程。 DAX 公式會重新計算，以反映資料或表達式本身的變化。對於計算列，如果變更公式，則必須立即重新計算整列。對於度量值，當上下文發生變更（例如，篩選器、資料透視表行/列欄位）或手動更新資料透視表時，會進行重新計算。

這些重新計算可能會影響效能，尤其是在效能方面。 如果在大表中使用了許多複雜的計算列或密集型迭代函數，則可能會出現問題。因此，一個好的做法是盡可能將大部分邏輯移到度量值而不是列。

DAX公式錯誤檢測與修正

編寫 DAX 公式時，通常會遇到以下三種類型的錯誤： 句法錯誤、語意錯誤與計算錯誤每一種情況都有其自身的特徵和自我糾正的方式。

語法錯誤是最簡單的： 缺少括號、逗號位置錯誤、函數名稱拼字錯誤等等。自動完成幫助和 DAX 函數參考可以幫助您避免許多此類陷阱。

即使語法正確，語意錯誤和計算錯誤仍然會發生， 該公式在模型上下文中執行了一些不合理的操作。例如，引用不存在的表或列，向函數傳遞錯誤數量的參數，混合不相容的類型，或依賴先前出現錯誤的列。

在這些情況下，DAX 通常會標記 整列資料計算結果錯誤。不只是特定的行，因為列被視為一個單元。如果一列僅包含元資料但尚未處理（未載入任何資料），則會顯示為灰色，並且依賴該列的公式將無法正確計算。

特殊情況是值 NaN（非數字）例如，當 0 除以 0 時，可能會出現 NaN 值。如果某一列包含 NaN 值，則對這些值進行排序或分類可能會產生異常結果，因為 NaN 值無法像其他數字一樣進行比較。在這種情況下，建議使用 IF 語句或其他邏輯函數將 NaN 值替換為 0 或其他可處理的數值。

與表格模型和 DirectQuery 模式相容

您在 Power Pivot 中建立的 DAX 公式通常是： 與 SQL Server Analysis Services 表格模型相容這意味著您可以將模型遷移到表格伺服器，並繼續利用您已經建立的邏輯。

然而，當表格模型以模式實現時 DirectQuery可能有以下局限性： 某些關係型資料庫不直接支援某些 DAX 函數。 或者，由於查詢的委託方式不同，它們可能會傳回略有不同的結果。

在這種情況下，查閱表格引擎的具體文件非常重要。 驗證關鍵措施 確認啟動 DirectQuery 後它們是否繼續如預期運作。

實際應用場景：使用 CALCULATE 函數和篩選器進行複雜計算。

DAX的優勢之一在於其執行能力。 依賴自訂聚合和動態篩選器的複雜計算CALCULATE 和 CALCULATETABLE 函數是此類場景的核心。

計算允許 重新定義表達式求值的過濾器上下文例如，您可以要求“按特定年份篩選的銷售額總和，即使資料透視表顯示的是其他年份”，或“不應用某些產品篩選條件的總金額”。

任何 DAX 函數接受表作為參數的地方， 您可以傳遞該表的篩選版本。這可以透過使用 FILTER 函數或在 CALCULATE 函數中指定條件來實現。這樣就可以建立能夠適應數千種條件組合的度量值，而無需建立中間列。

也有可能 選擇性地移除現有過濾器 使用 ALL 或 ALLEXCEPT 等函數。例如，要計算特定經銷商相對於經銷商總數的貢獻，您可以讓度量值將目前上下文中的值除以「ALL」上下文中的值（不按經銷商篩選）。

在其他情況下，您需要使用 “外循環”的值也就是說，需要參考上一行或上一次迭代的上下文。這時就需要用到像 EARLIER 這樣的函數，它們允許最多兩層巢狀循環，對於建立排名、分組總計或依賴上一行上下文的計算非常有用。

我使用 DAX 處理文字、日期和鍵。

DAX 也提供許多工具 處理文字和日期當資料來源包含格式不尋常的日期、複合鍵或需要轉換為時間值的文字欄位時，這一點至關重要。

Power Pivot 不直接支援它們 關係中的複合鍵如果您的資料來源使用多個列作為鍵，則在許多情況下您需要建立 一個計算列，將這些部分連接成一個單獨的鍵 並將其用作關係欄位。

當日期格式無法被引擎識別時（例如，不常見的區域格式日期或像 01032009 這樣的整數被匯入為文字），您可以建立以下公式：

=DATE（右（，4），左（，2），中（，3，2））

透過這種表達方式， 您從字串中提取的片段重建有效的 SQL Server 日期這樣一來，您就可以毫無問題地使用時間智慧功能。

您也可以 使用公式更改資料類型將日期或數字字串乘以 1,0 可將其轉換為數字，或與空字串連接可將其轉換為文字。此外，還有一些特定函數可以控制回傳值類型（例如，截斷小數、強制傳回整數等）。

列和度量中的條件值和錯誤處理

就像 Excel 一樣，DAX 也包含以下用途的函數： 根據條件回傳結果 並能優雅地處理錯誤。例如，您可以使用巢狀的 IF 語句，根據經銷商的年銷售額將其標記為「優選」或「超值」。

然而，在計算列中， 你不能容忍部分行出錯而其他行沒有錯誤。如果某一行出現錯誤，則整列都會被標記為錯誤。這比傳統電子表格需要更嚴格的錯誤控制。

為防止簡單的除以零或空值導致整列資料出錯，建議 在先前的檢查中包裝敏感操作 使用 IF 和資訊函數，即使資料組合很奇怪，也總是傳回有效值。

在建立模型時，它可能很有用。 讓錯誤在一開始就出現，這樣你才能找到並修正它們。但是，一旦你將其發布給其他用戶，就必須確保公式安全，並且在資料透視表或視覺化圖表中不會出現任何錯誤訊息。

時間智能：累計總計、比較和自訂時間段

DAX 的一大吸引力在於其時間智慧特性。 它們可讓您處理日期範圍、計算累計總數、比較時間段以及產生自訂時間視窗。 只要你的日曆表配置良好，就能相對輕鬆完成。

可以製定措施 按日、月、季或年累計銷售額使用特定時間函數計算每個期間的期初和期末餘額，或比較一年與上一年、一個季度與一個季度等的銷售額。

此外，您還可以恢復 自訂日期集例如“促銷活動開始後的前 15 天”或“去年同期”，然後將該集合傳遞給函數，該函數會匯總該特定日期範圍內的資料。

諸如 PARALLELPERIOD 等與平行週期相關的函數 它們有助於對時間間隔進行比較。例如，分析某項活動與往年同期相比，是否取得了更好的效果。

數值排名與比較：前 N 名與動態排名

當你需要展示的時候 僅包含最相關的元素 （例如，10 款最暢銷的產品），您有兩種主要方法：在資料透視表上使用 Excel 的篩選功能，或使用 DAX 建立動態排名。

Excel在資料透視表中提供「前10名」類型的篩選器， 非常容易設定 僅顯示給定數值欄位上方或下方的項目。您可以依照項目數、累積百分比或數值總和進行篩選。

這種方法的弊端在於： 此濾鏡僅用於展示效果。如果基礎資料發生變化，您需要手動更新資料透視表，篩選器才能正確反映這些變化。此外，您不能在其他 DAX 公式中重複使用該排名。

另一種方法是建立一個 用於分配排名的計算列或度量值 使用 DAX 對每個元素進行計算。這種方法計算量更大，但也有優勢：排名會動態重新計算，並且可以用於資料切片器，讓使用者可以選擇查看前 5 名、前 10 名、前 50 名等等。

然而，在擁有數百萬行資料的模型中， 動態排名可能很繁瑣。 因此有必要評估性能上的損失是否能被其帶來的功能性收益所抵消。

在 Power Pivot 中設計 DAX 模型時的最佳實踐

對於 Power Pivot 中的 DAX 模型而言，僅僅公式「有效」是不夠的，它還不足以保證模型的可維護性和良好性能。 建議遵循一系列良好做法。 這在實際專案中會產生很大的影響。

一條反覆出現的建議是： 當計算實際上是動態聚合時，應優先使用度量值而不是計算列。 且不是固定屬性。計算列會佔用記憶體並一次重新計算，而度量值僅在需要時才進行評估。

它也非常有用 在 DAX (VAR) 中使用變數 為了簡化複雜的公式，避免多次重複相同的計算，並提高可讀性，這有助於提升模型的效能，也便於他人審查時理解模型。

最後，清晰的名稱和極簡的內部文件至關重要。 給測量值和列賦予描述性名稱。避免使用晦澀難懂的縮寫，並記錄最重要的公式。這可以降低新用戶的學習難度，也能避免幾個月後你再次使用該模型時遇到麻煩。

掌握 Power Pivot 中的 DAX 函數，不是要記住所有的函數，而是要理解公式如何與關係模型、篩選上下文和資料更新互動。 具備紮實的計算列、度量值、時間函數、錯誤處理和良好設計實踐的基礎您的表格模型將變得更加靈活，更容易分析，最重要的是，只需將幾個欄位拖曳到透視表中，即可回答複雜的業務問題。