PowerBIのINDEX、OFFSET、WINDOW関数について使い方をまとめる

2023.03.01

本サイトは広告収入およびアフィリエイト収益を受けております。

スポンサーリンク
スポンサーリンク

お疲れ様です。きざきまるおです。

今回はPowerBIで比較的新しい関数であるINDEX、OFFSET、WINDOW関数についてまとめていこうと思います。
これらの関数をうまく使うことで表現力が劇的に向上するかと思いますので、ぜひお試しください。

それではどうぞ。

事前準備

事前準備として、こちらの記事で記載しているテーブルなどなどを使用します。ファイル等はリンク記事内にありますので、そちらからダウンロードしてみてください。
注意としては、ファイル内の値はすべてランダム値なので、今回の結果とは異なる数値になります。

INDEX

指定された列をソートし、ソートされたデータの内、指定した順番のデータを取得することが出来る関数です。

index = 
INDEX(
  順番,
  テーブル(デフォルトはALLSELECTED),
  ORDERBY,
  空白値の処理方法(現在はKEEPのみ),
  PARTITIONBY
)

それぞれ説明していきます。

順番:何番目のデータを取得するかを数値で指定します。こちらに関しては特に補足するほど複雑なことはないのでここら辺までにしておきます。

テーブル:こちらはALLSELECTEDがデフォルトとなっています。推測の理由になってしまいますが、仕様上、後々出てくるORDERBYやPARTITIONBYで使用する列に内部フィルターがかかってしまうと処理に不具合が発生するからかなと思っています。

ORDERBY:指定した列を昇順もしくは降順で並べ替えをします。順番で指定している数値で取得できる値が変わるので、こちらはしっかり意図して設定しましょう。

空白値の処理方法:こちらは並べ替えをする列の中に空白があった場合にどのような値で埋めるかを指定する項目です。現在の状況だとKEEP(0or負or最大値(文字列))のみ対応しています。

PARTITIONBY:こちらで指定した列を集計し、この列ごとの順番を取得します。

今回のサンプルコードは以下になります。
年月と商品名でソートし、年月にグルーピングした結果の１番目の総額を取得しようとしています。

index = 
CALCULATE(
    SUM('orders'[総額]),
    INDEX(
        1,
        ALLSELECTED('orders'[年月_数値], 'orders'[商品名]),
        ORDERBY('orders'[年月_数値], ASC, 'orders'[商品名]),
        KEEP,
        PARTITIONBY('orders'[年月_数値])
    )
)

はい。年月ごとにグルーピングされた状態で商品名の一番最初の総額の値のみが取得されています。
スライサーで指定した値の内、何番目かの値をピンポイントで取得したいというケースで使えそうですね。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/index-function-dax

OFFSET

指定列でソートし、その前後の値を取得することができる関数です。
前後の値との差分を確認したいときなどに使えるかと思います。

offset = 
OFFSET(
  前後値,
  テーブル(デフォルトはALLSELECTED),
  ORDERBY,
  空白値の処理方法(現在はKEEPのみ),
  PARTITIONBY
)

INDEXとの違いは最初の前後値のみですので、こちらについて説明します。

前後値:データを取得する値を示します。現在の行を起点としてどれくらい前後の値を取得するかを数値で設定します。

ということで早速サンプルコードを見てみましょう。

offset = 
CALCULATE(
    SUM('orders'[総額]),
    OFFSET(
        -1,
        ALLSELECTED('orders'[年月_数値], 'orders'[商品名]),
        ORDERBY('orders'[年月_数値], ASC, 'orders'[商品名]),
        KEEP,
        PARTITIONBY('orders'[年月_数値])
    )
)

はい。現在の値を起点としてoffset列は一個手前の値を取得できていますね。
こちらを応用して以下のようなメジャーを作成することで一個前の値との差分を取得することが出来ます。

総額-offset = 
SUM([総額])-
CALCULATE(
    SUM('orders'[総額]),
    OFFSET(
        -1,
        ALLSELECTED('orders'[年月_数値], 'orders'[商品名]),
        ORDERBY('orders'[年月_数値], ASC, 'orders'[商品名]),
        KEEP,
        PARTITIONBY('orders'[年月_数値])
    )
)

いかがでしょうか？
今回はサンプルデータなので数値に意味はありませんが、こちらをうまく使うことによって前月差の数値などなどを算出するなど用途で応用が利きそうですね。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/offset-function-dax

WINDOW

指定した列から設定した間隔内のデータを取得します。

window = 
WINDOW(
  start,
  相対値or絶対値,
  end,
  相対値or絶対値,
  テーブル(デフォルトはALLSELECTED),
  ORDERBY,
  空白値の処理方法(現在はKEEPのみ),
  PARTITIONBY
)

start endの値のみ上記２つの関数と異なりますので、そこら辺の説明をしていきます。

start:取得する値の開始値を数値で設定します。相対値の場合は現在の値からで絶対値の場合はパーティションで指定した値の最初から取得します。

end:こちらは取得する値の末尾になります。値の取得方法はstartと考え方が同じです。

はい。サンプルコードを見ていきましょう。
今回は前後の値を合計してみます。

window = 
CALCULATE(
    SUM('orders'[総額]),
    WINDOW(
        -1,
        REL,
        1,
        REL,
        ALLSELECTED('orders'[年月_数値], 'orders'[商品名]),
        ORDERBY('orders'[年月_数値], ASC, 'orders'[商品名]),
        KEEP,
        PARTITIONBY('orders'[年月_数値])
    )
)

いかがでしょう？
ここまでDAX関数でできるようになると一気に選択肢が広がった気がしますね。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/window-function-dax

以上で３つの関数を紹介しました。
これらはいままでSQLでできていたけどPowerBIで出来なかったもどかしい部分を見事にカバーしてくれていますね。マスターすることでより自由度が上がること間違いなしです。

それではまた。

タイトルとURLをコピーしました