【続】Microsoft Power BI Desktop - 入門講座 ～DAX-TABLE FUNCTION特訓編～

このセクションではTABLE FUNCTIONを学びます

• TABLE FUNCTIONとは、DAXの中の１つの分類名で、DAXの成果物として値ではなく、テーブル／表を返します

• ですので、TABLE FUNCTION単独によってのみで使われることはなく、TABLE FUNCTIONの結果を使って（＝TABLE FUNCTIONの結果として出される表を使って）何かしらの計算をすることになります

• 前のセクションの最後に、SUMXを紹介しました。具体的に言うと、TABLE FUNCTIONで返された表に対し、SUMXで集計をするイメージです

• これから紹介するTABLE FUNCTION DAXは下記の通りです

  • FILTER

  • ALL

  • RELATEDTABLE

• まずは次のレクチャーにて、TABLE FUNCTIONによって、どんなテーブルが作られるのか？これを可視化します。これは単に理解のためにTABLE FUNCTIONによってできる表を再現するものです

• その後、具体的にTABLE FUNCTIONを見ていきます

• 一通りTABLE FUNCTIONを見ていった後、こんどは学習したTABLE FUNCTIONを組わせて集計をしてみますのでお楽しみに！

前のセクションでやりましたCALCULATED COLUMNに対して、CALCULATED TABLEを説明します

• CALCULATED TABLEとは、DAXによって作られたテーブルのことです

• つまり、これから学習するFILTER、ALL、RELATEDTABLEによって作るテーブルとも言えます

  • DAXによってテーブルを作成します

• TABLE FUNCTIONによる結果は、そのまま他のDAXの結果として使われ、実際に目にすることができません

• ですのでイメージをつけづらい。これもTABLE FUNCTIONを分からなくする原因の一つです

• 後のレクチャーで説明する際、理解を促進するためにTABLE FUNCTIONによりどんなテーブルがDAX上できているのか？可視化のためにテーブルを作って説明します

• ここではその元となるテーブルを作ります

• データビュー テーブルツール / 新しいテーブル

• 【コピー】売上データ = 売上データ

• データビューに行き、テーブルがまるっきりコピーされたことを確認します

  • 日付データだけ書式が変わった場合は直しておきます

• このテーブルを使って、TABLE FUNCTIONの結果を見ていきます

FILTERの説明をします

• FILTERは、あるテーブルを指定し、そのテーブルに対し任意の条件にあったものを残したテーブルを返します

• 前のレクチャーで作ったテーブルのコピーをFILTERを使って加工してみます

• 商品番号が323だけを抽出したテーブルにします

• 【コピー】売上データ = FILTER('売上データ', '売上データ'[商品番号]="323")

  • もし"323"でエラーが出たら、データ型が数字のまま。323で試す

    
    

FILTERの説明をします

• SUMXと組み合わせて使ってみます

• ここまでは、わざわざテーブルを作りましたが、テーブルを使わずメジャー上だけで計算してみます

• 長いDAXを書くときは、改行をうまくつかうと分かりやすくなります

• 商品＃323の売上合計 =

      SUMX(

          FILTER('売上データ1','売上データ1'[商品番号]="323"),

          '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

          )

  • もしエラーがでたら、商品番号のデータ型に注意

• これにより、テーブルを作った時と同じ結果が得られます

• FILTERにより、テーブルに文字通りフィルターをかけたい値だけを残すことができます

• ポイントは、TABLE FUNCTIONによって、実際にテーブルを作らなくてもよい、メジャーの式の中だけで存在させることができる、ということです

FILTERの応用として、リレーションシップがある、他のテーブルにある値を条件としてみます

• 以前に紹介したRELATEDを使います

• ここでは、商品カラーがレッドだけのテーブルを作り、それを集計します

• まずは、どんなテーブルを作るのか？を見てみます

  • 【コピー】売上テーブル by Filter レッド = FILTER('売上データ1', RELATED('商品マスター1'[カラー])="レッド")

  • これにより商品カラーがレッドだけのテーブルができました

• では、SUMXを使って、レッドだけの集計をします

  • 商品カラーがレッドだけの売上 =

        SUMX(

            FILTER('売上データ1',RELATED('商品マスター1'[カラー])="レッド"),

            '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

        )

• ご覧のように、DAXの強みは、DAXの中にDAXを組み込むことにより、より柔軟な集計ができる点にあります

• ここまで、FILTERを見ていきました。次にALLの説明をします

ALLの説明をします

• 前のレクチャーでは、テーブルをフィルターかけて縮小しました

• 実務では逆にフィルターをかけられたテーブルを大きくしたい時がある

• 例えば全体に対する割合を出すとき

