日付テーブルについてお話ししようと思うのだ。
ふわっとまとめ
- Auto date/time は OFF にしておいたほうがよいぜ
- 日付テーブルには ALL('日付テーブル') っていう、CALCULATE modifier が適用されているのだぜ
自動で設定してくれる機能や実現するためのローコードが存在するとき、それらを勉強しなくてもよいということではない。以降において、それらを理解していることが望ましく、少なくとも知っていることが前提になっているということだ。
知っておくべきこと
Auto date/time
自動で 日付テーブルを生成したり更新する機能があるけれども、必要な機能ですか？という自問自答をしてみてはどうだろうか。
おすすめ設定 Power BI Desktop
デフォルトの設定では、日付テーブルを生成する機能が ON である。ただ、想像するより便利な機能ではないから Global では "OFF" にしておくとよい。
[Options] - [GLOBAL] - [Data Load] - [Time intelligence] - [Aute date/time for new file] : OFF
で、必要な時だけ ON にしておくほうがスマートだ。
[Options] - [CURRENT FILE] - [Data Load] - [Time intelligence] - [Aute date/time]
なぜか
そもそも使いたいなと思うことがないのだけど、合理的と思える理由を挙げる。
- 生成された日付テーブルに列の追加や、フォーマットを変更することができない。
- データモデルに含まれる多くの日付列ごとに、日付テーブルが作成されることがある。
- Analyze in Excel では表示されない日付テーブルになり、期待する結果を得られないことがある。
- 勉強を邪魔する。うざい(心の叫び)
それでも使いたいのなら、この機能が ON であるときどのようなことが起きているのか。どのような結果を得ることができるのかをよーく理解しておくべきだ。この機能を理解していて利用に値する機会であるならば ON にすべきだ。ただ、この先深く勉強しなければならないとき、こんなことがきっかけでつまずくのはもったいなさすぎるのだ。
日付テーブルとしてマークする
タイム インテリジェンス 関数 を使う集計を行うとき、日付テーブルは必須である。このとき、タイム インテリジェンス 関数が十分に機能するかどうか、適切なテーブルとデータになっているかの検査も実施してくれる機能だ。なので、必ず利用する。では、どのような機能なのか。
実施される検証
タイム インテリジェンス 関数で指定する引数は 日付(日付時刻)列 なので、要件を満たす日付列になっているか検証が行われる。
- 日付（日付時刻)列の存在
- 日付列値の重複もしくはギャップ
- 日付列に時刻情報がないこと( AM 12:00:00 のみ許容 )
追加される機能
タイム インテリジェンス 関数による集計が期待通りに行われることがほぼ保障されること。"ほぼ"というのは、日付テーブルのデータに問題があるときは別であるから。
上記重要なこと以外だと、クイック メジャーによるタイム インテリジェンス 集計がちょっと使えるようになるかもね。
行われる内部の動作
テーブルと列のプロパティ変更が行われる。Power BI Desktop の作業では、ここまで意識する必要はないけれども。
- テーブルのプロパティ : Data Category = 2(Time)
- 日付列のプロパティ : Key = True
データセット更新するとき不運にも日付列に重複が発生したとき、 列の Key プロパティにより更新が失敗に終わるのだ。
日付テーブルの特有の動作
いろんな方のモデルや記述されたメジャーを見ていると、日付テーブル特有の動作を理解していないのでは？と思うことが多いのだ。
日付テーブルはスタースキーマでいうところのディメンジョンテーブルの一種なわけだが、定義されるデータは日付であり、どのデータモデルでも同じ使われ方なのである。なので、日付テーブルに関しては特有の動作をしていて便利なはずの機能なのだ。
特有の動作は、
- 日付テーブルとしてマークされている
- 日付列 ( 1 : 多 ) 日付列 のリレーションシップが設定されている
のいづれかで発動する。
特有の動作とは
日付テーブル : Dates としたとき、常に ALL( Dates ) もしくは REMOVEFILTERS( Dates ) が暗黙的に適用されるのである。
Sales amount =
SUMX( Sales, Sales[Unit price] * Sales[Quantity] )
Sales amount (Cumulative) =
VAR MaxDate = MAX( Dates[Date] )
VAR Result =
CALCULATE(
[Sales Amount],
Dates[Date] <= MaxDate
)
RETURN
Result
このとき、
EVALUATE
SUMMARIZECOLUMNS(
Dates[Year], Dates[Month],
"Sales amount (Cumulative)", [Sales amount (Cumulative)]
)
ORDER BY Dates[Year], Dates[Month]
が期待する結果で集計されるのだ。
Sales amount (Cumulative) =
VAR MaxDate = MAX( Dates[Date] )
VAR Result =
CALCULATE(
[Sales Amount],
Dates[Date] <= MaxDate,
// ALL( Dates ) REMOVEFILTERS( Dates ) 👈暗黙的に適用されている
)
RETURN
Result
CALCULATE の filter 引数 は、依然としてベストプラクティス通り 列フィルタで記述できるのだ。
filter 引数に利用する ALL*関数は、 CALCULATE modifier ってことにも理解が必要。
思ったこと🙄
記述されたメジャーを見ると、あぁ勉強足りなくね？とか、間違った勉強しちゃってんじゃね？と思うのである。