Excel DAX - 公式

上一個
下一個

DAX 是一種用於在 Power Pivot 表中建立自定義計算的公式語言。您可以使用專為處理關係資料並在 DAX 公式中執行動態聚合而設計的 DAX 函式。

DAX 公式與 Excel 公式非常相似。要建立 DAX 公式，您需要鍵入等號，後跟函式名稱或表示式以及任何必需的值或引數。

DAX 函式與 DAX 公式

DAX 公式可以包含 DAX 函式並利用其用法。這是 DAX 公式在重要方面與 DAX 函式不同的原因。

  • DAX 函式始終引用完整的列或表。如果您只想使用表或列中的特定值，則可以向公式新增篩選器。

  • 如果您想基於逐行方式自定義計算，Power Pivot 提供了一些函式，讓您可以使用當前行值或相關值來執行根據上下文變化的計算。

  • DAX 包含一種型別的函式，它返回表作為其結果，而不是單個值。這些函式可用於為其他函式提供輸入，從而計算整個表或列的值。

  • 一些 DAX 函式提供時間智慧，使您可以使用有意義的日期範圍建立計算，並在並行期間比較結果。

瞭解DAX公式語法

每個DAX公式都具有以下語法 -

  • 每個公式必須以等號開頭。

  • 在等號右側，您可以鍵入或選擇函式名稱，或鍵入表示式。該表示式可以包含由DAX運算子連線的表名和列名。

以下是一些有效的DAX公式 -

  • [column_Cost] + [column_Tax]
  • = Today ()

瞭解IntelliSense功能

DAX 提供了 IntelliSense 功能，使您能夠快速準確地編寫 DAX 公式。使用此功能，您無需完全鍵入表、列和函式名稱，而是在編寫 DAX 公式時從下拉列表中選擇相關名稱。

  • 開始鍵入函式名稱的前幾個字母。自動完成將顯示一個包含名稱以這些字母開頭的可用函式的列表。

  • 將指標放在任何函式名稱上。將顯示 IntelliSense 工具提示，提供函式的用途。

  • 單擊函式名稱。函式名稱將出現在公式欄中，並顯示語法，這將在您選擇引數時為您提供指導。

  • 鍵入您想要的表名的第一個字母。自動完成將顯示一個包含名稱以該字母開頭的可用表和列的列表。

  • 按 TAB 或單擊名稱以將自動完成列表中的專案新增到公式中。

  • 單擊Fx按鈕以顯示可用函式的列表。要從下拉列表中選擇一個函式，請使用箭頭鍵突出顯示該專案，然後單擊“確定”以將該函式新增到公式中。

  • 透過從可能的表和列的下拉列表中選擇它們或鍵入所需的值來為函式提供引數。

強烈建議使用此方便的 IntelliSense 功能。

在哪裡使用DAX公式？

您可以在建立計算列和計算欄位時使用DAX公式。

  • 您可以在計算列中使用 DAX 公式，方法是新增一列，然後在公式欄中鍵入表示式。您可以在 PowerPivot 視窗中建立這些公式。

  • 您可以在計算欄位中使用DAX公式。您可以建立這些公式 -

    • 在 Excel 視窗的“計算欄位”對話方塊中，或

    • 在 Power Pivot 視窗的表的計算區域中。

相同的公式在計算列或計算欄位中使用時，其行為可能會有所不同。

  • 在計算列中，公式始終應用於列中的每一行，貫穿整個表。根據行上下文，值可能會發生變化。

  • 但是，在計算欄位中，結果的計算在很大程度上取決於上下文。也就是說，透視表的結構以及行和列標題的選擇會影響計算中使用的值。

瞭解DAX中的上下文概念以編寫DAX公式非常重要。在您開始使用 DAX 的初期，這可能有點困難，但是一旦您掌握了它，您就可以編寫有效且滿足複雜動態資料分析需求的 DAX 公式。有關詳細資訊，請參閱章節 - DAX 上下文。

建立DAX公式

您已經在上一節中瞭解了 IntelliSense 功能。在建立任何 DAX 公式時請務必使用它。

要建立 DAX 公式，請使用以下步驟 -

  • 鍵入等號。

  • 在等號右側，鍵入以下內容 -

    • 鍵入函式或表名的第一個字母，並從下拉列表中選擇完整名稱。

    • 如果您選擇了函式名稱，請鍵入括號“（”。

    • 如果您選擇了表名，請鍵入方括號“[”。鍵入列名的第一個字母，並從下拉列表中選擇完整名稱。

    • 用“]”關閉列名，用“)”關閉函式名。

    • 在表示式之間鍵入 DAX 運算子或鍵入“,”以分隔函式引數。

    • 重複步驟 1 - 5，直到DAX公式完整。