• 商品名で括られた売上のテーブルを作ります。ここに全体に対する売上割合を出していきます

• また、スライサーの影響を受けないものを作ります。テストのために国名を示すスライサーを用意します

• このような、フィルターをかけられたテーブルを大きくしたい。フィルターを無視したい時にALLを使います

• これまでのページ名は、FILTERとして、新しいページを開きALLとします

• コピー 売上テーブルで見てみます

  • 【コピー/ALL】売上テーブル = ALL('売上データ1')

  • この結果は、単に元の売上テーブルと同じです

  • ポイントは、このテーブルはどんなフィルターやスライサーが入っても変化を受けない、ということです

• ALLで返すテーブルをSUMXで集計します

  • ALL売上合計 =

        SUMX(

            ALL('売上データ1'),

            '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

        )

• できたメジャーをテーブルに入れてみます。全部同じ値。しかも合計は一致しています

• また、国別スライサーでどう動くか？確認してみます。スライサーの影響を受けません

• 全体の金額が一致していることを確認

• では、全体に対する割合を計算する用意ができました。全体に対する割合を出します

  • 売上の全体に対する割合 = DIVIDE('売上データ1'[売上] , '売上データ1'[ALL売上合計])

• これにより無事、全体に対する割合が出せました

• 国別のスライサーをかけたとしても、ALL売上合計の値は変わらないため、常に全体に対する割合を出してくれます

ここでは補足として、総計のパーセントとの違いを示します

• 違いは「何を全体としてとるか？」です

• ALLは常に総合計をとります

• 統計のパーセントでは、その時のコンテキストを踏まえての合計をとります

ALLの応用として、扱っている商品の全体的な平均とその平均との差異を出します

• まず、商品の平均を計算するメジャーを作ります

  • 商品単価平均 = AVERAGE('商品マスター'[売価（円単価）])

• 次に、全体の商品の平均を計算するメジャーを作ります

  • 全体の商品平均価格 = CALCULATE('商品マスター'[商品単価平均],ALL('商品マスター'))

• 今度は、商品の平均と全体平均との差をとるメジャーを作ります

  • 全体平均との差異 = '商品マスター'[商品単価平均]-'商品マスター'[全体の商品平均価格]

  • ここで思わず、商品マスターの商品単価をとりがちですが、メジャー内はメジャーしかとれないことに注意です

• ちなみに、作ったメジャーがどのテーブルにいくか？は、メジャーツール / ホームテーブルで設定できます

• こうして作ったメジャーを並べてみると、価格帯の分布が見えてきます

ここでは、これまで学習したSUMX、FILTER、ALLを組み合わせて使ってみます

• ALLのページを複製し、FILTER x ALLというページを用意します

• FILTERとALLを組み合わせることで、特定の条件にあったテーブルを返し、それをSUMXで集計します

• ここでこう思った方いらっしゃるかもしれません「FILTERとALLって全く真逆な用途を持つDAX。組み合わせて何ができるのか？」と

• スライサーや、フィルターコンテキストなどを一旦リセットして、フィルターし直すイメージです

• コピー 売上テーブルで見てみます

  • 【コピー/FILTER x ALL】売上テーブル = FILTER(ALL('売上データ1'),RELATED('地域マスター1'[国名])="アメリカ")

• 実際にやってみます。例えば、前のレクチャーのケースで、今度はアメリカだけの中での % of totalで例示する場合

  • アメリカだけの売上合計 =

            SUMX(

                FILTER(

                    ALL('売上データ1'),

                    RELATED('地域マスター1'[国名])="アメリカ"

                ),

                '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

            )

• では、アメリカの売上合計に対する割合を出します

  • アメリカ売上に対する売上割合 = DIVIDE('売上データ1'[売上],'売上データ1'[アメリカだけの売上合計])

前のセクションでは、RELATEDというDAXを紹介しました。ここでは、RELATEDTABLEというTABLE FUNCTIONを紹介します

• RELATEDとの違い。RELATEDはたった１つの値を返します。RELATEDTABLEならテーブル自体を返すことができます

• さて、唐突ですが、モデルビューを見てみます。商品マスターと売上データは、One to Manyの関係になっています

• 前のレクチャーでは、売上テーブルに商品マスターからカラーの１列をもってきます

• One to Manyの関係では、RELATEDによりOneの方から、Manyの方へとデータをもってくることができます

• ちょうどVLOOKUP/XLOOKUPの要領です

• では逆にやるとどうなるか？できません。RELATEDをやろうとしても、候補すらでません

• なぜならRELATEDはたった１つの値しか返さないから、どの時の値を返したらいいか分かりません

