1. はじめに
こんにちは。株式会社ジールの@RizumuUEDAです。
PowerBIを使用してレポート開発を行う中で「メジャーが思ったように動いてくれない！」という場面に多々直面しました。PowerBIに関しては経験値も少なかったため、調べながらDAXをいじり挙動を確認しつつなんとか対応...という形になっていました。
本記事では、上記の中で経験したつまずきポイントと、メジャーやコンテキストなど基礎知識について整理したいと思います。
具体的には下記内容を扱います。
- PowerBIにおける計算列とメジャーの違い
- PowerBIにおけるコンテキストの概念
- つまづきポイント① PowerBIにおけるフィルタの考え方
- つまづきポイント② 各フィルタ関数の挙動
本記事で使用しているデータはすべてサンプルデータとなります。
2. 前提知識
本章では、PowerBIで使用される計算式言語DAXと、DAXで記述される要素である計算列とメジャー、DAXの評価環境であるコンテキストについて整理します。
2-1. 計算列とメジャー
DAX（Data Analysis Expressions） は計算式を記述する言語で、PowerBIやPowerPivotなどで使用されます。DAXで記述される計算結果の要素として計算列とメジャーがあります。それぞれの特徴は以下の通りです。
-
計算列：表の行ごとに計算して得られる値から作成される列。計算結果はテーブル内にデータとして格納され、データ更新の際に再計算される。
-
メジャー：行やテーブルをまたいで集計し算出されるスカラー値。式として保存され、ビジュアル描画の度に動的に算出される。
メジャーは式のみが保存されるため、特に元データ量が多い場合には計算列よりもファイルサイズを押さえることができます。一方で描画の度に算出するため、式が複雑な場合はビジュアル描画時間が長くかかってしまう可能性があります。データ量や計算式の複雑さによっても適切に使い分けることが重要です。
2-2. コンテキスト
DAXには式の評価環境（計算対象）としてコンテキストという概念が存在します。具体的には「行コンテキスト」と「フィルタコンテキスト」の2種類があり、それぞれの特徴は以下の通りです。
-
行コンテキスト：現在の行に関連するすべての列の値。計算列など各行に対して処理を行う際に適用される。
-
フィルタコンテキスト：フィルタやスライサ機能によって特定断面で絞り込まれたデータのサブセット。メジャーなど特定断面に対して集計処理を行う際に適用される。
2-3. 挙動の例示
売上金額の算出を例に、計算列(行コンテキスト)とメジャー(フィルタコンテキスト)の差異を図示します。
日用商品の売上個数が記録された売上明細テーブル(下図A)、各商品の単価が記録された商品単価テーブル(下図B)から各商品の売上額（個数×単価）を算出します。両テーブルは「商品」列でリレーションを有しています（下図C）。
計算列で以下の「売上_計算列」列を作成しました。
売上_計算列 = [売上個数] * RELATED('商品単価'[単価])
商品、個数、単価と並べてビジュアルを作成しました（下図上）。
このとき計算列では、各行ごとに「売上個数」と「単価」から積が算出されテーブルに保存されています（下図下、青枠）。
次にメジャーで以下の「売上_メジャー」を作成しました。
売上_メジャー = SUMX('売上明細', '売上明細'[売上個数] * RELATED('商品単価'[単価]))
商品カテゴリスライサと合わせてビジュアルを作成しました（下図上）。
このときメジャーでは、スライサで選択された値のデータを絞り込み売上合計を集計します（下図下、赤枠）。集計の背後では、絞り込まれた範囲内で行コンテキストに基づき個数×単価が計算されています。
また、計算列とは異なりテーブルに計算結果は保存されていないこともわかります。
※今回は「個数(列)×単価(列)」のため問題ありませんが、SUMXで「個数(メジャー)×単価(メジャー)」をするとコンテキストトランジションという事象が発生するため注意が必要です（参考：https://qiita.com/k_maki/items/2543156f215bf82847a6）
3. 検証
本章では、私が実際のレポート開発で扱ったものと類似のケースをもとに、実際にDAXを使いながら挙動を検証・整理します。
下記ビジネスシナリオおよびデータは、実在する特定の企業とは一切関係ない点をご了承ください。
また、機能検証のため簡易的に作成したシナリオであり、現実と照らして違和感ある部分もあるかと思いますがご容赦ください...！
3-1. シナリオ
日用品を製造・販売するとある企業は、東京・大阪・福岡に3つの支店を持ちます。企業本社は期末の損益計算のため、各支店から売上報告を受け「支店別売上報告」としてまとめます。
本社は支店別売上報告からPowerBIを使用して、商品ごとの利益額を集計しレポート化します。集計値としては以下の5つを計算します。
- 売上
- 原価
- 粗利益（= 売上 - 原価）
- 販売費
- 営業利益（= 粗利益 - 販売費）
3-2. レポート
PowerBIのインプットデータ「支店別売上報告」として以下の表データを読み込みました。概要は以下の通りです。
- 赤枠内が東京支店に対応し、大阪支店、福岡支店のデータが下に続く。
- 商品カテゴリと商品には親子関係があり、下図赤枠内のものが全量。
- 商品ごとに科目（売上、原価、販売費）と金額[万円]が記録されてる。
「マトリックス」ビジュアルを使用して、「列」に商品カテゴリと商品、「値」に各集計値を計算するメジャーを指定しました。商品ごと集計値ごとに3部門の合計が算出されています。
各メジャーのDAXは以下の通りです。
// 売上
売上 = CALCULATE(SUM('支店別売上報告'[金額]), FILTER('支店別売上報告', '支店別売上報告'[科目]="売上"))
// 原価
原価 = CALCULATE(SUM('支店別売上報告'[金額]), FILTER('支店別売上報告', '支店別売上報告'[科目]="原価"))
// 粗利益
粗利益 = [売上] - [原価]
// 販管費
販売費 = CALCULATE(SUM('支店別売上報告'[金額]), FILTER('支店別売上報告', '支店別売上報告'[科目]="販売費"))
//営業利益
営業利益 = [粗利益] - [販売費]
ここで新たに発生した以下の要件をレポートへ反映することになりました。
マスク、除菌シート以外は自社製造製品のため研究開発費がかかっており、研究開発費（合計2000万円）を計上したい。自社製造製品の売上額の比率に応じて按分し、販売費に配賦（計上）して販管費とする。
3-3. つまずきポイント
上記の追加要件の実装を、実務でつまずいたポイント2つと合わせて整理します。
①自社製造製品のみに絞り込み：合計行への反映
まず、売上比率を算出するにあたり、売上の対象を自社製造製品のみに絞り込みたいです。はじめは下記の「売上_対象のみ_誤」の式で「自社製造製品でないマスク、除菌シートは売上を0にする」ことを目指しました。
// 売上_対象のみ_誤
売上_対象のみ_誤 = IF(SELECTEDVALUE('支店別売上報告'[商品]) in {"マスク", "除菌シート"}, 0, [売上])
しかし上記ではうまくいかず、結果的には下記の「売上_対象のみ」の式を使用しました。
// 売上_対象のみ
売上_対象のみ = CALCULATE([売上], FILTER('支店別売上報告', not('支店別売上報告'[商品] in {"マスク", "除菌シート"})))
両者の計算結果は以下の通りであり、前者ではマスク、除菌シートそれぞれの値は0になっているものの小計、合計には依然として計上されたままでした。後者の式では合計行にも正しく反映されています。
これは、私のPowerBIにおけるフィルタ機能の考え方が誤っていたことが原因でした。
直感的なイメージだと個別の値がベースにあり、小計、合計と加算していくイメージでしたが、PowerBIでは合計がベースとしてあり、フィルタがかかり細分化されていくイメージになります。
前者のようにIF文で特定箇所を指定するだけだと、細分化された後に個別箇所のみが置き換えられているに過ぎないため、前段の合計行には元の値が残ります。後者のように全体の合計を出すタイミングから除外して計算する必要があるということになります。この理解が当時は十分にできていなかったため「合計行に反映されない！」と苦戦していました。
②売上合計の算出：フィルタ関数の仕組み
次に、絞り込んだ売上に対する売上合計を算出します。
結果としては下記の「売上合計」で正しく合計を算出できました。
// 売上合計
売上合計 = CALCULATE([売上_対象のみ], ALL('支店別売上報告'))
//売上比率
売上比率 = DIVIDE('支店別売上報告'[売上_対象のみ], '支店別売上報告'[売上合計])
//研究開発費
研究開発費 = [売上比率] * 2000
上記の各式を用いることで、各商品の売上比率に応じて研究開発費（合計2000万円）を按分することができました。
このときALLなどフィルタ関数の扱いに苦戦したため、代表的な関数の挙動イメージを整理します。
部門をスライサとして追加し下図左上のようなビジュアルを作成しました。表示されている各値には「部門」「商品カテゴリ」「商品」「科目」のフィルタが適用されています。各フィルタ関数を用いることで、フィルタ適用を様々に操作できます。
以下、各関数の詳細を記述します。
① CALCULATE
CALCULATE(<計算式>, <フィルタ1>, <フィルタ2>, ...)
メジャーのベースとなるCALCULATE関数は、フィルタコンテキストを操作する機能を持つ関数です。第2引数以降にフィルタを記述することで、既存のフィルタコンテキストを「上書き・追加・削除」できます。
例えば前述の「売上」メジャーでは、第2引数に「科目＝売上」を指定することでフィルタコンテキストを追加しています（下図赤枠）。指定しないと、科目に関するフィルタは適用されず「東京支店、除菌スプレー」に関する値が合計されます（下図青枠）。
② FILTER
FILTER(<対象テーブル>, <フィルタ>)
対象テーブルに対し、指定したフィルタを適用できます。
上記の「売上」メジャーでは「支店別売上報告」に対し「科目＝売上」を適用しています。また、ブール型であれば複雑な条件でも指定できます。
③ KEEPFILTERS
KEEPFILTERS(<フィルタ>)
FILTER関数と同様に新たなフィルタを追加する関数ですが、「既存のコンテキストを明示的に保持する」点が異なります。先ほどの自社製造製品の絞り込みも、既存のフィルタを保持したうえで商品に関してフィルタ追加できます。
④ ALL/REMOVEFILTERS/ALLSELECTED
ALL(<対象テーブル/対象列>)
対象テーブルまたは列に適用されたフィルタを全て削除（無視）します。
REMOVEFILTERS(<対象テーブル/対象列>)
対象テーブルまたは列に適用されたフィルタを明示的に削除します。
ALLSELECTED(<対象テーブル/対象列>)
スライサなどで指定された外部フィルタは保持したうえで、対象テーブルまたは列に適用されたフィルタを削除します。
ALL/REMOVEFILTERSではテーブルを指定するとすべてのフィルタが削除され、すべての箇所で同じ値になります。
また、ALLSELECTEDも含め列を指定すると、その列のフィルタのみ削除されます。例えば「商品」を指定すると、商品に関して同じ値になっていることがわかります。
ALLSELECTEDでテーブルを指定すると、外部フィルタである「部門」以外のフィルタが削除されます。部門ごとすべて同じ値になっていることがわかります。
⑤ ALLEXCEPT
ALLEXCEPT(<対象テーブル>, <対象列>)
対象テーブルのうち、対象列以外に適用されたフィルタを削除します。
例えば「商品」を指定するとその他のフィルタが削除され、部門やカテゴリ間では同じ値になっていることがわかります。
最後に、本節冒頭で按分した研究開発費を用いることで、営業利益にも無事反映することができました！
おわりに
本記事では、PowerBIにおけるフィルタの考え方やフィルタ関数についてポイントを図式化・整理しました。
今回の内容のようにかみ砕くとイメージしやすいですが、実務レベルの複雑なロジックになると挙動が見えにくくなるかと思います。そんなときは上記のようなイメージを思い出せると解決の糸口が見えるかなと思いますので、少しでも参考になれば幸いです。
参考
本記事を作成するうえで、以下の資料を図表や説明の参考にさせていただきました。
- [Power BI] DAXの 計算列 vs メジャー -Qiita
- 「メジャー」と「計算列」と「計算テーブル」の違い -Zenn
- DAX の数式のコンテキスト -Microsoft
- 行コンテキストとフィルターコンテキストについて 1（PowerBI/PowerPivot/DAX）-Qiita
- Power BIにおけるコンテキストとは ～コンテキストの種類、活用～ -FROGWELL
- 行コンテキストとフィルターコンテキスト -パワークエリ パワーピボット きほんのき
株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：