雑・Excel入門試論 - 脱VLOOKUPの思考 22 DAX - CALCULATE

（※この記事は2022年12月21日に書かれています。Excelのバージョンは、Microsoft® Excel® for Microsoft 365 MSO (バージョン 2212 ビルド 16.0.15928.20144) 64 ビット です）

　フィルターコンテキストの動きを理解する上で、`CALCULATE` 関数を知らなければなりません。

# 基本

```text:CALCULATE構文
CALCULATE(
    <expression>                       // 評価される式
    [, <filter1> [, <filter2> [, …]]]  // フィルター
)
```

　第1引数は式またはメジャーになります。第2引数はフィルターです。

## ブール型フィルター
　True/Falseの条件によってフィルターを行います。

```text:Bool1
Bool1:=CALCULATE(
    SUM(financials[Sales]),
	financials[Product] = "Paseo"
)
```
　上記は、製品名が「Paseo」の売上を計算しています。
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0fe4e2fd-5d0b-7d1b-96c2-017e3422107f.png"/>

　次に、最終日の売上を計算させてみます。

```text:Bool2_NG
Total Sales on the last date = 
CALCULATE(
    SUM(financials[Sales]), 
    financials[Date] = MAX(financials[Date])
)
```
　この式はうまく動きそうですが、以下のようなエラーが出ます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a2eac6ec-e64a-b1a0-4f0a-8c0469bc32db.png)

:::note alert
2022年12月21日現在では、ExcelのDAXでは、CALCULATEのフィルターにスカラー値を返す集計関数を含めることはできません。
:::
そこで以下のように書き換えます。

```text:Bool2_OK
Total Sales on the last date:=
VAR _LastDate = MAX(financials[Date])  // 最終日を_LastDateへ代入
RETURN
CALCULATE(
	SUM(financials[Sales]),
	financials[Date] = _LastDate      // 日付が最終日でフィルター処理
)
```
　ここで、DAX式の変数の扱いを説明します。

```text:変数
メジャー名 :=
VAR 変数名 = 式
RETURN
  式
```
　`VAR` の後には変数名と式を書き、RETURN以降にメジャーで計算される式を書きます。変数はいくつでも書くことができます。

:::note info
　Power BIでは、2021年9月のリリースでスカラー値を返す集計関数を含めることができるようになっています。
:::
　このように、ExcelのDAXは、Power BIよりリリースが遅く、動作が異なる場合があります。


# テーブルフィルター
　テーブル式フィルターでは、テーブルオブジェクトがフィルターとして適用されます。

　先の例で、最期の日付をMAXで取得して計算する方法がありましたが、変数を使わずに最期の日付をテーブル型で取得して計算する方法があります。

```text:TableFilter
TableFilter := CALCULATE(
    SUM(financials[Sales]),
    LASTDATE(financials[Date])
)
```
　`MAX` はスカラー値を返す関数ですが、`LASTDATE` は日付列の最期の日付を1列1行のテーブルで返します。結果は同じになりますが、こちらはテーブルフィルターを使った計算式になります。

### フィルター関数

|関数|名称|説明|
|:--|:--|:--|
ALL|All|指定されたテーブル、または列の全てのフィルターを削除します
ALLEXCEPT|All Excpet|指定された列を除く他の列のフィルターを削除します
ALLNOBLANKROW|All No Blank Row|指定されたテーブル、または列の空白を除く全てのフィルターを削除します
KEEPFILTERS|Keep Filters|既存のフィルターを削除せずにフィルターを追加します
USERELATIONSHIP|Use Relationship|非アクティブなリレーションシップを使用し、干渉するリレーションシップを非アクティブにします。
CROSSFILTER|Crossfilter|フィルター方向を変更します

Excelで未サポートの関数

|関数|名称|説明|
|:--|:--|:--|
REMOVEFILTERS|Remove Filters|指定されたテーブル、または列からフィルターを削除します|

## ALL
　ALLを使った例です。

```text:CALCULATE_ALL
CALCULATE_ALL:=CALCULATE(
    [Sales],
    FILTER(
        ALL(financials[Product]),
        financials[Product] = "Paseo"
    )
)
```
　この式では、フィルターにテーブルを使っているので、テーブルフィルターと呼びます。そして、 `FILTER` 関数は、`financials[Product]` のフィルターを削除しているので、製品名でフィルターした表はすべて同じ値になってしまいます。そして、これは、上のBool1で書いたDAX式と同じ結果を示します。

<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/67845cb5-f8a9-5d50-31ec-bfbdf5f466e8.png"/>

　つまり、`CALCULATE` のフィルターで書かれた列は、外のフィルターを削除してしまいます。

## KEEPFILTER

　外のフィルターを削除せずに集計を行いたい場合は、`KEEPFILETERS` を使用します。

```text:CALCULATE_KEEPFILTERS
CALCULATE_KEEPFILTER := CALCULATE(
	SUM(financials[Sales]),
	KEEPFILTERS(
        financials[Product]="Paseo"
    )
)
```
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/50f82063-1ba0-7c7a-005f-7d594cc51359.png"/>

　これは、期待通りの動きをしています。

```text:CALCULATE_FILTER
CALCULATE_FILTER := CALCULATE(
    SUM(financials[Sales]),
    FILTER(
        financials, 
        financials[Product]="Paseo"
    )
)
```
　これも、先程と同じ期待通りの動きをしますが、ここで行われているのは、テーブル全てのフィルターを消して計算を行っており、より多くのリソースが使用されています。パフォーマンスの良い計算を行うためには、フィルターの削除を行う場合にテーブル全体ではなく、必要な列に絞って行うことが大切です。

## ALLEXCEPT

　製品売上の割合を見たい場合、以下のような式を使います。