• ここでRELATEDTABLEの出番になる。このDAXなら、テーブルごと返すことができる

• ここでは、実務で使えるケースである商品マスターに、1列足して売上データの結果を持っていきたい

• こうすることにより、商品マスターを見ることでその商品がどのくらいの売上を上げているか？が分かる

• 販売額 =

      SUMX(

          RELATEDTABLE('売上データ1'), '売上データ1'[受注数量]*'売上データ1'[売価（円単価）]

      )

• 数式の結果をレポートビューで、商品マスターで作る商品一覧の販売額と、売上データで作る販売額を作成し比較する

• 一致している

• RANKXで一列追加。RANXを列参照じゃなく、ネストで先ほど作ったものを入れる

• これは常に絶対的なランキング

• 国でスライサーをとり、国別にランキングの順位がどうなっているか？を見る

  • ランキングが抜けているなぜか？という視点が持てる例

COUNTROWS, FILTER, RELATEDTABLEを使ったDAXを作ってみる

• 今度は、RELATEDTABLEを使って、売上ではなく販売回数で見てみます

• COUNTROWSというDAXを使います。これにより指定のテーブルに何行あるのか？が数えることができます

• 売上、売上単価は注意がいきがちですが、では販売回数は？に着目します

• 商品マスターのテーブルをクリック

• ホーム / 新しい列

  • 販売回数 =

            COUNTROWS(

                RELATEDTABLE('売上データ')

            )

• 販売回数によるランキング列も加えてみる

• なぜ販売回数が少ないのに、売上が一位なのか？

  
  

このセクションでは、カレンダーマスターをDAXで作っていきます

• これまで通常であれば、エクセルでカレンダーマスターを用意していたと思います

• それ自体は何も悪くありません

• しかし、DAXで作るといとも簡単にできますので、ぜひみなさんのレパートリーに加えていただきたいと思います

• ここでは、カレンダーマスターを作る意義について復習します？

  • 複数のテーブルをつなげる時に必要

  • 抜けのないカレンダーが作れる（例. 売上がゼロなのとデータがないのとは違う）

  • より柔軟に日付データの加工が可能

• 逆に言えば、もしデータが一つのテーブルですべてまとまっており、日付の抜けなど気にしない、日付データに加工が不要であれば作る必要はない（でも、これから見ていくように作ること自体はすごく簡単なので、省略する理由がない）

• Power BIデフォルトで日付データはグルーピングがあるが、カレンダーマスターの方が使い勝手がよい

このレクチャーでは、あとで使うことになる日付情報に関連するDAXを紹介します

• しかし、あとのレクチャーで紹介するカレンダー関連のTABLE FUNCTIONと組み合わせることで威力を発揮するのでお楽しみに！

• データビューで売上データを選びます

• ホーム / 新しい列により、下記のDAXにより列を追加します

• MONTH/YEAR 該当の数字を返す

• WEEKDAY 曜日を数字で返す

  • WEEKDAYでは数字のオプションについて（例. 日曜日が１か月曜日が１か）

• 他のレクチャーで、ゼロからカレンダーを作った後に、上述のDAXを使ってカレンダーテーブルを充実させる

• ここでは一部のみ紹介しました。ポイントは、日付という一つのパラメータを使って、いろいろなヘッダーが切り取れるということです

ここからDAXによってカレンダーマスターを作っていきます。まずはCALENDARAUTOから

• ホーム / 新しいテーブル

• CALENDARAUTO() を使うと今入っているデータの日付を見て、一番小さいのと大きい日付の年を見て、1月1日から12月31日までをとる

• 列を選択し、列ツールで書式を変更する。普通は時間は邪魔

• 一番手っ取り早い

• もし売上データしかなければ使えるかも

• 列を選択し、画面下のデータ数をみてみる。1096個

• 今回の日付のレンジは2016-2018年で2016年はうるう年。なので366日+365日+365日=1096日で抜けがない

• しかし、顧客マスターで誕生日などあると、一気にレンジが広くなる

  • レポートビューで、データの入力を使って、販売データとはかけ離れた日付データを入れてみる

• パラメータとして、終わりの月をとることができる。例えば決算月

• CALENDARAUTO(3)と入れると、データモデル中の日付範囲で、開始が4月1日、終わりが3月31日のレンジを返す

• なので、もし、扱うデータの日付範囲が関連のある範囲しかなければ、これが最も簡単。しかし１つでも外れ値があるとそちらに引っ張られるので注意

カレンダーマスターを作る時に使って欲しい大本命である CALENDAR について説明します

• 結論から言うと、このレクチャーの後に、もっと便利で洗練されたやり方を説明します

