はじめに

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

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

続きものです。

DAXクエリ＆DAX.do

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

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

参考記事

`SUMMARIZECOLUMNS`関数の文法

まず書いてみる

SUMMARIZE関数やADDCOLUMNS関数と違って、SUMMARIZECOLUMNS関数は元のテーブルを必要としないため、直感的に記述していくことができます。

EVALUATE
SUMMARIZECOLUMNS ( 'Product'[Category], Customer[Country])
ORDER BY
'Product'[Category]

結果

列のグループ化は簡単。リレーションシップが直接つながっていないテーブル同士でも問題なく動作している。

`CROSSJOIN` 関数でも同じことがかける

CROSSJOIN.dax

EVALUATE
CROSSJOIN ( VALUES ( 'Product'[Category] ), VALUES ( Customer[Country] ) )
ORDER BY 'Product'[Category]

もちろん`SUMMARIZE`関数でも同じことが書ける

リレーションがつながっていないため、SUMMARIZE関数で最初に指定するテーブルにProductテーブルやCustomerテーブルを指定しても、エラーが返ってくる。

エラーになる.dax

EVALUATE
SUMMARIZE ( 'Product', 'Product'[Category], Customer[Country] )
ORDER BY 'Product'[Category]

エラーになる.dax

EVALUATE
SUMMARIZE ( 'Customer', 'Product'[Category], Customer[Country] )
ORDER BY 'Product'[Category]

列が見つからないよとエラーが出る。

正しい記述方法

正しい記述方法Salesテーブルを指定する.dax

EVALUATE
SUMMARIZE ( 'Sales', 'Product'[Category], Customer[Country] )
ORDER BY 'Product'[Category]

結果

SUMMARIZE関数でも同じ結果を出すことができました。

集計列の追加をしてみる。

`SUMMARIZECOLUMNS`関数

集計列の追加.dax

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Category],
    Customer[Country],
    "SalesAMT",
    SUMX ( 
        'Sales', Sales[Net Price] * Sales[Quantity] 
    )
)
ORDER BY 'Product'[Category]

正しい数値がでています。

フィルターコンテキストを変更してみる。

フィルターコンテキストを変更する.dax

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Category],
    Customer[Country],
    "SalesAMT", SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ),
    "TotalAMT",
        CALCULATE (
            SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ),
            REMOVEFILTERS ( 'Product'[Category] ),
            REMOVEFILTERS ( Customer[Country] )
        )
)
ORDER BY [SalesAMT] DESC

SUMMARIZE関数だけでは難しかったフィルターコンテキストの操作も直感的に行うことができました。

パフォーマンス比較（`SUMMARIZE`関数と`SUMMARIZECOLUMNS`関数）

同じ結果がでるクエリを書いて実行してみる。

SUMMARIZE/ADDCOLUMNS.dax

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

SUMMARIZECOLUMNS.dax

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

結果は同じ。

DAX Studioで結果を確認。発行されているクエリの数がSUMMARIZECOLUMNS関数では１つだけ。SUMMARIZE関数で作成したDAXクエリの方は２回スキャンが走ってしまっており、行数が増えてきたときにパフォーマンスの差がでそうです。

SUMMARIZE関数は非推奨？🤔

参考記事から引用します。

SUMMARIZE関数はほとんど非推奨と考えた方がよいでしょう。SUMMARIZEの唯一の安全な使用法は、追加式を含まず、テーブルを結合してSQL言語のSELECT DISTINCTと同等の結果を得るためにのみSUMMARIZEを使用する場合です。

SUMMARIZECOLUMNS関数は、非常に最適化されたクエリプランを生成します。そして、SUMMARIZEやADDCOLUMNS/SUMMARIZEパターンの代わりとして考慮すべきです。

Conclusion
You should consider the SUMMARIZE function almost like a deprecated one. The only safe use of SUMMARIZE is when you do not include additional expressions, and you use SUMMARIZE only to join tables and obtain a result equivalent to a SELECT DISTINCT in SQL language.
The SUMMARIZECOLUMNS function produces a very optimized query plan, and you should consider it as a replacement to SUMMARIZE and ADDCOLUMNS/SUMMARIZE pattern.

注意点

便利で処理も早いSUMMARIZECOLUMNS関数ですが、比較的最近追加された関数のため、使用できるモデルに制限があります。

Excel 2010: サポートされていません。
Excel 2013: サポートされていません。
Excel 2016: 外部フィルターコンテキストがない場合のみサポートされます (*)
Analysis Services 2012: サポートされていません。
Analysis Services 2014: サポートされていません。
Analysis Services 2016: サポートされています。
Power BI Desktop: サポートされています。

(*) Excel 2016 では、SUMMARIZECOLUMNS に外部フィルターコンテキスト（CALCULATE や CALCULATETABLE など）を適用できないため、メジャー式で使用することはできません。

まとめ

まだ理解しているとは言い難いけど、手を動かして調べていくと少しわかってくるもんだ。

SUMMARIZECOLUMNS関数の研究～SUMMARIZE関数は非推奨?🤔～