お疲れ様です。きざきまるおです。
今回はフィルター関数についてまとめていこうと思います。
こちらはPowerBIのレポートを作るうえで非常に大事な関数となっておりますので、詳細まで書いていこうと思います。
それではどうぞ。
フィルター関数とは
テーブル内のデータを抽出する際の条件として働く関数です。
SQLでいうところのWHERE句に該当する部分なので内容は多岐に渡ります。
フィルター関数のマスター=DAXマスターといっても過言ではないので時間をかけて学習する価値はあるでしょう。
事前準備
データなどについてはこちらを参照してください。
それに加えてまずはordersテーブルとcalendarテーブルにリレーションを張りましょう。
次にordersテーブルに列を追加して以下DAXを入力しましょう。
年月_数値 = RELATED('calendar'[年月_数値])CALCULATE
こちらはSQL文でいうところのWHERE句にあたります。
メジャーでの表現力を上げるためには必須の関数となりますので、ぜひ覚えておきましょう。
calculate = CALCULATE('テーブル名',フィルター条件)サンプルコード
今回は年月が”202204″かつ商品名が”book”の総額を算出しようと思います。
calculate_sum =
CALCULATE(
SUM('orders'[総額]),
'orders'[年月_数値] = 202204,
'orders'[商品名] = "book"
)
はい。ということで条件にあった値が算出されましたね。
こちらの関数は今後のサンプルコードでも頻繁に出てくるのでよく覚えておいてください。
公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/calculate-function-dax
ALL
テーブルもしくは列のすべてのデータを返します。
ALL関数が指定されたテーブルはスライサーで指定された条件、表で指定された条件などのフィルター条件を除外します。
スライサーで日付を選択した状態で過去の値を抽出する際に利用します。
ただ、テーブルのすべての値を取得するため、多用すると負荷がかかるため注意。
all = ALL('テーブル名'([列]))サンプルコード
テーブルを引数に指定するSUMXで使うパターンとCALCULATE文の中でALLを使用するパターンを紹介します。
事前準備としてordersテーブルの[年月_数値]を設定したスライサーを準備しましょう。
<SUMXパターン>
all_sumx =
SUMX(
ALL('orders'),
'orders'[総額]
)比較のためにALLを使用しないパターンも作りましょう。
notall_sumx =
SUMX(
'orders',
'orders'[総額]
)スライサーで”202204″を選択した結果を比較してみましょう。
上がALLを使ったもので下は使っていないものです。
はい。ALLを使用しているほうはすべてを合計した値で使用していないほうはスライサーで選択した月のみの集計となっていますね。
次はCALCULATE文の中でALLを使用するパターンを見てみましょう。
こちらは１か月前の値を取得するケースで紹介します。
<CALCURATE文の中でのALL>
all_calculate =
CALCULATE(
SUM('orders'[総額]),
ALL('orders'),
'orders'[年月_数値] = MAX('orders'[年月_数値])-1
)再び比較用のメジャーを作成します。
notall_calculate =
CALCULATE(
SUM('orders'[総額]),
'orders',
'orders'[年月_数値] = MAX('orders'[年月_数値])-1
)今回は前月の値を取得するのでスライサーで”202205″を選択します。
上がALLを使ったもので下は使っていないものです。
はい。ALLを使っているほうは値が表示されましたが使っていないほうは空白となってしまいましたね。
なぜこのようなことが起こるのかというと、スライサーで選択することでテーブルデータに内部的にフィルターがかかるためです。
上記の例でいうと、ALLを使っていないほうは存在しない”202204″のデータを抽出しようとしているため(空白)という結果になってしまいます。
ALL関数はいろいろな場面で使えるので覚えておいて損はないでしょう。
より詳細まで知りたい方は公式マニュアルをご参照ください。
https://learn.microsoft.com/ja-jp/dax/all-function-dax
ALLEXCEPT
指定した列に適用されているフィルターはそのまま保持して、それ以外のデータはフィルター条件を無視して取得します。
複数スライサーが選択されている状態で片方のスライサーの条件を無視したい場合などで使えます。
allexcept = ALLEXCEPT('テーブル名',[列名]...)サンプルコード
事前準備として年月スライサーに加え、商品名スライサーを追加しましょう。
こちらはCALCULATE文で使用されることが多いのでそちらで見てみましょう。
allexcept_sumx =
CALCULATE(
SUM('orders'[総額]),
ALLEXCEPT('orders', 'orders'[商品名])
)比較はALL関数のCALCULATEパターンで作成します。
all_calculate_diff =
CALCULATE(
SUM('orders'[総額]),
ALL('orders')
)スライサーで”202204″と”book”選択した結果を比較してみましょう。
上がALLEXCEPTで下はALLです。
はい。ということでALLEXCEPTでは見事に商品名のみフィルター条件にかかって集計されていますね。
ALLEXCEPTでは列を複数指定できるため、スライサーの数が2~3個だったらこの関数を使用すると表現力がアップしますね。
公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/allexcept-function-dax
ALLSELECTED
指定した列に適用されているフィルターは無視して、それ以外のデータはフィルター条件を適用して取得します。
多数のスライサーが選択されている状態で一つだけスライサーの条件を無視したい場合などで使えます。
allselected = ALLSELECTED('テーブル名'[列名]...)サンプルコード
ALLSELECTEDもCALCULATE文で使用されることが多いのでそちらで見てみましょう。
allselected_sumx =
CALCULATE(
SUM('orders'[総額]),
ALLSELECTED('orders'[商品名])
)ALLEXCEPTで作成した２つのメジャーと比較してみましょう。
スライサーは”202204″と”book”選択します。
１番目がALLSELECTED２番目はALLEXCEPTで３番目はALLです。
はい。意図していた動きとは違って、商品名スライサーの影響を受けてしまってます。
実はこれは使用通りの動作で、テーブルなどの内部フィルターでは効力を発揮するのですが、外部フィルターは反映してしまうという弱点があります。
公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/allselected-function-dax
FILTER
条件に合致した行のみを含むテーブルを返す関数です。
CALCULATE文の条件や、新たなテーブルを作成するときによく使います。
filter = FILTER('テーブル名',条件)サンプルコード
FILTER関数に関しては２パターン確認してみましょう。
確認内容は新しいテーブルの作成とCALCULATE文での使用です。
<新しいテーブル作成>
新しいテーブルを作成し、以下DAXを入力してください。
filter =
FILTER(
'orders',
[総額] > 100000
)
はい。無事に[総額]が100000以上に該当するデータのみ抽出したテーブルが作成できましたね。
同じようなメジャーの作成抑止やリレーションを度外視したテーブルが欲しいといったときに置く使うので覚えておくと便利でしょう。
次はCALCULATE文でのFILTER関数です。
<CALCULATE文でのFILTER>
calculate_filter =
CALCULATE(
SUM('orders'[総額]),
FILTER(
'orders',
[総額] > 100000
)
)
はい。少しわかりづらいですが、条件にあった値が抽出されています。
こちらはCALCULATEの条件で集計せずに列名を条件として使いたい時などに使います。
もちろんSUMXやAVERAGEXなどでも利用できますので、なかなか汎用性が高いですね。
公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/filter-function-dax
今回はここまでにします。
フィルター関数はPowerBIを使う上では欠かせない関数なのでマスターすると良いPowerBIライブになること間違いなしです。
それではまた。
