テクテク日記

テクテク＝テクノロジー&一歩ずつ（テクテク）

Power BIの日付テーブル③: 事例解説

Power BI DAX / Data Model

前回は日付スライサーで不要な期間を除外する便利なやり方を紹介しましたが、今回は、具体的な事例をもとに、陥りやすい落とし穴を含めて見ていきたいと思います。結論から言うと、タイムインテリジェンス関数で使用される日付テーブルの日付型列が主キー（Primary Key: 1対多のリレーションシップに際して1の側）列として認識されているかどうかで、結果が大きく変わります。実際の問題はこれよりも少し複雑で、以下にその詳細を解説していきます。

日付テーブルのリレーションシップ

リレーションシップはPower BIを活用するうえで非常に重要な概念で、1対多というリレーションシップに基づくスタースキーマを作ることがベストプラクティスとなっています。今回の事例を実際に見る前に、まずは日付テーブルと実績テーブル（以降、Factテーブル）のリレーションシップについて把握しておく必要があります。

  1. 日付型同士（Date型）のリレーションシップ
  2. それ以外（数値型やテキスト）の型同士のリレーションシップ
日付型同士のリレーションシップ

日付テーブルのリレーションシップを構築する際、無意識に上記1番を実践されている人も多いと思います（下図）。

こちらは最も簡便かつ正確な方法で、このリレーションシップを使えば、DailyベースでもMonthlyベースでも、タイムインテリジェンス関数を用いた時系列操作のメジャーを簡単に作成できるようになります。

さらに、日付テーブルが「日付テーブルとして認識される」ことを確実にするため、「日付テーブルとしてマークする」設定を行います。

これにより、選択した日付テーブルに主キーが定義され、前回解説した日付テーブルの自動階層作成が強制的に削除されます（詳細は公式ドキュメント（下記抜粋）からご確認ください）。

この設定を行っていない方も多いかもしれませんが、日付テーブルを作成したら「日付テーブルとしてマークする」ことがベストプラクティスの一つであると覚えておくと良いでしょう。ただし、Dailyベースの日付テーブルではなく、Monthlyベースの日付テーブルの場合は「日付テーブルとしてマークする」設定ができないため、別の方法で設定を行う必要があります（詳細は後述）。

上記のようにMonthlyベースの日付テーブルで「日付テーブルとしてマーク」を設定しようとすると、下図のようにエラーメッセージが表示されます。これは、日付テーブルは基本的にDailyベースで作成することが推奨されていることを意味します。しかし、Monthlyベースの日付テーブルを作成すること自体は問題ではないため、その際の留意事項（可視化に際してのエラー）をしっかり把握しておくことが重要です。

それ以外（数値型やテキスト）の型同士のリレーションシップ

日付型以外のリレーションシップは主に以下のようなケースが考えられます。Factテーブル側で日付型の列がなく、数値型やテキスト型の列しかない場合、これらをベースにリレーションシップを作ることがあります。

このパターンで作成されたリレーションシップは、タイムインテリジェンス関数を使用した計算において、正しく機能する場合としない場合があります。これが今回のメイントピックとなります。

タイムインテリジェンスが機能しない？

前置きが長くなりましたが、ここからはそれぞれ異なるパターンのシナリオについて見ていきます。今回は、Monthlyベースのデータを使用して解説しますが、DAXメジャーはそれぞれ以下のようになっています。

SalesAmt = SUM( Sales[売上高] )
YTD.Sales = CALCULATE( [SalesAmt], DATESYTD( dCalendar[Date], "3/1"))

dCalendar[Date]は日付型の列で、こちらのDateがリレーションシップとして使用された場合と使用されなかった場合の比較を行います。

YTD.Sales は、DATESYTDというタイムインテリジェンス関数を使用し、会計年度の終了日を3/1と指定しています（月次ベースの日付テーブルのため、3/1で表現）。これにより、SalesAmt は毎年4月から累積され、翌年の3月まで続きます。4月になると累積がリセットされ、新しい会計年度の累積が開始されます。
※ Tips: Monthlyベースのリレーションシップを作成する際は、月末で紐づけるのではなく、月初（1日）同士で紐づけることをお勧めします（閏年などの影響を避けるため）

なお、検証方法については、日付型の列を使用した場合と日付型以外の列を使用した場合に分けて説明します。

シナリオ1: 日付型の列同士のリレーションシップ

それでは、SalesAmtとYTD.Salesを、dCalendar[YM]（テキスト型）とdCalendar[Date]（日付型）の2つの軸でフィルターした結果を見ていきます。

ご覧の通り、どちらのビジュアルでも正しく結果が返ってきていることが分かります。YMというテキスト軸でも問題なくYTD.Salesが算出される挙動はシナリオ2: 疑問②に関連します。

シナリオ2: 日付型以外の列同士のリレーションシップ

このパターンの結果は以下の通りです。

ご覧の通り、日付型であるDate列ではYTD.Salesが正しく計算されていますが、テキスト軸であるYM列ではYTD.SalesがSalesAmtのままになっており、タイムインテリジェンス関数が機能していないことが分かります。この理由を探るべく、有料ツールであるTabular Editorで確認してみます。

