【Power BI】ALLSELECTED関数を使ってみる
目次
一部のフィルターは残したまま、それ以外のフィルターは無視して計算したい
ページフィルターの効果は残したまま、マトリクスに含まれているレコード内での平均値が出したい。
（正確にいうと、各カテゴリでの偏差が欲しい）
Power BIのレポートをつくっていて、私が実際にぶつかった壁です。
統計処理には、レコード全体での平均やレコード全体での標準偏差などを使って算出するものがあります。
- 使用するレコードを切り分けない（=フィルタリングしない）状態のままで計算したい。
- けれども、「60代以上の方で～」など、特定の層でフィルタリングした中で処理をしたいし、ゴミデータとして除去しなければいけないデータもある。
- ついでに、Power Queryでこねくり回すのは避けたい。
- ついでのついでに、もし可能なら、スライサーによるフィルタリングは維持したい。
ええ、ワガママですね。
フィルタリングしたいけれどもフィルタリングしたくない。そんなワガママにこたえてくれる関数がALLSELECTED関数です。
今回のゴールとしてALLSELECTED関数を作るとこんな表が作成できる！
DAX関数のALL関数を使ってみる
最初に考えたのはとにかくフィルタリングを外してやればいい、ということでした。
ALL関数を使って、全体での平均値を出すメジャーを作成します。
平均値 = AVERAGEX( ALL( 'テーブル名' ) , [値] )
さて、その結果はいかに。
※ページレベルフィルターで都道府県を埼玉県の2015年に絞り込むと
ん？埼玉って14くらいしか市区群が無い？いやいやそんなわけありません。
もしやと思い、設定していたページレベルフィルターをすべて外すと、
はい、合計値÷レコード数と平均値が一致しました。
ページレベルフィルターも無視して平均値を計算してくれたようです。
ALL関数は、すべてのフィルタリングを無視して計算をする仕組みになっているので、
その仕組みさえ知っていればそりゃそうよ、という結果です。
適用されているフィルターをすべて無視し、テーブル内のすべての行 (列内のすべての値) を返します。 この関数は、フィルターをクリアし、テーブル内のすべての行に基づく計算を実行する場合に便利です。-MSDN DAX 関数リファレンス-
https://msdn.microsoft.com/ja-jp/library/ee634802.aspx
さて困った。どうすればページレベルフィルターを維持できるのか。
それでいよいよALLSELECTED関数の登場というわけです。
ALLSELECTED関数を使ってみる
さて、先ほどのメジャーをすこーしだけ変えます。
平均値 = AVERAGEX( ALLSELECTED( 'テーブル名' ) , [値] )
これでどうでしょうか？
7,206,014÷114,381＝63と確かにページレベルフィルターによる影響は受けたまま、マトリクスの影響だけを無視してくれています。ついでに、ページレベルフィルターでなく、スライサーによって都道府県でフィルタリングをすると、
同様の結果がでていますので、スライサーの影響もちゃんと受けているようです。しかも、他のビジュアルを選択してデータを絞り込んだ場合にも対応しています。
目的達成です！やりました！
英語の文章をむりやり翻訳したような喜びようはさておき、ここで疑問が出てきます。
できたのはいい。けれど、どうしてできたのだろう。
ALLSELECTED関数の意味を整理する
さて、まずは基本の基本ALLSELECTED関数のリファレンスを見てみましょう。
現在のクエリの列および行からコンテキスト フィルターを削除し、他のすべてのコンテキスト フィルターと明示的なフィルターは保持します。
ALLSELECTED 関数は、クエリのすべての列および行を表すコンテキストを取得する一方で、
行フィルターおよび列フィルター以外の明示的なフィルターおよびコンテキストを維持します。
この関数を使用して、クエリの表示部分の合計を取得できます。-MSDN DAX 関数リファレンス-
https://msdn.microsoft.com/ja-jp/library/gg492186.aspx
なるほど、ぱっと見ただけではさっぱりわかりません。
リファレンスを見れば解決することが多いのですが、今回はさっぱりです。
訳が変だからわからないのかも！と思い原文を見てみたら、ほぼそのままの文章でした。
こういうときは、実践あるのみです。いろいろなフィルタリング機能の違いを見ていきましょう。
メジャーとフィルター、ビジュアルの関係を整理する
まず、マトリクスの仕組みを考えてみましょう。
マトリクスは行・列それぞれにカテゴリを割り当てて、そのカテゴリごとでの値を表示するビジュアルです。
地域と年代など、2つの視点を組み合わせて数値化できるのが便利でよく使われています。
このビジュアルは以下の図のように、クエリに対してフィルターをかけたときに該当するデータの集計を表示するものです。下図の合計のところには他にも平均値や中央値、最初の値など、集計値を選択することができます。
それでは値のところにメジャーを入れた場合にはどのように動作するでしょうか？
平均値をメジャーでつくって値に放り込んでみましょう。
さいたま市も川口市も人口と平均値が一緒になりました。さいたま市という1か所の平均値、川口市という1か所の平均値を求めていれば、人口÷市区群の数＝人口/1で一致します。
マトリクスの行・列に入れたカテゴリでフィルタリングをかけたあとに、平均値を出力するメジャーの計算を行っていることがわかります。
これは他のビジュアルでも共通で、
- ビジュアルにおいての分類によるフィルタリングが行われる
- メジャーにおいてのフィルタリング・処理が行われる
という順で処理が行われているようです。
ここにフィルター（ビジュアルレベル・ページレベル・レポートレベルフィルター）が追加されていた場合はどうでしょうか。
これはドキュメントが見つからなかったので予想になってしまいますが、
- フィルターによる絞り込みが行われる
（ビジュアルを選択したことによる絞り込みが行われる） - ビジュアルでのフィルタリングが行われる
- メジャーでのフィルタリング・処理が行われる
の順に行われていると推測できます。
ちなみに、スライサーはフィルターの下位に属する絞り込み手段（適用順の意味で）とのことですので、フィルターの亜種ととらえてよさそうです。
まとめ：ALLSELECTED関数は内部的なフィルターを無視する関数だ！
今までの動作からALLSELECTED関数の意味をまとめてみましょう。
内部の軸や行・列などで分けられた時系列・カテゴリなど
フィルター、スライサー、他ビジュアルでのカテゴリの選択による絞り込み
といえるでしょう。
まさに私のワガママを形にしてくれていた方々に脱帽です。
↓今回の資料を含め、わからないことがあったらまず見にいっているMSDNのDAX関数リファレンスです。
一部ページが翻訳されていないことがありますが、引数から実例まで載っています。
https://msdn.microsoft.com/ja-jp/library/ee634396.aspx
ぜひ参考にしてください。
とはいえ、もっと詳しいことを聞いてみたい方もいらっしゃるかと思います。
弊社ではそういったご相談に対しても対応が可能ですので、是非お気軽にお問い合わせください。
＜Power BIハンズオンセミナー＞
弊社ではPower BIをはじめとするさまざまな無料オンラインセミナーを実施しています！
＞＞セミナー一覧はこちら
＜Power BIの導入支援＞
弊社ではPower BIの導入支援を行っています。ぜひお気軽にお問い合わせください。
＞＞Power BIの導入支援の詳細はこちら
＜PowerBIの入門書を発売中！＞
弊社ではPower BIの導入から基本的な使い方・活用方法の基礎などをわかりやすく解説した書籍も販売しています。
＞＞目次も公開中！書籍の詳細はこちら
