Power Pivot 中的 DAX 案例

本節提供範例的連結,示範如何在下列案例中使用 DAX 公式。

  • 執行複雜計算

  • 使用文字與日期

  • 條件值及測試錯誤

  • 使用時間智慧

  • 排名與比較值

本文內容

開始使用

案例:執行複雜計算

為樞紐分析表建立自訂計算

將篩選套用至公式

選擇性地移除篩選以建立動態比率

使用來自外部迴圈的值

案例:使用文字與日期

依連接建立索引鍵資料行

根據從文字日期提取的日期部分來撰寫日期

定義自訂的日期或數位格式

使用公式變更資料類型

案例:條件值及測試錯誤

根據條件建立值

測試公式中的錯誤

案例:使用時間智慧

計算累計銷售額

比較一段時間的值

計算自訂日期範圍內的值

案例:對值進行排名與比較

在樞紐分析表中只顯示前十個專案

使用公式以動態方式排序專案

開始使用

造訪Dax 資源中心 Wiki ,您可以在其中找到所有有關 DAX 的資訊,包括由業界領先的專業人員和 Microsoft 提供的博客、範例、白皮書及影片。

案例:執行複雜計算

DAX 公式可以執行複雜的計算,涉及自訂匯總、篩選及使用條件值。 本節提供如何開始使用自訂計算的範例。

為樞紐分析表建立自訂計算

[計算] 和 [CALCULATETABLE] 是功能強大且彈性的函數,可用於定義計算欄位。 這些函數可讓您變更將在其中執行計算的內容。 您也可以自訂要執行的匯總或數學運算類型。 如需範例,請參閱下列主題。

將篩選套用至公式

在大部分的 DAX 函數將資料表做為引數時,您通常可以使用 FILTER 函數(而不是資料表名稱),或將篩選運算式指定為其中一個函數引數,來傳遞篩選的資料表。 下列主題提供如何建立篩選,以及篩選會如何影響公式結果的範例。 如需詳細資訊,請參閱篩選 DAX 公式中的資料

FILTER 函數可讓您使用運算式來指定篩選準則,而其他函數則是專門用來篩選掉空白值。

選擇性地移除篩選以建立動態比率

您可以在公式中建立動態篩選,輕鬆回答下列問題:

  • 目前產品的銷售量對一年總銷售額有何貢獻?

  • 這個部門對所有營業年份的總利潤有多少貢獻,與其他部門相比有多少?

您在樞紐分析表中使用的公式可能會受到樞紐分析表內容的影響,但您可以透過新增或移除篩選來選擇性地變更內容。 [全部] 主題中的範例會說明如何執行此動作。 若要在所有轉售商的銷售額中找出特定轉銷商銷售額的比率,您可以建立一個測度,以計算目前內容的值除以 [所有內容] 的值。

ALLEXCEPT 主題提供如何在公式中選擇性地清除篩選的範例。 兩個範例會逐步引導您完成結果如何隨著樞紐分析表的設計而變化。

如需如何計算比率和百分比的其他範例,請參閱下列主題:

使用來自外部迴圈的值

除了在計算中使用目前內容的值之外,DAX 還可以在建立一組相關的計算中使用前一個迴圈的值。 下列主題提供如何建立公式的逐步解說,該公式會參照外部迴圈的值。 舊版函數最多可支援兩個層級的嵌套迴圈。

若要深入瞭解列內容和相關資料表,以及如何在公式中使用這個概念,請參閱DAX 公式中的內容

案例:使用文字與日期

本節提供 DAX 參考主題的連結,其中包含使用文字、解壓縮與撰寫日期和時間值,或根據條件建立值的常見案例範例。

依連接建立索引鍵資料行

Power Pivot 不允許複合鍵;因此,如果您的資料來源中有複合鍵,您可能需要將它們合併成單一索引鍵欄。 下列主題提供一個範例,說明如何根據複合鍵建立計算結果欄。

根據從文字日期提取的日期部分來撰寫日期

Power Pivot 使用 SQL Server 日期/時間資料類型來處理日期;因此,如果您的外部資料包含以不同方式格式化的日期(例如,如果您的日期是以 Power Pivot 資料引擎無法辨識的區域日期格式撰寫),或是如果您的資料使用整數代用鍵,則您可能需要使用 DAX 公式來提取日期部分,然後將這些部分組成有效的日期/時程表示。

例如,如果您的日期欄已表示為整數,然後匯入為文字字串,您可以使用下列公式,將字串轉換成日期/時間值:

= DATE (右([Value1],4),LEFT ([Value1],2),MID ([value1],2))

Value1

結果

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

下列主題提供有關用來提取及撰寫日期之函數的詳細資訊。

定義自訂的日期或數位格式

如果您的資料包含不是以其中一個標準 Windows 文字格式表示的日期或數位,您可以定義自訂格式,以確保正確處理值。 將值轉換成字串或從字串轉換時會使用這些格式。 下列主題也提供可用於處理日期和數位的預先定義格式的詳細清單。

使用公式變更資料類型

在 Power Pivot中,輸出的資料類型是由來源資料行所決定,而且您無法明確指定結果的資料類型,因為 Power Pivot會決定最佳的資料類型。 不過,您可以使用由 Power Pivot 執行的隱含資料類型轉換來操作輸出資料類型。 

  • 若要將日期或數位字串轉換成數位,請乘以1.0。 例如,下列公式會計算目前的日期減去3天,然後輸出相對應的整數值。

    = (TODAY ()-3) * 1。0

  • 若要將日期、數位或貨幣值轉換成字串,請將該值連接成空字串。 例如,下列公式會以字串形式傳回今天的日期。

    = "" & TODAY ()

