カテゴリー別アーカイブ: 裏技

リレーションのツボ~クロスフィルターの方向を単一もしくは双方で調整

【クロスフィルターの方向を調整すると、リレーションの罠を回避することができます】

Power BIを始める時に壁に当たりやすいのが「リレーション」です

リレーションがスパゲティ状態になり、よくわからなくなったことがある人もいらっしゃるのではないでしょうか?

今回の記事ではこのスパゲティ状態を解読する、回避するヒントを「クロスフィルター」の観点で解説したいと思います

尚、当記事は理想のリレーションを作成する方法を解説しようとする意図ではなく、あくまでスパゲティ状態を解読、回避するヒントを「クロスフィルター」に絞って解説しようとしているということを予めご了承お願いします

ポイント

VLOOKUP関数はエクセル関数の中でも代表的な関数です

Power BIのリレーションも基本的には同じ内容です

ただ違う点が1点あります

それは影響範囲です

VLOOKUP関数を使用すると関連するのは2つの表のみです

VLOOKUP関数を使用した表とVLOOKUP関数が参照した表です

Power BIの場合は違います

例えば、次にょうなリレーションがあったとします

上の画像では「店名」と「来客数」はつながっていません

ところが、店名から作成したスライサーを動かすと来客数のマトリックス表も動きます

ここでもう一度、リレーションを確認してみましょう

「店名」と「店長」間は双方向でつながっています

店名から店長に影響を与えることもできるし、店長側から店名に影響を与えることができます

つまり、「店名⇒店長」の関係は存在します

更に「店長」と「来客数」の関係を見てみましょう

「店長」と「来客数」は単一の方向でつながっています

つまり「店長⇒来客数」の関係は存在します

ですので、「店名⇒店長⇒来客数」すなわち「店名来客数」の関係が存在するのです

ここがVLOOKUP関数と違う点です

間接的につながりが表の間で広がっていくようになっています

矛盾の排除

リレーションをいじっていると、思ったようにテーブル(表)の間でリレーションが作成できないケースがあります

それはポイントで解説した「影響の拡大」により矛盾が生じている可能性あります

前述のリレーションを次のように変えてみましょう

「店名」と「店長」間のリレーションを削除し「店名」と「来客数」間でリレーションを作成しました

こうなると、店名と店長のスライサー間ではシンクロが発生しません

ここで、店番と店長間でリレーションを作成しようとすると「破線」になりリレーションが作成できません

これがリレーションの矛盾です

店番と店長間でリレーションができると、来客数へのパス/通り道が2つ存在してしまうからです

ではどうするか?

店名と来客数間のリレーションを削除し、ポイントで解説した内容に戻すことももちろんできます

次の内容では、「店名」と「来客数」のリレーションを残したままで「店名」と「店長」間をリレーションしたいと思います

単一⇒双方向

今、店長と来客数間は単一の方向でクロスフィルターが行われています

方向は「店長⇒来客数」です

こちらを双方向にします

すると来客数を通じて「店番⇒店長」間のリレーションが間接的に成立します

これで2つのスライサーもシンクロします

<まとめ>

今回はリレーションを使用していて陥りやすい箇所を、クロスフィルターの観点で回避する方法を解説しました

エクセルワークシート関数のVLOOKUP関数を使用するのと違い、Power BIでは影響箇所が間接的に飛び火していくのも大きなポイントです

逆に飛び火を利用できるようになると、Power BIを使いやすくなります

そうはいっても、まだまだリレーションの箇所は様々なポイントがまだまだあります

今後、また別な機会で「多対多」のリレーションなども含めて解説していきたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

スライサーではできない操作をパラメーターで行う~メジャー連動~

【パラメーター機能の内容をメジャーに組み込めば、スライサーではできないような操作ができることがあります】

Power BIにパラメーターという機能をご存じでしょうか?例えばグラフの表示数が多すぎて、重要な数字が読み取れないことがあります。そんな時はパラメーターでグラフの表示数を変えることができます

今回の記事ではパラメーターとメジャーを組み合わせた上記の事例を紹介します

パラメーターとは?

パラメーターは一見スライサーと似ていますが、設定するのはあくまで数字の範囲となります

パラメーターはモデリングタブから追加することができます

実際に追加する時には「数値範囲」を指定します

次に開く画面では、出力する数値範囲を設定します

