見出し画像

PowerBI -複数のテーブルをソースにして、ボタンで集計地を切り替える方法

今回やりたいことのイメージを確認します。

下記リンク先のレポートは、右上にあるボタン「Total Sales」と「Profit」で、表示させるグラフの集計対象を切り替えることができます。

画像1

今回は、この例における「Total Sales」と「Profit」が
別々のテーブルに記録されているような場合に、どうやって切り替えを行うかについてです。

1つのテーブルに切り替えたい情報のすべてが入っている場合は簡単

1つのテーブルの1つの列にすべての値が入っている場合であれば、もっと簡単です。
(1つのテーブルの複数の列に切り替えたい全ての情報が入っている場合でも、メジャーを少し工夫すればよいだけなので、簡単です)

よって、わざわざ今回書こうとしているやり方をする必要はないです。

一応、なぜ「簡単」なのかというと、
そのテーブルにある情報だけを使って、
特定の1つの列でフィルターを作り、
特定の1つの列を参照したメジャーを作れば、
簡単に作成できるからです。

文章で書くと分かりづらいかもなので、、、
例えば、こんな構成のテーブルは、簡単です。

画像2

このテーブルをPower BIに取り込んで、
(他にDimension Table なども作る必要はなく、)
切り替えボタン付きのレポートができます。

こんな感じです。

画像3

一応ここは本題ではないので、、
この簡単な方の作り方は割愛します。

2つ以上のテーブルに分かれる情報を、1つのグラフ上で切り替える場合

例えば、切り替えボタン付きのレポート(グラフ)を作りたいのに、
そのレポート(グラフ)に乗せるべき情報が複数のテーブルに分かれていたら、なんらかの工夫をしないとできないです。

工夫の仕方は1つではありませんが、
今回はHASONEVALUEとSWITCHを使った切り替えボタンの作り方を説明します。


使用するテーブルは、先ほど一つだったテーブルが、
Total SalesテーブルとProfitテーブルに分かれているものとします。

画像5


このサンプルデータをそれぞれPower BIに取り込むのですが、
取り込みと同時に、
「date」列がそれぞれにあるので、「日付テーブル」も作ってそれぞれの列を繋ぎます。

画像5


先ほどの簡単な例では、
1つのテーブルにすべての情報が存在していて、
その中には、切り替えボタンを作るための情報(Total SalesとProfit)も1つの列にありました。

けれども今回の例では、
「Total Sales」と「Profit」という文字列の情報は存在しません。
(記録先である各テーブルが、その意味を含んでいるので、
実務上も普通はわざわざ列を設けてその情報は書き込まないと思います)


なので、もう1つ、Filter用のテーブルを作ります。
そして、このFilter用のテーブルには、リレーションシップを設定しません。

(これまでの僕の経験では、、フィルターのために作ったテーブルには、リレーションシップを設定したことがないです。多分、フィルター専用のテーブルにリレーションシップは必要なし!と覚えておくとよいと思います(たぶん)。)

フィルター用のテーブルを作るために、
レポート画面>ホーム>データの入力 から
次のように「Total Sales」と「Profit」が一つの列に入ったテーブルを作ります。

画像6

Filter用のテーブルにはリレーションシップを設定しないので、
こんな感じでぽつんと存在することになります。

画像7


以上の作業が終わったら、

ひとまず、レポートを途中まで作ってみます。

レポート画面の「視覚化」の中にある、
「スライサー」をレポート内に配置して、
FilterTableの「Total Sales」と「Profit」と書いた列をぶっこみます。

画像8

単純にぶっこむだけだと、見栄えがボタンっぽくないので、
「スライサー」を選択した状態で、「書式」>「全般」>「方向」から、「横」を選択します。
するとボタンっぽくなります。

画像9


つづきまして、、
折れ線グラフを作ります。
「視覚化」の中のグラフアイコンから、「折れ線グラフ」を選択します。

そして、「軸」に日付テーブルの「date」を入れておきます。

画像10

これであとは、この「折れ線グラフ」の「値」に入れるべきメジャーを作成すればよい状態になりました。


やりたいことを念のため確認しておくと、
次の集計をしたいということです。

1.Profitボタンを押したら、折れ線グラフにProfitの推移だけ出す
2.Total Salesボタンを押したら、折れ線グラフにTotal Salesの推移だけ出す
3.両方のボタンが押されていたら、ProfitとTotal Salesの合計の推移を折れ線グラフに出す。

(※3だけ非現実的な前提を置いてしまうのはすみません、今回はTotal SalesとProfitでしたが、製品Aと製品Bというような切り替えを行う場合は、「じゃぁ製品Aと製品Bの合計は?」と聞かれても、両方選択すれば表示できるので、そこを伝えたい目的です。)

メジャーは次のように作ります。

Total_Amount
   = IF (
       HASONEVALUE ( 'Filter Table'[identification] ),
       SWITCH (
           TRUE (),
           SELECTEDVALUE ( 'Filter Table'[identification] ) = "Total Sales", SUM ( TotalSalesTable[Amount] ),
           SELECTEDVALUE ( 'Filter Table'[identification] ) = "Profit", SUM ( ProfitTable[Amount] )
       ),
       SUM ( TotalSalesTable[Amount] ) + SUM ( ProfitTable[Amount] )
   )

式の中で使っている各関数の役割は、

IF() :(Total Salesだけ、とかProfitだけではなく)2つとも選択された場合に、2つの合計を算出するために使用しています。

HASONEVALUE() :スライサー上で選択された値が1つだけである場合(One valueだけ持っている(Have/Has)場合)に、True()を返しますので、
この式中では、『1つだけ選択されている場合は、SWITCH()の式の結果を返し、そうでない場合は、SUM+SUMで表現されている式の結果を返す』ということになります。

SWITCH() :SEITCH関数は、最初にTRUE()を持ってくる書き方と、別の判定式を持ってくる場合で大きく2パターンの書き方がありますが、
ここでは、前者の先にTRUE()を持ってくる書き方をしています。
この書き方をすることで、第2引数がTRUE()である場合は、第3引数の結果を返し、第4引数がTRUE()である場合は、第5引数の結果を返す、という意味合いになります。
詳しくはこちらを・・・


作成したメジャーを「折れ線グラフ」の「値」にぶっこむと、
目的のレポートができます。
(変化がわかりやすくなるように、「折れ線グラフ」>「書式」>「図形」から、「ステップ」をオンにしています)

Profitを押すと・・・

画像11

Total Salesを押すと・・・

画像12

(Ctrlを押しながら)両方選択すると・・・

画像13

うまく機能していますね!




Power BI でレポートに必要なメジャーをその都度作成していると、
たまに「このメジャーどうやって作ったっけ?」となることがあるので、
ここにメモしました。

でわ


(追記)もしボタンの選択肢を3つ以上にしたい場合は、
・テーブルを統合する
・メジャーの中でIFやSWITCHを用いてすべての組み合わせに対応する
等の対応を行えば、2つだけ選択された場合などにも対応できます。
でも、ボタンが3つ以上になる場合は、スライサーの書式>選択範囲のコントロール>単一選択 をオンにして、2つ以上を選択させないように制御しておくのが一番手っ取り早いです。

この記事が気に入ったらサポートをしてみませんか?