テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

ベストプラクティス - 列の非表示

Power BI Desktopは無料ツールですので、データ分析用に手軽に試すことが可能です。それゆえ、基礎知識が足りないまま、間違った使い方をしてしまう可能性があります。今回はその事例と解決法を紹介したいと思います。

想定事例

今回の事例は下図の通り、データモデルに読み込んだ後、列の型を変更しようとするやり方ですが、結論から言いますと、非常によろしくないバッドプラクティスとなります(やってはいけないレベル)。

これを行う理由は数値列をそのままビジュアルにドラッグ&ドロップして(メジャーとして)使用するパターンであり、(下図にもある通り)桁区切りを行うことが目的ですが、Power BIを使い始めた人が最初に直面する最も基礎的なミス(理由は後述)となります(下図)。

※ 暗黙的メジャー(Implicit measure)については下記詳述

この例では、Power BI Desktopのデータビューから列を選択して、桁区切りを設定できますが、レポートビューの上から、テーブル列をクリックし、リボンにある「列ツール」からも設定が可能となります。

どちらにも共通することですが、列を1つずつ選択するしか、桁区切りを設定できません。このやり方はExcelに慣れている人がやりがちと推測していますが、暗黙的メジャー(Implicit measure)と明示的メジャー(Explicit measure)の違いを理解する必要があります。

暗黙的メジャーと明示的メジャー

こちらも初心者が必ず知っておく必要がある概念ですが、Power BIとExcelでは下図の通り、暗黙的(Implicit)と明示的(Explicit)の2つのメジャーが存在します。

アイコンから見て何となく想像がつくと思いますが、

DAXで明確に定義したものが明示的メジャー(Explicit measure)、数字列となっている全ての列は初期状態では全て暗黙的メジャー(Implicit measure、Σやfxマーク)

となります。どちらもビジュアルに使用できますが、結論から言いますと、

  1. ファクトテーブルにある数値列は全てDAXで定義(明示的メジャーを構築)
  2. その後、それらの数値列を非表示

することがベストプラクティスとなります。

上図で言えば、SalesAmountやSalesQuantityという列はSalesSalesQtyというメジャーに相当しますが、この後、SalesAmountとSalesQuantityを非表示にします。

ここまでの解説を踏まえ、今回の事例は

暗黙的メジャーを使用し、データがメモリに読み込まれてから列の型を変更

するプラクティスであり、これには諸々の落とし穴が待っています。

落とし穴の解説

落とし穴として、以下数点あります。

  • そもそもこのやり方で計算結果の書式設定を行わない
  • メジャー参照ができない
  • Excelで分析時の動き
  • ビジュアルの性質(Aggregation)を考慮

まず、上記全てに共通する前提ですが、データモデルにデータが読み込まれる前に、Power Queryエディタ内で各数値列のデータ型が正しく設定されている必要があります。これを行っていないと、下図のように、データ型がテキスト型として扱われてしまう可能性があり、結果的に暗黙的メジャーでも明示的メジャーでもビジュアルが表示されません。

そもそもこのやり方で計算結果の書式設定を行わない

間違っているやり方である、ということだけ頭に入れて頂くだけで結構です(理由は下記詳述)。ちなみに、データモデルに読み込まれた後にデータ型を変更した場合の弊害については下記記事もご参考ください。

メジャー参照ができない

このやり方では暗黙的メジャーしか作れないことになるため、CALCULATE等を使って、Power BIで洗練されたレポートを構築することができなくなってしまいます。CALCULATEはフィルターコンテキストを変更できるDAX関数ですが、最初の引数は明示的メジャーを参照する必要があり、数値列を参照したDAX式が必要となります。

Excelで分析時の動き

Excelで分析」はPower BIとExcelを連携されるために非常にクールな機能となります。

ネット記事や他社BIベンダーの解説でよく、”脱Excel”のようなことを言及してますが、日本ではまだ圧倒的にExcelのユーザーが多く、Power BIを活用している人と比べると、少なくとも見積もって数十倍以上の開きがあります。それだけ、Excelはニーズがあり、

Excelを時代遅れとして見ること自体が時代遅れ

であると断言できます。重要なことは、Excelユーザーを如何によりアップスキルさせ、Excel + Power BIで何ができるかを知ってもらうことですが、その役割の一端を担うのが「Excelで分析」になるわけです。

ここではExcelがPower BI DesktopのAnalysis Serviceインスタンスに接続して、データモデルをクエリした際の動きについて紹介します。そこで、まずPower BI DesktopにSalesAmtというメジャーを作り、売上高という列を使った場合と同様、テーブルビジュアルに結果を表示(桁区切りは設定せず)しています。

