データモデル を構成するとき、ディメンジョンテーブル / ファクトテーブル間のリレーションシップを用いた関連付けが困難なときはよくあること。そんなときどうするか？実績などの結果が想定していた予算や目標と比較してどのようになったかを集計できるようにしてみる。データ粒度がデータモデル内のディメンジョンテーブルとマッチさせることができないときどうするか？です。説明に使用している例ではこの構成でなくても対処可能だけど、特定の環境下(たまたまそういうデータ)のこと。
※デモファイルのリンクは下の方に置いてあるのでご自由にどうぞ。
手段としてはリレーションシップを使用する/しないのいすれかで、ファクトテーブルに対しどのようにフィルターを伝搬し集計するか。例として、"実績"と"予算"を取り上げると、
実績
日ごとの実績が集計されている。そして、日付テーブルに関連付けされている。
予算
月ごと、年ごとの組合せなど。これは日付テーブルに関連付けできない。
実績の集計に 日付テーブル を用いていることがほとんどなので、予算に関するテーブルも 日付テーブルに関連付けて集計したいわけです。
年月ごとの予算額を持っているが、日付テーブル：カレンダーのキー列は [日付] なので、テーブル''予算' を関連付けができない。
リレーションシップを使用する
まずはおススメの方法。
多対多(Many-to-Many)のリレーションシップを実現するときに利用できる 双方向フィルタと中間テーブルを用いる。結論から述べるとこんな感じ。
ほとんどのリレーションシップは 1対多(One-to-Many) で関連付けを定義することができて、1側で発生するフィルターを矢印の方向で多側に伝搬させ集計とするのだけど、Power BI のデータモデルでは双方向にフィルターを伝搬させることができる。
リレーションシップに利用する列を追加する
テーブル間でリレーションシップに使用できる列はそれぞれひとつづつなので、関連付けしたい日付テーブルと共通の値をもつ列を "予算 テーブル" に追加する。ここでは "年月" という列で特定の年月を 集計 できるようにしておく(月ごとの行が1行とは限らないから)。日付 テーブルには "年月" (yyyy年mm月)という列はすでに用意していて、これに合わせている。日付 テーブルに [年月]列を用意しておくことは他の場面でも役立つことなのでおススメ。
中間のテーブル
日付 テーブルと予算 テーブル でリレーションシップに利用できる列が用意できたので、フィルターを伝搬させる中間のテーブルを用意する。
このテーブルでは、おおもとのディメンション テーブル である 日付テーブルの[年月]列を用い、[モデリング] → [新しいテーブル]で。VALUES Function (DAX) | DAX Function Reference - Filter Functions に 列を指定することで、年月を表す値が一意になるテーブルになる。
リレーションシップを設定
中間のテーブルは "1" にして 予算テーブルと日付テーブルは それぞれ "多"。クロス フィルター の方向に違いがある。
メジャーを作成
これらリレーションシップで日付テーブルから予算テーブルまでの関連付けが完了しているのであとは難しいことはなく、いつも通りに集計できるようにするだけ。たとえば、
予算額 = SUM( '予算'[金額])
予算額 年累計 = TOTALYTD( [予算額], 'カレンダー'[日付] )
予算額 累計 =
CALCULATE(
[予算額],
FILTER(
ALL( 'カレンダー'[日付] ), [日付] <= MAX( 'カレンダー'[日付] )
)
)
予算 達成率 =
DIVIDE(
[受注額 年累計],
CALCULATE(
[予算額],
FILTER( ALL( 'カレンダー' ), [年] = MAX( 'カレンダー'[年] ) )
)
)
で、こんな感じに。
リレーションシップを使用しない
TREATAS 関数
TREATAS Function | DAX Function Reference - Other Functions
リレーションシップで関連付けされていなくてもフィルターを適用させることができる特殊な関数
TREATAS(
table_expression, --- "1"側に相当するフィルターテーブル。一意の行をもつテーブル
<column>[, column].... --- "多"に相当するテーブルの関連付けに使用する列
)
ポイントは、
- 第1引数は 一意の行をもつテーブル
- 第2引数以降は列を指定し第1引数の列の順序で突合せ
メジャーを作成
予算額 =
CALCULATE(
SUM( '予算'[金額] ),
TREATAS(
SUMMARIZE( 'カレンダー', 'カレンダー'[年], 'カレンダー'[月] ), '予算'[年], '予算'[月] )
)
)
'カレンダー'[年] と '予算'[年]、 'カレンダー'[月] と'予算'[月] の組合せでフィルタが適用されるので、日付テーブルからのフィルターが適用されて 予算額が集計される算段。なお、他のメジャーはリレーションシップで関連付けされたものと同じなので記述はしない。
リレーションシップを使用した場合と何が違うのか
パフォーマンスは落ちる
ごく自然な話であるけれども、リレーションシップを使用することで集計の最適化なされているということでしょうね。とはいえ気になるほど遅くなるということはないかもしれない。こればっかりはデータ次第なので。ただ、リレーションシップの使用を優先的に検討されるべきかと
柔軟な関連付けができる
複雑なデータモデルや難解なメジャーを作成しメンテナンス続けるよりは十分な価値があると思う。これまでも同様なニーズはあったわけで、実現のために FILTER 関数とか INTERSECT 関数を駆使する必要があったのだけど、最適化された集計ができるように実装されたということでしょう。