Power BI等のDAXは怖くないよ!

17105 ワード
PowerBI PowerBITips DAX PowerBIDesktop PowerBI テキストリンク

はじめに

Power BI Desktop等で使えるDAXについて、私が最初の一歩と感じたところをまとめてみました。

ご注意

　以下は「主にここから」という代表的な使い方や考え方です。Power BIで表現できる方法等は、同じ結果でも複数あります。どちらの結果も正しく、実行時間の差（計算効率）がある、あとで何をしたかったわかる等それぞれ特性があります。でてくる結果が誤りでなければ、複数の方法があり、こうじゃなくちゃいけないということはないんです。トライしてDAXに慣れていくことが大事です～。

　また、ここで記載することも、私が基本的なところと思っているものを取り出しているというものです。各DAXの詳細は、マイクロソフトのドキュメントやLearn等を参照してください。

Power BIは基本的に最新版を使う

　Power BI Desktop等は基本毎月更新です。最新版かどうかでメニュー等も変わっていきます。以下の図は、基本は2020年7月版に基づいており、今後表記等が変わっていくかもしれませんが、適時読み替えてください。

DAXはPower BIで以外でも登場する

　なぜタイトルがPower BI 等 になっているかというと、ExcelのPower Pivot等、DAXに対応する他のものでも使える共通言語だからです。

DAXって、Power BIのどこで主に使うの?

　Power BIの画面は (1) ビジュアルをモデリング等を行うメイン画面（正式名がわからないため、勝手に読んでいるもの）と、(2)読み込んだデータを整えるPower Query エディターの大きく2種類の画面があります。その中で主に使われる言語も、(1)ではDAX(計算列やメジャー等で使う言語。Data Analysis Expressionsの略)、(2)ではPower Query M 数式言語(formula language)の2種類あります。
　DAXとMを区別して、DAXはメイン画面で使うと覚えましょう。

メイン画面からPower Queryへは

　データソースからテーブルを選択した後の、ダイアログ「ナビゲーター」の、[データの変換]（黄色い[読み込み]ではない）や、読み込み後のメイン画面の上部リボンにある[データの変換]からPower Query エディター画面を呼び出せます。
　メイン画面に戻るには、[閉じて適用]または、[x]で閉じる時に適用させます。
通常最初に行うのは、Power Queryで、適時行き来します。

最初に行うのはPower Query エディター

データを読み込んだ後、左のPower Query Editorでデータを整える段階が最初。

  • 不要な列の削除
  • 必要な列の不要データの削除
  • 型を整える
  • 明らかに必要な列の追加

等、豊富なメニュー等から行い、事前に整えます!

最初にこれらを行ってからメイン画面に進むことができますが、この順が定番!
Power Queryでも、DAXでも、どちらでもできることがありますが、原則はこの流れで。

進め方のおさらい

  1. Power Query エディターでデータを整える
  2. ビジュアルを作る
  3. 必要に応じて、DAXで計算式やメジャーを作る
  4. 1,2の課程を試行錯誤でいろいろ繰り返し、(どうしても必要な場合)0にも戻り微修正する

という流れで可視化を進めていきます。

メイン画面には3つのビュー

メイン画面のどこから?

　[レポート] ビューまたは[データ] ビューでテーブルを選択すると、メニューに[テーブル ツール]が表示され、リボンには「新しいメジャー」等がでてきます。ここでいう「新しい」とは「新規○○」という意味です（What's NewのNewではないイメージ）。

テーブルの 右クリックのメニューから もよく使われます

メニュー[モデリング]から入る方法でもOKです。

どういった動機でDAXを作るの?

大きく以下の2つがあります。
- 計算列　テーブルに計算後の新しい列が必要というとき
- メジャー テーブルや、特定行のフィルターで、集計等を行うとき

計算列は必ず実行され、メジャーはビジュアルで使われるときに実行されます。

この表には追加で計算列が欲しい、特別な集計をしたいのでメジャーで...、といった時に作ります。

基本的な記載方法

