SQL Server のことなら SQL Quality SQL Server パフォーマンス チューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム > 技術情報 > Power BI 自習書 No.1 無料で利用できる Power BI を試しみよう

Microsoft Power BI 自習書シリーズ (HTML 版)
「No.1 無料で利用できる Power BI を試しみよう」

松本美穂と松本崇博が執筆した Power BI 自習書シリーズの「No.1 無料で利用できる Power BI を試しみよう」の HTML 版です。 日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

6.10 DAX による実践的な計算式の追加

DAX(Data Analysis Expressions)は、前出のオープンデータで市町村別の人口を取得する際に、「[都道府県名] & [市町村名]」という文字列連結で利用したり、SQL Server からのデータ取得の際に、「[単価] * [数量]」という乗算(売上金額の計算)で利用したりしました。

DAX は、Power BI の以前のバージョンで提供されていた「PowerPivot」ツールに搭載されていた機能で、現在の Power BI では、Power BI Desktop での[データ ツール]として実装されています。

00516

DAX Power Query Formula Language は、文字列連結や乗算などであれば、どちらを利用しても大丈夫で、似たような機能になりますが、大きな違いは、DAX では、「メジャー」と呼ばれる計算式を定義できる点です。メジャーは、合計(SUM)や平均(AVG)、最大値(MAX)、最小値(MIN)、カウント(COUNT)、個別のカウント(DISTINCT COUNT)などで、さらに DAX の組み込み関数CALCULATE や、PREVIOUSYEARDATEADD など)を利用することで、構成比率や、前年比前年金額などを計算していくこともできます。

00517

◆ メジャーの確認(暗黙的なメジャー)

これまで説明してきたグラフでは、自動的に「合計」が計算されているものを利用してきました。このように、合計が自動計算されるものは、(内部的には)暗黙的なメジャーが利用されています。これを確認してみましょう。

1.ここでは、SQL Server からの接続で利用した NorthwindJ データベースから取得したデータで試してみます。次のように[フィールド]ペインから[区分名]と[売上金額]を追加すると、自動的に売上金額の「合計」が計算されています。

00518

2.自動的に計算されていることを確認するには、次のように[視覚化]ペインで[]に配置した[売上金額]の[▼]ボタンをクリックします。

00519

これが[合計]になっている場合は、自動的に合計が計算されるようになります。ここでは、平均(AVG)や最小(MIN)、最大(MAX)、カウント(個別)(DISTINCT COUNT)、カウント(COUNT)を選択することで、別の計算方法を選択することができます。

3.次に、[平均]を選択して、売上金額の平均が計算されることを確認してみましょう。

00520

平均を選択すると、売上金額の平均が計算されることを確認できます。

◆ DAX でメジャーを作成(事前に平均などを計算しておく)

1.次に、DAX を利用して、メジャーを作成して、事前に売上金額の平均をしてみましょう。これを行うには、まず[データ]をクリックして、[データ ツール]を開きます。

00521

データ ツールが開いたら、[フィールド]ペインで[受注明細]テーブルを選択します。

2.次に、[新しいメジャー]をクリックします。

00522

これで、DAX 式が入力できるようになるので、平均を計算することができる AVERAGE 関数を利用するために、「AV」と入力します。これで、インテリセンス(入力補完)機能が働いて、AVERAGE 関数が選択候補に表示されるので、これを選択して、Tab キーを押下します。

3.次に、どの列を対象とするのか、テーブルと列の一覧が、「'テーブル名'[列名]」形式で表示されます。

00523

ここでは、「'受注明細'[売上金額]」を選択します。DAX 式では、テーブル名は「'」(単一引用符)で囲んで、列名は [ ] で囲む決まりがあります。なお、DAX 式では「RELATED」という関数を利用すれば、(リレーションシップのある)別のテーブルの列を指定することもできます。

4.最後に「)」を入力して、引数を閉じれば、メジャーの作成が完了です。

00524

5.メジャーの名前を変更するには、次のように「Measure =」の部分を変更したい名前に置き換えます。

00525

6.変更後、[レポート]をクリックして、レポート デザイナーに戻って、[フィールド]ペインで[受注明細]テーブルを展開すると、作成した[売上金額平均]メジャーが表示されることを確認します。

00526

これをテーブルに追加すると、平均が計算されていることを確認できます。

このように、DAX 式を利用して、メジャーを作成しておくことで、合計以外の計算(平均や最小、最大、カウント、個別のカウント)を事前に行っておけるようになるので、大変便利です。

