2種類のテーブルをカレンダーテーブルでつなぐ
時系列データをBIツールで扱う場合、カレンダーテーブルを別に作り、計測データが入っているテーブルと紐付ける（リレーションを設定する）ことがあります。
例えば、以下のデータでは、新型コロナウイルスの日毎の陽性者数テーブルと死亡者数テーブルをカレンダーテーブルに紐づけています。
以下、簡易のカレンダーテーブルの作り方です。
使っているデータは「Our World in Data」の「Coronavirus Pandemic (COVID-19)」から取得しました。
「Power BI Desktop」を使い加工しています。
DAXを使い新しいテーブルを作成
左側の「データ」アイコン（表の形のアイコン）をクリックし、「新しいテーブル」を選びます。
以下のDAX式を記述します。
取得したテーブルの最小値（MIN関数を使用）と最大値（MAX）を指定することで、カレンダーの期間を設定しています。
calendarテーブル = CALENDAR(MIN('owid-covid-data_case'[date]), MAX('owid-covid-data_case'[date]))
次のようなテーブルが作成できます。
リレーションシップを設定しカレンダーを共有
後は、左側のパネルから「モデル」アイコンをクリックし、リレーションの設定をすれば、最初の画面の関係付けを付与したモデルが作成できます。
リレーションシップはテーブル間をドラッグ&ドロップすることで設定もできますし、画面上の「リレーションシップの管理」を使ってもできます。
新型コロナウイルスに関する２つのテーブルがカレンダーテーブルを間にはさみ紐づいた形になります。
カレンダーテーブルをx軸、左のy軸に陽性者数テーブル、右のy軸に死亡者数テーブルと設定することで、作成したグラフです。
DAXを使い「MM月」や「曜日」列などを追加
カレンダーテーブルにもう少し情報を追加します。
右側のアイコン「データ」をクリックして「テーブルツール」→「新しい列」を選んで「年」や「月」、「曜日」列などを追加します。
# 「年」列を追加する式
YYYY = YEAR('calendarテーブル'[Date])# 「月」列を追加する式。一桁の数字を返す
MONTH = MONTH('calendarテーブル'[Date])# 「月」列を追加する式。MMの形式で返す
MONTH_MM = FORMAT('calendarテーブル'[Date], "MM")# M月のフォーマットで返す。日付型の「月」を文字型に変更し、"月"を結合
MONTH_月 = CONCATENATE(CONVERT('calendarテーブル'[MONTH], STRING), "月")# 「曜日」列を追加する式。一桁の数字を返す
WEEKDAY = WEEKDAY('calendarテーブル'[Date])# FORMAT関数で"aaa"と指定すると日本語一文字を返す
WEEKDAY_曜日 = FORMAT('calendarテーブル'[Date], "aaa")上記の式を使って追加した列です。
FORMAT関数をもう少し使う
上記で曜日表記のために使用した「FORMAT」関数。
調べてみると、「MM月」の記述スタイルにも対応できるなど便利なことがわかったのでもう少し使ってみました。
# 「YYYY年」表記
YYYY年 = FORMAT('calendarテーブル'[Date], "yyyy年")# 月を「MM」と2桁の数字
MONTH_月2 = FORMAT('calendarテーブル'[Date], "mm")# 「YYYY年MM月」表記
YYYY年MM月 = FORMAT('calendarテーブル'[Date], "yyyy年mm月")上記関数を追加したカレンダーテーブルです。
参考サイト