例如，您想查詢東部地區的總銷售額。您可以編寫如下所示的DAX公式。East_Sales是表名。Amount是表中的一列。

SUM ([East_Sales[Amount])

正如在章節 - DAX 語法中所討論的，建議在每次引用任何列名時都使用表名以及列名。這被稱為 - “完全限定名稱”。

DAX公式會根據它是用於計算欄位還是計算列而有所不同。有關詳細資訊，請參閱以下部分。

為計算列建立DAX公式

您可以在 Power Pivot 視窗中為計算列建立DAX公式。

  • 單擊要新增計算列的表的選項卡。
  • 單擊功能區上的“設計”選項卡。
  • 單擊“新增”。
  • 在公式欄中鍵入計算列的DAX公式。
= DIVIDE (East_Sales[Amount], East_Sales[Units])

此DAX公式對East_Sales表中的每一行執行以下操作 -

  • 將一行中Amount列的值除以同一行中Units列的值。

  • 將結果放置在同一行的新增列中。

  • 重複步驟 1 和 2，直到它完成表中的所有行。

您使用上述公式添加了一列，用於顯示這些單位的銷售單價。

  • 如您所見，計算列也需要計算和儲存空間。因此，僅在必要時才使用計算列。儘可能使用計算欄位。

有關詳細資訊，請參閱章節 - 計算列。

為計算欄位建立DAX公式

您可以在 Excel 視窗或 Power Pivot 視窗中為計算欄位建立DAX公式。對於計算欄位，您需要預先提供名稱。

  • 要在 Excel 視窗中為計算欄位建立 DAX 公式，請使用“計算欄位”對話方塊。

  • 要在 Power Pivot 視窗中為計算欄位建立 DAX 公式，請單擊相關表中計算區域中的一個單元格。以 CalculatedFieldName:= 開頭編寫DAX公式。

例如，Total East Sales Amount:=SUM ([East_Sales[Amount])

如果您在 Excel 視窗中使用“計算欄位”對話方塊，則可以在儲存公式之前檢查公式，並養成檢查公式是否正確的習慣。

有關這些選項的更多詳細資訊，請參閱章節 - 計算欄位。

使用公式欄建立DAX公式

Power Pivot 視窗也有一個公式欄，類似於 Excel 視窗公式欄。公式欄使建立和編輯公式變得更容易，並使用自動完成功能最大程度地減少語法錯誤。

  • 要輸入表名，請開始鍵入表名。公式自動完成將提供一個包含以這些字母開頭的有效表名的下拉列表。您可以從一個字母開始，並鍵入更多字母以縮小列表範圍（如果需要）。

  • 要輸入列名，您可以從所選表中的列名列表中選擇它。在表名右側鍵入方括號“[”，然後從所選表中的列列表中選擇該列。

使用自動完成的提示

以下是使用自動完成的一些提示 -

  • 您可以在DAX公式中巢狀函式和公式。在這種情況下，您可以在包含巢狀函式的現有公式的中間使用公式自動完成。插入點之前的文字用於在下拉列表中顯示值，插入點之後的文字保持不變。

  • 您為常量建立的已定義名稱不會顯示在自動完成下拉列表中，但您仍然可以鍵入它們。

  • 不會自動新增函式的結束括號。您需要自己新增。

  • 您必須確保每個函式在語法上都是正確的。

瞭解插入函式功能

您可以在 Power Pivot 視窗和 Excel 視窗中找到標記為fx的“插入函式”按鈕。

  • Power Pivot 視窗中的“插入函式”按鈕位於公式欄左側。

  • Excel 視窗中的“插入函式”按鈕位於右側“計算欄位”對話方塊的“公式”右側。

單擊fx按鈕時，會顯示“插入函式”對話方塊。“插入函式”對話方塊是查詢與 DAX 公式相關的 DAX 函式的最簡單方法。

“插入函式”對話方塊可幫助您按類別選擇函式，並提供每個函式的簡短說明。

Understanding Insert Function

在 DAX 公式中使用插入函式

假設您要建立以下計算欄位：

Medal Count: = COUNTA (]Medal])

您可以使用以下步驟使用“插入函式”對話方塊：

  • 單擊“結果”表的計算區域。
  • 在公式欄中鍵入以下內容：
Medal Count: =
  • 單擊“插入函式”按鈕 (fx)。

出現“插入函式”對話方塊。

  • 在“選擇類別”框中選擇“統計”，如下面的螢幕截圖所示。

  • 在“選擇函式”框中選擇“COUNTA”，如下面的螢幕截圖所示。

Using Insert Function

您可以看到，已顯示選定的 DAX 函式語法和函式說明。這使您可以確保它是您要插入的函式。

  • 單擊“確定”。“Medal Count:=COUNTA(”將顯示在公式欄中，並且還會出現一個顯示函式語法的工具提示。

  • 鍵入“[。這意味著您即將鍵入列名。當前表中所有列和計算欄位的名稱都將顯示在下拉列表中。您可以使用 IntelliSense 來完成公式。

  • 鍵入 M。下拉列表中顯示的名稱將限制為以“M”開頭的名稱。

  • 單擊“Medal”。

Click Medal

  • 雙擊“Medal”。“Medal Count: = COUNTA([Medal]”將顯示在公式欄中。關閉括號。

  • 按 Enter 鍵。您已完成。您也可以使用相同的過程來建立計算列。您還可以按照相同的步驟在 Excel 視窗的“計算欄位”對話方塊中使用“插入函式”功能插入函式。

  • 單擊“公式”右側的“插入函式”(fx)按鈕。

出現“插入函式”對話方塊。其餘步驟與上述步驟相同。

在 DAX 公式中使用多個函式

DAX 公式最多可以包含 64 個巢狀函式。但是，DAX 公式不太可能包含如此多的巢狀函式。

如果 DAX 公式包含許多巢狀函式，則具有以下缺點：

  • 建立公式將非常困難。
  • 如果公式存在錯誤，則除錯將非常困難。
  • 公式評估速度不會很快。

在這種情況下，您可以將公式拆分為更小的可管理公式，並逐步構建大型公式。

使用標準聚合建立 DAX 公式

執行資料分析時，您將對聚合資料執行計算。DAX 中有幾個聚合函式（如 SUM、COUNT、MIN、MAX、DISTINCTCOUNT 等）可用於 DAX 公式。

您可以使用 Power Pivot 視窗中的“自動求和”功能自動建立使用標準聚合的公式。

  • 單擊 Power Pivot 視窗中的“結果”選項卡。將顯示“結果”表。
  • 單擊“Medal”列。將選擇整列 -“Medal”。
  • 單擊功能區上的“開始”選項卡。
  • 單擊“計算”組中“自動求和”旁邊的向下箭頭。
Creating a DAX Formula Using Standard Aggregations
  • 在下拉列表中單擊“COUNT”。
Click Count

您可以看到，計算欄位“Medal 的計數”出現在列“Medal”下方的計算區域中。DAX 公式也顯示在公式欄中：

Count of Medal: = COUNTA([Medal])

“自動求和”功能為您完成了工作 - 建立了用於資料聚合的計算欄位。此外，“自動求和”還採用了 DAX 函式“COUNT”的適當變體，即“COUNTA”（DAX 具有“COUNT”、“COUNTA”、“COUNTAX”函式）。

注意 - 要使用“自動求和”功能，您需要單擊功能區上“自動求和”旁邊的向下箭頭。如果您改為單擊“自動求和”本身，則會得到：

Sum of Medal: = SUM([Medal])

並會標記錯誤，因為“Medal”不是數字資料列，並且列中的文字無法轉換為數字。

Error

您可以參考“DAX 錯誤參考”一章以瞭解有關 DAX 錯誤的詳細資訊。

DAX 公式和關係模型

如您所知，在 Power Pivot 的資料模型中，您可以處理多個數據表，並透過定義關係來連線這些表。這將使您能夠建立有趣的 DAX 公式，這些公式使用相關表之間列的相關性進行計算。

在兩個表之間建立關係時，您需要確保用作鍵的兩個列的值至少在大多數行（如果不是全部）上匹配。在 Power Pivot 資料模型中，鍵列中可能存在不匹配的值，並且仍然可以建立關係，因為 Power Pivot 不強制實施引用完整性（有關詳細資訊，請參閱下一部分）。但是，鍵列中存在空白或不匹配的值可能會影響 DAX 公式的結果和資料透視表的顯示。

引用完整性

建立引用完整性涉及構建一組規則，以便在輸入或刪除資料時保持表之間定義的關係。如果您沒有專門確保這一點（因為 Power Pivot 不強制實施），則在進行資料更改之前建立的 DAX 公式可能無法獲得正確的結果。

如果實施引用完整性，則可以防止以下問題：

  • 在相關表中新增行時，主表中沒有關聯行（即，鍵列中的值匹配）。

  • 更改主表中的資料，這將導致相關表中出現孤立行（即，鍵列中資料值為在主表鍵列中沒有匹配值的行的行）。

  • 刪除主表中的行時，相關表的行中有匹配的資料值。

列印頁面
上一頁
下一頁
廣告

© . All rights reserved.