CALCULATE in DAX by SQLBI Youtube Video

YoutubeのSQLBIチャンネルの動画で、DAXのCALCULATEの使い方を学びます。

01: Filters are tables

　以下のような表があります。

　フィルターを使って、Greenだけ集計するDAXを書きます。

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

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

GreenSales = 
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL('Product'[Color]),
        'Product'[Color] = "Green"
    )
)

02: Add single column filter

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

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

　GreenSalesの全てのセルには、2つのフィルターが働いています。1つは、現在選択されている色、そしてメジャーによって指定された緑色です。その結果、値が表示されるのはGreenの行のセルだけになります。

03: Remove single column filters

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

All Colors =
CALCULATE(
    [Sales Amount],
    REMOVEFILTERS('Product'[Color])
)

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

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

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

04: Add table filter (and difference with column filters)

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

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

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

GreenSales =
CALCULATE (
    [Sales Amount],
    FILTER('Product', 'Product'[Color] = Green)
)

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

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

05: Remove table filters

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

Pct = 
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        REMOVEFILTERS('Product')
    )
)

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

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

Pct =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], REMOVEFILTERS('Product'[Color]))
)

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

06: Using ALLSELECTED

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

Pct =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], REMOVEFILTERS('Product'[Color]))
)

　スライサーでフィルターすると、Colorのフィルターが削除されているため、パーセンテージの合計は100%になりません。

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

Pct = 
DIVIDE(
    [Sales Amount],
    CALCULATE(
        [Sales Amount],
        ALLSELECTED('Product'[Color])
    )
)

07: Add multicolumn filters

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

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

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

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

　ビデオでは、上記の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は外部フィルターに置き換わるものではなく、外部フィルターと同時に働きます。
　現在のバージョンで使える書き方にすると、以下のようになります。

Contoso Green Sales = 
CALCULATE(
    [Sales Amount],
    KEEPFILTERS(
        'Product'[Brand] = "Contoso" || 'Product'[Color] = "Green"
    )
)

08: Add filters using IN

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

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

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

Red Green Sales =
CALCULATE(
    [Sales Amount],
    KEEPFILTERS('Product'[Color] IN {"Red", "Green"})
)

09: VALUES vs. KEEPFILTERS

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

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

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

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

　これで、年ごとのパーセンテージになり、それぞれの年のQ1,Q2,Q3,Q4の合計は100になります。
　以下のように、フィルターの順番を入れ替えても、結果は同じになります。DAXエンジンは、書かれた順番ではなく、全体を最適化して動作しているからです。

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

10: Using TREATAS

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

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

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

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

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

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

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

Sales 2007-2008 = 
CALCULATE(
    [Sales Amount],
    TREATAS( 
        {(2007, 12), (2008, 1)}, 
        'Date'[Year Number], 
        'Date'[Month Number])
)