覚え書きです。
結論
- 日付の列が複数あるテーブルで、ビジュアルによって結合する列を切り替えたい場合、USERELATIONSHIPを使用する。
- まずは非アクティブなリレーションシップを作成しておく。
- 次にCALCULATE関数とUSERELATIONSHIP関数を使用してメジャーを作成する。
- メジャー = CALCULATE(SUM([売上]),USERELATIONSHIP('t_出荷予定'[検品日],'カレンダー'[Date]))
- そのメジャーを使用したビジュアルでは、USERELATIONSHIPで設定したリレーションシップが適用され計算される。
- 最後にこの方法のデメリットと公式で推奨される対応を追記しました。
解説
日付を扱うレポートでは、通常カレンダーテーブルを作成し[Date]列とデータテーブルの日付情報列の間でリレーションシップを作成します。
すると、カレンダーテーブルのデータを軸にして、年月ごと、曜日ごと、週ごとなど様々な軸でグラフ(ビジュアル)を作成したり、スライシングすることができます。
ただし、データテーブル内に日付情報の列が複数ある場合は、どちらか一つの列でしかレーションシップを作成できません。
複数作成しようとしても、非アクティブなリレーションシップとして点線で表示されます。
[図1]
上記の写真では「出荷予定日」と関連付けていますが、「検品日ベースの売上」といったビジュアルを作成したい場合はどうすればいいのか。
| 出荷予定日 | 検品日 | 数量 | 単価 | 売上 |
|---|---|---|---|---|
| 2020/6/24 | 2020/6/22 | 4 | ¥395 | ¥1,580 |
| 2020/6/24 | 2020/6/22 | 17 | ¥468 | ¥7,956 |
| 2020/6/24 | 2020/6/22 | 12 | ¥2,577 | ¥30,924 |
| 2020/6/24 | 2020/6/22 | 18 | ¥2,426 | ¥43,668 |
USERELATIONSHIP関数を使用すると解決することができるようです。
非アクティブなリレーションシップの作成
予め、[Date]と[検品日]でもリレーションシップを作成します。
[図1]のように非アクティブなリレーションシップになりますが問題ありません。
メジャーの作成
以下のようにCALCULATE関数とUSERELATIONSHIP関数を使用してメジャーを作成します。
※USERELATIONSHIP関数はCALCULATE関数内でしか使用できません。
売上_検品日 = CALCULATE(SUM([売上]),USERELATIONSHIP('t_出荷予定'[検品日],'カレンダー'[Date]))
結果
左：[Date]と[出荷日]で結合した結果(アクティブなリレーションシップ)
右：[Date]と[検品日]で結合した結果(USERELATIONSHIPによる結合)
'21/2追記
いつの間にか公式マニュアルが充実していました。
メジャーの数が多くなる場合は、日付列ごとにカレンダーテーブルを作成するのが良さそうです。
https://docs.microsoft.com/ja-jp/power-bi/guidance/star-schema#role-playing-dimensions
USERELATIONSHIPによる方法は以下のようなデメリットがあります
- 合計や平均といった自動メジャーが使用できない。
- 特にドリルスルー画面で勘違いしやすいです。
- ドリルスルー画面で作成した自動メジャーを使用した日付ごとの合計、カウントといったグラフは全てアクティブなリレーションシップが適用されています。
- よって、ドリルスルー画面では全てUSERELATIONSHIPでメジャーを作成する必要があります。
- 使用するメジャー2～3個程度ならUSERELATIONSHIPで問題ないと思います。