パラメーターを設定すると同時に専用のテーブルも作成されます

メジャーの設定

パラメーターを設定したら、メジャーの中にパラメーターで出力される値を組み込みます

折れ線グラフの各内容はそれぞれメジャーになっています

こちらにIF式を追加します

IF式にはパラメーターとの連動を組み込みます

このIF式によりパラメーターの値が1になった場合には、BLANK=グラフ表示なしになります

<まとめ>

今回はパラメーターを使用してメジャーを操作し、グラフの表示本数をコントロールできるようにしました

パラメーターの操作は今回紹介した「数値範囲」に加えて「フィールド」というのがあります

フィールドには直接メジャーを設定することができます

この場合はパラメーターをスライサーのように使用できます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

マトリックス表の見栄えをカスタマイズする

【ピボットテーブルのようにマトリックス表も自在に並びを変えれるようにする】

マトリックス表については「こんな風にできないか?」という問い合わせが何件かありましたので、まとめて記事にしました

まず一点目は合計の並びを変えられないかということです

通常、合計は一番右もしくは一番下になります

但し、合計がスクロールしないと見れないような大きい表の場合、まず最初に合計を見れた方がいい場合もあります

これが1点目です

2点目は値を並べる場合に、並びを横ではなく縦にできないか?ということです

確かに、並びが横よりも縦の方が見やすいケースもあります

それでは、上記の2点に分けてそれぞれを解説していきます

合計の位置を変える

テーブル作成

合計の位置を変えるには、下の画像のような並び替え用のテーブルを作成するところからはじめます

真ん中のManyの列で他のテーブルとリレーションを組みます

Oneの列では「Total」にManyの全ての内容が集約されるようにします

そして、OrderでOneの列の「Total」が1番目に並ぶように順番を指定します

リレーション

作成したテーブルは他テーブルとリレーションを作成します

列の並び替え

値を集約した「Total」が一番左(上)にくるように列の並び替えをします

これで、並べ替え用のテーブルをマトリックス表の列や行に配置すれば完成です

縦に並び変える

マトリックス表は通常では次の画像のように値が横に並びます

こちらの横並びを縦並びに変えるには「ビジュアルの書式設定」で「値のオプション」を操作します

上の画像の「値を行に切り替え」をオンに変えます

そうすれば並びが縦に変ります

<まとめ>

今回はマトリックス表をカスタマイズする方法を解説しました

Power BIの場合には機能が多く「ビジュアルの書式設定」もとても分かりにくいです

調べた結果「ビジュアルの書式設定」でできることもよくあります

ただ列や行の並び順については、どうしても「ビジュアルの書式設定」では解決できず、別テーブルを作成することになります

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

グラフの並び順を変える3つの方法~条件列、DAX、リレーション~

【グラフのX軸が思うように並んでくれない、そんな悩みは今回の記事で解決できます】

Power BIのグラフの並び変更は、通常だとグラフの右上の三点リーダーをクリックした後に下記の画像の箇所で変更します

今回は上の並べ方では解決できない場合の対処方法を3つ紹介します

いずれの方法もデータビュー画面・列ツールにて「列で並べ替え」機能を使用します

まずは「1.並び変えたい列を選択」した後「列ツール」の「列で並べ替え」にて、「2.順位を記述した列」を指定します

読者の方の中には「その方法は知っている、だけどDAXを書くとエラーになった」という方もいらっしゃると思います

今回の記事ではエラーを回避する方法も解説しています

やりたい事

今回使用するデータは次の画像のデータです

こちらの商品の並びを次のようにします

A⇒B⇒C⇒D⇒E ➡ C⇒D⇒E⇒A⇒B

DAX1

一つ目の並べ替え方法はDAXのSwitch関数で変える方法です

下は別データで並び変えた事例です

*過去記事⇒こちら

上の画像ではMONTHの並び順を1月ではなく、4月からはじまるように変えています

では、今回の場合はどうかというと、次のようなSwitch関数の式を書きます

ところが、今回の場合は実際に並び替えるとエラーが発生します