7.このようなメジャーは、次のようにも利用できます(前出のメジャー リーガーの 2014年の投手成績の例)。

00527

事前に、防御率(ERA)や奪三振(SO)、与四球(BB)に対する「平均」を計算するメジャーを作成しておくことで、最終行に、全選手の平均を表示しておけるようになります。

◆ DAX で構成比率

次に、各商品区分の構成比率(各商品の受注金額が全体の何%なのか)を計算してみましょう。

1.構成比率は、各商品区分の受注金額を「全体合計」で割り算すれば良いので、データ ツールを開いて、DAX 式を次のように記述します。

構成比率 = SUM('受注明細'[売上金額]) / CALCULATE(SUM('受注明細'[売上金額]), ALL('商品区分'[区分名]))

00528

CALCULATE という関数を利用すると、第2引数に「ALL('商品区分'[区分名])」と指定することで、全体合計(商品区分全部の合計)を計算することができます。

また、作成した「構成比率」メジャーは、「0.166」のような形で 10進数形式の結果になるので、[書式]を[パーセンテージ]に変更しておくことで、「16.6%」のように表示できるようになります。。

2.次に、[レポート]をクリックして、レポート デザイナーに戻って、「構成比率」メジャーを追加してみます。

00529

構成比率」(各商品の受注金額が全体の何%なのか)が正しく計算できていることを確認できます。

◆ DAX で前年金額(PREVIOUSYEAR 関数)

次に、前年金額を計算してみましょう。

1.前年金額を取得するには、データ ツールを開いて、DAX 式を次のように記述します。

前年金額 = CALCULATE(SUM('受注明細'[売上金額]), PREVIOUSYEAR('日付'[Date]))

00530

CALCULATE 関数では、第2引数に「PREVIOUSYEAR」関数を指定することで、前年のデータを取得することができます。なお、「PREVIOUSYEAR」関数の部分は、次のように「DATEADD」関数を利用しても同じ結果(1年前のデータ)を取得することができます。

前年金額 = CALCULATE(SUM('受注明細'[売上金額]), DATEADD('日付'[Date], -1YEAR))

2.設定後、[レポート]をクリックして、レポート デザイナーに戻って、「前年金額」メジャーを追加してみます。

00531

マトリックス]グラフを選択して、[]に[区分名]、[]に[日付]テーブルの[Year]列を配置して、[]に[売上金額]と[前年金額]を配置することで、前年金額を確認することができます。

◆ DAX で商品分類を作成(文字列のワイルドカード検索を行う SEARCH 関数)

DAX では、SEARCH 関数を利用して、文字列検索(文字列のワイルドカード検索)を行うこともできます。

1.まずは、データ ツールを開いて、SEARCH 関数を利用してみましょう。ここでは、[商品]テーブルを選択してから、[新しいメジャー]ではなく、[新しい列]をクリックして、DAX 式を次のように記述します。

Column  = IFERROR(SEARCH("果汁", '商品'[商品名]), -1)

00532

商品名」列に「果汁」という文字が含まれるデータには「1」が返り、その他のデータの場合は「-1」が返ることを確認できます。

2.この SEARCH 関数を IF OR と組み合わせて利用すると、次のように文字列で検索して、商品分類を作成できるようになります。

00533

分類 = IF OR(IFERROR(SEARCH("果汁", '商品'[商品名]), -1) >= 1,
               IFERROR(SEARCH("清涼", '商品'[商品名]), -1) >= 1), "ジュース",
         IF ( IFERROR(SEARCH("コーヒー", '商品'[商品名]), -1) >= 1, "コーヒー",
            IF ( IFERROR(SEARCH("ビール", '商品'[商品名]), -1) >= 1, "ビール",
              IF ( IFERROR(SEARCH("ワイン", '商品'[商品名]), -1) >= 1, "ワイン",
                   "その他"
                 )
               )
            )
         )

商品名に、「果汁」または「清涼」というキーワードが含まれている場合には「ジュース」に分類、「コーヒー」が含まれているなら「コーヒー」、「ビール」が含まれているなら「ビール」、「ワイン」が含まれているなら「ワイン」、それ以外のデータは「その他」に分類するようにしています。

3.DAX 式を記述したら、[レポート]をクリックして、レポート デザイナーに戻って、「分類」列を追加してみます。

00534

分類ごとの売上金額の合計を計算できていることを確認できます。

