このページでは、DAXのフィルター関数であるALL、ALLEXCEPT、ALLSELECTED関数の機能と使い方を紹介します。機能の違いや、使い方も例をもとに記載します。
・使用する関数：ALL, ALLEXCEPT, ALLSELECTED
関数の比較と使用データの紹介
ALL、ALLEXCEPT、ALLSELECTEDなどのALL系関数は、すべてフィルターを制御する関数です。
各関数の比較を行う際に使用するデータは、MIcrosoftからダウンロードできる財務サンプル ブックを使用します。
ページ内の直接ダウンロードするからダウンロードできます。
ダウンロードしたExcelブックは、月ごとにSegment、Country、Product、Discount Band、Profitなどの財務関係のデータが入っています。テーブル名はSheet1とします。
上記のデータを使用して各ALL系関数のふるまいを見ていきます。
ALL関数
ALL関数は指定したテーブルにかかるフィルターをすべて無視する関数です。
・ALL(テーブル名)
・ALL(テーブル名[列名])
引数にテーブル名を入れるとそのテーブル全体のフィルターを無視。列名を入れるとその列にかかるフィルターを無視します。
例として下記のメジャーの計算結果を比較してみます。
ProfitのSUM =
SUM(Sheet1[Profit])
//[Profit]の合計ProfitのSUM(ALL) =
CALCULATE(
SUM(Sheet1[Profit]),
ALL(Sheet1)
)
//Sheet1のフィルターを無視した[Profit]の合計関数の確認
- メジャーを使用して、Profitの合計を計算します。
- スライサーを使ってCountryでフィルターをかけます。
ProfitのSUM(ALL)は、Countryのフィルターを無視して、Profitの合計値を計算しています。
注意点と補足
- フィルターペインからビジュアルごとにフィルターを設定した場合でも、値は同じです。
- テーブルで各Countryにおいて、上記の2つのメジャーを比較。
ProfitのSUM(ALL)は、Countryの行コンテキストを無視して値を算出するので、注意が必要です。
ALLEXCEPT関数
ALLEXCEPT関数は指定した列にかかるフィルター以外をすべて無視します。つまり残しておきたいフィルターのある列を指定する際に使用します。
ALLEXCEPT(テーブル名[列名])
指定した列以外のフィルターを無視。
例として下記のメジャーの計算結果を比較してみます。
ProfitのSUM =
SUM(Sheet1[Profit])
//[Profit]の合計ProfitのSUM(ALLEXCEPT) =
CALCULATE(
SUM(Sheet1[Profit]),
ALLEXCEPT(Sheet1,Sheet1[Country])
)
//[Country]のフィルター以外を無視した[Profit]の合計関数の確認
- メジャーを使用して、CountryとProductの組み合わせの合計を計算します。
- スライサーを使用してProductにフィルターをかけます。
- ProfitのSUM(ALLEXCEPT)は、Productの行コンテキストを無視したProfitの合計が計算されています。
(ProductによらずCountry毎のProfitのSUMが計算されている。) - スライサーを使用したProductのフィルターも無視して、Country毎の合計値を計算しています。
(フィルターペインからビジュアルごとにフィルターを設定した場合でも同じく無視する。)
このALLEXCEPTを利用することで、各カテゴリー内で各要素の構成比率を計算することが可能です。
比率_カテゴリー内_ = DIVIDE([ProfitのSUM],[ProfitのSUM(ALLSELECTED)])*100
//Country毎に各Productの構成比率を計算。
Productでフィルターをかけても計算結果に変更はありません。
ALLEXCEPT関数を使用した記事：
ALLSELECTED関数
ALLSELECTED関数は指定した列にかかるフィルターのみを無視します。
ALLSELECTED(テーブル名[列名])
指定した列以外のみのフィルターを無視。
例として下記のメジャーの計算結果を比較してみます。
ProfitのSUM =
SUM(Sheet1[Profit])
//[Profit]の合計ProfitのSUM(ALLSELECTED) =
CALCULATE(
SUM(Sheet1[Profit]),
ALLSELECTED(Sheet1[Country])
)
//[Country]のフィルターを無視した[Profit]の合計関数の確認
メジャーを使用して、CountryとProductの組み合わせの合計を計算します。
ProfitのSUM(ALLSELECTED)では、Countryの行コンテキストを無視したProfitの合計が計算されています。
(Countryによらず右テーブルと同じProduct毎のProfitのSUMが計算されている。)
また、ALLEXCEPTと同様にALLSELECTED関数を利用することで、各カテゴリー内で各要素の構成比率を計算することが可能です。
比率_カテゴリー内_ = DIVIDE([ProfitのSUM],ProfitのSUM(ALLSELECTED))*100
//Product毎に各Countryの構成比率を計算。
使用上の注意点
ALLSELECTED関数を使用した計算結果は指定した列のフィルターを無視しますが、スライサーなどの外部フィルターは無視することができません。影響を受けて計算結果が変わります。
フィルターペインからビジュアルごとにフィルターを設定した場合でも同じく無視することができません。
外部フィルターの影響を受けることを踏まえて、メジャーを作成しないと想定と異なる値を計算することになります。
まとめ
- ALL関数はフィルターをすべて無視する。
- ALLEXCEPT関数は指定した列以外のフィルターを無視する。
- ALLSELECTED関数は指定した列のフィルターを無視する。
- ALL関数、ALLEXCEPT関数はスライサーなどの外部フィルターも無視することができる。
- ALLSELECTED関数はスライサーなどの外部フィルターを無視することができないので、注意が必要。