ここで、外部ツールより、Analyze in Excelを立ち上げ、Excelに自動的にPivotテーブルが作られることを確認します。

GroupでSalesAmt(メジャー)と売上高(列)をそれぞれ見てみようとすると、SalesAmtでは期待通りの結果、売上高(列)はPower BI Desktopの時とは異なり、フィールドに値として使用することができないことが分かります。これは、2022年5月末時点において、「Excelで分析」は明示的メジャーとして定義されたメジャーは計算結果として使えるものの、数値列を使いたくても使えない、という制限があるためです。

ただし、「Excelで分析」については今後、暗黙的メジャー(数値列をドラック&ドロップ)としても使用可能になる方向に動いていますりました。そのため、上図は間もなく既に古い資料となりますが、今の時点では制限事項の1つとなっていることに留意してください

一方で、売上高は数値列であるものの、下図のように、行にドラッグ&ドロップすることで、階層を構築することが出来てしまいます。

余程の特殊ニーズでない限り、このような形でデータを見ることはないと思いますが、実はこれが原因でモデルサイズが肥大化してしまう要因となっていることもあり、データモデルのサイズがあまりにも大きい場合はこの動作をOFFにしてしまうのも手です。

詳細事例はPower BI CATチームに所属するChrisさんの下記ブログを参照して頂きたいのですが、念のため、やり方を記載しておきます。

Tabular Editorのインストール

上記ブログと同じようなことを実現するためにはTabular Editorが必要となります。Tabular Editorは外部ツールのうち、最も有名なツールの1つであり、これについての詳細は別の機会に紹介しますが、無料バージョンのTabular Editor 2をベースに解説していきます。概要として押さえて欲しいポイントとして、以下3つを覚えておくと良いでしょう。

  • Power BIのデータモデルを構築するTabular Model*1を操作するための高機能なテキストエディタ
  • Tabular Editor 2は無料、Tabular Editor 3は有料
  • 開発者向けのツールであるが、一般ユーザーも積極的に使っていくべきツール

TE2とTE3の違い

手順

  1. Tabular Editor 2(無料版)をインストール

    Release Tabular Editor 2.16.6 · TabularEditor/TabularEditor · GitHub

    下へスクロールし、どちらかを選択(.msi版がインストーラー版)

  2.  Power BI DesktopにTabular Editor 2が出現していることを確認

  3. 対象となるPower BI Desktopを立ち上げ、Tabular Editorを立ち上げる

  4. File > Preferences > Featuresで下図③を✅
    チェックを入れないと、下記機能を設定できない状態となりますので、ご注意ください。

  5. 「売上高」列をクリックし、OptionにあるAvailable in MDXという項目をFalseに設定

  6. 上のほうにある青いボタンをクリックし、セーブする(Power BIのデータモデルに対して、今行った変更を保存)

  7. Excelをアクティブにし、Pivotテーブルを右クリック > 更新

    これにより、先ほどまで行の中にドラッグされた「売上高」という列が選択から消え、Pivotテーブルからも消えていることが確認できる

  8. 同様に、「数量」、「定価売上」、「売上原価」の列について①~③までまとめて設定し、④を実施すると、「売上高」列と同じようになる

ここで今何をやっているかをまとめてみますと、

数値列は計算結果を表記するため、行や列としてそもそも選択させない(=階層として使わせない)

という考え方から来ており、これをTabular Editorから実現させたわけです(Power BIやExcelでは今のところ、実現できない)。今の概念と近い考え方ですが、下記のように、Power BI側の列を非表示させても、Excel上ではこれらの列は表示されなくなります。

ただし、この2つのやり方は概念的に似ていても、結果については大きく異なる可能性があることに留意されたい(=Tabular Editorを使うケースはモデルサイズを大きく減らす可能性も)。

ビジュアルの性質(Aggregation)を考慮

最後に、ビジュアルの性質について考えてみます。Power BIのビジュアルの構造はフィルタコンテキスト(スライサー、フィルター、ビジュアル等でフィルターされる)によって構成されており、切り口×指標(メジャー)でビジュアルを表現することになります(フィルターについては下記参照)。

 従って、上述したメジャー参照(CALCULATEを使ってフィルターコンテキストを変更する等)を可能にするため、数値列をそのままビジュアルにドラッグ&ドロップしても期待通りの結果にはならず、数値列からメジャー(殆どの場合、ベースメジャー*2)を作ることになります。繰り返しになりますが、Sales等のファクトテーブルでは、

数値列はベースメジャー用に参照され、最終的に非表示される