　Power BI Desktop における DAX の基本事項 - Power BI | Microsoft Docsより、DAXの構文はこのようになっています。

読み方は以下。

" "Total Sales" という名前のメジャーについて、Sales テーブル内の [SalesAmount] 列の値の合計を計算する (=)。 "

もう少しやわららかく表現すると

" "Total Sales" というメジャーは 、Sales テーブル 内の [SalesAmount]列 の 値の合計を計算する (=)。 "

英語でいうと、

　For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.

整形前

　[新しいメジャー]を選択すると以下のように表示されます。

左の「1」は行数を表すもの、「メジャー = 」は既定のメジャー名です。このメジャー名は何か名前を付けるため、[Ctrl]+[A]で全選択をして、○○ = のように書き始めます。

1行でDAXを記載すると以下のようになります。

メジャー名TotalSalesのDAX
TotalSales = SUM(Sales[SalesAmount])

整形後

　DAXは関数の入れ子ができるので、よく改行やタブを使って整形されます。

入れ子になって整形されたDAX例
Dates = CALENDAR(
        DATE(2019,1,1) ,
        TODAY()
        )

閉じ括弧がどことペアになっているか、文字の上にいけばわかります。

主なキーボード操作（ショートカット）

  • [SHIFT] + [Enter]で改行
  • [ALT] + [Enter]でも改行
  • 必要に応じて[Tab]でインデント（字下げ）
  • [Ctrl] + マウスの中ボタンのくるくるでフォント拡大/縮小
  • [Enter]で確定(間違ってEnterして確定しても直せば、OK)

インテリセンス(入力の候補だし)

　DAXを途中まで入力すると候補がでてきます。全部覚える必要はないです。

関数の場合

テーブルの場合

"'"（シングルクォテーション）の入力でテーブルの候補が呼び出されます。

"["で囲まれるのは列名ですね

コメント

メジャー名 = の後に 「// 」を入れた箇所は行末までコメントになります（コメントと認識したところは緑色に）。

このようなルールはプログラミングみたいな感じですね。

1行で書ける簡単でよく使うメジャーは??

読み込んだテーブルが何レコードあるか?を知りたいとき、カードのビジュアルに項目を読み込み、「カウント」を選択...、とやってもいいのですが、以下のメージャーをDAXで書いて作ってから、読み込むこともできます。

日本語で、メジャー「レコード数」は、Salesテーブルの行数を数える（COUNTROWS） です。

あと、合計のSUM等ありますね。

メジャーは値を返す以外に、テーブルも作ってくれます。

メジャーを書くだけでなんとテーブルを作ってくれます。
代表的なものはカレンダーテーブルです。

新しいテーブルはこちらから

カレンダーテーブル

カレンダーテーブルの例（ @yugoes1021 さんから教わったもの。深謝。 ）

カレンダーテーブルの例
カレンダー = 
// テーブル名は「カレンダー」
var BaseCalendar = CALENDARAUTO()
RETURN
    GENERATE(
        BaseCalendar,
        var BaseDate = [Date]
        var YearNumber = YEAR(BaseDate)
        var MonthNumber = MONTH(BaseDate)
        var DateNumber = DAY(BaseDate)
        var DayNumber = WEEKDAY(BaseDate, 1)

        RETURN ROW(
            "年_番号", YearNumber,
            "年", FORMAT(BaseDate, "yyyy"),
            "月_番号", MonthNumber,
            "月", FORMAT(BaseDate, "mm"),
            "年月_番号", FORMAT(BaseDate, "yyyymm"),
            "年月", FORMAT(BaseDate, "yyyy/mm"),
            "月日_番号", FORMAT(BaseDate, "yyyymmdd"),
            "月日", FORMAT(BaseDate, "mm/dd"),
            "日_番号", DateNumber,
            "日", FORMAT(BaseDate, "dd"),
            "曜日_番号", DayNumber,
            "曜日", SWITCH(DayNumber, 1, "日", 2, "月", 3, "火", 4, "水", 5, "木", 6, "金", 7, "土",""),
            "相対月数", DATEDIFF(TODAY(), BaseDate, MONTH)
        )
    )