外部ツールからTabular Editor 3を立ち上げます。

Tabular Editor 3の画面で以下の操作を行います。

① New Pivot Grid

② Dateを右側のPivotへドラッグ

③ SalesAmt とYTD.Salesもドラッグ

④ 2018年12月1日のYTD.Salesのセルを右クリック > Debug this value

⑤ Step inを2回クリックし、DATESYTDで評価された結果（テーブル）を取得

⑥ 2018/9/1 ～ 2018/12/1 までの期間がテーブルとしてフィルターされていることを確認

Power BIのフィルターコンテキストに詳しい方はご存じかと思いますが、CALCULATEは現在のフィルターコンテキストを変更できる唯一のDAX関数です。通常のSUM関数で計算した場合、2018年12月1日時点の数値が表示されますが、CALCULATEとDATESYTDを使用することで、まず集計期間がフィルターされ、その後SalesAmtというメジャーで評価（集計）されます。これにより、当該セルでは2018年9月から2018年12月までのSalesAmtの合計が計算されるようになります。

ここでのポイントは、上図の「Current expression」で表示されたValueが4ヶ月分のテーブルフィルターであり、これはdCalendar[Date]という列を使用したことに起因する点です。

ここでdCalendar[Date]をdCalendar[YM]に変更し、同じ操作を①～⑥まで繰り返してみます。①～⑤までは省略し、⑥の結果は以下の通りになりました。

タイムインテリジェンス関数であるDATESYTDが期待通りの結果セットを返しているのに、なぜYTDとしてのフィルターが効いていないのでしょうか？それは、YMという列に対してフィルター解除が行われていないためです。つまり、YMの列はオリジナルのフィルターコンテキストを保持しており、'18/12は1ヵ月分のフィルターに過ぎないのです。

フィルターの解除という概念は以下のように覚えておくと良いでしょう。

  1. 列 or テーブルにフィルター解除が適用される（ALLやREMOVEFILTERSを使用）
  2. 新たに指定したフィルターを適用する

従って、dCalendar[YM]で正しいYTD.Salesを計算するためには、YTD.Salesを以下のように書き換える必要があります。dCalendarの他の列でもフィルターが効くよう、dCalendar[YM]列だけではなく、他の列全てに対してフィルターを解除しているところがポイントです。

YTD.Sales = CALCULATE( [SalesAmt], DATESYTD( dCalendar[Date], "3/1") )
↓↓↓
YTD.Sales =
     CALCULATE(
            [SalesAmt],
            DATESYTD(
                dCalendar[Date],
                "3/1"
            ),
            REMOVEFILTERS(dCalendar)
            //ALL(dCalendar)でもOK
        )

変更後のDAXメジャーを使用すれば、テキスト軸であるYMでも無事YTD.Salesを算出することができるようになります。

シナリオ2: 疑問①

ここで1つ疑問が出てきます。REMOVEFILTERSを使用する前にも関わらず、なぜdCalendar[Date]列ではYTD.Salesは正しく算出されたのでしょうか？答えは、フィルターとして使用された軸（列）は、

列に対するフィルターについて、CALCULATEのオーバーライド（上書き）機能により、知らずのうちにフィルターが解除されている

ためです。例えば以下のDAXメジャーがあるとします。

//dProduct[Category]がAであるSalesAmt（糖衣構文による自動オーバーライド）
Sales.CatA = CALCULATE( [SalesAmt], dProduct[Category] = "A" )

これをdProduct[Category]という切り口で表示すると以下のように、全てのCategoryがAのSalesAmtとしてオーバーライドされて算出されます。

この数式は、見えないところで以下のように機能しているため、DAXを初めて触る人の多くがこれを知らず、最初から戸惑うことになります。フィルターを解除するDAX関数であるALLが効いているため、Category列のフィルターが一旦全て解除され、新たにAという条件が適用されます。その後、BやCなどのセルでフィルターコンテキストが変更（上書き）されるため、全ての結果がAのSalesAmtとなってしまいます。

//dProduct[Category]がAであるSalesAmt(本来の構文）
Sales.CatA =
CALCULATE (
    [SalesAmt],
    FILTER (
        ALL ( dProduct[Category] ),
        dProduct[Category] = "A"
    )
)

なお、各Categoryの数値をオーバーライドしないようにするためには、KEEPFILTERS関数を使い、以下の数式に変更します。

//dProduct[Category]がAの値でオーバーライドしないSalesAmt
Sales.CatA =
 CALCULATE ( [SalesAmt], KEEPFILTERS ( dProduct[Category] = "A" ) )

この前提知識を基に、dCalendar[Date]を切り口にした場合のYTD.SalesおよびそのDAX式について説明します。dCalendar[Date]を切り口とする場合、DATESYTDは各セル（データポイント）で本来のフィルターコンテキストを上書きし、各データポイントで異なるテーブルフィルターが適用されて計算されることになります（下図）。

