Power BI DAX 函數：OFFSET 用法與教學｜計算資料平移的好工具！

先備知識

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

  • Row Context
  • CALCULATE 函數
  • Iterator 函數
  • ALLSELECTED 函數
  • PREVIOUSDAY 函數
  • SUMMARIZE 函數

WINDOW 函數

微軟於 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. 其它以子數據組為依據的計算

OFFSET 函數是什麼？

定義

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

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

OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )

參數

參數 說明
delta
  • 任何回傳純量數值的 DAX 表達式。
  • 代表基於當前列要往前或往後幾列計算資料。
  • 往前為負；往後為正。
    •
    relation
  • （可選）任何回傳資料列的資料表運算式。
  • 若有指定此參數，則 <orderBy> 和 <partitionBy> 中指定的資料行必須來自此參數內的資料行。
  • 若忽略此參數，則 <orderBy> 和 <partitionBy> 必須是完整的資料行，並且來自同一張資料表。
  • 預設為使用 ALLSELECTED() 中 <orderBy> 和 <partitionBy> 的所有列。
    • orderBy
  • （可選）定義排序 Partition 的資料行。
  • 若忽略此參數，則 <relation> 必須明確被指定。同時，排序的依據會使用從 <relation> 資料表內扣除指定在 <partitionBy> 的其餘資料行。
    • blanks
  • （可選）定義遇到 blank 該如何排序的 enumeration。
  • 這個當前是參數保留給未來使用。
  • 目前唯一支援的數值是 KEEP，同時也是預設數值。
    •
    partitionBy
  • （可選）定義遇到 <relation> 的資料表該如何被 partition。
  • 如果忽略，則整個資料表會當作是一個 partition 而已。
    •

    回傳值

    一或多列來自 relation 的資料表。

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

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

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

    問題情境

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

    OFFSET 使用在資料平移問題
    圖一、OFFSET 使用在資料平移問題

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

    • Customer：代表顧客
    • Date：代表某一位顧客產生銷售的日期
    • Total Sales：代表該顧客在該日期產生的銷售金額
    • Previous Sales：代表該顧客基於當前日期的前一筆銷售金額

    其中紅色箭頭指的是 Ana Perry 的每筆銷售所對應的前一筆銷售；綠色線指的是 Alexandra Jenkins 每筆銷售對應的前一筆紀錄。

    而使用 OFFSET 函數便能使我們輕易地完成 Previous Sales 這個欄位的計算。

    那麼如果不使用 OFFSET 函數，能夠達成一樣的目標嗎？這就要讓我們來談談為了達成「針對當前顧客的前一筆銷售」這個計算，我們會遇到什麼困難？

    挑戰一：資料窗格不同

    OFFSET 使用在資料平移問題
    圖二、OFFSET 使用在資料平移問題

    在上圖的紅框處中，如果從日期的角度出發，2020/04/22 的前一筆銷售紀錄會是 2020/04/01，而不是綠框處的 2020/04/14。

    這是因為資料被劃分為不同的窗格（WINDOW），上圖的 Ana Perry 是屬於其中一個窗格；Alexandra Jenkins 又是另一個窗格。兩個窗格又各自有不同的一群銷售紀錄。

    因此在計算 Previous Sales 時，是需要依據當前窗格的前一筆銷售記錄日期。

    挑戰二：資料間隔非定值

    OFFSET 使用在資料平移問題
    圖三、OFFSET 使用在資料平移問題

    上圖中，從 2020/04/01 到該窗格的下一筆資料 2020/04/22 總共過了 22 天；而從 2020/04/22 到該窗格的下一筆資料 2020/05/17 總共過了 17 天。兩個差距是不同的。

    基於以上兩個原因，我們並不能使用 PREVIOUSDAY 函數來獲得前一個日期。但 OFFSET 卻可以幫助我們完成。

    OFFSET 用法

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

    首先，OFFSET 是回傳一個多列的資料表，本質上就是一個資料表。而資料表可以作為 CALCULATE 函數的篩選參數。因此我們可以建立一個新量值（Measure）：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Previous Sales =
    CALCULATE (
        [Total Sales],
        OFFSET (
            -1,
            SUMMARIZE (  ALLSELECTED ( Sales  ), Customer[Customer], ‘Date'[Date] ),
            ORDERBY ( ‘Date'[Date]  ),
            KEEP,
            PARTITIONBY  ( Customer[Customer] )
        )
    )

    其中，CALCULATE 第一個參數 [Total Sales] 為一計算銷售額的量值，至於寫法可以下載 .pbix 檔案來參考。

    想要自己試試看嗎？

    ​

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

    這裡可以下載👇👇👇

      Built with ConvertKit

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

      OFFSET 第一個參數

      第一個參數我們指定了 -1，代表所計算的依據將往前平移一個距離。

      OFFSET 第二個參數

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

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

      SUMMARIZE 內第一個與第二個參數分別代表該回傳的資料表會依據 Customer 與 Date 兩個欄位做分群（grouping）。

      如果單純將這第二個參數建立一個計算資料表（Calculated Table）會顯示如下。紀錄每一個顧客的每一筆銷售日期。

      OFFSET 中的 SUMMARIZE 函數回傳的資料表
      圖四、OFFSET 中的 SUMMARIZE 函數回傳的資料表

      OFFSET 第三個參數

      第三個參數使用 ORDERBY ( 'Date'[Date] )，代表 OFFSET 輸出的資料表將以 Date 欄位做排序。

      OFFSET 第四個參數

      單純指定目前可用的唯一值 KEEP。

      OFFSET 第五個參數

      第五個參數使用 PARTITIONBY ( Customer[Customer] )，代表 OFFSET 輸出的資料表將以 Customer 欄位做窗格分群。

      OFFSET 回傳的資料表

      如果單純將 OFFSET 用計算資料表顯示，會長成如下：

      圖五、OFFSET 函數回傳的資料表

      乍看之下與前一個 SUMMARIZE 算出來的結果好像一樣？但其實不然，若我們仔細觀察這個資料表的總列數，可以發現這張表總共有 10,151 個列。

      OFFSET 資料表列數
      圖六、OFFSET 資料表列數

      相對地，SUMMARIZE 算出來的表有 28,522 個列，而有 18,401 個不重複顧客。

      SUMMARIZE 資料表列數
      圖七、SUMMARIZE 資料表列數

      這是由於針對每一個顧客在往前平移一個距離時，第一筆日期會對應不到前一筆紀錄，因此會被剔除，也就會造成 OFFSET 計算出來的資料列數目比 SUMMARIZE 資料列數目還要少。

      例如圖四的人名從 Zoe Watson 到 Zoe Reed 都僅有一筆銷售紀錄，在套用到 OFFSET 以後會對應不到前一筆紀錄，所以會被剔除。

      OFFSET 視覺化結果

      使用 OFFSET 函數視覺化以後就可以如圖一般每一筆銷售都可以對應到前一筆資料。

      OFFSET 用法
      圖八、OFFSET 視覺化結果

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

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      Previous Sales (TOJSON) =
      TOJSON (
          OFFSET (
              -1,
              SUMMARIZE ( ALLSELECTED ( Sales ), Customer[Customer], ‘Date'[Date] ),
              ORDERBY ( ‘Date'[Date] ),
              KEEP,
              PARTITIONBY ( Customer[Customer] )
          )
      )
      TOJSON 視覺化 OFFSET 的資料列
      圖九、TOJSON 視覺化 OFFSET 的資料列

      從上圖的紅框中可以知道，針對 Ana Perry 在 2020/02/13 這個當前列的狀況下，其 OFFSET 的資料列為 Ana Perry 2020/02/08 的資料。以此類推直到最後一筆。

      結論

      使用 OFFSET 函數可以幫助我們輕易地在不同窗格內實現上一筆資料的抓取。

      更延伸的應用還有與前一筆或前 N 筆資料比較以計算差值或比例。至於這該怎麼寫量值，就要依據商業需求而定了。

      想要自己試試看嗎？

      ​

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

      這裡可以下載👇👇👇

        Built with ConvertKit

        發佈留言

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

        熱搜文章

        Repeated indices found in GroupBy or Projections
        解決 Power BI 格式化顏色出現 Repeated indices found in GroupBy or Projections 問題
        OFFSET 用法
        Power BI DAX 函數：OFFSET 用法與教學｜計算資料平移的好工具！
        WINDOW Function
        DAX WINDOW Function 窗函數｜計算移動平均的好幫手
        Index Function
        Power BI DAX INDEX Function 介紹與 2 種範例