ことになります。例えば、下図のようにSalesAmtというメジャーを作った後、売上高という列を非表示する、といった具合になります。

当然ながら、この作業は以下2点について気を付ける必要があり、判断とやり方を考慮する必要があります。

  1. 不要な数値列はデータモデルに読み込まない = 判断
  2. 数値列が多い場合の対応 = やり方

1はデータモデルの最適化するために最も重要な考慮点の1つであり、

無駄な列はPower Queryの段階で削除し、モデルに読み込まない

ことがポイントとなります。

2については残った数値列をまとめてメジャー(SUM)で定義し、列を非表示できると便利ですが、Power BI Desktop単体でそれを実現することはできず、新規モデルにおいて、ベースメジャー用の数値列が多い場合、Tabular Editorを使うことで対処できます。

以下、Tabular Editorを使って、複数の数値列から同時にメジャーを作り、書式の設定及び数値列を非表示するやり方を紹介します。

  1. データモデル及びSalesテーブルの確認

  2. Tabular Editorを立ち上げ、以下の順序でAdvanced Scriptingウィンドに、C#コードを貼り付け(②)、列を複数選択した状態(③)で実行(④)

    C#コード
    // 選択した列でSUMメジャーを作り、そのまま列を非表示
    foreach(var c in Selected.Columns)
    {
        var newMeasure = c.Table.AddMeasure(
            c.Name + "合計" ,                    // Name
            "SUM(" + c.DaxObjectFullName + ")",    // DAX式
            c.DisplayFolder                        // フォルダの表示
        );
        
        // メジャーの書式を設定:
        newMeasure.FormatString = "#,0";

        // メジャーのドキュメンテーションを追記:
        newMeasure.Description = "このメジャーは" + c.DaxObjectFullName + "の合計値";

        // 列を非表示:
        c.IsHidden = true;
    }   
  3.  成功すると、以下のように数値列は非表示され、新しいメジャーが生成されるため、⑦のステップでデータモデルに今の設定を保存し戻す


    注:今回の例では、ベースメジャーを作るという意図で、全ての列に対してSUMメジャーを適用している。比率等のメジャーはこれらベースメジャーを実施した後作ることになる

  4. Tabular Editor側で保存完了を確認し、Power BI Desktop側に戻ると、数値列が全て非表示となり、代わりに同時作成されたメジャーが出現していることが確認できる

なお、ここでのC#スクリプトはTabular Editorの公式サイトからサンプルとして取得することができるため、C#が分からない人でも、少しプログラミングを知っている人であれば、自分用にいろいろカスタマイズできると思います。

docs.tabulareditor.com

このように、Tabular Editorを使えば、モデリングに際して非常に高い効率化が期待できます。Tabular Editorは開発者向けのツールですが、将来的にアナリストやビジネスユーザーでもある程度使いこなしていきたいツールと言えます。

おまけ&まとめ

小数点の設定

 //全てのメジャーを小数点第一位に設定
foreach (var m in Model.AllMeasures) { m.FormatString = "#,0.0";}

//全てのメジャーを整数に表示
foreach (var m in Model.AllMeasures) { m.FormatString = "#,0";}

//メジャー名に"率"を含むものはパーセント表示、それ以外は整数表示

foreach (var m in Model.AllMeasures)
    var mName = m.Name;
    if(mName.Contains("率")){
    m.FormatString = "0.0%";
    }
    else
    {
    m.FormatString = "#,0";
    }
}

上記コードをTabular EditorのAdvanced Scriptingウィンドで実行すれば、以下のように全てのメジャーを小数点第一位や整数に設定したり、"率"という文字を含むメジャー名はパーセント表示、それ以外は整数表示にしたりすることができます。

今回のトピックはデータモデルに関することでしたが、Power BIではメジャーを作り、その参照している列を非表示するという基本的なことについて見ていきました。派生的に細かい部分の説明が必要であったりしましたが、

Salesテーブルは最終的に全て非表示になり、メジャーテーブルになる

ことがテイクアウェイとなります。年月と商品列は全てKeyとなるため、これらの列も非表示にすることで、Salesテーブル(ファクト)はメジャー専用のテーブルになります。Salesのみならず、他のファクトテーブル(在庫・仕入・原材料・仕掛品等)も同じ考え方となります。

*1:Power BI等のツールでデータを可視化(ナビゲート)するため、実際のデータが入っていないモデルを定義したメタデータ(テーブル、リレーションシップ、メジャー等)

*2:他のメジャーの基本となるメジャーで、例えば売上高(年間累積売上高や四半期売上高を作るための指標)といったメジャー