DAXが難しいと言われる理由の一つは、このような隠れた動きを理解し覚える必要があるからです。しかし、しっかり覚えておくことで、他のユースケースでも役立つと思います。

シナリオ2: 疑問②

もう一つの疑問として、なぜシナリオ1ではテキスト列であるdCalendar[YM]でもREMOVEFILTERSが効いたかということです。実は、

日付型の列同士でリレーションシップを作った場合、タイムインテリジェンス関数を使った計算では、CALCULATEの中で自動的にALL(dCalendar)が見えないところで効くようになっている

ためです（下記の通り）。

YTD.Sales = CALCULATE( [SalesAmt], DATESYTD( dCalendar[Date], "3/1"))
↓↓↓
YTD.Sales =
    CALCULATE(
            [SalesAmt],
            DATESYTD(
                dCalendar[Date],
                "3/1"
            ),
            ALL(dCalendar) //この部分は非表示であるが、適用されている
        )

公式ドキュメントを探してもなかなか見当たらないため、これを機に覚えておきましょう。シナリオ1と2を見る限り、日付型の列同士でリレーションシップを作ることが最も混乱を避ける方法であることが分かると思います。

シナリオ3: 日付型以外の列同士のリレーションシップ（キー列を指定）

これまでの解説から、シナリオ2では、日付型以外の列でリレーションシップを作った場合、タイムインテリジェンス関数を使った時系列メジャーを作成する際に、ALLまたはREMOVEFILTERSを明示的に指定する必要があることが分かりました。しかし、メジャー数が増えてくると、この点を忘れてしまう可能性が高くなるため、この方法はあまり推奨されません。

そこでシナリオ2であっても明示的にフィルター解除を行わなくても良い方法をご紹介します。やり方は至って簡単、日付テーブルで「キー列を指定」するだけです。

前提条件

キー列の設定

この設定を行うだけ、シナリオ2のYTD.SalesでALLまたはREMOVEFILTERSを明示的に指定しなくても以下のように正しく計算されるようになります。

これは、dCalendar[Date]列でリレーションシップを構築していなくても、Date列が主キー（ユニークなレコードのみを含む列）として認識されるため、上記のフィルター解除を行うDAX関数が自動的に見えない形で機能するようになったためです。

主キーは日付テーブルだけでなく、それ以外のDimensionテーブルで全て指定できますので、意識的にこれを設定しておくと良いでしょう。

おまけ

Power BI歴が長い方は、Excel Power PivotがMicrosoftのセルフサービスBIの「先輩」であることをご存知かと思います。Excel Power Pivotでも同様にモデルを作成することができ、モデルビューは以下のようになります。
※ Excel Power PivotはM365を使用している人であれば、全て無料で利用可能

DAXメジャーはメジャーグリッドと呼ばれるセルで定義しますが、書式を一括で適用できる等、結構便利です。

Excelはピボットテーブル分析がメインとなりますが、今回の事例でピボットを作ると以下のようになります。これまでの解説の通り、案の定、YTD.Salesは期待する結果となっていません。

しかし、Excel Power PivotはPower BIとは異なり、日付テーブルがDailyでなくても「日付テーブルとしてマーク」を設定することができます。Power Pivotを立ち上げてdCalendarを選択し、リボンの「デザイン」タブから、下図のように設定していきます。

設定が完了すると、ご覧の通り、ピボットの集計結果が正しく反映されるよになります。

Excel Power Pivotは、DAXエンジンがPower BIと同じであっても、挙動が異なることがあります。ここで、Excelブックを閉じて、新しい Power BI Desktop の pbix ファイルを立ち上げます。Power BI Desktop には、作成した Excel Power Pivot をインポートする機能がありますので、以下の手順でインポートを行います。

成功すると、Power BI DesktopにはExcel Power Pivotで構築したセマンティックモデルの全てが読み込まれます。

ここで面白いのは、Monthlyベースで「日付テーブルとしてマーク」の設定ができなかった日付テーブル（dCalendar）が、インポートした Excel Power Pivot のセマンティックモデルでは既に設定されていることです。

従って、今回の事例で使ったpbixからビジュアルをコピーしてくると、以下のように、YTD.SalesがYM列であっても正しく機能することになります。

しかし、「日付テーブルとしてマーク」を一度オフにし、再度オンにしようとすると、Power BI Desktop からは設定できないというメッセージが表示され、振り出しに戻ることになります。

その場合、YTD.Sales の計算結果を正しく反映させるためには、再度「キー列を Date 列で指定」する必要があります。

最後に

日付テーブルとタイムインテリジェンス関数を使った時系列メジャーの計算は思った以上に複雑で、細部に注意が必要です。迷ったときは以下のルールに従うと良いでしょう。

  • 日付型同士の列でリレーションシップを作れる場合は、これを最優先する
  • 日付型以外の列でリレーションシップを作る必要がある場合は、日付テーブルでキー列を設定することを忘れない
  • フィルター解除を行うALLやREMOVEFILTERSを把握しておく