GROUPBY関数の研究🤔～GROUPBY関数の使いどころ編～

はじめに

すこし複雑なメジャーを記述しようとすると、避けては通れないテーブル操作関数。主要なSUMMARIZE、ADDCOLUMNS、 SUMMARIZECOLUMNSなどを研究してみます。

テーブル操作関数シリーズ

続きものです。

1. SUMMARIZE関数の研究～集計列の追加はなぜ非推奨なのか🤔～
2. ADDCOLUMNS関数の研究～集計列追加のベストプラクティス🤔～
3. 集計列追加のベストプラクティス🤔～とはいえ例外だってあるぞ～
4. SUMMARIZECOLUMNS関数の研究～SUMMARIZE関数は非推奨?🤔～
5. GROUPBY関数の研究🤔～基本動作確認編～

DAXクエリ＆DAX.do

今回の検証はDAX.do上でDAXクエリを書いて検証していきます。
DAXクエリがわからない方は先にこの記事を読んで、DAX.doで少し触ってからのほうが理解が進むでしょう。

DAXクエリについてはこちらの記事を参照してください。

参考記事

復習

列のグループ化をするだけなら、GROUPBY関数でも、SUMMARIZE関数でも結果は一緒。書き方も一緒。

EVALUATE
GROUPBY (
    Sales,
    'Product'[Brand],
    'Product'[Category]
)

EVALUATE
SUMMARIZE (
    Sales,
    'Product'[Brand],
    'Product'[Category]
)

集計列の追加をするときはCURRENTGROUP ()関数

集系列を追加するときは、GROUPBY関数の場合はCURRENTGROUP ()関数を使用できる。グループ化した表を再利用できる。

EVALUATE
GROUPBY (
    Sales,
    'Product'[Brand],
    'Product'[Category],
    "SalesAmt",
        SUMX (
            CURRENTGROUP (),
            Sales[Net Price] * Sales[Quantity]
        )
)
ORDER BY [SalesAmt] DESC

SUMMARIZE関数でも同じものがかける。

EVALUATE
SUMMARIZE (
    Sales,
    'Product'[Brand],
    'Product'[Category],
    "SalesAmt",
        SUMX (
            Sales,
            Sales[Net Price] * Sales[Quantity]
        )
)
ORDER BY [SalesAmt] DESC

同じ結果がでている。

入れ子になったグループ化

これから掲載するコード、長くて見にくいかもしれませんが、Dax.doで作成しています。DAX.doのコード入力画面にいれて実行すれば同じ結果がでますので、ご活用ください💪

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        ADDCOLUMNS (
            SUMMARIZE ( Product, 'Product'[Category], 'Product'[Subcategory] ),
            "Average Price", CALCULATE ( AVERAGE ( Product[Unit Price] ) )
        ),
        'Product'[Category]
    ),
    "Max SubCat Avg Price", CALCULATE ( MAX ( [Average Price] ) )
)

これを実行すると、エラーとなる。

DAXは物理的な列だけを集計できるため、DAX内で作成した列の集計はできない。

修正

EVALUATE
ADDCOLUMNS (
    VALUES ( 'Product'[Category] ),
    "Max SubCat Avg Price",
        CALCULATE (
            MAXX (
                ADDCOLUMNS (
                    SUMMARIZE ( Product, 'Product'[Category], 'Product'[Subcategory] ),
                    "Average Price", CALCULATE ( AVERAGE ( Product[Unit Price] ) )
                ),
                [Average Price]
            )
        )
)
ORDER BY [Max SubCat Avg Price] DESC

こう書き直すと物理列の集計となり、正しい数字がでる。が、冗長であるのは否めない。

ここでGROUPBY関数

CURRENTGROUP ()関数を使うと、計算列にアクセスすることができるため、無事に計算をすることができる。

EVALUATE
GROUPBY (
    ADDCOLUMNS (
        GROUPBY (
            Product,
            'Product'[Category],
            'Product'[Subcategory]
        ),
        "Average Price", CALCULATE ( AVERAGE ( Product[Unit Price] ) )
    ),
    'Product'[Category],
    "Max SubCat Avg Price", MAXX ( CURRENTGROUP (), [Average Price] )
)
ORDER BY
[Max SubCat Avg Price] DESC

一番深いところのGROUPBY関数はSUMMARIZE関数に置換可能

EVALUATE
GROUPBY (
    ADDCOLUMNS (
        SUMMARIZE ( Product, 'Product'[Category], 'Product'[Subcategory] ),
        "Average Price", CALCULATE ( AVERAGE ( Product[Unit Price] ) )
    ),
    'Product'[Category],
    "Max SubCat Avg Price", MAXX ( CURRENTGROUP (), [Average Price] )
)
ORDER BY [Max SubCat Avg Price] DESC

結果は変わらない。

ADDCOLUMNS関数を削除

GROUPBY関数とその集計列作成で使えるCURRENTGROUP関数を使うと計算列にアクセスできるため、ADDCOLUMNS関数を削除することができる。ただし、メジャーを参照していたりする場合はADDCOLUMNS関数を削除することはできない。（CURRENTGROUP関数を使ってメジャーの集計はできないため）

EVALUATE
GROUPBY (
    GROUPBY (
        Product,
        'Product'[Category],
        'Product'[Subcategory],
        "Average Price", AVERAGEX ( CURRENTGROUP (), Product[Unit Price] )
    ),
    'Product'[Category],
    "Max SubCat Avg Price", MAXX ( CURRENTGROUP (), [Average Price] )
)
ORDER BY
[Max SubCat Avg Price] DESC

GROUPBY関数の使いどころ

イテレーターのグループ行にアクセスする場合は、ADDCOLUMNS/SUMMARIZEの代わりにGROUPBY関数を使用することもできます。ネストされたグループ化操作がある場合に必要となり、もっとも内側のグループではSUMMARIZE関数を使用できますが、内側のグループ化関数で計算された列にアクセスするには、GROUPBY関数とCURRENTGROUP 関数を使用する必要があります。

感想

もう一回書けと言われて、もう一回書ける自信はないけど、100回書いたら覚えられるかもって思えるきっかけにはなったかな😅つかいこなせば複雑な計算も思うがまま。頑張りましょう～💪