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

はじめに

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

CALCULATE in DAX

CALCULATE関数とは

ドキュメントから引用。

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

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

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

サンプルデータ

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

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

動作検証

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

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

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

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

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

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

KEEPFILTERS関数との組み合わせ

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

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

FILTER関数の間違った使い方

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

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

REMOVEFILTERS関数との組み合わせ

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

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

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

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

割合を求める => DIVIDE関数

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

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

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

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

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

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

ALLSELECTED関数との組み合わせ

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

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

複数条件を指定する

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

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

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

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

INとの組み合わせ

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

{ } はテーブルコンストラクターで、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")
}
こうやって書くと、こうなる。

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

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

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

VALUES関数との組み合わせ

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

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

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

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

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

TREATAS関数との組み合わせ

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

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

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

value
2011
2012

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

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

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

まとめ

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

ドキュメント