メジャーを定義しその結果をビジュアルで使用するとき、集計自体は期待する結果をするもののその集計結果が望ましくない状態になることがある。たとえば、
- 月単位で集計した結果均なのに 日単位の列でも集計されてしまう。
- 実績のない日付でも集計されてしまう。
列見出し/行見出し や スライサーから影響を受けるとき、集計に使用される フィルタ(フィルタ コンテキスト) がどのようなものなのか。これに合わせて結果をハンドリングする定義が必要。
ビジュアルの設定で調整できるものもあるけれども、ビジュアルを追加変更することはままあるので、できるだけ モデリング の中で収めておく方針。
メジャーに "条件" を定義する
メジャーには 評価に必要な条件を盛り込めばよい。
実績のない列を集計しない
日付テーブルは連続した"日付"を持ち、この日付列を使用した集計が行われる。なので、実績のない日付であっても集計が行われることがある。
(左)そのまま (右) ひと手間かけた
受注額 = SUMX ( '受注', [数量] * RELATED ( '商品'[単価] ) )
受注額累計 =
IF (
NOT ISBLANK ( [受注額] ), // <--- ここポイント
CALCULATE (
[受注額],
FILTER (
ALLSELECTED ( 'カレンダー'[Date] ),
ISONORAFTER ( 'カレンダー'[Date], MAX ( 'カレンダー'[Date] ), DESC )
)
)
)
日付テーブルの日付列ごとでメジャー[受注額]を評価するのだけど、その日付に関連する列が存在しない場合 [受注額] は BLANK と評価 される。これを IF Function (DAX) で使用している。
この例では メジャー[受注額]とその累計メジャー[受注額累計]いずれも単純な加法なので、こんな感じで要件を満たせるかと。
表示に使用するメジャー(集計結果)すべてが BLANK になるとき、例えばマトリクスの場合であれば、該当する行の集計結果すべてが BLANK になるとき、その行は 非表示 になる。こればビジュアルの既定の動作。非表示になる列を表示するには、オプション： "データにない項目を表示する" を使用するとよいかと。Excel ピボット テーブルにも同じような機能はある。
列見出し / 行見出しなどに合わせる
各フィールドの値が集計されるに至ったフィルター(フィルタ コンテキスト)から集計をハンドリングする。
フィルタ コンテキストって、なにさ
| 年\区分 | A | B | 合計 |
|---|---|---|---|
| 2017 | [年] = 2017 && [区分] = "A" | [年] = 2017 && [区分] = "B" | [年] = 2017 |
| 2018 | [年] = 2018 && [区分] = "A" | [年] = 2018 && [区分] = "B" | [年] = 2018 |
| 2019 | [年] = 2019 && [区分] = "A" | [年] = 2019 && [区分] = "B" | [年] = 2019 |
| 合計 | [区分] = "A" | [区分] = "B" | (すべて) |
列見出し / 行見出し以外すべての値の集計にはフィルタが自動的に適用されていて、2017年 B区分 のフィールドの フィルタ コンテキストは、[年] = 2017 && [区分] = "B"
ひとまずはうまくいく
[年]列 / [月]列 の組合せ もしくは [年月]列 ことで集計を定義しているし、見出しには集計に使用する列が含まれているから。(必要十分である)
受注額移動平均(6M) =
IF (
NOT ISBLANK ( [受注額] ),
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( 'カレンダー', 'カレンダー'[年], 'カレンダー'[月], 'カレンダー'[年月] ),
DATESINPERIOD ( 'カレンダー'[Date], LASTDATE ( 'カレンダー'[Date] ), -6, MONTH )
),
[受注額]
)
)
期待しないビジュアルになることがある
ドリリングや見出しレベルの操作などで [日]列が集計に影響したり、集計に必要な列が不足したりすれば、定義された計算は期待通りにならない。
では、どうするか - IS と HASONE
集計に適さない列が追加された もしくは 集計に必要な列が不足するとき、集計結果を BLANK にしてしまえばよい。集計の定義を鑑みると集計された値は意味を持たないことが多いし。
フィルタが適用されているかどうか
ISFILTERED Function (DAX)
引数は 列名 で、対象の列に対し 直接フィルタ が適用されているかどうかがわかる。
ISCROSSFILTERED Function (DAX)
同じ結果を得ることができるが、リレーションシップによるフィルタ(クロス フィルター)も含まれるという違いがある。
同じビジュアルだけど階層レベルが "年 および 月" のときだけ集計され表示される。
受注額移動平均(6M) ISFILTERED =
IF (
NOT ISBLANK ( [受注額] )
&& ( NOT ( ISFILTERED ( 'カレンダー'[日] ) || ISFILTERED ( 'カレンダー'[Date] ) ) )
&& (
( ISFILTERED ( 'カレンダー'[年] ) && ISFILTERED ( 'カレンダー'[月] ) )
|| ISFILTERED ( 'カレンダー'[年月] )
),
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( 'カレンダー', 'カレンダー'[年], 'カレンダー'[月], 'カレンダー'[年月] ),
DATESINPERIOD ( 'カレンダー'[Date], LASTDATE ( 'カレンダー'[Date] ), -6, MONTH )
),
[受注額]
)
)
NOT ( ISFILTERED ( 'カレンダー'[日] ) || ISFILTERED ( 'カレンダー'[Date] ) )
[日]列 もしくは [Date]列 にフィルタが適用されてない
( ISFILTERED ( 'カレンダー'[年] ) && ISFILTERED ( 'カレンダー'[月] ) ) || ISFILTERED ( 'カレンダー'[年月] )
[年]列 と [月]列 の組合せ もしくは [年月]列 にフィルタが適用されている
これで要件満たすならそれで構わないのだけど、ビジュアルに影響するスライサー を使うときにはもう少し工夫が必要。
評価された値がひとつかどうか
HASONEVALUE はよく使うので憶えておくのは必須。
HASONEVALUE Function (DAX)
直接フィルタもしくはクロスフィルタ適用により 引数で指定する 値がひとつ になっているかどうか。
COUNTROWS ( VALUES ( 'カレンダー'[日] ) ) = 1 と同じ評価
HASONEFILTER Function (DAX)
直接フィルタが適用された結果、値がひとつになっているかどうか。
ここでは使っていないけど、
受注額移動平均(6M) HASONEVALUE =
IF (
NOT ISBLANK ( [受注額] )
&& NOT ( HASONEVALUE ( 'カレンダー'[日] ) || HASONEVALUE ( 'カレンダー'[Date] ) )
&& (
ISFILTERED ( 'カレンダー'[年月] )
|| ( ISFILTERED ( 'カレンダー'[年] ) && ISFILTERED ( 'カレンダー'[月] ) )
),
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( 'カレンダー', 'カレンダー'[年], 'カレンダー'[月], 'カレンダー'[年月] ),
DATESINPERIOD ( 'カレンダー'[Date], LASTDATE ( 'カレンダー'[Date] ), -6, MONTH )
),
[受注額]
)
)
スライサーで列にフィルタを適用したとき、メジャーで ISFILTERED を使用している式が TRUE と評価されてしまうことがある。こういう場合は HASONEVALUE の方が都合がよかったりする。
おまけ
工夫すると、シンプルに記述できたり、異なる集計を切り替えたりすることはできますね。
受注額移動平均 =
VAR AVG5d =
AVERAGEX (
VAR Dates =
TOPN (
5,
FILTER ( ALL ( '受注'[受注日] ), '受注'[受注日] <= LASTDATE ( 'カレンダー'[Date] ) ),
'受注'[受注日], DESC
)
RETURN
DATESBETWEEN ( 'カレンダー'[Date], FIRSTDATE ( Dates ), LASTDATE ( Dates ) ),
[受注額]
)
VAR AVG6m =
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( 'カレンダー', 'カレンダー'[年], 'カレンダー'[月], 'カレンダー'[年月] ),
DATESINPERIOD ( 'カレンダー'[Date], LASTDATE ( 'カレンダー'[Date] ), -6, MONTH )
),
[受注額]
)
RETURN
SWITCH (
TRUE (),
ISBLANK ( [受注額] ), BLANK (),
ISFILTERED ( 'カレンダー'[年] ) && ISFILTERED ( 'カレンダー'[月] )
&& ISFILTERED ( 'カレンダー'[日] ), AVG5d,
ISFILTERED ( 'カレンダー'[年月] ) && ISFILTERED ( 'カレンダー'[日] ), AVG5d,
ISFILTERED ( 'カレンダー'[年] ) && ISFILTERED ( 'カレンダー'[月] ), AVG6m,
ISFILTERED ( 'カレンダー'[年月] ), AVG6m,
BLANK ()
)