Power BI 1つのテーブルに複数の日付列がある場合のリレーションシップ


# やりたいこと

:::note info
複数の日付列があるデータから、異なる基準日で集計した数字を1つの表に並べたい
:::

* 例として、企業の採用に関するデータを使います
* 候補者が求人に応募した日、選考をパスして内定を提示した日、正式に入社する日の3つの日付があります
* このデータから完成形にあるような表を作ります

    <details><summary>データ</summary>
    
    | 氏名 | 性別 | 応募日 | 内定日 | 入社日 |
    | --- | --- | --- | --- | --- |
    | 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日 |  |  |
    
    </details>

    <details><summary>完成形</summary>
    
    | 年月     | 応募 | 内定 | 入社 |
    | -------- | ------ | ------ | ------ |
    | 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      |
    </details>


* 表の各列は応募日、内定日、入社日を基準日として人数を集計します（2023年3月なら以下）
    * 応募人数：応募日が2023/3月の人数 → 1人（Dさん）
    * 内定人数：内定日が2023/3月の人数 → 3人（A、B、Cさん）
    * 入社人数：入社日が2023/3月の人数 → 0人


# 日付列が複数ある場合のリレーションシップ

日付列が複数ある場合のリレーションシップの設定方法は2つあります。
参考：[スター スキーマと Power BI での重要性を理解する - 多様ディメンション](https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema#role-playing-dimensions)

1. 日付列ごとに日付ディメンションを作成する
1. 日付ディメンションを1つだけ作成し、`USERELATIONSHIP関数` で非アクティブなリレーションを使用する

:::note warn
前提：2つのテーブル間のリレーションシップは1つしかアクティブにできないため、方法2では非アクティブなリレーションができてしまいます
:::

# どちらの方法を使えばよいのか

* 結論、やりたいことを実現できるのは`USERELATIONSHIP関数` を使う方法2です
* ただ、公式マニュアルは日付列ごとに日付ディメンションを作成する方法1を推奨しています
* 両者の違いを整理するためにも、今回は両方試してみます

## 方法1: 日付列ごとに日付ディメンションを作成する

まずは、「日付列ごとに日付ディメンションを作成する方法」から試します。

### リレーションシップ

**応募日、内定日、入社日それぞれに日付ディメンションを作成**するのでリレーションは以下のようになります。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1104230/e9cd5dec-1ca5-baf3-ba30-8863d1ddf028.png">

### 完成形の表を作ってみる

候補者テーブルから`応募日`、`内定日`、`入社日`を使ってカウントの列を作ります。
あとは、日付ディメンションから`年月`フィールドを持ってくれば、完成形になりそうですが、日付ディメンションは3つあります。**どのテーブルの`年月`フィールドを持ってくればよいのでしょうか？**

試しに、Dim 応募日テーブルの`年月`を使ってみると以下のような集計表が得られました。
よく見ると、**応募数はどの行も想定通りですが、内定数と入社数は完成形の表と数字が違っています**。2023年3月であれば、`内定日`が2023/3月であるA、B、Cさんの3人になってほしいのに、1人になっています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1104230/bfe764d0-491b-740f-2b69-a9436a28738e.png)

### なぜ内定数と入社数はズレたのか

Dim 応募日テーブルの`年月`を使ったことが原因です。
Dim 応募日テーブルは候補者テーブルの`応募日`とリレーションが設定されているので、**応募日を基準に人数を集計**しています。つまり、内定列には「`応募日`が2023年3月であるレコードの`内定日`の件数をカウントした結果」が入っています。2023年3月の内定1件は`応募日`2023年3月であるDさんが該当します。

:::note alert
「方法1: 日付列ごとに日付ディメンションを作成する」では、1つの表に異なる基準日で集計した数字を並べることはできない
:::

## 方法2: USERELATIONSHIP関数を使用する

では、次に「日付ディメンションを1つだけ作成し、`USERELATIONSHIP関数` で非アクティブなリレーションを使用する方法」を試します。

### リレーションシップ

日付ディメンションは1つだけ作成します。
**`応募日`とのリレーションだけアクティブ、`内定日`と`入社日`のリレーションは非アクティブ**です。

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1104230/ec639616-841f-dd7d-69f7-3b902a1e618e.png">

### 内定数と入社数をカウントするメジャーを作成する

公式マニュアルに記載がある通り、この方法では`内定日`と`入社日`のリレーションを使って集計するためにはメジャーを作成する必要があります。

> 非アクティブなリレーションシップを使用する唯一の方法は、USERELATIONSHIP 関数を使用する DAX 式を定義することです。 

早速作ってみます。応募数は自動メジャーを使えばよいので割愛します。

```DAX:内定数のDAX
内定数 = 
CALCULATE(
    COUNT('候補者'[内定日]),
    USERELATIONSHIP('候補者'[内定日], 'Dim 日付'[Date])
)
```

```DAX:入社数のDAX
入社数 = 
CALCULATE(
    COUNT('候補者'[入社日]),
    USERELATIONSHIP('候補者'[入社日], 'Dim 日付'[Date])
)
```

### 完成形の表を作ってみる

Dim 日付の`年月`フィールド、候補者テーブル`応募日` のカウント、`内定数メジャー`、`入社数メジャー`を並べて表を作ってみると以下のような集計表が得られました。

今回は想定通りの結果が得られています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1104230/e64a6280-d47f-83cb-cb4b-13de2c6d7daa.png)


### なぜ内定数と入社数はズレないのか

使用する日付ディメンションは1つだけで、内定数や入社数を集計する際は、**基準日を切り替える**ように`USERELATIONSHIP` 関数を使っているからですね。

:::note info
「方法2: USERELATIONSHIP関数を使用する」では、1つの表に異なる基準日で集計した数字を並べることができる
:::

## 結論

複数の日付列があるデータから、異なる基準日で集計した数字を1つの表に並べたい場合は、`USERELATIONSHIP` 関数を使いましょう

ただし、`USERELATIONSHIP` 関数を使う方法では以下のようなデメリットもあります。
用途に合わせて使い分けが必要ということですかね。

* 非アクティブなリレーションシップの集計用にDAXを実装する点は手間
* `USERELATIONSHIP` 関数を使用する方法では、『応募日が2023年1月、かつ入社日が2023年5月』のように異なる日付で同時にフィルター処理することができない

## 参考

* [スター スキーマと Power BI での重要性を理解する - Power BI | Microsoft Learn](https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema#role-playing-dimensions)
* [PowerBI リレーションシップを作成したい列が複数ある場合の対応 #PowerBI - Qiita](https://qiita.com/Rambosan/items/80c395c07339c6ad687b#%E7%B5%90%E6%9E%9C)