この記事では、Power BIで多対多リレーションシップをする方法を解説します。
- 多対多リレーションシップとは何か？
- 多対多リレーションシップをするための操作方法
- 多対多リレーションシップの注意点
YouTube動画で見たいかたは、こちらからどうぞ！
多対多リレーションシップとは？
初めに、多対多リレーションシップとは何かを解説します。
下の2つのテーブルを見てください。
このとき２つのテーブルを、都道府県でリレーションシップしたいと思います。
Power BIでは、この多対多リレーションシップを実現する方法は２つあります。
-
多対多カーディナリティのリレーションシップを使う
- 中間テーブルで都道府県一覧を作成して、一対多のリレーションシップをする
それぞれの方法について、解説していきます。
多対多カーディナリティのリレーションシップを使う
１つ目は、多対多カーディナリティのリレーションシップを使って、直接「都道府県」列で多対多の紐付けをする方法です。
ただし、一般的には多対多カーディナリティのリレーションシップは非推奨で、一対多のリレーションシップをするようなデータモデルを設計する方が推奨されています。
多対多カーディナリティを使っても、レポートでのデータ表示が問題ない場合は使ってもよいと思います。
参考：Microsot Learn「Power BI Desktop で多対多リレーションシップを適用する」
中間テーブルを作成する
２つ目は、中間テーブルで「都道府県一覧」を作成し、２つのテーブルと一対多のリレーションシップをする方法です。
中間テーブルの「都道府県」列は、一意の値をもつ列になります。
そのため、都道府県一覧と都道府県別売上一覧、都道府県一覧と地域別人口の間で、一対多のリレーションシップをすることができます。
この方法は、多対多リレーションシップの時に一般的に使われる方法です。
参考：Microsot Learn「多対多のリレーションシップのガイダンス」
多対多リレーションシップの方法
今説明した多対多リレーションシップの２つの方法について、Power BIの画面での操作方法を解説していきます。
サンプルデータとして、地域別人口と、売上一覧の２つのシートを用意しました。
PowerQueryで中間テーブルを作成する
１．まずPower BI Desktopの、ホームタブ＞「Excelブック」から、サンプルのExcelデータを取り込みます。
２．地域別人口、売上一覧の2つのテーブルにチェックを入れ、「データの変換」をクリックします。
３．Power Queryエディターが開くので、ここで中間テーブルを作成します。
４．売上一覧のテーブルを選択して、ホームタブ＞「クエリの追加」＞「クエリを新規クエリとして追加」をクリックします。
５．２つ目のテーブルに「地域別人口」を選択して、「OK」をクリックします。
６．２つのテーブルが結合しました。
都道府県列以外は不要なので、都道府県列を選択後、「列の削除」＞「他の列の削除」をクリックして削除します。
７．都道府県列を選択後、ホームタブ＞「行の削除」＞「重複の削除」を選択します。
これで中間テーブルができました。
Power Queryエディターの中で中間テーブルを作成しておけば、２つのテーブルに新たな都道府県のデータが追加された時、中間テーブルの都道府県データも自動的に追加されます。
８．テーブル名を分かりやすく変えたら、ホームタブ＞「閉じて適用」をクリックします。
中間テーブルを使ったレポートの表示
１．モデルタブを開くと、中間テーブルを使ったリレーションシップができています。
「都道府県一覧」と「売上一覧」、「都道府県一覧」と「地域別人口」が、一対多でリレーションシップされています。
一旦レポートタブに戻り、レポートを作成してみます。
２．テーブルを選択し、以下の値を入れてみましょう
- 都道府県一覧：都道府県
- 売上一覧：売上
- 地域別人口：人口
３．ついでにクイックメジャーを使い、一人当たりの売上も出してみます。
売上一覧の「売上」を右クリック＞「新しいクイックメジャー」をクリックします。
４．以下の値を設定し「OK」をクリックします。
- 計算：除算
- numerator：売上の合計
- denominator：人口の合計
５．作成したクイックメジャーの名前を「1人当たりの売上」に変更し、テーブルに追加します。
中間テーブルを使った方法では、中間テーブルである「都道府県一覧」の「都道府県」列をデータに入れているので、全ての都道府県が表示されています。
この時、売上、人口のデータがないところは空白で表示されています。
売上÷人口(一人当たりの売上)のデータも、どちらかのデータが空白の場合は計算できないため、売上、人口のデータが揃っていない都道府県は空白で表示されます。
人口の合計は、都道府県に紐づく地域の人口の合計が表示されています。
地域別人口にあるデータの人口しか合計されないことに注意しておきましょう。
多対多カーディナリティを使ったレポートの表示
次に、多対多カーディナリティのリレーションシップをしてみます。
１．モデルタブに戻り、一旦今のリレーションシップを右クリックで削除します。
２．売上一覧の都道府県列を、地域別人口の都道府県列にドラッグ&ドロップします。
３．リレーションシップの作成で、カーディナリティは「多対多」、クロスフィルターの方向は「双方向」を選択して「OK」をクリックします。
クロスフィルターの双方向は、売上、地域別人口それぞれでデータを絞ったとき、お互いにフィルターを適用するという意味です。
多対多リレーションシップが作成されました！
４．レポートタブに戻り、テーブルでデータを見てみます。
「都道府県」列は、売上一覧か、地域別人口どちらかの列を入れる必要があるため、今回は「売上一覧」の「都道府県」列を入れてみます。
都道府県には、千葉県が表示されませんでした。
千葉県のデータは、売上一覧の都道府県にはなく、地域別人口の都道府県にしかないためです。
５．「地域別人口」の「都道府県」を代わりに入れると、今度は千葉県が表示され、神奈川県が表示されなくなりました。
千葉県は売上のデータがないため、空白で表示され、売上÷人口も神奈川県は空白で表示されます。
６．先ほどの中間テーブルを作成したテーブルと比べてみましょう。
データとしてはどちらも正しいデータですが、リレーションシップした列に、どちらかのテーブルにしか無い列データがあった場合、グラフのデータに入れた方の列の値のみ表示されます。
今回の場合、上記のような表を表示するならば、基本的には売上のレポートを見たいと思うので、多対多カーディナリティのリレーションシップで売上一覧にある都道府県のみ表示するのでも問題ないかもしれません。
一方で、両方のテーブルの都道府県列、全てのデータを表示したいという場合は、中間テーブルを作成して、中間テーブルの列をグラフで使う必要があります。
中間テーブルをよく使うパターンとして、日付列のマスターテーブルを作る場合があります。
売上がない日があったとしても、日付軸に日付を表示したい場合などです。
両方のテーブルの値が必要か、片方だけで良いか、状況によって異なると思うので、使い分けてもらえればと思います。
多対多リレーションシップの注意点
多対多リレーションシップをするときの注意点も留意しておきましょう。
試しに、テーブル列に「都道府県」列の代わりに、「市区町村」列を入れてみましょう。
売上の合計がおかしな値になっていますぞ…！
例えばさいたま市、所沢市、春日部市の売上合計は、埼玉県のネット＋店舗の売上合計が表示されているんだよ。
このように、多対多リレーションシップをした場合、紐づいていないデータを表示しようとすると、不正確な値が表示されます。
多対多リレーションシップを使う場合は、上記のことを理解して使いましょう。
※これは多対多カーディナリティのリレーションシップ、中間テーブルを使う場合両方で、同様の表示になります。
最後に
この記事では、Power BIで多対多リレーションシップをする方法を紹介しました。
多対多リレーションシップが必要となる場面は多くあります。
今回紹介した多対多カーディナリティのリレーションシップを使う方法と、中間テーブルを作成する方法は、目的に応じて使い分けてください。
基本的には多対多カーディナリティのリレーションシップは推奨されておらず、一対多のリレーションシップとなるようなデータモデル設計をする方がよい点も考慮しておきましょう。
