CALCULATE in DAX by SQLBI Youtube Video

Youtubeの[SQLBI](https://www.youtube.com/channel/UCcc21gBGNJwZM_eDEByeN-Q)チャンネルの動画で、DAXのCALCULATEの使い方を学びます。

# 01: Filters are tables
[![IMAGE](https://i.ytimg.com/vi/Tk-7gBt9CDE/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLBVd3BVcGw9uXBykaPspix6RnSr3A)]
(https://www.youtube.com/watch?v=Tk-7gBt9CDE&list=WL&index=7)

　以下のような表があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2ca26014-e812-20b2-6951-9ddc80636e46.png)
　フィルターを使って、Greenだけ集計するDAXを書きます。

```text
GreenSales =
CALCULATE (
    [Sales Amount],
    'Product'[Color] = Green
)
```
　上の式は、エンジンが以下のように書き換えているため、期待する結果になりません。**CALCULATEのフィルターで指定されている項目は、外側のフィルターは無視され、内側のフィルターのみ適用されます。**つまり、GreenSales列は、各行の条件が何であっても、すべてにGreenの値が表示されてしまいます。

```text
GreenSales = 
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL('Product'[Color]),
        'Product'[Color] = "Green"
    )
)
````
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/73955f8e-e3c4-47ca-5133-1295589262b1.png)

# 02: Add single column filter
[![IMAGE](https://i.ytimg.com/vi/cgIi3iFKOvE/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLB-1aYVa5MZyVxq9eK2ao4WdyB5nw)]
(https://www.youtube.com/watch?v=cgIi3iFKOvE)

　CALCULATEは、外部フィルターを内部フィルターで上書きしてしまいます。外部フィルターを保持し、内部フィルターを追加するには**KEEPFILTERS**を使います。

```text
GreenSales =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS('Product'[Color] = Green)
)
```

　GreenSalesの全てのセルには、2つのフィルターが働いています。1つは、現在選択されている色、そしてメジャーによって指定された緑色です。その結果、値が表示されるのはGreenの行のセルだけになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/11e9b754-d2de-8630-d23b-9d1c973d5929.png)

#03: Remove single column filters
[![IMAGE](https://i.ytimg.com/vi/VPeo5PF4UTM/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLBJYel1FELonGwQmve1RvWO5nW85A)]
(https://www.youtube.com/watch?v=VPeo5PF4UTM&t=26s)

　外部フィルターを削除するには**REMOVEFILTER**を使います。

```text
All Colors =
CALCULATE(
    [Sales Amount],
    REMOVEFILTERS('Product'[Color])
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3dec4a9d-0404-8e82-c71c-438ef8290494.png)

　REMOVEFILTERSで注意すべき点は、現在のビジュアルからのフィルター、スライサーから発生するフィルター等、全てのフィルターを削除します。ここでは、スライサーも無視され、すべてのセルに総計が表示されています。

　行の表示をColorからBrandに変更してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/6b582fd1-b79b-167f-5814-62305ebb9a5d.png)

　このメジャーでは、Colorのフィルターは削除されていますが、Brandの行フィルターは削除されません。

#04: Add table filter (and difference with column filters)
[![IMAGE](https://i.ytimg.com/vi/EawkPc_iLs4/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLABSGbOAFNfwFitDbCRl00jUTVNxQ)]
(https://www.youtube.com/watch?v=EawkPc_iLs4&list=WL&index=10)

　列フィルターとテーブルフィルターの違いを見ましょう。

```text
GreenSales =
CALCULATE (
    [Sales Amount],
    'Product'[Color] = Green
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2cd0186e-b2e2-550a-481a-b0f62ca5f431.png)

　第1回で見たように、上記の式はうまく働きません。それを修正するため、多くの人が間違うのは、これをテーブルフィルターに置き換えています。

```text
GreenSales =
CALCULATE (
    [Sales Amount],
    FILTER('Product', 'Product'[Color] = Green)
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/bb0bdc17-b8a3-3c68-4750-294e5e29bc46.png)

　結果は求める値と一致しますが、その理由を理解することが重要です。
　このメジャーでは、テーブル全体のフィルタリングをおこなっており、全ての計算を遅くします。このような構文を使用することに慣れてしまうと、フィルターを変更したときに正しい答えを求めることができなくなります。

　テーブルフィルターの代わりに列フィルターを使うことがベストプラクティスです。

# 05: Remove table filters
[![IMAGE](https://i.ytimg.com/vi/9qKe66uBce8/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLBWh78a9Et-rAvKkfsArbEo0knHpw)]
(https://www.youtube.com/watch?v=9qKe66uBce8&list=WL&index=11)

　メジャーを作成して、全ての色に対する売上高の割合を表示させます。

```text
Pct = 
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        REMOVEFILTERS('Product')
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d6254d95-84d0-325c-e3e0-e7943f43d064.png)

　ブランドごとの色の割合を表示させたいので、Brandを追加し、Colorをドリルダウンさせます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f79f75ce-8108-e336-0d77-db57ee081840.png)

　Pctは、全体からの割合になっています。求めるのは、製品ごとの割合なので、テーブル・フィルターではなく、列フィルターに変更します。

```text
Pct =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], REMOVEFILTERS('Product'[Color]))
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1923bfcb-7686-c51a-7ad2-805949c9bf84.png)

　メジャーを使って計算させる時には、CALCULATEができるだけ少ない計算を行うよう心がけてください。

#06: Using ALLSELECTED
[![IMAGE](https://i.ytimg.com/vi/UehWR5wWWEo/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLDaGmBDrJSKKsDMQaVt9a4nUEobkQ)]
(https://www.youtube.com/watch?v=UehWR5wWWEo&list=WL&index=12)

　以下のようなフィルターを使って表が作られています。

```text
Pct =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], REMOVEFILTERS('Product'[Color]))
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/fb764fa2-6b69-53e6-5f34-102544f3e1c9.png)

　スライサーでフィルターすると、Colorのフィルターが削除されているため、パーセンテージの合計は100%になりません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/57da839d-8189-7dc4-0c3c-ee932b83d204.png)

　スライサーで選択されたものの中でパーセンテージを取りたい場合は、**ALLSELECTED**を使います。

```text
Pct = 
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        ALLSELECTED('Product'[Color])
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2329078b-ac42-1382-bdc9-c4da59d0ee38.png)

#07: Add multicolumn filters
[![IMAGE](https://i.ytimg.com/vi/kQjYG6TJVp8/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLDdA4H4tQXlM3SVd-e5JjHKUgPM0w)]
(https://www.youtube.com/watch?v=kQjYG6TJVp8&t=5s)

　2つの項目でフィルターをかけたい場合、以下のようなメジャーを書いてみました。

```
Contoso Green Sale =
CALCULATE(
    [Sales Amount].
    'Product'[Brand] = "Contoso",
    'Product'[Color] = "Green"
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b3137d95-8663-305f-2761-edf8fc048057.png)

　この表のContoso Green Salesに表示されるのは、BrandがContosoでColorがGreenの合計です。
　BrandがContosoまたはColorがGreenの値を見たいときはどうしますか。

```
Contoso Green Sale =
CALCULATE(
    [Sales Amount].
    'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b88592c0-e591-8c6e-1ef5-47824be48d28.png)

　ビデオでは、上記のDAXはエラーになってしまいますが、現在のバージョンでは機能します。しかし、BrandとColorのフィルターが解除されてしまうため、全てがBrandがContosoまたはColorがGreenの値の合計になってしまいます。

　FILTERを使うと、

```
Contoso Green Sale =
CALCULATE(
    [Sales Amount].
    FILTER(
        ALL('Product'),
        'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
    )
)
```

　上記は、'Product'テーブル全体のフィルターを解除してしまうので、以下のように2つの列のみフィルターの削除を適用ます。テーブル全体の解除より、限定的な解除が安全で望ましいです。

```
Contoso Green Sale =
CALCULATE(
    [Sales Amount].
    FILTER(
        ALL('Product'[Brand], 'Product'[Color]),
        'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
    )
)
```

　作りたいのは、行フィルターが適用されたものなので、KEEPFILTERSを使って、以下のように書き換えます。

```
Contoso Green Sale =
CALCULATE(
    [Sales Amount].
    KEEPFILTERS(
        FILTER(
            ALL('Product'[Brand], 'Product'[Color]),
            'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
        )
    )
)
```
　KEEPFILTERSは外部フィルターに置き換わるものではなく、外部フィルターと同時に働きます。
　現在のバージョンで使える書き方にすると、以下のようになります。

```text
Contoso Green Sales = 
CALCULATE(
    [Sales Amount],
    KEEPFILTERS(
        'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2e912c41-b8a5-3f1e-e6a7-4c27ac99d293.png)


#08: Add filters using IN
[![IMAGE](https://i.ytimg.com/vi/4-WSXQ6qLec/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLB52J907ZNA8beWJfBHhqW3BN3NFw)]
(https://www.youtube.com/watch?v=4-WSXQ6qLec)

　RedとGreenの集計を行います。

```text
Red Green Sales = 
CALCULATE(
    [Sales Amount],
    'Product'[Color] IN {"Red", "Green"}
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9bc148e8-cf67-acb0-1aad-35b68a9025f8.png)

　RedかGreenの条件が、外部フィルターを上書きし、どれも同じ値になります。外部フィルターを保持したい場合には、KEEPFILTESを使います。

```
Red Green Sales =
CALCULATE(
    [Sales Amount],
    KEEPFILTERS('Product'[Color] IN {"Red", "Green"})
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/64e15b62-2f1b-ee43-035f-7995db5bb001.png)

#09: VALUES vs. KEEPFILTERS
[![IMAGE](https://i.ytimg.com/vi/LKj4GLJA-lw/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLD6igOeIFsCI780EQotlJ2ZGWk0qA)]
(https://www.youtube.com/watch?v=LKj4GLJA-lw&t=33s)

　その年の合計に対するパーセンテージを取得したいと思います。
　まず、CALCULATEの中で、フィルターを取り除くことから始めます。

```text
PCT =
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        REMOVEFILTERS('Date')
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2dad75a3-c487-c4b6-008f-cd4586c280da.png)

　REMOVEFILTERは、Dateテーブルのフィルターをすべて削除するため、表示されるのは全体の合計に対するパーセンテージです。
 年ごとのパーセンテージを得るためには、別のフィルターを作成する必要があります。
REMOVEFILTERSが適用された後に、その年のフィルターをVALUEを使って復元します。

```
PCT =
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        REMOVEFILTERS('Date'),
        VALUES('Date'[Year])
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/77a1a369-1c85-7190-abf3-31321909ce27.png)
　これで、年ごとのパーセンテージになり、それぞれの年のQ1,Q2,Q3,Q4の合計は100になります。
　以下のように、フィルターの順番を入れ替えても、結果は同じになります。DAXエンジンは、書かれた順番ではなく、全体を最適化して動作しているからです。

```
PCT =
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        VALUES('Date'[Year]),
        REMOVEFILTERS('Date')
    )
)
```

#10: Using TREATAS
[![IMAGE](https://i.ytimg.com/vi/Nr-_difQ7vw/hqdefault.jpg?sqp=-oaymwEYCKgBEF5IVfKriqkDCwgBFQAAiEIYAXAB&rs=AOn4CLAJAzb-3ZXVCDB4sx4LE7BWksbaQA)]
(https://www.youtube.com/watch?v=Nr-_difQ7vw&t=4s)

　2007年と2008年の売り上げを集計します。

```text
Sales 2007-2008 = 
CALCULATE(
    [Sales Amount],
    'Date'[Year Number] IN {2007, 2008}
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/62235153-bca5-4b78-f339-b63b323e42a4.png)

　このメジャーは、Year Numberのフィルターを削除してしまうため、全ての項目に 2007年と2008年の売上合計が表示されてしまいます。
　このようなメジャーは、年を行見出しに使うことはないので、Brandに置き換えます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/780207a6-733c-9461-2adc-dada561084c5.png)

　それぞれのブランドの２年間の売り上げが表示されます。

　TREATASを使った別の方法があります。

```text
Sales 2007-2008 = 
CALCULATE(
    [Sales Amount],
    TREATAS( {2007, 2008}, 'Date'[Year Number])
)
```

　年月でフィルターしたい場合は、以下のように書きます。

```text
Sales 2007-2008 = 
CALCULATE(
    [Sales Amount],
    TREATAS( 
        {(2007, 12), (2008, 1)}, 
        'Date'[Year Number], 
        'Date'[Month Number])
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b6a724e9-54c8-aa47-691b-c3f2b9194854.png)