DAX WINDOW Function 窗函數｜計算移動平均的好幫手

先備知識

由於本文會提及一些相關概念，在正式開始以前，請先確認你已經知道以下幾個名詞與代表的意涵。

  • Row Context
  • AVERAGEX 函數
  • ALLSELECTED 函數
  • SUMMARIZE函數

另外，本文章是 WINDOW 函數系列的第二篇文章，若想要觀看第一篇介紹 OFFSET 用法，請點此觀看。

【🆓 免費資源】從零開始，理解 Power BI 與 DAX 函數底層邏輯 🚀
如果你想：
  • 擺脫用 Excel 製作重複性報表的無限輪迴
  • 克服用 Excel 只能存一百萬列資料之限制
  • 徹底理解 Power BI 與 DAX 函數底層邏輯
  • 了解學習 Power BI 在就業市場的競爭力
歡迎點擊下方按鈕，報名免費課程。
我們還會送你一套《DAX 函數從零到壹學習藍圖》！萃取數十個常用函數，幫助你學習有方向！
免費 Power BI & DAX 課程 立即報名免費課程，領取藍圖！
—— Stark：雖然是免費課程，但內容絕對不馬虎，推薦你找時間好好看完！

WINDOW Function

微軟於 2022 年 12 月的更新中，釋出了三個函數，分別是：

  1. INDEX
  2. OFFSET
  3. WINDOW

微軟給這些函數一個集合名詞：「WINDOW 函數」。

如果你是很常使用 SQL 的使用者，那麼你應該對 SQL 中的 WINDOW 函數不陌生。Power BI DAX 中的這三款 WINDOW 函數可以達到與 SQL 中的 WINDOW 函數非常相似，但又有些差異。

相同的點在於，無論是 DAX 或者 SQL，兩者的 WINDOW 函數都是針對一資料表中的一群資料列（row）進行運算。

相反地，在 DAX 中，判斷「當前列（current row）」來用以計算的依據是 DAX 內獨有的 Row Context。另外，在 DAX 中，WINDOW 函數所回傳的是資料表而非純量數值（scalar value）。而這資料表可以被用來使用在 CALCULATE 或是其他 Iterator 函數如 SUMX 中。

INDEX vs. OFFSET vs. WINDOW

至於這些函數的計算依據與用途有哪些呢？就讓我們看下面的表格來簡單理解。

INDEX OFFSET WINDOW
計算依據 絕對位置 相對位置 絕對或相對位置
計算應用 1. 目標值與基準值比較
2. 查找特定項目 		當前值與先前值比較 1. 累加總計（Running Total）
2. 移動平均（Moving Average）
3. 其它以子數據組為依據的計算

從上表我們可以得知三個 WINDOW 函數所適用的情境均不相同。

在這篇文章，我們將專注於 WINDOW 函數中的 WINDOW 函數（好像繞口令 😂），理解它的定義，並且提供應用範例。

WINDOW Function（窗口函數）是什麼？

定義

根據微軟的官方，WINDOW 函數定義如下：

公式若超出邊界可以往右滑動唷👉

WINDOW ( <from> [, <fromType>], <to> [, <toType>] [, <relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] )

參數

參數 說明
from
  • 指定 WINDOW 計算的起始位置，可以是任何回傳純量數值的 DAX 表達式。
  • 起始位置的計算取決於 <fromType> 參數。
    • 如果 <fromType> 為 REL，WINDOW 中第一列位置則為從當前列往回（負值）或向前（正值）來獲取。
    • 如果 <fromType> 為 ABS，並且 <from> 是正整數。WINDOW 中第一列位置則為從當前 partition 中的第一列開始起算。
      • 索引是從 1 開始起算。
      • 1 表示 WINDOW 從 partition 的第一列開始。
      • 如果 <from> 是負數，則窗口是從 partition 的最後一列起算。
      • -1 表示 WINDOW 為 partition 的最後一列。