前述の月の並び替えの場合は、並び替え対象が「MONTH列」で順位列の式が「順位 = Switch(MONTH([Date]),1,10,・・・」となっています

つまり、Switch関数の中身が「並び替え対象」ではなく「Date列」となっています

今回の場合は、Switch関数の中身に「並び替え対象」が入っています

順位_DAX = SWITCH(‘Data'[商品],・・・

ですので、次のようなエラーがでます

これはSUM関数をSUM関数の対象範囲に含めるような形になります

ですので、今回は別な方法も紹介します

条件列

Power Queryエディタ画面で条件列を作成する方法も使えます

この条件列にて順位を指定すると次のようなグラフになります

別テーブルを手動で作成(DAX2)

最後に紹介する方法はこちらの画面:データの入力でテーブルを作成します

内容はこんな形で入力します

こちらで作成したテーブルはリレーションも組んでおきます

次にデータビュー画面でDAX(RELATED関数)を作成し、順位列を作成します

この順位列であれば循環の問題は発生しません

<まとめ>

今回は、グラフの並び順を変える方法を3つ紹介しました

DAXで順位列を作成する場合には「循環参照」の問題は常に意識しておきましょう

個人的にはPower Queryエディで条件列を作成するより、DAXで作成した方が手軽だと感じています

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

DAXを使用してデータが無い日付のデータも網羅して表示する方法

【DAXを使用して、データが無い日付のデータをカレンダーテーブルと組み合わせて表示できるようにします】

日付別のデータを表示する時に、データが無い日付も軸上に表示したいときがあります

上記のグラフだと3月4~5日が表示されていません

これは3月4~5日にデータが無いからです

エクセルの場合だと自動的に空白データを表示してくれます

Power BIの場合だと自動的に「表示しない」という判断をされてしまいます

今回は簡単なDAXでPower BIが自動判断した「空白」を埋める方法を紹介します

カレンダーテーブルの準備

まずは「空白のデータ」も網羅したカレンダーテーブルを用意します

こちらのカレンダーテーブルがグラフのX軸になります

空白を0で埋めるメジャーの作成

カレンダーテーブルを作成したとしても、通常のCALCULATEやSUMなどの関数では空欄を埋めることができません

ですので、空欄の場合には0で埋めるメジャーが必要です

一番簡単なのは次の式になります

金額_空白対応 =

VAR Sales=SUM(Sheet1[金額])
RETURN if(Sales,Sales,0)

変数:Salesに売上金額を格納した後にIF式により、Salesに値が無い場合には0を埋める式になっています

このメジャーをグラフの値に使用すれば、日付の空白が埋まるようになります

<まとめ>

今回は簡単なDAX式で空白を0で埋めることにより、データが無い日付を網羅したグラフを作成しました

ポイントとしては、カレンダーテーブルを使用する点とメジャーを作成する点です

別な方法としてはカレンダーテーブルに売上の列を作る方法もあります

この際もif式により空白の日付を0で埋める必要があります

ただDAXの方がシンプルにレポートを作成することができます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

レポートで説明文章を更新データに合わせて可変にする方法

【更新データを手動で書いたような文章で自動表示する方法を解説します】

データの更新に合わせてPower BIのレポート上の文章を可変にするには、メジャーの作成に加えて「改行」などの書式の工夫が必要です。今回は最終月の売上を表示する事例に基づいて様々なテクニックの解説を行います

やりたい事

売上データの毎月の報告を自動化するのが一番やりたいことです

「〇年×月の売上は△△△でした」

上の文章内の「〇年×月」、売上「△△△」をメジャーで自動取得できるようにします

書式の方も「月」と「の売上」の間に改行を入れつつ、売り上げもきちんと桁区切りにします

なお、今回使用するデータ/OrderDataは以下です

OrderData

一番右のyyyyMMの列は「新しい列の作成」にて追加しました

こちらの列で最後の月を判断します

SUMMARIZECOLUNS関数による集計

今回の記事の内容で一番難しいのはデータの最後の月を集計する点です

最後の月の抽出はMAX関数を使えば簡単そうです

ただそこから最後の月の売上を集計する必要があります

Power Queryでグループ化を行い、その後に集計する方法もあります

今回の記事ではDAXを組み合わせて、直接、最終月の売上を集計します

中心となるのSUMMARIZECOLUNS関数です

この関数はグループ化したテーブルを作成します

上の画像を見ると、「201904」「201905」と月単位で売上が集計されています

SUMMARIZECOLUNS関数の文法は次の通りとなります

SUMMARIZECOLUMNS(グループ化する列,新列名,集計式)

今回で言えば次の通りの式となります

SUMMARIZECOLUMNS(

‘OrderData'[yyyyMM] ⇒グループ化する列

,”月小計”, ⇒新列名:集計式で計算した列

SUM(‘OrderData'[受注数]) ⇒集計式

)

但し、SUMMARIZECOLUNS関数を使用して新たなテーブルを作成しておくわけではありません

SUMMARIZECOLUNS関数で作成したテーブルは変数に代入します

上の画像では変数:myTableに代入されています

最終月と売上の集計

まずは前述の通りSUMMARIZECOLUNS関数にてグループ化されたテーブルを作成します

そして最終月を計算した上で新たに作成したテーブルから最終月の売上を抽出します

最終月は以下の要領で算出して変数:MaxMonthに格納します

そして、myTableとMaxMonthを組み合わせて最終月の売上をSUMX関数を使用して集計します

SUMX関数の第一引数ではFILTER関数を使用します

グループ化したテーブルから、最終月と同様の行を抽出します

そして第二引数ではグループ化した際に集計した売上を指定します

文章となるメジャーの作成

文字の切り取り

文章で使う「年」や「月」は事前に分解してメジャーを作成しておきます

改行コード

改行はUNICHAR関数を使用します

UNICHAR関数の引数には「10」を指定します

桁区切り

売上の桁区切りにはFORMAT関数を使用します

FORMAT関数の第二引数では”#,###”で桁区切りを表現します

テキストボックスへのメジャーの設定

事前に文章となるメジャーは「TEXT」という名称で作成してあります

テキストボックスをレポート画面に設定すると、+ボタンが見えますのでこちらをクリックします

するとメジャーを設定できるようになります

メジャーの設定は「この値の計算方法」の欄にて行います

これでフォント文字や文字の位置などを調整したら終了です

<まとめ>

今回はメジャーを組み合わせて文章を可変にする方法を解説しました

記事の中では様々なテクニックが出てきました

・グループ化するDAX⇒SUMMARIZECOLUNS関数

・DAXによる改行⇒UNICHAR関数

・桁区切り⇒FORMAT関数

他の場面でも有効活用できる内容です

ぜひ手を動かしてトライしてみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

複雑なリレーションを分かりやすく分解して編集する方法

【Power BIを活用する上で難所の一つであるリレーションの編集を分かりやすい画面で行えるようにする】

Power BIのリレーション画面を一目見て「操作しにくい」と感じたことはないですか?

どうも全体像がつかめなかったりする場合もありますし、表示されているテーブルが多すぎる場合には関係性がよくつかめなかったりします

今回の記事では「モデルビュー」画面をうまく活用して、リレーションを編集しやすい単位でブロックを分けられるようにします

自動レイアウト

今回の記事ではリレーションを分解して編集する方法を解説するのですが、全体をすっきり表示する方法もあります

まず「+ボタン」でモデルビュー画面のページを増やします

ページを増やしたら「自動レイアウト」をクリックします

すると次のGIF動画のように、全体がすっきりした形で表示されます

こちらの画面でリレーションを新規に作成してみます

上のGIF画像では「顧客1」と「売上データ1」との間で新規にリレーションが作成されました

すると「すべてのテーブル」ページでも新規に作成されたリレーションが反映されます

リレーションの分解

ここから本題のリレーションの分解について解説します

まずページを新規に追加します

こちらに分解するリレーションにおいて中心となる「テーブル」を追加します

次に「ページの空白箇所」で右クリックをします

すると次のようなポップアップが表示されます

こちらの「関連テーブルを追加する」をクリックします

すると中心とするテーブルと既にリレーションがあるテーブルが表示されます

新規にリレーションを追加する場合にはテーブルを右から挿入します

逆に上の表示からテーブルを消したい場合にはこちらの「ダイアグラムから削除」をクリックします

なお、こちらで作成したリレーションは「すべてのテーブル」にきちんと反映されます

<まとめ>

今回はPower BIのモデルビュー画面にて、リレーションを分けて編集する方法を解説しました

画面を追加するだけで行えるのでとても便利です

Power BIでうまくリレーションする鍵は「分解」です

ぜひ今回紹介した画面をうまく活用して「分解」上手になりましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

複数のメジャーを集約して分かりやすくまとめる方法

【複数のテーブルに散らばったメジャーを分かりやすく1つのテーブルに集約します】

メジャーを作成していたら、いろんなテーブルに散らばってわからなくなった・・・そんな経験はないですか?

今回の記事ではメジャー専用のテーブルを作成してまとめる方法を解説します

テーブルの手動作成

まず最初に複数メジャーのまとめ先になるテーブルを作成します

テーブルの作成は手動で行います

次に開いた画面では、ダミーデータを入力します

列名を指定したりする必要はありません

どのみち、後で削除します

但し、テーブル名は指定してください

メジャーの選択

前述の手動で作成したテーブルに複数メジャーを移動します

移動の指定は「モデル」画面で行います

モデル画面の右にあるデータで移動元のメジャーをまとめて選択します

各テーブルにて3点をクリックすると「メジャーの選択」があります

このメジャーの選択をクリックすると、該当テーブル内のメジャーをまとめて選択できます

メジャーの移動

次に選択したメジャーの移動先を「プロパティ」欄の「ホームテーブル」で指定します

テーブルの整理

最初にダミーで入力したデータは消去しておきます

<まとめ>

今回は、複数のメジャーを一つのテーブルにまとめて整理する方法を解説しました

今回の記事のように常にメジャーは分かりやすく一か所にまとめておくと他の人も分かりやすいので、常に「メジャーテーブルの作成」は行っておきましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

画像でスライサーを選択する~スライサーを一目で分かるようにしよう!~

今回の記事では、画像でスライサーを選択できるようにします

この仕組みであれば、何を選択するか?何を選択しているか?は一目で分かります!

エクセルでも似たようなことはできます

ただ、画像に名前を付けるなどの面倒な処理が必要です

Power BIであれば、複雑な処理不要で画像スライサーを作成できます

画像URLの準備

画像はURLを使用して表示します

Power BI Desktop内で、画像のURLを管理する仕組みがあります

URLは今回の記事では、Googleフォトで用意しました

Googleフォト上で右クリックすると「画像アドレス(URL)」をコピーできます

コピーしたURLは、スライサーにする項目に紐づけられるようにコピーします

今回の記事では「データの入力」から新たにテーブルを作成しました

エクセルで別途、URLを指定したテーブルを作成するのもOKです

スライサーの作成

スライサーの作成を行う前に、データ管理画面で一つ事前処理が必要です

データ管理画面で「URL」を設定した列を選択した上で、データカテゴリを「画像のURL」に指定します

これで、通常のスライサーの作成処理と同じです

フィールドにはデータカテゴリを「画像のURL」に設定した列を指定します

これで完成です!

<まとめ>

今回はスライサーを画像で選択できるようにしました

画像はURLで処理するのが一番のポイントです

画像を活用すると、レポートが華やぎます!

ぜひ有効活用してみましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power BIの動くレポートをパワーポイントのスライドに挿入する方法

Power BI DesktopからPower BIサービスに発行した内容をプレゼンで使用したい時があります

プレゼンの際は、せっかく作ったスライサーやドリルダウンなどの動く素材もパワーポイント内で使用したいですよね?

今回の記事では、そんな都合の良い方法をご紹介します

Power BIサービスへの発行

まずは、Power BI DesktopからPower BIサービスへ発行処理を行いましょう

ライブデータの作成

Power BIサービスにログインしたら、マイワークスペースから該当のレポートを開きましょう

レポートを開いたら、エクスポート ⇒ PowerPoint ⇒ライブデータを埋め込む、を選択します

「ライブデータを埋め込む」をクリックしたら、次のような画面が出てきます

こちらでリンクをコピーします

パワーポイント内の操作

パワーポイントでは、レポートを挿入したいページでアドイン「Power BI」を「挿入タブ」から起動します

「Power BI」が無い場合は「アドイン」から「Power BI」を追加しましょう

「Power BI」を起動したら、次のような画面が開きます

こちらの画面で「ライブデータを埋め込む」にてコピーしたリンクを貼り付けます

これでパワーポイント内でレポートが動きます!

削除したい場合は、右上のこちらをクリックします

すると削除ボタンが出てきます

<まとめ>

今回は、Power BIの動く素材をパワーポイント内で使えるようにする方法を解説しました

一つのアプリ内で完結せず、他のアプリでも連携できるのが、Power PlatFormの良さです!

尚、動く素材を埋め込んだパワーポイントを共有する際には、Power BI Proライセンスが必要です

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