• しかし、このレクチャーで紹介するやり方を知っていれば、もっともやりたいことである、カレンダーマスターを作ること、自体は完了してしまいます

• ですので、CALENDARAUTOだと、範囲が広すぎる、この後に続くレクチャーの内容が、ちょっとすぐには使えないかなぁと思ったら、とりあえずこのレクチャーで紹介するやり方だけ覚えてください

• 例えば、2016年1月1日から2018年12月31日までのカレンダーを作りたいとします

• データビューにいきます

• ホーム / 新しいテーブル

• カレンダー by CALENDAR_Static = CALENDAR(DATE(2016,01,01),DATE(2018,12,31))

  • 注意。個人的に、 CALENDAR(2016/01/01, 2018/12/31)とやってしまいがち。実務でよくやってしまってた

  • Power BIのエラーメッセージでさえ変になってる

• しかし、このカレンダーはダイナミックではありません

• 実務では「とりあえずこのデータ」と加工していくと、過去データが出てくることがありますし、当然データは日々更新されていきます

• 次のレクチャーではCALENDARを使いつつ、ダイナミックに範囲を変える方法を見ていきます

ダイナミックに範囲をとるカレンダーをDAXを使って作成します

• まず前のレクチャーで作ったDAXをコピーします

• テーブルツール / 新しいテーブル

  • カレンダー by CALENDAR_Dynamic_1 = CALENDAR(

                            MIN('売上データ '[販売日]),

                            MAX('売上データ'[販売日])

                            )

• これだと、一番最初の日と一番最後の日をとる

• 今回の場合だと、たまたま抜けがないが、一番最初と最後の日付が中途半端な日、例えば2月3日と12月28日

• これでもできるが、1日から始まらず折れ線グラフなど作ると見栄えが悪い

• せっかくMINとMAXを使っているのでこれを利用し、対象範囲の1月1日から12月31日が出るようにする

• テーブルツール / 新しいテーブルで再度新しいテーブルを作ります。Calendar_2のDAXをコピペして、これをベースに作成します

• カレンダー by CALENDAR_Dynamic_2 = CALENDAR(

                          DATE(YEAR(MIN('売上データ'[販売日])),1,1),

                          DATE(YEAR(MAX('売上データ'[販売日])),12,31)

                          )

• 試しに、元データに2015/12/31と2019/01/01のデータを挿入してみます

DAXで作ったカレンダーマスターに情報を加えていき、使いやすいカレンダーマスターを作ります

• カレンダー by CALENDAR_Dynamic_2を拡充します

• YEAR, MONTHによりカレンダーを充実させていきます

• テーブルツール / 新しい列

• MONTH NUMBER を入れます

• WEEK NUMBERを入れます。調整して月曜日が１とします

• JAN, FEBといった月の名前を入れます

  • FORMAT Dates "mmm"

• MON, TUEといった曜日を入れます

  • FORMAT Dates "ddd"

• IF関数を使って、平日か週末か？を入れます

  • Weekday_or_Weekend = IF('カレンダー by CALENDAR_Dynamic_2'[Weekday] >= 6, "Weekend", "Weekday")

• 同じくIF関数を使って、暦とは異なる会計年度を入れます

  • Fiscal Year = IF('カレンダー by Calendar_3'[Month] <= 3, 'カレンダー by Calendar_3'[Year]-1, 'カレンダー by Calendar_3'[Year])

• なぜMONTH NUMBERや、WeekNumberを入れたのでしょうか？

• 理由は、次のレクチャーで説明します

ここでは、なぜ、月の名前があるのに月の番号を、そして曜日の番号を入れたのか？を説明します

• レポートビューでテーブルや折れ線グラフを作り、月の名前を入れてみます

• 並びがぐちゃぐちゃになります（同じことが曜日名にあてはまる）

• データビューで並びを設定します

• 該当列を選択し、列ツール / 列で並び替え

• これで並びは解決しました

• これは、曜日や月だけでなく、例えば事業部名でもできます

• でも、テーブルの数字が同じ数字なのはなぜでしょうか？

• 答えはあっけないです。でも実務でよく起こり、その時はなぜ？とフリーズしがちです

• 次のレクチャーで発表します

前のレクチャーの種明かしをします。テーブルの数字が同じ数字なのはなぜか？

• リレーションシップを組んでないからです

• ばからしく思うかもしれませんし、いい加減引っ張りすぎ！と思うかもしれません

• 実務ではよくあります。印象に残っていると忘れません

• エクセルでカレンダーマスターを作った時は、データの取り込みをするのでその一環でリレーションシップを組むが、なぜかDAXで作った場合は忘れがち

• あえてここで紹介したので、これを見たみなさんは、実務で同じことが起こったらすぐピンときます