« FanCtrlがWindows Defenderでブロックされた(2025/03/12) | トップページ | DIPSユーザーの操縦者情報の登録(2025年3月24日~) »

(Excel/Power BI)メジャーが予期しない値を返す原因とDAXコンテキストの理解

メジャーが期待した値を出さない多くの原因は、DAX式内のパラメータが指す範囲を誤解していることにあります。この範囲はコンテキストによって決まります。本記事では、DAX式内のテーブルや列のコンテキストが何を指すのかを整理しました。

素人の独自解釈なので間違っているかもしれません。間違っていれば修正したいので、コメントで教えていただけると幸いです。

※本記事ではメジャーと計算列を対象とし、計算テーブルやDAXクエリは扱いません。


1. DAXのパラメータの種類 
[*1]

DAX式の中のパラメータには次の種類があるようです。
1) 表 <table> : 表全体。例 : '表'
2) 列 <column> : 列。例 : '表'[列]
3) 値 <value>:1つの値。例 : "値"
4) メジャー : メジャーは1つの値<value>を示しますが、中に'表'や[列]などを含みます。例:[メジャー]

定数などの値はコンテキストの影響を受けませんが、表や列はコンテキストによって計算対象範囲が変わります。
メジャーは中に'表'や[列]などを含むので、コンテキストによって計算対象範囲が変わります。

[*1] ここでのパラメータは、DAX関数の引数やDAX式の中の参照のことであり、クエリパラメータやフィールドパラメータのことではありません。


2. DAXの計算対象

計算の対象はデータモデルのテーブルです。ピボットテーブルやマトリックスではありません。
(例 : ピボットテーブルに数量と単価と金額のメジャーを展開した場合、金額のメジャーはピボットテーブルの数量と単価を掛け算するのではなく、データモデルの数量と単価の掛け算を行います。)


3. 行コンテキストとフィルタコンテキスト

DAXのコンテキストには、次の2種類があります。

1) 行コンテキスト

現在処理している行のことです。
- 計算列では、DAX式が計算される行です。
- イテレータ関数(SUMX、FILTERなど)の内では、現在処理している行を指します。

2) フィルタコンテキスト

フィルタ関数やピボットテーブルの条件など、適用されるフィルタの集合を指します。
- 例:ピボットテーブルで「日付=2024/03/18、製品=A」の位置のメジャーは、該当条件を満たすデータモデルのレコードを対象とします。

余談:コンテキストとフィルタは厳密に言うと同じでは無いですが、コンテキストという単語がイメージできないのであれば、フィルタと読み替えたほうが理解しやすいかも知れません?


4. 適用されるコンテキスト

範囲に適用されるコンテキストは、DAX式の記述場所(メジャー、計算列)や、DAX式の中の記述場所の機能により異なります。

1) メジャー

基本的にはメジャーの中のパラメータが示す範囲は、メジャーが値を表示するピボットテーブルなどのセル位置のフィルタコンテキストに従います。但し、式内の関数や場所により範囲が異なる場合があります。

以下の例の青字斜字の部分が各項目で説明しているパラメータです。

(1) CALCULATE関数の第1引数の関数の中の範囲

"CALCULATE関数に掛かるコンテキスト[*2]"を第2引数以降の"フィルタ"に置き換えたコンテキストに従います。
第2引数が無い場合は、CALCULATE関数に掛かるコンテキストに従います。