```
Pct := DIVIDE(
    SUM(financials[Sales]),
    CALCULATE(
        SUM(financials[Sales]),
        ALL(financials)
    )
)
```
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/df33e284-bab2-355c-fd7c-b4a5ed960d17.png"/>

　では、国ごとの製品売上の割合が見たい場合、どのようにするのでしょうか。行にCountryを追加しただけでは、以下のようになります。

<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ce614e36-0f5b-2338-6025-5e40e1a5330e.png"/>

　分母は国ごとの売上合計になる必要があるので、REMOVEFILTERSを使って `REMOVEFILTERS(financials[Product])` とやりたいとこですが、ExcelではREMOVEFILTERSが使えないので、ALLEXCEPTを使います。

```text:ALLEXCEPT
AllExcept := DIVIDE(
    SUM(financials[Sales]),
    CALCULATE(
        SUM(financials[Sales]),
        ALLEXCEPT(financials,financials[Country])
    )
)
```
　これで、以下のような表が出来上がります。

<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0b7d1bea-9e65-5bfa-7847-8cfec4cd742d.png"/>

## ALLSELECTED
　フィルターを設定して、表示する製品を選ぶようにしたいと思います。その場合、先程の式だと以下のようになってしまいます。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/371305ba-a1d7-3282-2faf-5c3d7c9f04f9.png"/>

　選択された製品で100%になるように表示したいので、`ALLSELECTED` を使用して、外部のフィルターが有効になるように計算します。

```text:AllSelected
AllSelected :=DIVIDE(
    SUM(financials[Sales]),
    CALCULATE(
        SUM(financials[Sales]),
        ALLSELECTED(financials[Product])
    )
)
```
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/fc1e394d-e368-b47a-97bf-438fa933e5d4.png"/>

　外部のフィルターを生かして計算することができました。

## IN
　複数の製品を選択してフィルターしたい場合は、`IN` を使用することができます。

```text:CalculateIn
CalculateIn := CALCULATE(
    SUM(financials[Sales]),
    financials[Product] IN {"Paseo", "Amarilla"}
)
```

　この結果は、以下のようになってしまいます。
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1c6bc775-2cac-782c-7929-362fec72a2e4.png"/>

 Paseo と Amarilla の合計値が全てのセルに表示されています。外のフィルターを活かすためには、`KEEPFILTER` を使います。

```text:CalculateIn
CalculateIn := CALCULATE(
    SUM(financials[Sales]),
    KEEPFILTERS(
        financials[Product] IN {"Paseo", "Amarilla"}
    )
)
```
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/440f9db0-e6b4-bd9a-8988-29d5b442d6ae.png"/>

# コンテンツ
- [雑・Excel入門試論 - 脱VLOOKUPの思考 01 - ブック - 仕様と制限](https://qiita.com/spumoni/items/189004c01181c50a4821)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 02 - ブック - オプション](https://qiita.com/spumoni/items/75955db69cc34695de14)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 03 - ワークシート](https://qiita.com/spumoni/items/5408cae114034be528c4)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 04 - セル - 文字列型](https://qiita.com/spumoni/items/4ab1d7067418886c0582)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 05 - セル - 数値データ](https://qiita.com/spumoni/items/970b4b39a5a2185992ed)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 06 - セル - 日時データ](https://qiita.com/spumoni/items/9f270e181fbc45503759)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 07 -  リンクされたデータ型](https://qiita.com/spumoni/items/ed59d15753d992c3a630)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 08 - セル - 計算式・関数](https://qiita.com/spumoni/items/ae339c97627110e2134e)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 09 -  セル - 数値の書式設定](https://qiita.com/spumoni/items/1b3134d4b0284465976f)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 10 - セル - 日付と時刻の書式設定](https://qiita.com/spumoni/items/6eb602f69956a5e1502f)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 11 - セル - 条件付き書式](https://qiita.com/spumoni/items/04bd8e24284daef6f27a)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 12 - テーブル - テーブルの作成と入力規則](https://qiita.com/spumoni/items/f23beca01c5093c81c47)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 13 - テーブル - ソートとスライサー](https://qiita.com/spumoni/items/b66027008c2f9edb853f)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 14 - テーブル - 動的配列関数](https://qiita.com/spumoni/items/cc03e898f02b8c3017f7)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 15 - Power Query - エクセルのデータを読み込む](https://qiita.com/spumoni/items/fc38a4ae595b64af87f5)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 16 - Power Query - 変換](https://qiita.com/spumoni/items/100e0e3c6a5a59b03229)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 17 - Power Query - テーブルの結合](https://qiita.com/spumoni/items/debb9ea9b8692ca8fc8c)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 18 パワーピボット - データモデル](https://qiita.com/spumoni/items/08458978e86dc59f18ed)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 19 パワーピボット - 操作](https://qiita.com/spumoni/items/e7b3f245e87622e9bc79)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 20 パワーピボット - 日付テーブル](https://qiita.com/spumoni/items/340e0fcbc9cd10198112)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 21 DAX - コンテキストとイテレーター](https://qiita.com/spumoni/items/cddc7fa16341e20bafb8)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 22 DAX - CALCULATE](https://qiita.com/spumoni/items/e2218fece1ec01e177b0)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 23 DAX - タイムインテリジェンス](https://qiita.com/spumoni/items/6d8aeb9f80ecafb69c5a)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 24 - ダッシュボード - ピボットグラフ（Pivot Chart）](https://qiita.com/spumoni/items/ffb0d57a6272c2e5ad37)
- [雑・Excel入門試論 - 脱VLOOKUPの思考 25 - ダッシュボードの作成](https://qiita.com/spumoni/items/5e57ad587a343857e543)