[Power BI] CALCULATE関数、基礎の基礎😃

# はじめに

DAX関数の基礎でもあり真髄とも言われているCALCULATE関数について、SQLBIのYouTubeを見たので忘れないうちに写経しながら、自分の言葉にしておきます。

**CALCULATE in DAX**

https://youtube.com/playlist?list=PLU6II7MW-aiIees6mrPfdjt9c8noi7P66

# `CALCULATE`関数とは

ドキュメントから引用。

>変更されたフィルター コンテキストでテーブル式を評価します。

Power BIにおいて、一般的に言う「フィルター」をかけたいとき（Excel脳!?🤔）は、`CALCULATE`関数をまずは検討する。構文を確認しても、評価対象に対してのフィルターを後ろに追加していくような書き方になっているのがわかります。
ビデオの中では、**外側のフィルターコンテキストをオーバーライドするテーブル**という紹介がされています。

```dax
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
```

# サンプルデータ

Contosoのデータを使用しています。取得方法は下記記事を参考にしてください～😁

https://qiita.com/hanaseleb/items/adce363d68d76a354723

シンプルなスタースキーマ🌟

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/9c8c789b-42f7-6a9c-7f8b-f83b57f1d694.png)

# 動作検証

## まずはシンプルなものから

まずは単純に、条件式をひとつ書いてみる。テーブルビジュアルのどの色の列でも指定した条件（'Product'[Color] = "Green"）の数値がでる。

