Power Pivot 中的 DAX 案例

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

本節提供的連結將由範例示範 DAX 公式在下列案例中的使用方式。

  • 執行複雜計算

  • 處理文字與日期

  • 條件值及測試錯誤

  • 使用時間智慧

  • 對值進行排名與比較

本文內容

使用者入門

範例資料

其他資源

案例:執行複雜計算

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

將篩選套用至公式

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

使用來自外部迴圈的值

案例:處理文字與日期

藉由串連建立索引鍵資料行

根據擷取自文字日期的日期部分建構日期

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

使用公式變更資料類型

案例:條件值及測試錯誤

根據條件建立值

測試公式之中的錯誤

案例:使用時間智慧

計算累計銷售額

比較歷經一段時間的值

計算落入自訂日期範圍內的值

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

在樞紐分析表中只顯示前 10 名項目

使用公式以動態方式排序項目

使用者入門

取樣資料

如果您是 DAX 公式的新手,請先參閱 Power Pivot 樣本資料的範例。如需詳細資訊,請參閱取得 DAX 和資料模型範例資料的教學課程

其他資源

您也可能會想要造訪DAX 資源中心 Wiki您可以在哪裡找到各種不同的 DAX 包含部落格、 範例、 白皮書和產業前置專業人員和 Microsoft 提供視訊的相關資訊。

案例:執行複雜計算

DAX 公式可以執行涉及自訂彙總、篩選與使用條件值的複雜計算。本節提供有關如何使用自訂計算的入門範例。

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

CALCULATE 和 CALCULATETABLE 是既靈活又強大的函數,在定義導出欄位方面相當實用。這些函數可以讓您變更計算將要執行之所在的內容。您也可以自訂希望執行的彙總或數學運算的類型。如需相關範例,請參閱下列主題。

將篩選套用至公式

在 DAX 函數採用資料表做為引數的大部分情況下,您通常可以改為傳入已篩選的資料表,也就是使用 FILTER 函數代替資料表名稱,或是指定篩選運算式當做函數的其中一個引數。下列主題提供的範例會說明篩選的建立方式,以及篩選將如何影響公式的結果。如需詳細資訊,請參閱<篩選 DAX 公式中的資料>。

FILTER 函數可以讓您使用運算式來指定篩選準則,而其餘的函數則是專為篩除空白值所設計。

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

藉由在公式中建立動態篩選,您便能夠輕易回答類似下列的問題:

  • 目前產品的銷售量在年度總銷售量所佔的比重是多少?

  • 這個部門在公司營運至目前的累計總利潤中所佔的比重是多少?

樞紐分析表的內容,會影響您在樞紐分析表中使用的公式,但您可以選擇性地變更藉由新增或移除篩選的內容。所有的主題中的範例會顯示您如何進行此動作。若要尋找銷售額的比率特定的轉售商銷售上所有的轉售商,您可以建立的量值會計算目前內容除以所有內容] 的值] 的值。

ALLEXCEPT 主題會提供如何選擇性地在公式上清除篩選的範例。這兩個範例都將逐步解說何以結果會因樞紐分析表的設計而異的情形。

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

使用來自外部迴圈的值

DAX 不但可在計算中使用來自目前內容的值,也能使用來自前一個迴圈的值進而建立一組相關計算。下列主題將逐步解說如何建置公式參考來自外部迴圈的值。EARLIER 函數支援多達兩層的巢狀迴圈。

若要深入了解資料列內容和相關資料表,以及在公式中如何運用此概念的詳細資訊,請參閱<DAX 公式中的內容>。

案例:處理文字與日期

本節提供 DAX 參考主題的連結,這些主題包含了關於處理文字、擷取及建構日期和時間值,或是根據條件建立值等等幾種常見案例的範例。

藉由串連建立索引鍵資料行

Power Pivot 不允許使用複合索引鍵,所以如果您的資料來源有複合索引鍵,則您可能需要將其結合成為單一索引鍵資料行。下列主題提供的範例將示範如何根據複合索引鍵,建立導出資料行。

根據擷取自文字日期的日期部分建構日期

Power Pivot 是用 SQL Server 日期/時間資料類型來處理日期,所以如果您的外部資料包含了採用不同格式的日期,比如您的日期寫法為 Power Pivot 資料引擎無法辨識的地區日期格式,或者您的資料有使用整數 Surrogate 索引鍵,那麼您可能必須使用 DAX 公式擷取日期部分,然後將這些部分建構成有效的日期/時間表示法。

例如,假設您有一個日期資料行原本是以整數表示,爾後當您要將其匯入為文字字串時,就可以使用下列公式將字串轉換成日期/時間值:

=DATE(RIGHT([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 所執行的隱含資料類型轉換來操作輸出資料類型。如需有關類型轉換的詳細資訊,請參閱取得 DAX 和資料模型範例資料的教學課程

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

    = (TODAY ()-3) * 1.0

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

    =""& A2>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 公式的功能較為強大,尤其若將次序值加入至交叉分析篩選器,則只需要按一下交叉分析篩選器,即可變更所顯示的最高值數目。不過,由於計算過程相當耗費資源,這種方法可能不太適合有很多資料列的資料表使用。

在樞紐分析表中只顯示前 10 名項目

若要在樞紐分析表中顯示最高或最低值

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

  2. 選取 [值篩選]> [前 10 項]

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

    1. 選取 [最前] 以查看具有最高值的資料格,或選取 [最低] 查看具有最低值的資料格。

    2. 輸入您要查看之最高值或最低值數目。預設值是 10。

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

名稱

描述

選取這個選項可篩選樞紐分析表,只依項目值顯示最高值項目或最低值項目的清單。

百分比

選取這個選項可篩選樞紐分析表,只顯示加總成所指定百分比的項目。

加總

選取這個選項可顯示最高值項目或最低值項目的值總和。

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

  2. 按一下 [確定]

使用公式以動態方式排序項目

下列主題包含範例,說明如何使用 DAX 來建立儲存於導出資料行中的排名。因為 DAX 公式會以動態方式計算,所以即使基礎資料已經變更,您也一定能夠確定排名是正確的。而且,由於公式是用在導出資料行中,您可以在交叉分析篩選器中使用排名,然後選取前 5 名、前 10 名甚至前 100 名的值。

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×