您也可以使用下列函數來確保傳回特定的資料類型:

將實數轉換成整數

案例:條件值及測試錯誤

就像 Excel 一樣,DAX 也有一些函數可讓您測試資料中的值,並根據條件傳回不同的值。 例如,您可以建立一個計算結果欄,以根據年度銷售量的喜好來標示轉銷商。 測試值的函數也可以用來檢查數值的範圍或類型,避免意外的資料錯誤造成中斷計算。

根據條件建立值

您可以使用嵌套 IF 條件來測試值,並依條件產生新值。 下列主題包含一些條件處理和條件值的簡單範例:

測試公式中的錯誤

與 Excel 不同的是,計算結果欄的一列中不能有有效的值,另一列的值無效。 也就是說,如果 Power Pivot 資料行的任何部分出現錯誤,整個資料行都會以錯誤標示,因此您必須務必修正會導致無效值的公式錯誤。

例如,如果您建立一個除以零的公式,您可能會收到無窮大結果或錯誤。 如果函數預期數值時遇到空白值,部分公式也會失敗。 您在開發資料模型時,最好允許顯示錯誤,以便您按一下郵件,並解決問題。 不過,當您發佈活頁簿時,您應該納入錯誤處理,避免不預期的值導致計算失敗。

若要避免傳回計算結果欄中的錯誤,您可以使用邏輯與資訊函數的組合來測試錯誤,並總是傳回有效的值。 下列主題提供一些簡單的範例,說明如何在 DAX 中執行此動作:

案例:使用時間智慧

DAX 時間智慧函數包括可協助您從資料中檢索日期或日期範圍的函數。 然後,您可以使用這些日期或日期範圍來計算跨相似期間的值。 時間智慧函數也包含可搭配標準日期間隔使用的函數,可讓您比較各個月份、年份或季度的值。 您也可以建立公式,比較指定期間內的第一個日期和最後一個日期的值。

如需所有時間智慧函數的清單,請參閱時間智慧函數(DAX)。 如需如何在 Power Pivot 分析中有效使用日期和時間的秘訣,請參閱Power Pivot 中的日期

計算累計銷售額

下列主題包含如何計算結束語與期初餘額的範例。 這些範例可讓您在不同的間隔時間(例如日、月、季或年),建立執行中的餘額。

比較一段時間的值

下列主題包含如何比較不同時段內的總和的範例。 DAX 支援的預設時段為 [月]、[季] 和 [年]。

計算自訂日期範圍內的值

請參閱下列主題,瞭解如何在開始促銷後的前15天內取得自訂日期範圍的範例。

如果您使用時間智慧函數來取得一組自訂的日期,您可以使用該一組日期做為執行計算的函式輸入,以在時段建立自訂匯總。 請參閱下列主題,以取得如何執行此動作的範例:

  • PARALLELPERIOD 函數

    附註: 如果您不需要指定自訂的日期範圍,但使用的是標準會計單位(例如月、季或年),建議您使用專為此用途設計的時間智慧函數(例如 TOTALQTD、TOTALMTD、TOTALQTD 等)來執行計算。

案例:對值進行排名與比較

若要在欄或樞紐分析表中只顯示前 n 個專案,您有數個選項:

  • 您可以使用 Excel 2010 中的功能來建立 [頂端] 篩選。 您也可以在樞紐分析表中選取多個最高或最低值。 本節的第一部分說明如何篩選樞紐分析表中的前10個專案。 如需詳細資訊,請參閱 Excel 檔。

  • 您可以建立可動態對值進行排序的公式,然後依據排名值篩選,或使用排名值做為交叉分析篩選器。 本節的第二部分描述如何建立此公式,然後在交叉分析篩選器中使用該公式排名。

每個方法各有優缺點。

  • Excel 的 [常用] 篩選很容易使用,但篩選只是為了顯示。 如果樞紐分析表基礎的資料變更完畢,您必須手動重新整理樞紐分析表,才能查看所做的變更。 如果您需要動態使用排名,您可以使用 DAX 建立公式,將值與資料行中的其他值進行比較。

  • DAX 公式更為強大;此外,透過將排名值新增到交叉分析篩選器,您只要按一下交叉分析篩選器,就可以變更顯示的上限值數。 不過,計算是成本較高,而且這個方法可能不適合包含許多列的資料表。

在樞紐分析表中只顯示前十個專案

若要在樞紐分析表中顯示頂端或底端的值

  1. 在樞紐分析表中,按一下 [列標籤] 標題中的向下箭號。

  2. 選取 [值篩選]> [前10個]。

  3. 在 [前10個篩選] <[欄名]>對話方塊中,選擇要進行排名的資料行,以及值的數目,如下所示:

    1. 選取 [一層] 以查看具有最高值或下方的儲存格,以查看具有最低值的儲存格。

    2. 輸入您想要查看的最前面或最後面的值數目。 預設值為10。

    3. 選取您要顯示值的方式:

名稱

描述

項目

選取此選項可篩選樞紐分析表,只根據其值顯示最上面或最下方的專案清單。

百分比

選取此選項可篩選樞紐分析表,只顯示已加上指定百分比的專案。

Sum

選取此選項以顯示最前面或最後面專案的值加總。

  1. 選取包含您要排序之值的資料行。

  2. 按一下 [確定]

使用公式以動態方式排序專案

下列主題包含如何使用 DAX 建立儲存在計算結果欄中之排名的範例。 因為 DAX 公式是動態計算的,所以即使基礎資料已變更,您也可以隨時確定排名正確。 此外,因為公式是在計算結果欄中使用,所以您可以在交叉分析篩選器中使用排名,然後選取 [top 5]、[top 10] 或 [top 100] 值。

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×