【WIP】Excelパワーピボット DAX編

2023年8月20日 03:38

モダンExcelやPowerBIの学習法は色々あるけど、熱い想いが先行しすぎてて系統的な解説になってるマテリアルが少ない気がする
個人的なおすすめ教材は
・Udemyなら鈴木ひであき氏
・書籍なら鷹尾祥氏
くらいかな

はじめに

自動化のための7つのステップ

①みたてる：手元にあるものと欲しいものへの道筋をイメージする
②とりこむ：手元にある生データを、応用可能なデータに変換し、テーブルの形に落とし込む
③つなげる：役割に基づいてテーブルどうしを論理的に結びつける
④ならべる：表の形にして、計算結果を求めるための文脈を配置する
⑤かぞえる：文脈を受け取る共通の計算式を作り、数字としての集計結果を見せる
⑥えがく：グラフ形式で文脈と集計結果を物理的なイメージとして見せる
⑦くりかえす：新しいデータを吸い上げて、新しいレポートを完全自動で作り上げる

3部作での取り扱い

①Excelパワーピボット：7つのステップでデータ集計・分析を「自動化」する本→ExcelによるBIの全体像

②Excelパワークエリ：データ収集・整形を自由自在にする本→ExcelのETL機能であるPowerQuery、ステップ②〜③、DAXは対象外

③Excelパワーピボット（DAX編）： 3つのルールと5つのパターンでデータ分析をマスターする本→DAX、ステップ④〜⑤

序章：本書の位置付けとアプローチ

DAX（Data Analysis Expression）言語

DAXの処理対象はテーブル（セル範囲ではない）
・Excel関数の処理対象はセル範囲
・DAX言語の処理対象はテーブル、指定されたテーブルをフィルタで絞り込むのがDAXの処理

DAXは関数型言語
・VBAは手続型言語であるため、上から下に処理されていく（直感的に理解しやすい）
・DAXは関数型言語であるため、ネストの内側から処理されていく（直感的に理解しにくい）

DAXは新しい言語
・同じ結果を出すために複数の記述方法がある
・糖衣構文が追加され続けている
・初学者は糖衣構文を使うよりも、集計プロセスが解りやすい記述方法にするべき

補足：計算列とメジャーの違い

初学者がまず躓くところ

・計算列の作成時も、メジャーの作成時も、DAXは両方で使える
・列同士の計算ならM言語でもできる
・列間の計算（構成比、対前期比など）はDAX言語を使わないとできない

計算列
・データとして保存される（データビューで確認可能）ため、レポートファイルが大きくなる
・スライサーで使える
・マトリックスの行／列に指定できる
・DAX式に集合関数が無くても使える

メジャー
・データとして保存されない（データビューで確認不能）ため、レポートファイルが大きくならない
・スライサーで使える
・マトリックスの行／列に指定できない（値としてのみ使える）
・DAXは集合関数を指定しないと使えない

第1章：プロセス／ルール／パターン

モダンExcelにおける可視化プロセス

データソース：CSVなど
↓パワークエリ（M言語）
データモデル：テーブルとリレーションシップのセット
・テーブル：マスタ（Dimension Table）／トランザクション（Fact Table）
↓パワーピボット（DAX言語）
ピボットテーブル／ピボットグラフ

パワーピボットの動き方

①メジャー（計算式）に基づき、テーブルから部分集合（サブグループ）を作る
　・行（ピボットグラフにおける「凡例」）：2023年
　・列（ピボットグラフにおける「X軸」）：飲料
　・集計単位：2023年の飲料
②DAX集計関数に基づき、部分集合からスカラー（1次元の値）を算出する
　・スカラー：「2023年の飲料」の売上合計

3つのルール（第4章〜第6章）

DAXを使って適切なメジャー式を書くためのポイント

①フィルタ：テーブルの部分集合を作る機能
②リレーションシップ：テーブル（トランザクションとマスタ）を繋ぐ
③フォーカス：集計単位（集計関数を適用してスカラ値を得る単位）

5つのパターン（第7章〜11章）

メジャーの基本的な作成パターン（覚えとくべき型）

①全体部分パターン：
②独立テーブルパターン
③順位累計パターン：自己参照型類型／外部参照型類型
④組み合わせパターン：集合演算を使う
⑤時間軸分析パターン：連続型時間軸／循環型時間軸、タイムインテリジェンス関数

第2章：「集計する」とは何か

データモデル

データモデルに取り込むメリット
・1,048,576（2の20乗）行を超えるデータを内部データ（シートには表示されない）としてExcelファイルに保持できるようになる
・ピボットテーブルのデータソースとして、シートだけでなくデータモデルを指定できるようになる

集計の方法

「DAX」と似て非なるものに「DAXクエリ」がある

①ピボットテーブルとメジャーによる集計
・メジャーの出力はスカラ
・ピボットテーブルを作成し、フィールドにメジャーを追加し、メジャー式をDAXで記述する
・全てのメジャーは暗黙的にCALCULATE関数が追加されている（CALCULATE関数があって初めて集計単位ごとに集計できる）

②DAXクエリによる集計
・DAXクエリの出力はテーブル
・考え方
①集計単位を決める：VALUES関数
②サブグループを作る：ADDCOLMNS関数など
③スカラを算出する：SUMX関数など

評価環境（Evaluation Context）

DAXを記述する時、式の評価環境が2種類のどちらなのかを意識する
2種類を区別せずに使っていると、必ず壁にぶつかる

行参照環境（Row Context）
・評価対象は個々のレコード（行）
・行参照環境では、対象レコードのフィールド同士で計算できる（例：商品IDと商品名を文字列結合する）
・計算列を作る場合、行参照環境で評価されている
・メジャーを作る場合、行参照環境は存在しない（メジャーは行ではなく列に対して作成されるため）　※集計関数、フィルタ関数、X関数（イテレータ関数、1行ずつ処理する関数）などを使えば行参照環境を作れる

フィルタ環境（Filter Context）
・評価対象はレコードの集まり（サブグループ）
・DAX式が処理される前に適用されるフィルタ条件のこと
　・例：スライサー、フィルタ、ドリルする―
・CALCULATE関数を使うことでもフィルタ環境を作れる

環境移行（Context Transition）

CALCULATE関数を使って、行参照環境をフィルタ環境に変化させること
例えば、計算列を作成する時、
・計算列 = CALCULATE(SUM('販売伝票'[売上])
と記載する
重複行があると循環依存エラーが発生することがあるので注意

第3章：DAXの基礎

第4章：フィルター

第5章：リレーションシップ

第6章：フォーカス

第7章：全体・部分パターン

第8章：独立テーブル・パターン

第9章：順位・累計パターン

第10章：組み合わせパターン

第11章：時間軸分析パターン

付録

命名規則

テーブル
　・数字テーブル：F_
　・まとめテーブル：G_
　・パラメータテーブル：P_
　・それ以外のテーブル：T_
メジャーの変数（VAR）
　・スカラー：s_
　・テーブル：t_
DAXクエリの別名
　・仮想テーブルの追加列名：@

コーディングルール

CALCULATE関数：引数は③→①で意図する順番に並べる
= CALCULATE (
③[販売数量合計],
②VALUES ('G_カレンダー'[会計年度]),
①ALL ('G_カレンダー')
)

ドリルスルー機能

集計値をクリックすると明細レコードがシート出力される機能
ピボットテーブルとパワーピボットでは若干操作が異なる