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

# はじめに

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

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

続きものです。

1. [SUMMARIZE関数の研究～集計列の追加はなぜ非推奨なのか🤔～](https://qiita.com/akihiro_suto/items/a317d560fd298403bc15)
1. [ADDCOLUMNS関数の研究～集計列追加のベストプラクティス🤔～](https://qiita.com/akihiro_suto/items/ced4c9053f11fbe2baf3)
1. [集計列追加のベストプラクティス🤔～とはいえ例外だってあるぞ～](https://qiita.com/akihiro_suto/items/331aee0734b98c0ec345)
1. [SUMMARIZECOLUMNS関数の研究～SUMMARIZE関数は非推奨?🤔～](https://qiita.com/akihiro_suto/items/1a7c08aaf369ee1cc3a3)
1. [GROUPBY関数の研究🤔～基本動作確認編～](https://qiita.com/akihiro_suto/items/aaf2e4c98cf88833dc63)

## DAXクエリ＆DAX.do

https://dax.do/

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

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

https://qiita.com/hanaseleb/items/96b80210a7fc20c36a13

# 参考記事

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

# 検証開始

## 前回のおさらい

[前回の記事](https://qiita.com/hanaseleb/items/a317d560fd298403bc15)で、`SUMMARIZE`関数での集計列追加は**非推奨**であることがわかりました。
`SUMMARIZE`関数は列のグループ化だけに使おうと。

### 非推奨

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

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

結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/716b303d-985b-6fd2-ab31-1087b6f7e392.png)


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

```
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`関数でフィルターを削除したはずなのに削除されていないように見えます。これが非推奨の理由。内部の処理が複雑で直感的ではないからです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/22158e7a-a769-3402-0069-9d19768950f1.png)


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

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

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

## `ADDCOLUMNS`関数の文法

https://dax.guide/addcolumns/

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/3f35542a-db0c-f65e-9d3e-6fa098eb8bb6.png)

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

## 例

元のDAXクエリ

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

結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/0f8ec9b3-a09a-a121-ad9e-781b1988dccd.png)

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

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

結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/41ae746c-f0d9-5717-f399-17a886785d24.png)

### あれ？

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

なぜなのか🤔

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/4572fb32-f218-0cb1-bce5-7386f3f0b002.png)

## 修正

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

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

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

結果

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/456ed6c1-89e3-4706-7eac-9454b60733cf.png)


# 結論

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

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

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

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

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

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

結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/c12cfb9d-36ec-a9c9-6407-05cbdd63b069.png)

# まとめ

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

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

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

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

# 例外もあるらしい🤔

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

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