CALCULATEを制するものはPower BIを制する
おそらく、異論はないと思います。また「理解するのに時間が係る」という点でも共感を得られると思います。
ではどのように理解するかという点で私が個人的に最も重要だと考えるのが以下の点です。
- フィルターコンテクストとは何かを理解する（Power BIの評価コンテクストをコツコツ理解する（その２）フィルターコンテクストをすっきり理解するを参照）
- CALCULATE関数は「見えるパラメータ」と「見えていないパラメータ」を処理していることを知る
- CALCULATE関数がどのように処理しているかを理解する（3つの内部ステップとして理解する）
CALCULATE処理の「見えるパラメータ」と「見えていないパラメータ」
CALCULATE関数が処理するパラメータを整理するとこのようになります。
見えるパラメータは、関数の引数なので自然に意識できますが、見えていないパラメータは文字通り見えていないのでそれらが処理に関わっている（しかも重要に）ことを意識しずらいです。CALCULATE関数が返す結果は当然これら全てのパラメータを使って処理されるので、予想外の結果が返ってくる場合そのいずれかのパラメータに関して前提が間違っていたりする場合があります。
これらすべてのパラメータがどのようにCALCULATE関数で処理されているかを理解すれば、見えていないパラメータを自然に意識できるようになるでしょう。
CALCULATE関数がパラメータをどのように処理しているか
CALCULATE関数の処理全体を表すとこうなります。
「見えるパラメータ」と「見えていないパラメータ」は下図で示すような内部処理で、入力として処理されます。
全体の処理を３つのステップ分けて、どのように処理が進むかを見ていきましょう。
有効フィルターコンテクストを出力する処理
この処理では、フィルターコンテクストに、任意のフィルターを適用して、別の新しいフィルターコンテクスト（有効フィルターコンテクスト）を出力します。
例えば、フィルターコンテクストが店舗＝支店、カテゴリ＝食料品、商品名＝うまいピザ！ だとします。さらに上書きフィルターが 商品名＝All だとします。この場合、有効フィルターコンテクストは店舗＝支店、カテゴリ＝食料品、商品名＝Allになります。「商品名の項目については限定しない」という意図を表しています。
この処理でいくつか注意する点：
- フィルターコンテクストはREADONLYの状態で、変更されることはありません。入力として使われますが、出力される有効フィルターコンテクストは元のフィルターコンテクストのコピーです。
- 上書きフィルターの「上書き」という表現は、２つの入力（フィルターコンテクストと上書きフィルター）で同じ項目に対するフィルターが存在するときは、上書きフィルターが優先されるという意味です。
- もしフィルターコンテクスト側に上書きフィルターと同じ項目のフィルターが無い場合、上書きフィルターは単純にフィルターコンテクストに「加えられる」だけです。場合によっては何の効果もないことがあるので「上書き」というより「優先」という表現の方が正しいかもしれません。
- 上書きフィルターは複数指定できます。例：
CALCULATE(評価式、上書きフィルター１、上書きフィルター２、・・・上書きフィルターn) - 有効フィルターコンテクストはCALCULATEの処理が終了すると破棄されます。
有効モデルを出力する処理
前ステップで出力された「有効フィルターコンテクスト」を入力として取り、Power BIワークスペースの「モデル」に対して適用し、その結果を出力します。
例えば、有効フィルターコンテクストが 店舗＝支店、カテゴリ＝食料品、商品名＝Allだとすると、有効モデルは下図のようになります。
有効モデルも、CALCULATE関数の処理が終わったら破棄されます。
最終結果を出力する処理
最後のステップです。ついにCALCULATE関数のひとつめのパラメーターである「評価式」が「有効モデル」に対して評価されます。
例えば、「評価式」が合計 = SUM(Sales[AmountOfSales])というメジャーだとします。すると、SUMは下図のようなイメージになります。
SUMが集計するのは有効モデルにある３つの行の、AmountOfSales列の値、ということになります。
具体的使用例
例えば、このようなビジュアルがあったとします。
ビジュアルでは各店舗、カテゴリ、商品名に対して以下のメジャーを使って売り上げを合計を表示しています。
合計 = SUM(Sales[AmountOfSales])
ここで「支店の売り上げにもっとも貢献しているカテゴリはどれ？」や「本店の売り上げにもっとも貢献している商品はどれ？」っといった疑問があると仮定します。ひとつのやり方としては、各店舗ごとのカテゴリ・商品別の店舗全体の売り上げに対する割合で示す方法があります（これが実際のビジネスでよく使われる集計かどうかは分かりません。あくまでも例です）
割合を％表示するとして、分母の値は以下のようなCALCULATE関数を使って「店舗ごとの売り上げの合計」の値を求めることが出来ます。
CALCULATE([合計], All('Item'[カテゴリ]), ALL('Item'[商品名]))
このCALCULATE関数における、上書きフィルターの効果を図示すると以下のようになります。
ビジュアルの文脈から得られる「カテゴリ」と「商品名」に関しては、強制的に「カテゴリ＝All（全て）」と「商品名＝All（全て）」が採用されます。「店舗」の値についてのみ、文脈から得ることになります。つまりこのCALCULATEが集計する[合計]は常に、その「文脈で指定された店舗に限定した、売り上げの合計」になります。
そこで、この「店舗あたり売り上げ合計」をメジャーとして定義して、ビジュアルに加えて値を確認してみます。
店舗当たり合計 = CALCULATE([合計], All('Item'[カテゴリ]), ALL('Item'[商品名]))
下図では緑枠に「支店の売り上げ合計」が青枠に「本店の売り上げ合計」が表示されています。
includeの方法で、例えば以下のセルでのフィルターコンテクストを調べて見ました。
下図が示す通り、フィルターコンテクストは「店舗＝支店」だけでなく「カテゴリ＝食料品」と「商品名＝デカイアイス」も含まれています。それにも関わらず、カテゴリと商品が限定されずに「全て」になっているのはCALCULATE関数に渡した上書きフィルターの効果だと分かります。
では「店舗あたり売り上げ合計」を再利用して以下のようなメジャーを定義して、ビジュアルに加えてみます。
店舗あたり％ = DIVIDE(
'_Measure'[合計],
CALCULATE([合計], All('Item'[カテゴリ]), ALL('Item'[商品名]))
)
結果、店舗あたり（本店と支店別々）でのカテゴリと商品の割合が％表示で示すことが出来ました。
まとめ
CALCULATE関数は以下の点を理解すればなかりすっきりと理解することが出来ます
- フィルターコンテクストとは何かを理解する（Power BIの評価コンテクストをコツコツ理解する（その２）フィルターコンテクストをすっきり理解するを参照）
- CALCULATE関数は「見えるパラメータ」に加えて「見えていないパラメータ」も処理する
- CALCULATE関数の処理を３つのステップに分解して理解する
- CALCULATE関数の上書きパラメータの作用を理解する
- CALCULATE関数で、モデルがどのように処理されるかを理解する