```dax
GreenSales = CALCULATE(
    [SalesAmount],
    'Product'[Color] = "Green"
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/78ac5d58-0dd1-55ef-07c0-abd7c1eb7be9.png)

外側のスライサーで色を絞り込んでも動きに変化はなし。`CALCULATE`関数は、すべてのフィルターコンテキストを考慮しないで、`CALCULATE`関数の中にある条件のみを考慮して計算結果を出します🙄

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/c4e0a71a-aba1-e572-e83b-838a5a95b1db.png)

`ALL`関数を利用してフィルターコンテキストをすべて無視するように明示的に書くのと同じ動作をしています。

```dax
GreenSalesFALL = CALCULATE(
    [SalesAmount],
    FILTER(
        ALL('Product'[Color]),
        'Product'[Color] = "Green"
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/f12a4460-f71c-657a-be02-5147004da92a.png)

## `KEEPFILTERS`関数との組み合わせ

すべてのフィルターコンテキストを無視することが求めている結果でない場合もあります。`CALCULATE`関数は規定の動作が全てのフィルターコンテキストを上書きしてしまうので、明示的にフィルターコンテキストを残す必要があります。

```
GreenSalesKF = CALCULATE(
    [SalesAmount],
    KEEPFILTERS(
        'Product'[Color] = "Green"
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/5519b204-c120-6a23-3e29-94ca53ea7975.png)

## `FILTER`関数の間違った使い方

同じような結果を得られる、`FILTER`関数を使用した書き方。しかしこれは、既存のフィルターコンテキストとは別に、テーブル全体を再度スキャンしにいく動作をするため、計算コストが高く、結果が得られるのに非常に時間がかかることがあります。すでにあるフィルターコンテキストを活かすように、`KEEPFILTERS`関数を使用しましょう🎉

```dax
FilterGreen = CALCULATE(
    [SalesAmount],
    FILTER(
        'Product',
        'Product'[Color] = "Green"
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/d207aa36-e33d-ec8d-65e2-1ede521f9815.png)


## `REMOVEFILTERS`関数との組み合わせ

次は`REMOVEFILTERS`関数との組み合わせです。`REMOVEFILTERS`関数を使用すると、指定した列にかかっているフィルターを削除することができます。この場合では`'Product'[Color]`列にかかっているフィルターを削除するので、結果的にすべての売上が表示されています。

```dax
All Colors = CALCULATE(
    [SalesAmount],
    REMOVEFILTERS('Product'[Color])
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/4ab4cc76-fa42-0ae9-58fb-11cafe4ac374.png)

`'Product'[Color]`列にかかわるフィルターはすべて削除されるので、外側のスライサーで色を選択しても全体の売上が表示されたままになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/7d2e5b6f-d5eb-fe7b-1c34-681f54e0bf73.png)

テーブルビジュアルの列に`'Product'[Brand]`を指定すると、こちらは行ごとのコンテキストが保持されて、値が変化する😁

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/95f4820f-a57c-a2f3-f053-fa859048ef26.png)

## 割合を求める => `DIVIDE`関数

割合を求めるときに任意の分母を計算するためにも`CALCULATE`関数は使われます。下記のようなDAXを記述して結果をみてみます。

```dax
Pct% = 
DIVIDE(
    [SalesAmount],
    CALCULATE(
        [SalesAmount], 
        REMOVEFILTERS('Product')
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/55f234ff-b273-b52f-7f64-2782623d335b.png)

一見うまく動作しているように見えますが、`REMOVEFILTERS`関数を使用して削除しているフィルターはテーブル全体にかかっているフィルターです。その検証のため、マトリックスビューで`'Product'[Brand]`列も追加してみます。すると、各ブランドごとに割合が全体に対する割合になっています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/8de29174-c9fa-2841-b7dc-7ebe0d1fe546.png)

一つの列からフィルターを削除したい場合は列を指定する必要があります。
**テーブル全体のフィルターコンテキストを操作してしまうと、誤った結果が出る場合があるので、最小限の列を指定する**ことを忘れないようにしましょう😀

```dax
PctColor% = 
DIVIDE(
    [SalesAmount],
    CALCULATE(
        [SalesAmount], 
        REMOVEFILTERS('Product'[Color])
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/9547e36a-9491-e87b-2054-886b238eaade.png)

この状態で今度はスライサーで絞り込んでみると、各項目ごとの数字が全体に対しての割合を表示したままなので、合計欄も含めて理解しにくい数字が表示されています。ということで次に進みます🤔

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/14165efb-3136-ba46-7630-6c4858835595.png)


## `ALLSELECTED`関数との組み合わせ

上記のような場合は、`ALLSELECTED`関数を使うと期待通りの動作になります。`ALLSELECTED`関数は外部のフィルター（スライサー）は保持をして、ビジュアルでのフィルターを削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/eac5c1d5-2db2-b775-3c04-c439a56313af.png)

今度は`ALLSELECTED`関数を列指定ではなく、`Product`テーブル全体にしてみます。すると、今度はビジュアルからくる`[Brand]`列のフィルターコンテキストも適用されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/00797b13-5d14-318c-33ea-bf11c136109d.png)

## 複数条件を指定する

複数の条件を指定したいときももちろんありますよね。`CALCULATE`関数で条件を複数並べて書いてどのような結果になるか確認してみます。

```dax
Contoso Green = 
CALCULATE(
    [SalesAmount],
    'Product'[Brand] = "Contoso",
    'Product'[Color] = "Green"
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/062df260-574a-de78-cfb4-e44d0730098d.png)

このように、`CALCULATE`関数で条件を並べて書くと**AND**扱いになります。では**OR条件**を指定したいときはどのようにすればよいでしょうか。**OR条件**には演算子`||`を使用できます。**AND条件**も`&&`演算子を使用できます。

```dax
Contoso Green = 
CALCULATE(
    [SalesAmount],
    'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/6755d833-e82a-e841-fd90-cba775aba76b.png)

## `IN`との組み合わせ

`IN`を使用して複数の条件を指定することもできます。結果を見ればわかるとおり、`IN`を使用すると`OR`条件になります。RedかGreenの売上が計算されていることがわかります。同じカラムの複数条件を指定するときはコンパクトにかけて良さそうです。

:::note
{ } はテーブルコンストラクターで、Valueという列名のテーブルを作成します。その中に ( ) で行を作成することも可能。
    {
        (1.5, DATE(2017, 1, 1), CURRENCY(199.99), "A"),
        (2.5, DATE(2017, 1, 2), CURRENCY(249.99), "B"),
        (3.5, DATE(2017, 1, 3), CURRENCY(299.99), "C")
    }
こうやって書くと、こうなる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/ac0b10a2-3855-6b75-7359-99c8454cb60c.png)
:::

https://docs.microsoft.com/ja-jp/dax/table-constructor

```dax
Red Green Sales = 
CALCULATE(
    [SalesAmount],
    'Product'[Color] IN { "Red" , "Green" }
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/23ea88c5-6f68-8de9-0a62-1f1a60d42a7d.png)

`IN`演算子を`KEEPFILTERS`関数でくくると、どのような動きになるでしょうか。`KEEPFILTERS`関数を使用すると外部のスライサーや、ビジュアルのフィルターコンテキストの影響を受けるので、GreenとRedの行にだけ売上が表示される結果になります。

```dax
Keep Red Green Sales = 
CALCULATE(
    [SalesAmount],
    KEEPFILTERS(
        'Product'[Color] IN { "Red" , "Green" }
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/4ed87c89-1f92-3f7e-d4b3-af10a46e5842.png)

## `VALUES`関数との組み合わせ

次はカレンダーテーブルを使用して実験をしていきます。クオーターごとに分けられているテーブルで、売上の比率を表示するために、下記のようなDAXを書いて適用します。結果は図の通り。

```dax
PctDate% = 
DIVIDE(
    [SalesAmount],
    CALCULATE(
        [SalesAmount], 
        REMOVEFILTERS('Date')
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/e415c3c8-c551-ce33-8c31-dfd0cc21dec9.png)

これだと、売上が立っている日付、すべての期間が対象になって、その中の割合を表示しています。実際の業務では年ごとに計算されるのが一般的ですよね。`VALUES`関数を使用して、`REMOVEFILTERS`関数のあとにフィルターの条件を追加してあげます。

```
PctDateValues% = 
DIVIDE(
    [SalesAmount],
    CALCULATE(
        [SalesAmount], 
        REMOVEFILTERS('Date'),
        VALUES('Date'[Year])
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/68ece0da-1cfe-e11d-0e44-aa2919aa8dac.png)

結果は図の通り、年ごとのフィルターが適用されて、年ごとに合計すると100％になるようになりました。`VALUES`関数と`REMOVEFILTERS`関数の記述する順番に意味はありません😮どちらでも結果は同じになります。



## `TREATAS`関数との組み合わせ

最後に少し応用編🤔ある年の売上を計算したいときに以下のようなDAXを書いて、結果をみてみます。結果は正しく計算されています。

```dax
Sales 2011-2012 = 
CALCULATE(
    [SalesAmount], 
    'Date'[Year] IN { 2011 , 2012 }
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/c9af7b9d-e611-44d6-1b8a-b7e3738924c6.png)

次にこのような式を書いてみます。`{ 2011 , 2012 }`の部分はValueという列名のついたテーブルとして認識されており、リレーションシップが貼られていないためエラーは出ませんが、先程のように正しく計算はされず、`[SalesAmount]`と変わらない数字が出ています。

|value|
|---|
|2011|
|2012|

https://docs.microsoft.com/ja-jp/dax/table-constructor

```dax
Sales 2011-2012 TE = 
CALCULATE(
    [SalesAmount], 
    { 2011 , 2012 }
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/c93af405-173c-87a8-616c-76e6ac54649c.png)

このようなときに使用するのが`TREATAS`関数です。最初の引数にテーブルを指定し、そのテーブルでフィルターをかけるべき列を指定します。すると、先ほどと同じ正しい数字が計算されていることを確認できます。

```dax
Sales 2011-2012 TREATAS = 
CALCULATE(
    [SalesAmount], 
    TREATAS(
        { 2011 , 2012 },
        'Date'[Year]
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/2cc7073f-91df-82d0-ad16-8140e945ff18.png)

# まとめ

以上、`CALCULATE`関数やフィルターコンテキストと少しは仲良くなれた気がします。なにもわかっていないことをわかることができただけでも収穫有り💪😅

# ドキュメント

https://docs.microsoft.com/ja-jp/dax/calculate-function-dax

https://docs.microsoft.com/ja-jp/dax/treatas-function

https://docs.microsoft.com/ja-jp/dax/values-function-dax