ADDCOLUMNS関数の研究～集計列追加のベストプラクティス🤔～

はじめに

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

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

続きものです。

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

DAXクエリ＆DAX.do

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

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

参考記事

検証開始

前回のおさらい

前回の記事で、SUMMARIZE関数での集計列追加は非推奨であることがわかりました。
SUMMARIZE関数は列のグループ化だけに使おうと。

非推奨

この時点で結果は正しいですが、フィルターコンテキストを操作する場合がある場合、複雑な操作になるため誤った結果がでてしまう場合が多く、SUMMARIZE関数での集計列追加は非推奨なのでした。

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

結果

次に集計列のフィルターコンテキストを操作してみます。

EVALUATE
SUMMARIZE (
    Sales,
    'Product'[Brand],
    "SalesAMT", SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ),
    "TotalAMT",
        CALCULATE (
            SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ),
            REMOVEFILTERS ( 'Product'[Brand] )
        )
)
ORDER BY
[SalesAMT] DESC

結果

この操作でわかるように、フィルターコンテキストの操作が思うようにいっていません。REMOVEFILTERS関数でフィルターを削除したはずなのに削除されていないように見えます。これが非推奨の理由。内部の処理が複雑で直感的ではないからです。

推奨 → SUMMARIZE関数は列のグループ化だけに使おう

ということで推奨はグループ化だけに使うこと。

集計列の追加にはADDCOLUMNS関数を使おう🎉

ADDCOLUMNS関数の文法

最初の引数はテーブルです。SUMMARIZE関数でグループ化したテーブルを指定します。

例

元のDAXクエリ

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

結果

ADDCOLUMNS関数を使用して書き換えたもの

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

結果

あれ？

結果が期待したものにはなりません…😭

なぜなのか🤔

参考記事を私なりに読み解くと…「Brand」列に応じた行ごとに適切な評価コンテキストが集計列[SalesAMT]に伝わってほしいけど、そういう動作はしないようです。

修正

行コンテキストをフィルターコンテキストに変換してADDCOLUMNS関数に伝えてあげれば期待した数字が表現できそう。
ということで、DAXクエリの集計列をCALCULATE関数で囲います。

CALCULATE関数は行コンテキストをフィルターコンテキストに変換してくれます。

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

結果

期待した数字がでるようになりました。

結論

集計列追加のベストプラクティスは🎉😀🎉

1. SUMMARIZE関数でグループ化する
2. ADDCOLUMNS関数で集計列を追加する
3. CALCULATE関数で集計列を加工

DAX式で表すと以下のようになります。

ADDCOLUMNS(
    SUMMARIZE( <table>, <group by column> ),
    <column_name>, CALCULATE( <expression> )
)

ただし、集計列にモデルで定義済みのメジャーを指定した場合、CALCULATE関数`は不要です。

EVALUATE
ADDCOLUMNS ( SUMMARIZE ( Sales, 'Product'[Brand] ), "SalesAMT", [Sales Amount] )
ORDER BY [SalesAMT] DESC

結果

まとめ

一番重要なことは繰り返し、

• SUMMARIZE関数`は列のグループ化にだけ使う
• ADDCOLUMNS関数で集計列を追加する
• CALCULATE関数で行コンテキストをフィルターコンテキストに変換させる

まとめたベストプラクティスが下記。

ADDCOLUMNS(
    SUMMARIZE( <table>, <group by column> ),
    <column_name>, CALCULATE( <expression> )
)

例外もあるらしい🤔

参考記事を読んでいると、ADDCOLUMNS関数とSUMMARIZE関数で行うベストプラクティスにも例外は存在するようです。

それは、次回、集計列追加のベストプラクティス🤔～例外編～に続きます。