Power BIで表示したところ。色分けもありわかりやすいです。

テーブルを作れるのは非常に強力ですね。

よく使うものはテンプレート化して保管を

　前項のカレンダーテーブルを読み込んだ後、日付を「/」表示にする、時刻を除くなど整えます。
　

また、「○○_番号」で○○を列で並べ替えておくと、曜日等が規則正しく並びます。

並べ替えた後、ビジュアルでは使わない「○○_番号」を非表示にします（非表示のものは薄くなります）

これらを整えた後、時系列を扱うようなものではよく使うので、分析のひな形としてテンプレート保管しておくと便利です。

　Power BIの保管をテンプレート（.pbit）で保管できます。時系列を扱う分析で、毎回作って曜日等の表示順も整えたカレンダーテーブルを呼び出すのが面倒であれが、テンプレートで保管しておきましょう。必要なカレンダーの期間の開始日等はデータによって調整し、原則2年分等長期で持っておきます。

保存時メモ

テンプレートの呼び出しは、.pbitファイルからの起動または、メニューのインポートから

メジャーの特徴のひとつ「ピンポイントで欲しいデータを特定」

　「全体の売上」というSUM('Sales'[SalesAmount])のメジャーを作っておきます。そして、特に注目しているもの（例ではたまたまカテゴリーの「Audio」）をピックアップして絞り込んで、計算し表示できます。

サンプル：日本の人口

　こちらのホームページに、Power BI DesktopのWebで接続して、計算列とメジャーの違いを見てみましょう。

【ホームメイト】47都道府県別の人口一覧

データのクリーニングは、Power Queryで適当に行い、以下のような形となりました。

計算列を作る

　新しい計算列の「人口順」で、人口ランキングの列を作ります。

計算列の人口順
人口順 = 
    RANKX(
        '人口',
        [人口]
    )

メジャーの場合は、フィルターを引き継いでくれる!

　今度はメジャーで作ってみます。

メジャーの人口順
人口順(メジャー) = 
    RANKX(
        ALLSELECTED('人口') ,
        CALCULATE(
            SUM('人口'[人口]
            )
        )
    )

計算列とメジャーの結果は同じですね。

ところが、エリアのフィルターで絞った場合、どうでしょうか？

メジャーはフィルターで絞られたエリアでの順位、計算列ではあらかじめ計算された値を出しているだけなので全体の順位のままです。
このような計算列とメジャーの違いがあります。

その他、メジャーの特徴

  • 関数の入れ子ができる
  • 2つのコンテキストがある（行コンテキスト、フィルターコンテキスト）

etc,etc...(ここでは割愛)

クイックメジャーとは

　1からメジャーを作らなくても、クィックメジャーから項目を選択することで、メジャーのDAXを作ってくれる、それがクイックメジャーです。作られたDAXをカスタマイズすることもできます。

尚、クイックメジャーでできたDAXが最も効率がよいもの、メンテナンス性が高いものとは限りません。
方法は数通りあるの原則で、別な書き方を検討していく、いったことが中級的なイメージでしょうか。

参考 Power BIクイックメジャー「星評価」 - Qiita

DAXのリファレンス

参考ページ等

おわりに

　Microsoft MVP (Power BI) のお二人、 @PowerBIxyz 、 @yugoes1021 さんにPower BI勉強会等で教わり、自習であちこち、MicrosoftのLearnやDocs、YouTube等で学んだことで、ここが最初の一歩だ!と思ったところをまとめてみました。
　正直に言いますと、DAXなんだかこえ－、要点わかんねー、難しいーって、半年以上思っていましたw が、2020/07/18に参加した、Power BI 勉強会 @ 東京 #18 - connpass他最近の勉強会で、 @PowerBIxyz さんに教わったり、主に海外のDAXに関する動画を閲覧している内に、だいぶ怖くないなぁ、と思えるように変わってきたところでして、今回のメモを書きました～
　ご参考にしていただけますと幸いです。