このように、DAX(Data Analysis Expressions)式を利用することで、既存のメジャーをもとに計算を行ったり、関数処理を行ったり、IF や OR 演算を行ったりできるようになるので、大変便利です。

◆ DAX の参考情報

DAX の詳細については、以下の Power Pivot のヘルプ ページが参考になると思います。
http://msdn.microsoft.com/ja-jp/library/gg399181(v=sql.110).aspx

00535

目次へ | 前のページへ | 次のページへ

事例1

MPNロゴ


SQLQualityは執筆とセミナーを通じて技術の啓蒙やエンジニアの育成支援も行っています
最新刊
SQL Server 2012 の教科書
SQL Server 2012 の教科書(ソシム)

弊社オリジナル制作の
SQL Server 2012 自習書も
マイクロソフトのサイトで公開中!
ロングセラー
ASP.NET でいってみよう  SQL Server 2000 でいってみよう
ASP.NET でいってみよう
第7刷 16,500 部発行
SQL Server 2000 でいってみよう
第12刷 28,500 部発行


セミナー風景
セミナー風景

弊社執筆の
SQL Server 2012 自習書
マイクロソフトのサイトで公開中
全30冊
ダウンロードはこちら
弊社執筆の
SQL Server 2008 R2 自習書
マイクロソフトのサイトで公開中
全30冊
目次はこちら
松本美穂のコラム
(公開活動などのお知らせ)

第 51 回:PASS Summit と MVP Summit で進化を確信!
第 50 回:新しくなった Power BI(2.0)の自習書を作成しました!
第49 回:Excel 2016 の Power Query を使う
第 48 回:新しくなった Microsoft Power BI ! 無料版がある!!
第 47 回:「Microsoft Azure SQL Database 入門」 完成&公開!
第 46 回:Microsoft Power BI for Windows app からの Power BI サイト アクセス
第 45 回:Power Query で取得したデータを PowerPivot へ読み込む方法と PowerPivot for Excel 自習書のご紹介
第44回:「SQL Server 2014 への移行とアップグレードの実践」ドキュメントを作成しました
第43回:SQL Server 2014 インメモリ OLTP 機能の上級者向けドキュメントを作成しました
第42回:Power Query プレビュー版 と Power BI for Office 365 へのクエリ保存(共有クエリ)
第41回:「SQL Server 2014 CTP2 インメモリ OLTP 機能の概要」自習書のお知らせです
第40回: SQL Server 2012 自習書(HTML版)を掲載しました
第39回: Power BI for Office 365 プレビュー版は試されましたか?
第38回: SQL Server 2014 CTP2 の公開
第37回: SQL Server 2014 CTP1 の自習書をご覧ください
第36回: SQL Server 2014 CTP1 のクラスター化列ストア インデックスを試す
第35回: SQL Server 2014 CTP1 のインメモリ OLTP の基本操作を試す
第34回: GeoFlow for Excel 2013 のプレビュー版を試す
第33回: iPad と iPhone からの SQL Server 2012 Reporting Servicesのレポート閲覧
第32回: PASS Summit 2012 参加レポート
第31回: SQL Server 2012 Reporting Services 自習書のお知らせ
第30回: SQL Server 2012(RTM 版)の新機能 自習書をご覧ください
第29回: 書籍「SQL Server 2012の教科書 開発編」のお知らせ
第26回: SQL Server 2012 の Power View 機能のご紹介
第25回: SQL Server 2012 の Data Quality Services
第24回: SQL Server 2012 自習書のご案内と初セミナー報告
第23回: Denali CTP1 が公開されました
第22回 チューニングに王道あらず
第21回 Microsoft TechEd 2010 終了しました
第20回 Microsoft TechEd Japan 2010 今年も登壇します
第19回 SQL Server 2008 R2 RTM の 日本語版が公開されました
第18回 「SQL Azure 入門」自習書のご案内
第17回 SQL Server 2008 自習書の追加ドキュメントのお知らせ
第16回 SQL Server 2008 R2 自習書とプレビュー セミナーのお知らせ
第15回 SQL Server 2008 R2 Reporting Services と新刊のお知らせ
第14回 TechEd 2009 のご報告と SQL Server 2008 R2 について
第13回 SQL Server 2008 R2 の CTP 版が公開されました
第12回 MVP Summit 2009 in Seattle へ参加

技術コミュニティでも活動中

Microsoft MVP for SQL Server

松本美穂松本崇博

松本崇博 Blog(SQL Server Tips)
松本美穂ブログ(SQL Serverノート)