fromType
  • （可選）改變 <from> 參數的行為。
  • 數值為 ABS（絕對） 或 REL（相對）。
  • 默認數值為 REL。
    • to
    • 指定 WINDOW 計算的結束位置，可以是任何回傳純量數值的 DAX 表達式。
    • 結束位置的計算取決於 <toType> 參數。
      • 如果 <toType> 為 REL，WINDOW 中最末列位置則為從當前列往回（負值）或向前（正值）來獲取。
      • 如果 <toType> 為 ABS，並且 <to>是正整數。則為窗口結束點距 partition 開始點的位置。
        • 索引是從 1 開始起算。
        • 1 表示 WINDOW 從 partition 的第一列開始。
        • 如果 <to> 是負數，則窗口是從 partition 的最後一列起算。
        • -1 表示 WINDOW 為 partition 的最後一列。
    toType
  • （可選）改變 <to> 參數的行為。
  • 數值為 ABS（絕對） 或 REL（相對）。
  • 默認數值為 REL。
    • relation
  • （可選）任何回傳資料列的資料表運算式。
  • 若 有指定此參數，則 <orderBy> 和 <partitionBy> 中指定的資料行必須來自此參數內的資料行。
  • 若 忽略此參數，則 <orderBy> 和 <partitionBy> 必須是完整的資料行，並且來自同一張資料表。
  • 預設為使用 ALLSELECTED() 中 <orderBy> 和 <partitionBy> 的所有列。
    • blanks
  • （可選）定義遇到 blank 該如何排序的 enumeration。
  • 這個當前是參數保留給未來使用。
  • 目前唯一支援的數值是 KEEP，同時也是預設數值。
    • partitionBy
  • （可選）定義遇到 <relation> 的資料表該如何被 partition。
  • 如果忽略，則整個資料表會當作是一個 partition 而已。
    •

    回傳值

    來自當前窗格中的所有資料列。

    看完以上的定義以後還是很難一眼看出來在幹嘛嗎？

    沒關係，我跟你一樣，當初第一次看到這個函數的時候也是滿頭問號，想說到底要怎麼使用？

    請看下一個小節的問題情境你就會明白 WINDOW 用法了。

    問題情境

    為了簡單說明 WINDOW Function 用法以及適合解決的問題情境，我們需要先了解最後的結果會長成什麼樣子。

    如同在文章開頭所說的，WINDOW Function 特別適合用來計算移動平均類的問題。

    因此，在下列的範例中，我們將要計算過去三個月的移動平均銷售額，量值的名稱為 [Moving 3 Months Average Sales]。最後視覺化的結果如下列截圖所示。

    WINDOW Function
    WINDOW Function 示例

    如上圖所示，我們總共有四個欄位分別是：

    • Fiscal Year：代表財會年度
    • Date：代表月份
    • Total Sales：代表該月份產生的銷售金額
    • Previous Sales：代表該月份過去三個月的移動平均銷售金額

    例如：在 [Moving 3 Months Average Sales] 的紅色框框數值 $2,002,473.56 為來自其左邊紅色框框內 $1,423,357.32、$2,059,567.54、$2,524,495.83 三個數值的平均。而綠色框框的數值計算為相同的道理。

    使用 WINDOW 函數便能使我們輕易地完成 [Moving 3 Months Average Sales] 這個欄位的計算。

    WINDOW Function 範例：計算移動平均

    那麼 WINDOW 究竟要如何使用呢？

    首先，我們要計算的是每三個月的平均值，代表需要一列一列逐列計算，因此可以使用 AVERAGEX 函數。而 WINDOW 是回傳一個多列的資料表，本質上就是一個資料表，我們就可以使用 WINDOW 函數回傳的資料表作為參數傳入 AVERAGEX 函數中。基於上述分析，我們可以建立一個新量值（Measure）：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    Moving 3 Months Average Sales =
    AVERAGEX  (
         WINDOW  (
             -2,
            REL,
             0,
            REL,
             SUMMARIZE  (
                  ALLSELECTED  ( Sales  ),
                 ‘Date'[Fiscal Year],
                 ‘Date'[Month],
                 ‘Date'[MonthKey]
             ),
             ORDERBY  ( ‘Date'[Fiscal Year],  ASC, ‘Date'[MonthKey],  ASC  )
         ),
        [Total Sales]
    )

    其中，根據定義 AVERAGEX 函數第一個參數需要為資料表，因此可以使用 WINDOW 函數回傳的資料表傳入；第二個參數需要 DAX 運算式，因此使用 [Total Sales] 來計算銷售額，至於 [Total Sales] 詳細寫法可以在下方下載 .pbix 檔案。

    想要自己試試看嗎？

    ​

    如果你對文章內使用的 pbix 檔案有興趣

    這裡可以下載👇👇👇

      Built with ConvertKit

      由於第一個參數是一整個 WINDOW 函數，在這函數中共指定六個參數，分別對應到「WINDOW 函數是什麼？」這個小節所提到的定義。以下將就這六個參數內容個別介紹：

      WINDOW 第一個參數

      WINDOW Function 參數說明

      第一個參數我們指定了 -2，加上第二個參數指定 REL，因此計算的起始點為當前列往前平移兩個距離後的位置。

      WINDOW 第二個參數

      第二個參數為 REL，代表計算起始位置的方式為相對值。

      WINDOW 第三個參數

      第三個參數我們指定了 0，加上第四個參數指定 REL，因此計算的終點為當前列。

      WINDOW 第四個參數

      第四個參數為 REL，代表計算起始位置的方式為相對值。

      WINDOW 第五個參數

      第五個參數使用 SUMMARIZE 來回傳一個虛擬資料表。至於 SUMMARIZE 函數的詳細定義與用法不在這篇文章討論的範疇，有興趣可以參考官方文件。

      SUMMARIZE 內第一個參數 ALLSELECTED( Sales ) 會回傳一個資料表。因為使用 ALLSELECTED，所以 Sales 資料表會移除來自資料行與資料列的篩選，但是會保持來自外部的篩選。

      SUMMARIZE 內第一個與第二個參數分別代表該回傳的資料表會依據 Fiscal Year、Month、MonthKey 三個欄位做分群（grouping）。

      WINDOW 第六個參數

      第六個參數使用 ORDERBY ( 'Date'[Fiscal Year], ASC, 'Date'[MonthKey], ASC )，代表 WINDOW 輸出的資料表將以 'Date'[Fiscal Year] 與 'Date'[MonthKey] 兩個欄位做排序。

      WINDOW 函數視覺化結果

      為了更深入理解 WINDOW 函數，我們可以再寫一個量值，用 TOJSON 將 WINDOW 包起來。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      WINDOW Function (TOJSON) =
      TOJSON  (
           WINDOW  (
               -2,
              REL,
               0,
              REL,
               SUMMARIZE  (
                    ALLSELECTED  ( Sales  ),
                   ‘Date'[Fiscal Year],
                   ‘Date'[Month],
                   ‘Date'[MonthKey]
               ),
               ORDERBY  ( ‘Date'[Fiscal Year],  ASC, ‘Date'[MonthKey],  ASC  )
           )
      )
      WINDOW Function 參數說明

      從上圖的紅框中可以知道，在計算 2017 Sep 這個當前列前三個月的移動平均時，其所計算的依據為 2017 Jul、2017 Aug、2017 Sep。

      同理，綠框處的 2017 Aug 計算時，由於往前推不滿三個月，因此會依據 2017 Jul、2017 Aug 來計算。

      透過 TOJSON 這個函數可以使我們更輕易地理解計算過程中的變數，同時也可以做來驗證。

      WINDOW Function 搭配 Parameter 動態更新

      上述的例子使用的窗格是三個月，但也有可能前端使用者在看報表時會想要看不同個月下的移動平均結果。

      為了達到這個目標，我們可以使用 Power BI 內建的 Parameter 功能，將其作為篩選器，讓前端使用者選擇 0~11 的數值（代表用過去 1~12 個月作為窗口），進而動態更新計算。

      視覺化後的結果如下列所示。

      WINDOW Function 搭配 Parameter

      至於如何使用 Parameter 則不在本文的討論範圍，不過我也有提供範例檔，如果你有興趣也可以下載來玩看看。

      想要自己試試看嗎？

      ​

      如果你對文章內使用的 pbix 檔案有興趣

      這裡可以下載👇👇👇

        Built with ConvertKit

        結語

        本文從微軟所釋出的 WINDOW 函數開始，分別介紹 WINDOW Function 的定義，並提供一個簡單的範例，最後還有延伸的應用。

        當然，更多的應用就要依據商業需求而定了。

        發佈留言

        發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *