Power BI DAX INDEX Function 介紹與 2 種範例

先備知識

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

  • Row Context
  • ALLSELECTED 函數
  • SUMMARIZE函數
  • TOJSON 函數
  • DIVIDE 函數

另外,本文章是 WINDOW 系列函數(目前包含 INDEXOFFSETWINDOW)的第三篇文章。在先前我已發表過兩篇文章,分別介紹 OFFSET 函數以及 WINDOW 函數,若想要觀看前兩篇文章,可以點擊下方的按鈕前往閱讀。

在文章開始之前,如果你想要一邊動手練習,那麼可以在下面下載。

想要自己試試看嗎?

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

這裡可以下載👇👇👇

    Window Functions

    眼尖的你應該有發現,上面的標題我故意在 Function 後面加上了「s」,這是因為在微軟 2022 年 12 月的更新中,釋出了三個函數,分別是:

    1. INDEX
    2. OFFSET
    3. WINDOW

    針對這三個函數,微軟提供一個集合名詞:「Window Funtions」。

    所以,可以知道的是,Window Functions 其實是一個比較大的範圍,其下目前包含著三個不同的函數。

    Window Functions
    Window Functions 所含的函數們

    如果將 Window Functions 直接翻譯成中文「窗函數」或是「Window 函數」可能會與 WINDOW 函數本身混淆。因此,在這篇文章中,若提及集合名詞時就會使用 Window Functions,而針對單一函數就會使用全部英文大寫稱呼該函數名稱,例如本文使用 INDEX 函數來稱呼。

    DAX Window Functions v.s SQL Window Functions

    如果你是很常使用 SQL 的使用者,那麼你應該對 SQL 中的 Window Functions 不陌生。

    Power BI DAX 中的這三款 Window Functions 與 SQL 中的 Window Functions 非常相似,但又有些差異

    相同的點在於,無論是 DAX 或者 SQL,兩者的 Window Functions 都是針對一資料表中的一群資料列(row)進行運算

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

    INDEX vs. OFFSET vs. WINDOW

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

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

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

    在這篇文章,我們將專注於 Window Functions 中的 INDEX Function,理解它的定義,並且提供應用範例。

    INDEX Function(INDEX 函數)是什麼?

    定義

    根據微軟的官方,INDEX Function 的定義如下:

    INDEX ( <position> [, <relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] )

    參數

    參數 說明
    position
    • 回傳資料列所依據的絕對位置
    • 起始位置從 1 開始,若:
      • <position> 為正數: 1 為第一列, 2 為第二列,以此類推。
      • <position> 為負數: -1 為最後一列, -2 為倒數第二列,以此類推。
    • <position> 超過邊界、為零、為 BLANK() 時, INDEX 將回傳一個空表。
    relation
  • (可選)任何回傳資料列的資料表運算式。
  • 有指定此參數,則 <orderBy><partitionBy> 中指定的資料行必須來自此參數內的資料行。
  • 忽略此參數,則 <orderBy><partitionBy> 必須是完整的資料行,並且來自同一張資料表。
  • 預設為使用 ALLSELECTED()<orderBy><partitionBy> 的所有列。
  • orderBy
  • (可選)定義排序 partition 的資料行。
  • 忽略此參數,則 <relation> 必須明確被指定。同時,排序的依據會使用從 <relation> 資料表內扣除指定在 <partitionBy> 的其餘資料行。
  • blanks
  • (可選)定義遇到 BLANK() 該如何排序的 enumeration。
  • 這個當前是參數保留給未來使用。
  • 目前唯一支援的數值是 KEEP,同時也是預設數值。
  • partitionBy
  • (可選)定義遇到 <relation> 的資料表該如何被 partition。
  • 如果忽略,則整個資料表會當作是一個 partition 而已。
  • 回傳值

    回傳絕對位置的資料列,該資料列是由其屬於的 partition 決定,由 <partitionBy> 參參數定義哪個資料列作為 partition 的依據。並且排序是由 <orderBy> 參數決定。

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

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

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

    問題情境

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

    如同在文章開頭所說的,INDEX 函數特別適合用來獲取絕對位子上的資料值

    在下列範例中,我們想要獲得每一財政年度中,銷售額最高的季度,並且回傳該銷售數值

    圖中每一個 Partition 包含每一個財政年度 Q1 ~ Q4 四個季度的資訊,[Total Sales] 代表每一個季度的銷售額,而 [Max Sales Quarter in Year] 代表該財政年度裡,最大的銷售額是多少。

    以 Partition 1 來說,最大的銷售數值是 $6,103,487,60,這數值的來源是 FY2018 Q4 的結果。其它的 Partition 則以此類推。

    INDEX Function
    INDEX Function 應用範例

    那麼,究竟如何才能完成這項計算呢?使用 INDEX 可以輕易地協助解決這項問題。

    想要自己試試看嗎?

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

    這裡可以下載👇👇👇

      INDEX Function 範例一:計算 Partition 內最大值

      首先,我們需要思考,想要獲得是每一個 Partition 內的最大值,若 Parition 是降冪排序,則可以從 Partition 中的第一個絕對位置上取得該數值為最大值,這便可以利用 INDEX 函數完成。

      為了獲得排序過後的資料表,我們首先要有一張未排序過的每季度銷售額資料表。

      步驟一、建立未經排序的每季度銷售資料表

      第一步我們利用 DAX Studio 或是在 Power BI 內建立計算資料表,搭配寫出以下的 DAX 資料表表達式。該表達式會回傳一個資料表,總結各年度的銷售額。

      1
      2
      3
      4
      5
      6
      7
      Sales by Quarter per Year=
      SUMMARIZE  (
           ALLSELECTED  ( ‘Date’  ),
          ‘Date'[Fiscal Year],
          ‘Date'[Fiscal Quarter],
           “@Sales”, [Total Sales]
      )

      以上資料表會長成如下,很好!第一步完成了!

      仔細看會發現這步驟所回傳的資料表,在同個年度內,不同季度的銷售額是還未經過排序的。

      銷售額對季度總計資料表
      銷售額對季度總計

      步驟二、將資料表排序並利用 INDEX 函數抓取最大值

      在這步驟,我們就可以引入 INDEX 函數將上一步驟的資料表做排序。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      Sorted Sales by Quarter per Year =
      INDEX  (
           1,
           SUMMARIZE  (
               ALLSELECTED  ( ‘Date’  ),
              ‘Date'[Fiscal Year],
              ‘Date'[Fiscal Quarter],
               “@Sales”, [Total Sales]
           ),
           ORDERBY  ( [@Sales],  DESC  ),
           KEEP,
           PARTITIONBY  ( ‘Date'[Fiscal Year]  )
      )

      運行過後的結果如下圖。可以發現針對每一個年度,我們都可以抓到對應的最高銷售的季度。

      INDEX Function 所回傳的最大值
      INDEX Function 所回傳的最大值

      步驟三、建立量值並使用 CALCUATE 函數與前步驟的資料表

      最後一步驟,可以在 Power BI 中新建量值並且使用 CALCUALTE 函數與 [Total Sales],搭配前面所寫的資料表(作為計算的篩選),來獲得目標數值。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      Max Sales Quarter in Year =
      CALCULATE  (
          [Total Sales],
           INDEX  (
               1,
               SUMMARIZE  (
                    ALLSELECTED  ( ‘Date’  ),
                   ‘Date'[Fiscal Year],
                   ‘Date'[Fiscal Quarter],
                    “@Sales”, [Total Sales]
               ),
               ORDERBY  ( [@Sales],  DESC  ),
               KEEP,
               PARTITIONBY  ( ‘Date'[Fiscal Year]  )
           )
      )

      最後利用表格視覺化後的結果如下所示。

      INDEX Function 計算後的結果
      INDEX Function 計算後的結果

      步驟四、利用 TOJSON 函數驗證

      在 Power BI 中,有一個叫做 TOJSON 的函數可以用來驗證計算過程中的結果。

      在我們的例子中,我們可以建立一個量值,將步驟二產生的資料表作為參數傳入 TOJSON 中。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      Max Sales Quarter in Year (TOJSON) =
      TOJSON  (
           INDEX  (
               1,
               SUMMARIZE  (
                    ALLSELECTED  ( ‘Date’  ),
                   ‘Date'[Fiscal Year],
                   ‘Date'[Fiscal Quarter],
                    “@Sales”, [Total Sales]
               ),
               ORDERBY  ( [@Sales],  DESC  ),
               KEEP,
               PARTITIONBY  ( ‘Date'[Fiscal Year]  )
           )
      )

      視覺化以後,如下圖所示,可以發現在同一個年度中,INDEX 所抓到的資料都是同一個數值。因此可以確定我們的計算是正確的。

      TOJSON 視覺化 INDEX Function 中間計算結果
      TOJSON 視覺化 INDEX Function 中間計算結果

      INDEX Function 範例二:計算 Partition 內與最大值差距百分比

      第二個範例算是前一個範例的延續。

      有了上個範例算出來的每個季度的最大銷售值以後,就可以很輕易地針對單一年度內每個季度的銷售去計算與最大值的差距。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      Sales Gap to Max Quarter Sales =
      VAR  maxSalesQuarterInYear =
           CALCULATE  (
              [Total Sales],
               INDEX  (
                    1,
                    SUMMARIZE  (
                        ALLSELECTED  ( ‘Date’  ),
                       ‘Date'[Fiscal Year],
                       ‘Date'[Fiscal Quarter],
                        “@Sales”, [Total Sales]
                    ),
                    ORDERBY  ( [@Sales],  DESC  ),
                    KEEP,
                    PARTITIONBY  ( ‘Date'[Fiscal Year]  )
               )
           )
      RETURN
           DIVIDE  ( [Total Sales] –  maxSalesQuarterInYear, [Total Sales]  )

      視覺化以後的結果如下。可以得知,在每一個 Partition 中,各銷售數據與最大值的差距是多少。

      INDEX Function 應用範例
      INDEX Function 應用範例

      結語

      本文從微軟所釋出的 WINDOW Funtions 開始,首先釐清 WINDOW Functions 這一集合名詞的概念。

      接著介紹 INDEX 函數的定義,並提供兩個簡單的範例,從分析問題到拆解成小問題的方式,帶各位實作。

      希望看完這篇文章的你已經對 INDEX 函數有一定的認識,並且可以運用其觀念,發揮在適合的應用場域。

      發佈留言

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