例 : CALCULATE(SUM('表'[列1]), '表'[列2] = "定数", ALL('表'[列3]))
例 : RANKX(ALL('table'[column]), CALCULATE(SUM('表'[列1]), '表'[列2] = "定数", ALL('表'[列3]))

この例では、CALCULATE関数に掛かるコンテキストが、第2引数の'表'[列2]="定数"と、第3引数のALL('表'[列3])のANDに変更されたコンテキストが、SUM関数の中の'表'[列1]のコンテキストになります。
(ALL関数の中の'表'[列3]は(3)メジャーのフィルタコンテキストに従います。)

[*2] CALCULATE関数に掛かるコンテキストとは
    - CALCULATEの外側に関数が無ければ、メジャーに掛かるフィルタコンテキストです。
    - イテレータ関数などの中のCALCULATEの場合は、外側の関数の行コンテキストなどをフィルタコンテキストに変えたコンテキストです。[*3]

[*3] 行コンテキストと同じ行があれば、フィルタコンテキストは複数行を示します。

(注意 : 第2引数以降のフィルタ内の関数の引数は、(3)メジャーのフィルタコンテキストに従います。)

参考:CALCULATE関数の中で、CALCULATE関数に掛かるコンテキストと第2引数以降のフィルタのANDをコンテキストにする場合は、第2引数以降にKEEPFILTERS関数を用いて、CALCULATE関数に掛かるコンテキストを残します。次の記事の[M売上_Top3]がその事例です。

ExcelのピボットテーブルでTop3とその他合計と合計行を表示するメジャー
https://bigarrow.tea-nifty.com/blog/2025/10/post-2f7984.html

(2) 関数の引数の内、関数内の行コンテキストやフィルタコンテキストが直接掛かる範囲

関数内の行コンテキストやフィルタコンテキストに従います。

例 : SUMX('table', '表'[列])  :  'table'の1行毎に'表'[列]を加算します。
例 : FILTER('table', '表'[列] = "定数")  :  '表'[列]="定数"を満たしている行のみに'table'を絞り込みます。
例 : CALCULATE(SUM('table'[column]), '表'[列] = "定数")  :  '表'[列]="定数"を満たしている行のみにCALCULATE関数内のコンテキストを変更して'table'[column]に適用し、加算します。

(3) その他の関数の引数(基本)

メジャーに掛かるフィルタコンテキストに従います。これが基本です。

例 : SUM('表'[列])、ALL('表')、VALUES('表'[列]2)、SUMX('表',...)、FILTER('表',...)、
例 : CALCULATE(SUM('table'[column1]), VALUES('表'[列2]), FILTER(ALL('表'[列3]), '表'[列3] <= MAX('表'[列3])
                                        (1)                              (3)                            (3)           (2)                     (3)

(FILTER('表', フィルタ)は'表'をフィルタしたテーブルを示しますが、FILTER('表', フィルタ)の'表'はフィルタされる前のテーブル(メジャーに掛かるフィルタコンテキストに従う)です。)

(4) イテレータ関数の第2引数のメジャー

イテレータ関数の行コンテキストを変換したフィルタコンテキスト[*3]に従います。[*4]
例 : RANKX(ALL('table'[column]), [メジャー])

[*4]「メジャーを呼び出したときの DAX の評価ルール」 によりDAX の "メジャーの暗黙的な CALCULATE の適用" が行われます。これは(3)の基本仕様とは異なります。

(5) タイムインテリジェンス関数の第1引数

DATEINPERIODなどの第1引数は、日付テーブルを指定しますが、これは一般的な関数と異なりコンテキストが掛からず、全てが対象になります。
DATEINPERIOD('Date',... とDATEINPERIOD(ALL('Date'),... は、同じです。


2) 計算列

計算列はDAX式が値を表示する行コンテキストに従いますが、関数内はコンテキストがありません。

(1) 直接参照される列(基本1)

行コンテキストに従います。

例 : = '表'[列]

(2) CALCULATE関数の第1引数の関数の中の範囲

"コンテキストが無い=全範囲"を第2引数以降の"フィルタ"で変更したコンテキストに従います。

例 : CALCULATE(SUMX('表'[列]), フィルタ...)

(3) その他の関数の引数(基本2)

"コンテキストが無い=全範囲"を示します。

例 : SUM('表'[列])、VALUES('表'[列])、SUMX('表',...)、FILTER('表',...)


5. その他

1) ALLの戻り値

(1) ALL('表')はテーブル<table>を返します。
(2) ALL('表'[列])は、[列]のみのテーブル<table>を返します。 1列の場合でも列<column>を返すのではありません。

例 : FILTER(ALL('表'[列1]), '表'[列1] = "値") はOKですが、FILTER(ALL('表'[列1]), '表'[列2] = "値") はエラーになります。理由は、ALL('表'[列1]) に '表'[列2]が無いからです。

(3) ALL('表'[列1])は、指定された列のフィルタを解除し、フィールド値の重複行を削除します。(重複行を削除するためにDISTINCT(ALL('表'[列]))と記述する必要はありません。重複行を削除しない場合は、REMOVEFLITERS('表'[列])を使用します。)
(4) ALL('表')は、テーブル全体のフィルタを解除します。全く同じ行があっても重複行は削除されません。

2) "1列のテーブル"は"テーブル"であり、"列"ではありません

テーブル<table>はテーブル型の構造を持ち、列<column>とは型が異なります。

例 : SUMX(<table>, <expression>)で、SUMX(ALL('表'[列]), ...) はOKですが、SUMX('表'[列], ...) はエラーになります。
例 : SUM(<column>)で、SUM('表'[列]) はOKですが、SUM(ALL('表'[列])) はエラーになります。

3) DAX式のエラー箇所の調べ方

[Power Pivot]-[管理]-[データビュー]または[データ]-[データモデル]-[データモデルの管理]-[データビュー]の数式バーで、式のエラー部分に赤波線の下線が付きます。下線が付かないケースもあります。

4) 日付テーブル、カレンダーデーブルの勧め

