やりたいこと
複数の日付列があるデータから、異なる基準日で集計した数字を1つの表に並べたい
-
例として、企業の採用に関するデータを使います
-
候補者が求人に応募した日、選考をパスして内定を提示した日、正式に入社する日の3つの日付があります
-
このデータから完成形にあるような表を作ります
データ
氏名 性別 応募日 内定日 入社日 Aさん 男性 2023年1月17日 2023年3月7日 2023年5月1日 Bさん 男性 2023年1月24日 2023年3月1日 2023年4月3日 Cさん 男性 2023年2月14日 2023年3月9日 Dさん 女性 2023年3月14日 2023年6月8日 2024年4月1日 Eさん 男性 2023年4月18日 2023年5月9日 2023年6月1日 Fさん 男性 2023年6月9日 2023年8月17日 2024年4月1日 Gさん 女性 2023年7月7日 2023年8月8日 2023年9月1日 Hさん 男性 2023年7月9日 2023年8月2日 2023年10月2日 Iさん 男性 2023年7月21日 2023年8月8日 Jさん 男性 2023年9月1日 2023年9月22日 2023年10月16日 Kさん 男性 2023年9月8日 完成形
年月 応募 内定 入社 2023年1月 2 0 0 2023年2月 1 0 0 2023年3月 1 3 0 2023年4月 1 0 1 2023年5月 0 1 1 2023年6月 1 1 1 2023年7月 3 0 0 2023年8月 0 4 0 2023年9月 2 1 1 2023年10月 0 0 2 2024年4月 0 0 2 -
表の各列は応募日、内定日、入社日を基準日として人数を集計します（2023年3月なら以下）
- 応募人数：応募日が2023/3月の人数 → 1人（Dさん）
- 内定人数：内定日が2023/3月の人数 → 3人（A、B、Cさん）
- 入社人数：入社日が2023/3月の人数 → 0人
日付列が複数ある場合のリレーションシップ
日付列が複数ある場合のリレーションシップの設定方法は2つあります。
参考：スター スキーマと Power BI での重要性を理解する - 多様ディメンション
- 日付列ごとに日付ディメンションを作成する
- 日付ディメンションを1つだけ作成し、
USERELATIONSHIP関数で非アクティブなリレーションを使用する
前提：2つのテーブル間のリレーションシップは1つしかアクティブにできないため、方法2では非アクティブなリレーションができてしまいます
どちらの方法を使えばよいのか
- 結論、やりたいことを実現できるのは
USERELATIONSHIP関数を使う方法2です - ただ、公式マニュアルは日付列ごとに日付ディメンションを作成する方法1を推奨しています
- 両者の違いを整理するためにも、今回は両方試してみます
方法1: 日付列ごとに日付ディメンションを作成する
まずは、「日付列ごとに日付ディメンションを作成する方法」から試します。
リレーションシップ
応募日、内定日、入社日それぞれに日付ディメンションを作成するのでリレーションは以下のようになります。
完成形の表を作ってみる
候補者テーブルから応募日、内定日、入社日を使ってカウントの列を作ります。
あとは、日付ディメンションから年月フィールドを持ってくれば、完成形になりそうですが、日付ディメンションは3つあります。どのテーブルの年月フィールドを持ってくればよいのでしょうか？
試しに、Dim 応募日テーブルの年月を使ってみると以下のような集計表が得られました。
よく見ると、応募数はどの行も想定通りですが、内定数と入社数は完成形の表と数字が違っています。2023年3月であれば、内定日が2023/3月であるA、B、Cさんの3人になってほしいのに、1人になっています。
なぜ内定数と入社数はズレたのか
Dim 応募日テーブルの年月を使ったことが原因です。
Dim 応募日テーブルは候補者テーブルの応募日とリレーションが設定されているので、応募日を基準に人数を集計しています。つまり、内定列には「応募日が2023年3月であるレコードの内定日の件数をカウントした結果」が入っています。2023年3月の内定1件は応募日2023年3月であるDさんが該当します。
「方法1: 日付列ごとに日付ディメンションを作成する」では、1つの表に異なる基準日で集計した数字を並べることはできない
方法2: USERELATIONSHIP関数を使用する
では、次に「日付ディメンションを1つだけ作成し、USERELATIONSHIP関数 で非アクティブなリレーションを使用する方法」を試します。
リレーションシップ
日付ディメンションは1つだけ作成します。
応募日とのリレーションだけアクティブ、内定日と入社日のリレーションは非アクティブです。
内定数と入社数をカウントするメジャーを作成する
公式マニュアルに記載がある通り、この方法では内定日と入社日のリレーションを使って集計するためにはメジャーを作成する必要があります。
非アクティブなリレーションシップを使用する唯一の方法は、USERELATIONSHIP 関数を使用する DAX 式を定義することです。
早速作ってみます。応募数は自動メジャーを使えばよいので割愛します。
内定数 =
CALCULATE(
COUNT('候補者'[内定日]),
USERELATIONSHIP('候補者'[内定日], 'Dim 日付'[Date])
)
入社数 =
CALCULATE(
COUNT('候補者'[入社日]),
USERELATIONSHIP('候補者'[入社日], 'Dim 日付'[Date])
)
完成形の表を作ってみる
Dim 日付の年月フィールド、候補者テーブル応募日 のカウント、内定数メジャー、入社数メジャーを並べて表を作ってみると以下のような集計表が得られました。
今回は想定通りの結果が得られています。
なぜ内定数と入社数はズレないのか
使用する日付ディメンションは1つだけで、内定数や入社数を集計する際は、基準日を切り替えるようにUSERELATIONSHIP 関数を使っているからですね。
「方法2: USERELATIONSHIP関数を使用する」では、1つの表に異なる基準日で集計した数字を並べることができる
結論
複数の日付列があるデータから、異なる基準日で集計した数字を1つの表に並べたい場合は、USERELATIONSHIP 関数を使いましょう
ただし、USERELATIONSHIP 関数を使う方法では以下のようなデメリットもあります。
用途に合わせて使い分けが必要ということですかね。
- 非アクティブなリレーションシップの集計用にDAXを実装する点は手間
-
USERELATIONSHIP関数を使用する方法では、『応募日が2023年1月、かつ入社日が2023年5月』のように異なる日付で同時にフィルター処理することができない