はじめに
コンテキストとCALCULATE関数を理解するのは難しい。とっても難しい。しかし、ここを理解できるかどうかがPower BI強者への分かれ道。データモデルもシンプルになる。
過去にこのような説明を考えつき、自分自身はスッキリした。これでみなの理解増進になるぞと、興奮とともに記事を同僚に紹介したが、同僚には困った反応をされ、誰のためにもならなかった（そりゃそうだ）。あれから1年半。リベンジだ。
具体例の列挙ではなく、理屈や考え方を説明して、本質的な理解の手助けをしたい。
この記事の対象読者、留意点等は以下:
- 対象読者
- CALCULATE関数を使ったことが無い人、これから勉強しようという人
- CALCULATE関数を使ったことはあるが、理解に自信が無い人
- かつてCALCULATE関数の理解で挫折した人
- やること
- メジャーを説明
- まずはフィルター コンテキストの理解から
- やらないこと
- 計算列: 計算列はあまり使わないため
- 行コンテキスト: イテレーター関数とセットで
- イテレーター関数: また今度
- 注意点
- 説明の簡略化のためデータをシングル テーブルとしているが、実際の利用時はスタースキーマでやること
サンプル
サンプル レポートを使って説明しよう。
ダウンロード方法
ここに保存しているので、ダウンロード アイコンをクリックしてダウンロードする。
サンプルの中身
上記は年、地方、魚種、_漁獲量を列とする以下のテーブルから作成している:
（説明はしないが、ちゃんとスター スキーマにしたものを同じレポート内に用意したので、本記事の内容が理解できたらスター スキーマ版のメジャーも理解してほしい。）
フィルター コンテキスト
テーブルやマトリックスの値や、折れ線グラフの一点、棒グラフの棒一つ等のことを、データ ポイントという。各データ ポイントの値がどのように計算されているかを確認することで、フィルター コンテキストのメジャーへの作用を理解してみよう。
先ほどの具体例でマトリックスの値、折れ線グラフのY軸の値がどのように計算されているかを考えてみる。「んなもん、直感的に明らかやろ、バカにしてんのか！」というママカリが飛んできたら、美味しくいただきます。一見して自明なことや理屈を考えるまでもないことでも、見方を変えながら注意深く観察することで本質をつかむことができる。最初は我慢して付き合っていただきたい。
順番に理解していこう。
データ ポイントの値はテーブルのデータから計算されている
当たり前のこと。存在しないデータから計算しているわけではない。
どのように計算されているかを考える
次の2ステップで計算されている。これもすんなり頭に入るだろう。
- Step 1: テーブルのデータを絞り込む
- Step 2: 絞り込んだ行（一つとは限らない）から、数字を計算する
データの絞り込みを理解
Step 1の「テーブルのデータを絞り込む」について考える。特定のデータ ポイントが、何によって絞り込みの条件が与えられるかを考えよう。
- ビジュアルによるもの: マトリックスであれば行見出しや列見出し、折れ線グラフであればＸ軸、凡例等。
- スライサーによるもの: レポート内のスライサー。
- フィルター ウィンドウによるもの: フィルター ウィンドウでビジュアル単位、ページ単位、すべてのページで設定したもの。
これらの絞り込みが、ビジュアル内のデータ ポイントごとに行われる。絞り込みの留意点は以下：
- 列の値により行を絞り込む（絞り込みの対象は列ではない）
- 複数の列でフィルターをかける場合、それらはＡＮＤ条件で重ね合わせられる
これが フィルター コンテキスト 。
数字の計算を理解
フィルター コンテキストで絞り込まれた行（１つとは限らない）から、何らかの方法で値を計算する。何らかの方法とは関数のこと。SUM、AVERAGE、MAX、MIN、COUNTROWS等で、まとめて 集計関数 と呼ぶ。何らかの集計が必要、つまりメジャーの戻り値として列そのものを設定できないということ理解してほしい。
公式ドキュメントは以下：
ここまでのまとめ
データ ポイントの値は以下の２ステップで計算される。
- Step 1 フィルター コンテキスト: データ ポイントの値を計算するために、テーブルの行を絞り込むためのフィルター。データ ポイント毎に生成される。
- Step 2 集計関数: 得られた行を元にデータ ポイントの値を計算する。
つまり、データ ポイント毎にフィルター コンテキスト✕集計関数で結果を得ているということ。ここまで理解して、やっとメジャーを理解できたことになる。
先の例で考える
マトリックスの例
スライサーが'漁獲量'[年] = 2015となっている状態で、「地方別魚種別漁獲量」マトリックスの、オレンジ色の枠のデータ ポイントを考える。
Step 1 フィルター コンテキスト
このデータ ポイントのフィルター コンテキストは以下から生成される:
- スライサーより、
'漁獲量'[年] = 2015 - 行見出しより、
'漁獲量'[地方] = "九州" - 列見出しより、
'漁獲量'[魚種] = "いわし類"
得られる行は以下:
Step 2 集計関数
このビジュアルの値フィールドには、[漁獲量]メジャーが設定されている。
漁獲量 = SUM('漁獲量'[_漁獲量])
したがって、'漁獲量'[_漁獲量]列の値を合計し、182,693を得る。これが表示されている値。表示されているすべての値はこの2ステップを経て計算されている。
折れ線グラフの例
「魚種別漁獲量（t）」折れ線グラフの、オレンジ色の枠のデータ ポイントを考える。
Step 1 フィルター コンテキスト
このデータ ポイントのフィルター コンテキストは以下から生成される:
- スライサーより、
'漁獲量'[年] >= 1986かつ'漁獲量'[年] <= 2015 - X軸より、
'漁獲量'[年] = 1991 - 凡例より、
'漁獲量'[魚種] = "いわし類"
得られる行は以下:
見てのとおり行が1つに絞られていない。EXCELに慣れているとちょっとここは理解しにくいかもしれない。なぜならば、EXCELであればX軸の値を格納した列、系列別のY軸の値を格納した列から折れ線グラフを作るが、Y軸の値はX軸に対して1つしかないため。
この疑問点は折れ線グラフを「テーブルとして表示」すれば解消される。以下がそのテーブル。実は折れ線グラフの前に内部的なマトリックスが作成されており、そのマトリックスを元に折れ線グラフが作成されているということ。
Step 2 集計関数
このビジュアルのY軸フィールドには、[漁獲量]メジャーが設定されている。
漁獲量 = SUM('漁獲量'[_漁獲量])
したがって、'漁獲量'[_漁獲量]列の値を合計し、2,057,512を得る。これが表示されている値。表示されているすべての値はこの2ステップを経て計算されている。
CALCULATE関数
CALCULATE関数はDAXの最重要関数。CALCULATEでコンテキストを変更して、自由自在に集計できるようになればDAX強者。この関数の使い方を理解することがこの記事の目標。
CALCULATE関数の重要性
CALCULATE関数が重要な理由は、メジャーのコンテキストを変更することで、データセット内のあらゆるデータから集計できるため。しかも、CALCULATE関数の結果もまたメジャーなので、結果も動的となる。
例えば、日別、カテゴリー別の売上データから、売上の年度累計、前年同期比やカテゴリー毎の構成比率などを計算できる。しかも、動的に結果を得られるため、ビジュアルを変更すれば同じ式のまま、年度累計ならカテゴリー毎の年度累計に、カテゴリーごとの構成比率なら月別のカテゴリーごとの構成比率を計算するといったことが可能。
CALCULATE関数の機能
シンタックス（構文）
CALCULATE(集計関数, フィルター1, フィルター2, …)
パラメーターの説明:
- 第一引数（集計関数）: 集計関数のためのDAX関数（SUM、MAX、COUNTROWS等）を設定
- 第二引数以降（以下、フィルター引数と呼ぶ）: ブール式またはフィルターを生成するDAX関数を設定
CALCULATE関数の機能は2つ:
- フィルター コンテキストを作り第一引数の集計関数に渡す1
- フィルター引数がある場合、既存のフィルター コンテキストをフィルター引数を用いて新しいフィルター コンテキストに変換する。これをコンテキスト変換と呼ぶことにする（MS公式用語でないので注意。コンテキスト トランジションとも違う。）。
これを図示すると以下:
以下、この記事ではこれら理解を目指す。
先に難しいポイントを述べておくと、CALCULATE関数の計算要素は集計関数とフィルター引数の2つだけと思ってしまうが、実は明示的に書かれていない既存のフィルター コンテキストも計算要素となっている（図を見れば明らかであるが既存のフィルター コンテキストを受け取っている）。CALCULATE関数は見えているもの（引数）だけではなく、見えないもの（既存のフィルター コンテキスト）も使っているということを強く意識する必要がある。
みんなここでつまづきます。安心してください（何を🙄？）。
コンテキスト変換のルール
コンテキスト変換のルールは以下:
- 列の値により行を絞り込む（絞り込みの対象は列ではない）
- 複数の列でフィルターをかける場合、それらはＡＮＤ条件で重ね合わせられる
- 同一列に対して、既存のコンテキストによるフィルターとフィルター引数がかかる場合、フィルター引数を優先する
最初のふたつは既に述べたことと同じで、最後のひとつが新しいもの。セットで覚えておこう。
コンテキスト変換の分類
CALCULATE関数におけるフィルター引数によるコンテキスト変換は以下の3種類に分類される：
- 上書/新規（列名=値（ブール式）、タイム インテリジェンス関数等）: 既存のコンテキストとフィルター引数が同一の列に対するフィルターの場合、フィルター引数が優先される。そうではない場合、既存のコンテキストにフィルター引数が新しく追加される。
- 追加（KEEPFILTERS関数）: 特定の列に対する既存コンテキストのフィルターにフィルター引数を追加する。
- 削除（ALL関数、ALLSELECTED関数、ALLEXCEPT関数）: 列またはテーブル単位で、既存コンテキストのフィルターを削除する。
- その他（USERELATIONSHIP関数、CROSSFILTER関数）: 上記以外。リレーションシップの変更等。
これら（その他除く）を図示すると以下:
これらにより、CALCULATE関数はコンテキストを変更して計算することができる。つまり、元々のフィルターを基準にテーブル上のデータを組み合わせて値を得ることができる。
CALCULATE関数を使ってみる
地方構成比を計算する
以下のマトリックスを考える。
このビジュアルの値フィールドには、[漁獲量地方別割合]メジャーを設定している。そのメジャーのDAX式は以下:
漁獲量地方別割合 =
VAR sum_ = SUM('漁獲量'[_漁獲量])
VAR total_sum = CALCULATE(SUM('漁獲量'[_漁獲量]), ALLSELECTED('漁獲量'[地方]))
RETURN DIVIDE(sum_, total_sum)
（VARとRETURNの説明はこちら）
（スター スキーマ版を表示）
漁獲量地方別割合ss =
VAR sum_ = [漁獲量ss]
VAR total_sum = CALCULATE([漁獲量ss], ALLSELECTED('D_地方'[地方]))
RETURN DIVIDE(sum_, total_sum)
このDAX式を説明する。CALCULATE関数を使っているのは3行目。第一引数はSUM('漁獲量'[_漁獲量])で、これは列の値を合計するだけなので、何も難しくない。第二引数のALLSELECTED('漁獲量'[地方])のALLSELECTED関数は、メジャーがセットされたビジュアルが生成したフィルターを削除するもの2。
以下のデータ ポイントに着目し、結果の57％がどのように計算されるかを確認しよう。
コンテキスト変換が行われる前のフィルター コンテキス（＝3行目計算時のフィルター コンテキスト）は、先ほどの例と同じで以下から生成される:
- スライサーより、
'漁獲量'[年] = 2015 - 行見出しより、
'漁獲量'[地方] = "九州" - 列見出しより、
'漁獲量'[魚種] = "いわし類"
得られる行は以下:
したがって、2行目で定義している変数sum_に格納される値は182,693。
このフィルター コンテキストに対し、ALLSELECTED('漁獲量'[地方])フィルター引数でコンテキスト変換が行われると、'漁獲量'[地方]列に対するフィルターが削除され、次のようになる:
- スライサーより、
'漁獲量'[年] = 2015 - 列見出しより、
'漁獲量'[魚種] = "いわし類"
ここが難しいポイント。CALCULATE(SUM('漁獲量'[_漁獲量]), ALLSELECTED('漁獲量'[地方]))と書かれているので、計算要素は集計関数SUM('漁獲量'[_漁獲量])とフィルター引数ALLSELECTED('漁獲量'[地方])の2つだけと思ってしまうが、実は明示的に書かれていない既存のフィルター コンテキストも重要な計算要素となっている。CALCULATE関数は見えているもの（引数）だけではなく、見えないもの（既存のフィルター コンテキスト）も使っているということを強く意識する必要がある。
得られる行は以下:
'漁獲量'[_漁獲量]列の合計319,705が、変数total_sumに格納される。
最終的な結果は、sum_をtotal_sumで割った値57%となる。
期初を100とした時の漁獲量を計算
以下の折れ線グラフを考える。
このビジュアルのY軸フィールドには、[期初来漁獲量割合]メジャーを設定している。そのメジャーのDAX式は以下:
期初来漁獲量割合 =
VAR sum_ = SUM('漁獲量'[_漁獲量])
VAR start_year = CALCULATE(MIN('漁獲量'[年]), ALLSELECTED('漁獲量'[年]))
VAR start_sum = CALCULATE(SUM('漁獲量'[_漁獲量]), '漁獲量'[年]=start_year)
RETURN DIVIDE(sum_, start_sum)*100
（スター スキーマ版を表示）
期初来漁獲量割合ss =
VAR sum_ = [漁獲量ss]
VAR start_year = CALCULATE(MIN('D_年'[年]), ALLSELECTED('D_年'[年]))
VAR start_sum = CALCULATE([漁獲量ss], 'D_年'[年]=start_year)
RETURN DIVIDE(sum_, start_sum)*100
このDAX式を説明する。CALCULATE関数を使っているのは3行目と4行目。
以下のデータ ポイントに着目し、結果の74がどのように計算されるかを確認しよう。
コンテキスト変換が行われる前のフィルター コンテキス（＝3行目計算時のフィルター コンテキスト）は、先ほどの例と同じで以下から生成される:
- スライサーより、
'漁獲量'[年] >= 1986かつ'漁獲量'[年] <= 2015 - X軸より、
'漁獲量'[年] = 1991 - 凡例より、
'漁獲量'[魚種] = "いわし類"
得られる行は以下:
したがって、2行目で定義している変数sum_に格納される値は'漁獲量'[_漁獲量]列の合計で、2,057,512。
3行目を説明。
VAR start_year = CALCULATE(MIN('漁獲量'[年]), ALLSELECTED('漁獲量'[年]))
既存フィルター コンテキストに対し、ALLSELECTED('漁獲量'[年]) フィルター引数でコンテキスト変換が行われると、X軸による'漁獲量'[年]列に対するフィルターが削除され、次のようになる:
- スライサーより、
'漁獲量'[年] >= 1986かつ'漁獲量'[年] <= 2015 - 凡例より、
'漁獲量'[魚種] = "いわし類"
ここでも、見えているもの（引数）、見えていないもの（既存のフィルター コンテキスト）を意識しよう。
得られる行は以下:
'漁獲量'[年]列の最小値1986が、変数start_yearに格納される。
4行目を説明。
VAR start_sum = CALCULATE(SUM('漁獲量'[_漁獲量]), '漁獲量'[年]=start_year)
既存フィルター コンテキストに対し、'漁獲量'[年]=start_year フィルター引数でコンテキスト変換が行われると、'漁獲量'[年]に対するフィルターが上書きされ、次のようになる:
- フィルター引数より、
'漁獲量'[年]=1986（start_year=1986なので） - 凡例より、
'漁獲量'[魚種] = "いわし類"
しつこいようだが、ここでも、見えているもの（引数）、見えていないもの（既存のフィルター コンテキスト）を意識しよう。
得られる行は以下:
'漁獲量'[_漁獲量]列の合計2,788,861が、変数start_sumに格納される。
最終的な結果は、sum_をstart_sumで割って100をかけた値73.77…=74となる。
その他の例
他にもいろいろできるよ。
- 年度累計
- 前年同期比
- カテゴリーごとの平均値
- カテゴリーごとの構成比率
ココが凄いよCALCULATE関数
こんなのコンテキストとかCALCULATE関数とか勉強しなくても、EXCELでもできますがw と思ったそこのあなた、凄いんですよこいつ。
例えば、1つめの[漁獲量地方別割合]メジャーなら、スライサーで年を切り替えると結果が変わるし、列見出しから魚種を除けば、魚種全体の構成比になる。
スライサーで年を切り替え
列見出しから魚種を削除
2つめの[期初来漁獲量割合]メジャーなら、タイムラインの年を変えて結果が変わるし、凡例を地方にすると、各地方毎の指数になる。
タイムラインの年を変更
凡例を地方に
おわかりいただけただろうか。これは元のコンテキストに応じて、変換後のコンテキストも変わるということ（元のコンテキストが何で、変換後のコンテキストが何になるのか、データ ポイントごとに考えてみよう）。単純な集計関数のメジャーだけではなく、CALCULATE関数でコンテキスト変換したメジャーも動的な計算が可能ということ。こういうことはEXCELでは無理。使いやすいメジャーを用意することで、レポート作成者の分析がはかどることにつながる。
おわりに
コンテキストとメジャーとCALCULATE関数の説明は以上。おつかれさまでした。
前回ほどではないにしても、だいぶ演繹的な説明になってしまった。最近気づいたが、論理と抽象化と分類が大好きな学科出身だから、そういうのが身についてしまっているのかもしれない。
少しでもみなさんのCALCULATE関数の理解の助けになれば幸いです💖ここがわからんとか、違っているとかコメントいただけると、とってもうれしいです💖💖
次のステップ
- リレーションシップとスタースキーマ: 本記事では簡略化のためにあえて基本を外したが、まずは基本をしっかり。
- 日付テーブル＆タイム インテリジェンス関数: 年度累計や前年同期比等のよく使う機能。
- フィルター引数として FILTER を使用しない: ベストプラクティスに従うこと。FILTER関数を使うのは推奨事項のみとする。
- イテレーター関数: もっと複雑な計算。
- コンテキスト トランジション: もっともっと複雑な計算。
- 良いDAXの書き方: 保守性、汎用性、再利用性が高いDAX式を目指す（プログラミングじゃん😅）