Power BIを使ってデータ分析されている方の中には、こう感じている方も少なからずいるでしょう。
「どうして、思うような計算結果を示してくれないのだろうか？」
そして、こう思われている方も相当数いるはずです。
「Power Query M はポチポチで簡単だけど、DAX はどうにも難しい……」
筆者も最初は躓きました。
#3つの基本事項
Power BIの理解で最も大切なのは、正しい「データモデル」の理解にあり、中でも留意すべきポイントが「3つの基本事項」です。
① 正しい「カレンダーテーブル」を作成する
② データモデルは「スタースキーマ」を心掛け、隠すべきは隠す
③ 2大ツール「M」と「DAX」を適材適所、上手に使い分ける
これら「3つの基本事項」は、Power BIで思うような計算をするために必要な「型」（「形」とも言う）であり、最低限守らなければいけない「ルール」です。
上記「3つの基本事項」を忠実に実践することで、より簡単にDAXを記述できるようになり、動作が安定し、計算も速くなります。
2大ツールの一つ、データ分析式「DAX」を苦手とされる方の多くは、この「3つの基本事項」を守っていないことが多いように思います。
本稿では、最も基本的、かつ、重要な事項の一つである、①正しい「カレンダーテーブル」を作成する、という点に主にフォーカスを当てて解説します。
なお、上記「3つの基本事項」は、Power BIの原型「モダンExcel」（「Power Pivot」、正式名「Power Pivot for Excel」と、「データの取得と変換」という名称の「Power Query」、これらがモダンExcel2大ツールである）を取り扱う際にも同様であることを申し添えます。
#カレンダーテーブル
DAXデータ分析式には、「カレンダーテーブル」が必要です。
これは「日付テーブル」と呼んだりすることもあれば、モダンExcelのPower Pivot for Excelでは「予定表」、英語版では「calendar」と表示されたりもします。
いずれも同じ「カレンダーテーブル」です。
ここでは「カレンダーテーブル」と表現することにします。
##カレンダーテーブルの作成方法
Microsoft（以下、MSと言う）は、カレンダーテーブルの作成方法を5つ紹介しています。
なお、下記で引用したMSのDocsでは「日付テーブル」と表現されていますが、上述のとおり「カレンダーテーブル」と同義です。
このように5つ列挙されていますが、大別すれば「カレンダーテーブル」の作成方法は3つということになります。
①[自動の日付/時刻]オプションで、カレンダーテーブルを作る
②DAXで、カレンダーテーブルを作る
③Power Query Mで、カレンダーテーブルを作る
このうち、①[自動の日付/時刻]オプションで、カレンダーテーブルを作るというのは、便利な反面、複数のカレンダーテーブルを作成しがちなので、避けた方が良いと言われます。
したがって、カレンダーテーブルは「②DAX」もしくは「③M」で作成するのが良いでしょう。
「カレンダーテーブル」については、下記のDocsが参考になります。
##DAXとM、それぞれの「カレンダーテーブル」
「正しい」カレンダーテーブルは、DAXとM、それぞれで作成することが可能です。
Power BIで、DAXによる「カレンダーテーブル」
DAXによる「カレンダーテーブル」の作成方法は、次のとおりです。
CalendarTable =
ADDCOLUMNS(
CALENDARAUTO (),
"年", YEAR ( [Date] ),
"月の番号", MONTH ([Date]),
"月", FORMAT([Date],"MMMM"),
"MMM-YYYY", FORMAT([Date],"MMM-YYYY"),
"曜日の番号", WEEKDAY([Date]),
"曜日", FORMAT([Date],"DDDD")
)
Power BIで、Power Query Mによる「カレンダーテーブル」
Mによる「カレンダーテーブル」の作成方法は、次のとおりです。
let
ソース = List.Dates( #date( 2014, 1, 1 ), 365, #duration( 1, 0, 0, 0 ) ),
テーブルに変換済み = Table.FromList(ソース, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"名前が変更された列 " = Table.RenameColumns(テーブルに変換済み,{{"Column1", "Date"}}),
挿入された年 = Table.AddColumn(#"名前が変更された列 ", "年", each Date.Year([Date]), Int64.Type),
挿入された月 = Table.AddColumn(挿入された年, "月の番号", each Date.Month([Date]), Int64.Type),
挿入された月の名前 = Table.AddColumn(挿入された月, "月の名前", each Date.MonthName([Date], "en-001"), type text),
追加されたカスタム = Table.AddColumn(挿入された月の名前, "MMM-YYYY", each Date.ToText([Date], "MMM-yyyy","en-US")),
追加されたカスタム2 = Table.AddColumn(追加されたカスタム, "曜日の番号", each Date.ToText([Date], "dd")),
追加されたカスタム1 = Table.AddColumn(追加されたカスタム2, "曜日", each Date.ToText([Date], "dddd","en-US")),
変更された型 = Table.TransformColumnTypes(追加されたカスタム1,{{"Date", type date}, {"年", Int64.Type}, {"月の番号", Int64.Type}, {"月の名前", type text}, {"MMM-YYYY", type date}, {"曜日の番号", Int64.Type}, {"曜日", type text}})
in
変更された型
DAXとM、両者を見比べてもらうと分かるように、どちらも同じ結果になります。
ちなみにこれは、後掲するモダンExcelの2大ツールの一つである「Power Pivot for Excel」の「日付テーブル」という機能で作成した「カレンダーテーブル」に合わせて、Power BIでDAXとMで作成しています。
Power BIでカレンダーテーブルを作るのは、DAXでもMでも構いません。
個人的には、Mで作るカレンダーテーブルの方がお勧めです。
また、モダンExcelでカレンダーテーブルを簡単に作るのであれば、「日付テーブル」機能を使い、Power Pivot for Excelで作るのが良いでしょう（後述）。
いずれにせよ、正しいカレンダーテーブルがなければ、Power BIも、モダンExcelも、思うような計算結果が得られないなどの問題が生じることになるので留意が必要です。
正しい「カレンダーテーブル」の作成方法については、こちらのページが参考になると思います。
##「日付テーブルとしてマークする」を行わないと、DAXの記述がかなり面倒なことになる
カレンダーテーブルを作成したら、「日付テーブルとしてマークする」を行うようにしてください。
「これが、カレンダーだよ」と、指定する必要もあり、これによりDAXの記述が簡単になります。
この「日付テーブルとしてマークする」を行わなくても、DAXは記述可能です。
しかし、「日付テーブルとしてマークする」を行わずにDAXを記述しようとすると、非常に面倒なDAX式となります。
具体的には、Power BIで「日付テーブルとしてマークする」を行わずにDAXを記述しようとすると、「REMOVEFILTERS関数」を使う必要が生じます。
REMOVEFILTERS関数を使う必要性については、こちらが参考になります。
REMOVEFILTERS関数そのものについては、こちらを参照してください。
#カレンダーテーブルに対する「誤解」
ここで、Power BIをいったん離れ、「モダンExcel」のお話をさせてください。
というのも、モダンExcel界隈では、ちょっと困ったことが起こっているからです。
そもそもモダンExcelとは、「データの取得と変換」でPower Queryエディターを起動し「M」を記述し、「Power Pivot for Excel」で「DAX」を記述しデータモデルにするという、言うなればPowe BIの原型ともいえる使い方をExcelで行うことを指します。
残念ながらモダンExcel界隈では、「カレンダーテーブル」に対するかなりひどい「誤解」があります。
##「なんちゃってモダンエクセル」でありがちな、ワークシートでカレンダーテーブルを作るのはやめるべき
モダンExcel界隈では、こんな感じの「なんちゃってカレンダーテーブル」（F列「年月」）が横行しています。
対象データの日付（A列）に存在する日付だけを持つ「なんちゃってカレンダーテーブル」（F列）は、そもそもMSが定義する正しい「カレンダーテーブル」の要件を満たしません。
上記のようなExcelワークシートで作った「なんちゃってカレンダーテーブル」を、分析したいデータのテーブルとリレーションするという分析手法が散見されますが、これはやめた方が良いと思います。
上述のような、ワークシート上に「カレンダーテーブル」らしきものを作成し、リレーションシップするという分析手法は、モダンExcel（Power QueryとPower Pivot for Excel）の正しい使い方ではありません。
それゆえ、思うような計算結果が得られなかったり、動作が安定しなかったり、計算処理に時間がかかったりなど、こうした間違った分析手法が様々な問題を引き起こす要因でもあるのです。
そもそもMSのPower BIの規定によれば、下記のような条件を満たした正しい「カレンダーテーブル」を作成する必要があります。
このMSの規定は、当然ながらモダンExcelの「Power Pivot for Excel」でも同様です。
なぜなら、Power BIの原型が「Power Pivot for Excel」だからです。
「Excelワークシート」で「カレンダーテーブル」を作ることが横行していますが、これは絶対に避けるべきです。
なぜなら、上掲のMSが規定する「カレンダーテーブル」の定義を満たすことが難しいからです。
##モダンExcelでは、とてつもなく厄介なことになるので、Power Pivot for Excelで「カレンダーテーブル」を作ることを強くお勧め！
しかも、モダンExcelのPower Pivot for Excelには、上掲 REMOVEFILTERS関数がそもそもありません。
よって、代替案が必要になります。
ALL関数などを駆使しながら、モダンExcelでは対応する必要もあるわけです。
ALL関数については、こちら。
色々考えるのは、面倒だと思います。
「簡単にDAXを記述したい！」
こう思われるのであれば、Power BIでも、その原型のPower Pivot for Excelでも、上記で解説したように「日付テーブルとしてマーク」は必須です。
「モダンExcel」Power Pivot for Excelの「カレンダーテーブル」
（和名「予定表」という名称で作成されることが多い）
##あなたの「モダンエクセル」は、なぜ、思うような計算結果を示してくれないのか？！
正しい「モダンExcel」と「なんちゃってモダンエクセル」の違いについて、下記noteにまとめました。
ご参考まで。
#「タイムインテリジェンス関数」を上手に、簡単に扱いたいのであれば、正しい「カレンダーテーブル」を作らなければならない
「カレンダーテーブル」は、DAXの醍醐味ともいえる「タイムインテリジェンス関数」とも深く関係します。
「カレンダーテーブル」を正しく作成しなければ、この「タイムインテリジェンス関数」が正しく動作もしません。
「タイムインテリジェンス関数」についての解説は、下記を参考にされると良いでしょう。
#②と③の論点も重要！
① 正しい「カレンダーテーブル」を作成する
② データモデルは「スタースキーマ」を心掛け、隠すべきは隠す
③ 2大ツール「M」と「DAX」を適材適所、上手に使い分ける
本稿ではこれまで、上記「3つの基本事項」のうち、「① 正しい「カレンダーテーブル」を作成する」の論点を取り上げました。
①以外の論点である、「② データモデルは「スタースキーマ」を心掛け、隠すべきは隠す」と「③ 2大ツール「M」と「DAX」を適材適所、上手に使い分ける」についても、基本的かつ重要な論点であり、初学者は特に理解すべき論点だと思います。
本稿では「① 正しい「カレンダーテーブル」を作成する」を解説するだけでかなりスペースを割いてしまいました！
②③の詳細については別稿に委ねますが、以下で簡単に②③の論点についても触れておくことにします。
##② データモデルは「スタースキーマ」を心掛け、隠すべきは隠す
Power BIにしろ、モダンExcelにしろ、正しい「データモデル」にすることがポイントの一つです。
「スタースキーマ」という星形の表をイメージし、ファクトテーブルと呼ばれる分析対象データそのものが存在するデータテーブルはメジャーだけにする、言い換えるならば「隠すべきは隠す」ということを心掛けるべきです。
データ「モデル」ですから、出すべきは出し、隠すべきは隠す！
「3つの基本事項」のうち「② データモデルは「スタースキーマ」を心掛け、隠すべきは隠す」については、次のMSLearnなどが参考になります。
##③ 2大ツール「M」と「DAX」を適材適所、上手に使い分ける
Power BIにしろ、モダンExcelにしろ、初学者が躓くのが、MとDAXの使い分けにあると思います。
なぜなら、Mでも、DAXでも、どちらでもできることが多いからです。
「3つの基本事項」のうち「③ 2大ツール「M」と「DAX」を適材適所、上手に使い分ける」の思考回路は、次のWEBが参考になります。
このWEBを踏まえると、MとDAXの使い分けについては、基本的に次のような思考回路を持つと良いのではないかなぁ、と思います。
M ➡
「きれいなデータにする」＝データクレンジング、「きれいなデータを準備する」＝データプレパレーション
DAX ➡
「きれいなデータを、データモデルにして、評価する」、平たく言えば、DAXで「計算する」
##MとDAXは「一体理解」が必要！
巷で横行する「Power Queryで、何でもかんでも計算する」、こうした短絡的な姿勢は果たしてどうなのかなぁとも思います。
そもそも上述のように、「M」と「DAX」は不可分のものです。
Power BIの画面構成を見てもこれは明らかですね。
Power BIでは初期画面で「DAX」を記述し、「ホーム」タブの「クエリ」グループの「データの変換」をクリックしてPower Queryエディターを開き「M」を記述する、こうしたことからも「M」と「DAX」は「一体理解」すべきものであり、適材適所で使い分けする必要があるわけです。
「モダンExcel」に目を転じれば、「モダンExcel」とクラシックな「従来Excel」について一体的な理解もなければ、本来の意味でのモダンExcelが機能しません。
というのも、従来からエクセルの代名詞のように使われる「ピボットテーブル」「ピボットグラフ」、場合によっては「テーブル」も使いことで、データ分析が簡単に行えるようになるからです。
したがって「モダンExcel」では、次のように「三位一体」の理解が必要になります。
・「きれいなデータにする」までが、M（モダンExcelであれば「データの取得と変換」という「Power Query」）の役目
・「きれいなデータを評価＝計算する」のが、DAX（モダンExcelであれば「Power Pivot for Excel」）の役目
・最終的に、ピボットテーブルやピボットグラフ（場合によっては、テーブル）で「可視化する」
M、DAX、従来Excel、それぞれには各々の役割があることを理解しておくべきだと思います。
どうも、この辺の誤解がモダンExcelにあるのかなぁ、その影響がPower BIにもあるのかなぁ……。
Power BIでMとDAXの「一体理解」が必要なように、モダンExcelでも「データの取得と変換」という「Power Query」、「Power Pivot for Excel」、これら2大モダンExcelツールと共に、「ピボットテーブル」「ピボットグラフ」「テーブル」という従来Excelの機能の活用ポイントを「三位一体」で理解する必要があります。
#まとめ
本稿後半では「モダンExcel」にも触れましたが、ここで述べた「3つの基本事項」は、Powe BIにもモダンExcelにも共通する「基本の型」です。
正しい「カレンダーテーブル」だけでも、色々な論点があることが理解できたと思います。
正しい「カレンダーテーブル」がなければ、色々面倒なことになることも分かったと思います。
正しい「カレンダーテーブル」をはじめとする「3つの基本事項」を守ることが、PowerBIでも、モダンExcelでも、大切なのです。
Power BI、その原型とも言えるモダンExcel界隈では、「カレンダーテーブル」に対するひどい誤解が蔓延している状況です。
こうした間違いを何とか解消できればなぁ、こうした思いから、正しい「カレンダーテーブル」を中心に解説させていただきました。
Power BI➡
MでもDAXでも、どちらでもカレンダーテーブルは作成できます。
いずれの場合でも「日付テーブルとしてマークする」ことは、お忘れなく！
モダンExcel➡
Power Pivot for Excel「日付テーブル」機能を使うのが手間なしです。
自動で「日付テーブルとしてマーク」されるからです。
（もちろん、Power Query「M」でもカレンダーテーブルは作成できます。その際は「日付テーブルとしてマーク」をお忘れなく！）
（※）Excelワークシートで「なんちゃってカレンダーテーブル」を作るのは、やめましょう！
これは、計算ミスの元です！ MSも推奨していません！
まずは、「基本の型」をしっかり理解しましょう！
そのポイントは上記「3つの基本事項」になります！
#モダンExcel入門（日経BP）
モダンExcelについては、拙著「モダンExcel入門～データ分析＆可視化の新しい教科書」（日経BP）も参考にしてみください。
モダンExcel初学者に必要最低限の基本的なMとDAXなどを、サンプルファイルを用いて解説し、実際に手を動かしながら理解を深めていただけるよう工夫をしています。
#モダンExcel研究所
WEB（Power Query「データの取得と変換」とPower Pivot for Excelの初学者向けコンテンツ）
note（たまに更新します）
Twitter（徒然なるままにつぶやきます）
モダンExcel研究所