このページでは、DAXのフィルター関数であるALL、ALLEXCEPT、ALLSELECTED関数の機能と使い方を紹介します。機能の違いや、使い方も例をもとに記載します。
・使用する関数:ALL, ALLEXCEPT, ALLSELECTED
関数の比較と使用データの紹介
ALL、ALLEXCEPT、ALLSELECTEDなどのALL系関数は、すべてフィルターを制御する関数です。
各関数の比較を行う際に使用するデータは、MIcrosoftからダウンロードできる財務サンプル ブックを使用します。
ページ内の直接ダウンロードするからダウンロードできます。
ダウンロードしたExcelブックは、月ごとにSegment、Country、Product、Discount Band、Profitなどの財務関係のデータが入っています。テーブル名はSheet1とします。
![財務サンプル ブックの抜粋。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-15-1024x181.png)
上記のデータを使用して各ALL系関数のふるまいを見ていきます。
ALL関数
ALL関数は指定したテーブルにかかるフィルターをすべて無視する関数です。
・ALL(テーブル名)
・ALL(テーブル名[列名])
引数にテーブル名を入れるとそのテーブル全体のフィルターを無視。列名を入れるとその列にかかるフィルターを無視します。
例として下記のメジャーの計算結果を比較してみます。
ProfitのSUM =
SUM(Sheet1[Profit])
//[Profit]の合計
ProfitのSUM(ALL) =
CALCULATE(
SUM(Sheet1[Profit]),
ALL(Sheet1)
)
//Sheet1のフィルターを無視した[Profit]の合計
関数の確認
- メジャーを使用して、Profitの合計を計算します。
![スライサーを設定していない場合のProfitの合計の結果。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-18.png)
- スライサーを使ってCountryでフィルターをかけます。
![スライサーを設定した場合のProfitの合計の結果。ProfitのSUM(ALL)の値はスライサーの影響を受けていない。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-19.png)
ProfitのSUM(ALL)は、Countryのフィルターを無視して、Profitの合計値を計算しています。
注意点と補足
- フィルターペインからビジュアルごとにフィルターを設定した場合でも、値は同じです。
![フィルターを使用した場合の注意点。スライサーと同じふるまいをする。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-22.png)
- テーブルで各Countryにおいて、上記の2つのメジャーを比較。
![ProfitのSUM(ALL)は行コンテキストを無視している。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-25.png)
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の組み合わせの合計を計算します。
![スライサーを設定していないProfitのSUM(ALLEXCEPT)の結果。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-29.png)
- スライサーを使用してProductにフィルターをかけます。
![スライサーを設定したProfitのSUM(ALLEXCEPT)の結果。Productの行コンテキストを無視している。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-30.png)
- ProfitのSUM(ALLEXCEPT)は、Productの行コンテキストを無視したProfitの合計が計算されています。
(ProductによらずCountry毎のProfitのSUMが計算されている。) - スライサーを使用したProductのフィルターも無視して、Country毎の合計値を計算しています。
(フィルターペインからビジュアルごとにフィルターを設定した場合でも同じく無視する。)
このALLEXCEPTを利用することで、各カテゴリー内で各要素の構成比率を計算することが可能です。
比率_カテゴリー内_ = DIVIDE([ProfitのSUM],[ProfitのSUM(ALLSELECTED)])*100
//Country毎に各Productの構成比率を計算。
![ALLEXCEPTの使用例。カテゴリーの比率をCountry毎に計算。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-36.png)
![フィルターの結果を無視した結果を表示した図。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-37.png)
Productでフィルターをかけても計算結果に変更はありません。
ALLEXCEPT関数を使用した記事:
![](https://yy-output.com/wp/wp-content/uploads/2024/05/マトリクスの小計に平均値を表示空白込み・カウント-300x158.png)
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)の計算結果を確認。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-42.png)
ProfitのSUM(ALLSELECTED)では、Countryの行コンテキストを無視したProfitの合計が計算されています。
(Countryによらず右テーブルと同じProduct毎のProfitのSUMが計算されている。)
また、ALLEXCEPTと同様にALLSELECTED関数を利用することで、各カテゴリー内で各要素の構成比率を計算することが可能です。
比率_カテゴリー内_ = DIVIDE([ProfitのSUM],ProfitのSUM(ALLSELECTED))*100
//Product毎に各Countryの構成比率を計算。
![各Countryの比率をProduct毎に計算している。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-43.png)
使用上の注意点
ALLSELECTED関数を使用した計算結果は指定した列のフィルターを無視しますが、スライサーなどの外部フィルターは無視することができません。影響を受けて計算結果が変わります。
![ALLSELECTED関数を使用した計算結果がフィルターの影響を受けて値が変わる可能性がある図。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-44.png)
![ALLSELECTED関数を使用した計算結果がフィルターの影響を受けて計算結果が変わった図。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-46.png)
フィルターペインからビジュアルごとにフィルターを設定した場合でも同じく無視することができません。
外部フィルターの影響を受けることを踏まえて、メジャーを作成しないと想定と異なる値を計算することになります。
まとめ
- ALL関数はフィルターをすべて無視する。
- ALLEXCEPT関数は指定した列以外のフィルターを無視する。
- ALLSELECTED関数は指定した列のフィルターを無視する。
- ALL関数、ALLEXCEPT関数はスライサーなどの外部フィルターも無視することができる。
- ALLSELECTED関数はスライサーなどの外部フィルターを無視することができないので、注意が必要。
![各関数の計算結果を表示し、比較した図。](https://yy-output.com/wp/wp-content/uploads/2024/01/image-47.png)