[Power BI] 2022年12月に追加された新DAX関数３つを考える🤔（INDEX、OFFSET、WINDOW）

はじめに

2022年12月に、Power BI のDAX関数に新たに3つ関数が加わりました。

• INDEX
• OFFSET
• WINDOW

触ってみる💪

INDEX関数

公式ドキュメント及びDAX Guide

説明

指定されたパーティションを指定された順序で並べ替えて、絶対位置 (position パラメーターで指定) にある行を返します。 現在の位置から 1 つの位置を導けない場合は、複数の行が返される可能性があります。

位置を指定して、その値をもってこれるようだ🤔

構文

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

relationは元となるテーブル。

絶対位置を指定するpositionは必須。orderByを指定していれば、relationは省略可能。

relationが省略されている場合はorderByとpartitionByで指定されている列をALLSELECTEDで囲ったものが指定される。

ORDERBY関数とPARTITIONBY関数は今回追加された新関数の中だけで使える補助的な関数。

blanksは、現状KEEPしか指定できない。今後アップデートがあるみたい。

わけが分からなくなるので、なるべく引数は省略せずに書いていく。

基本のかたち

INDEX_1 =
CALCULATE(
    [Sales Amount],
    INDEX(
        1,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名]),
        KEEP
    )
)

INDEX番号は1行目から順番に1,2,3...が割り当てられていて、ORDERBY関数で指定した並び順にそって、positionで指定した位置の行にアクセス可能。合計行も指定した行にアクセスしていることに注意。

INDEX番号を変えてみる。

INDEX_1 =
CALCULATE(
    [Sales Amount],
    INDEX(
        3,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名]),
        KEEP
    )
)

INDEX関数のposition引数で3を指定したので、結果はこうなる。

ORDERBY関数に並び順DESCを指定

CALCULATE(
    [Sales Amount],
    INDEX(
        1,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名],DESC),
        KEEP
    )
)

DESCを指定すると、内部で区分名が降順で並び替えられINDEXが付与されるため、ビジュアルで区分名を昇順に並べていた場合は一番下の行の数値をとってくるように見える。

合計値に要注意

合計値が、純粋な合計ではなく、ここも３行目の数値をもってきていることに注意💪

PARTITIONBY関数の追加

ディメンジョンが複数ある場合は、PARTITIONBY関数でグループ化した上でORDERBY関数で並び替えをすることができる。
ビジュアルには区分名と商品名の2つのディメンジョンを作成。

PARTITIONBY関数で、区分名ごとにINDEXを付与するように指定してみる。

CALCULATE(
    [Sales Amount],
    INDEX(
        1,
        ALLSELECTED(Product[区分名],Product[商品名]),
        ORDERBY( 'Product'[区分名],ASC,Product[商品名]),
        KEEP,
        PARTITIONBY('Product'[区分名])
    )
)

結果はこう。区分名ごとにpositionが1のSales Amountが選ばれているのがわかる。

OFFSET関数

公式ドキュメント及びDAX Guide

説明

同じテーブル内で、指定されたオフセットだけ "現在の行" より前または後にある 1行を返します。 現在の行から 1つの行を求められない場合は、複数の行が返される可能性があります。

現在の行を基準に、ずらした行の数字をとれそう🤔

構文

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

相対位置を指定するdeltaが必須。orderByを指定していれば、relationは省略可能。

基本のかたち

CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名] ),
        KEEP
    )
)

今いる行が常に０となり、そこを基準にdeltaで指定した分だけずらした値をとることができます。上の例では-1を指定しているので、一つ上の行の値を取得しています。

合計値に要注意

合計値はOFFSETをすることでBLANKになった数字分少なくなっていることに注意👍

PARTITIONBY関数を追加

今度も、商品名列を追加した上で、PARTITIONBY関数で区分名を囲ってみます。

OFFSET_3 = CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ALLSELECTED(Product[区分名], Product[商品名]),
        ORDERBY( 'Product'[商品名] ),
        KEEP,
        PARTITIONBY('Product'[区分名])
    )
)

すると期待したとおり、区分名ごとに商品名が昇順で並び、そのうえで１行上から数字を持ってくることができています。

スライサービジュアルを追加してみる

INDEX関数もOFFSET関数も、元となるテーブル（relation）はALLSELECTED関数で囲っています。元となるテーブルを明示的に指定をしなかった場合は、ORDERBYとPARTITIONBYで指定した列をALLSELECTEDで囲うというのがデフォルトの動きになります。今回は明示的にそれを書いてあげています。

ALLSELECTED関数で囲われているため、スライサービジュアルで商品名をしぼってもこのDAXは動作します。今回の場合は同区分に商品が２つ以上ある場合にだけ、ひとつ上の行にある数値を返せます。

WINDOW関数

公式ドキュメント及びDAX Guide

説明

指定された間隔内に配置されている複数の行を返します。

ドキュメントの説明はシンプル。

構文