日付テーブルを作成する理由は色々ありますが、メジャーが予期しない値を返すことを軽減する効果があります。日付テーブルを作成すると、ALL()で日付テーブルのみフィルタを解除し、ファクトテーブルのフィルタコンテキストを変えることが無くなることと、日付テーブルは全ての日付を含むことから、意図しないフィルタやデータの抜けによる計算漏れが起きにくくなり、メジャーが意図通りに動きやすいです。

5) ピボットテーブルの行・列に配置する項目

ピボットテーブルの行・列に配置する項目は、基本的にはディメンジョンテーブルの項目にします。こうすると、メジャーで配置した項目のフィルタをした場合、ファクトテーブルや、他のディメンジョンテーブルに影響しないので、メジャーの式が簡単になります。
ファクトテーブルの項目を行・列に配置すると、行・列項目のフィルタ式がファクトテーブルの他の列にも影響するので、想定外の値が出てしまうことがあります。

 

6. サンプルファイル

ダウンロード - 250313all.xlsx


7. 参考(外部リンク)

DAX 用語集
https://learn.microsoft.com/ja-jp/dax/dax-glossary

DAX の構文
https://learn.microsoft.com/ja-jp/dax/dax-syntax-reference

Power BI Desktop における DAX の基本を学習する
https://learn.microsoft.com/ja-jp/power-bi/transform-model/desktop-quickstart-learn-dax-basics

CALCULATE
https://learn.microsoft.com/ja-jp/dax/calculate-function-dax


8. 関連記事

Excelのピボットテーブルでグループ内の順位を項目にしたクロス表を作る
https://bigarrow.tea-nifty.com/blog/2025/03/post-e0f809.html

Excel メジャーのRANKXで順位を求める方法(式の中に適用されるコンテキスト)
https://bigarrow.tea-nifty.com/blog/2025/02/post-1ee8c7.html

累計メジャーDAXの勘違い(歯抜け)
https://bigarrow.tea-nifty.com/blog/2025/01/post-08315e.html

|

« FanCtrlがWindows Defenderでブロックされた(2025/03/12) | トップページ | DIPSユーザーの操縦者情報の登録(2025年3月24日~) »

コメント

コメントを書く



(ウェブ上には掲載しません)




« FanCtrlがWindows Defenderでブロックされた(2025/03/12) | トップページ | DIPSユーザーの操縦者情報の登録(2025年3月24日~) »