WINDOW ( from[, from_type], to[, to_type][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )

relation以下はOFFSETやINDEXと同じ。違うのは冒頭。

指定する必要があるfrom~toとfrom_typeやto_type。

相対位置（relative）を使用する場合はfrom_typeやto_typeにRELと書く。省略した場合はRELがデフォルト。

絶対位置（absolute）を使用する場合はABSと書く。

ここまでわかればあとは実践。

基本のかたち

WINDOW_1 = 
CALCULATE(
    [Sales Amount],
    WINDOW(
        -1,
        0,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名]),
        KEEP
    )
)

from_typeやto_typeを指定していないため、RELが適用されている。相対位置を使用するため、自分がいる行が0となる。

from → -1

to → 0

としているため、一つ上の行と自分の行の合計値がでているはず。

RELとABSを使いこなす

ABSを明示し、fromとtoに絶対位置を指定する。
この場合はORDERBYで並び替えた順に一番上の行から1,2,3...と番号が振られている。

下記DAX式ではABSで1から2と指定しているため、１行目と２行目の合計がすべての行で表示されます。

WINDOW_2 =
CALCULATE(
    [Sales Amount],
    WINDOW(
        1,ABS,
        2,ABS,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名]),
        KEEP
    )
)

ABSとRELを組み合わせてもOK

RELとABSは組み合わせても使えます。

下記のDAXでは、

fromに 1,ABS → ORDERBYで指定した列の１行目

toに 0,REL → 相対位置の場合、0は自分の位置

と指定しています。

WINDOW_3 = 
CALCULATE(
    [Sales Amount],
    WINDOW(
        1,ABS,
        0,REL,
        ALLSELECTED(Product[区分名]),
        ORDERBY( 'Product'[区分名]),
        KEEP
    )
)

絶対位置１行目（INDEXの１）から、自分の位置までの合計なので、結果的に累計値が表示されています。

ビジュアルで並び替える🤔

今日ここまで紹介した関数は、ビジュアル上でずらした値を持ってこれたり（OFFSET関数）、ビジュアル上で位置を指定した値をもってこれたり（INDEX関数）、ビジュアル上で範囲を指定した値をもってこれたり（WINDOW関数）していました。

ただこれは、ビジュアルの並び順と、それぞれの関数内にあるORDERBY関数で指定した列の順番が一致していたからそのように見えていただけです。

上図のように、さきほどまで累計を計算していたように見えていたテーブルも、 [Sales Amount] 列で並び替えると計算結果は変わっていないですが、もはや累計にはまったく見えないようになりました。

ビジュアル上で行を操作しているように見えてしまいますが、内部のテーブル、列で計算していることを理解😁

考察

ということで、2022年12月に追加された3つの関数と、2つのヘルパー関数を触ってみました。便利そうだけど、どこで使うべきなのか。考慮しないといけないことも多く、使い所が難しいなと感じました。TwitterやLinkedIn上でさまざまな人達が議論をしています。Power BI Blogの記事からも将来的な機能追加の足がかりという表現もあるので、今後これらの関数がどのように活かされてくるのか注目ですね💪

SQLBIのマルコさん

Twitterで以下のようなやりとりをしていました。

Kevinさん
新しい DAX 関数 INDEX () 、 OFFSET () 、および WINDOW ()に関する優れた包括的なビデオを引き続き探しています。それらをいじった。彼らは必ずしも私が期待したように振る舞っているとは言えません。

↓

Marco Russoさん（SQLBI）
1 月まで待ってください。まだ調査中です。明らかではありません。ただし、それらの主な用途はvisual calcs ( #powerbiではまだ利用できません) ですが、他の#dax計算の使用例もいくつかあります。

visual calcs🤔

どんな機能なのでしょうか。楽しみですね。

Power BI Blogではこんな記述が🤔

これらの新しい機能は非常に強力で柔軟性がありますが、正しく機能させるにはかなりの複雑さが必要です。これは、これらの機能に高い柔軟性を選択したためです。柔軟性の一部を犠牲にして、より簡単な DAX を優先する、より使いやすい関数が必要であることは認識しています。本日リリースする関数は、DAX を簡単にするという目標に向けた足がかりであり、ビルディングブロックであり、今後の基盤となります。このセクションを読んだ後、これらの関数が複雑なために自分に合わないと感じた場合は、DAX を簡単にするためにも DAX を簡単にするために作業していますのでご安心ください。

DAX を簡単にするという目標🤔

さきほどのvisual calcs とは関係あるのでしょうか。楽しみですね。

2022-12-19 追記

@marshal さんより、visual calcsについて語られているYouTubeを教えてもらいました。

私もこれから見てみます😀

まとめ

本記事は2022年12月のPower BI勉強会 DAX Boot Campで 主宰のかがたさん（@PowerBIxyz）中心に勉強・研究した内容です。というかほぼ教えてもらった内容です。記事にできたのは勉強した内容のごく一部です。Power BI、DAX、Power Queryの勉強をしている方、ぜひ勉強会に参加してみてください💪

結局、こうやって記事にして一番勉強になっているのは自分なんですよね。最後までお読みいただきありがとうございました。イイねお願いします～😁