[Power BI] ALL、ALLEXCEPT、ALLSELECTED関数の機能と使い方

このページでは、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]の合計

関数の確認

1. メジャーを使用して、Profitの合計を計算します。

2. スライサーを使って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]の合計

関数の確認

1. メジャーを使用して、CountryとProductの組み合わせの合計を計算します。

2. スライサーを使用してProductにフィルターをかけます。

• ProfitのSUM(ALLEXCEPT)は、Productの行コンテキストを無視したProfitの合計が計算されています。
  (ProductによらずCountry毎のProfitのSUMが計算されている。)
• スライサーを使用したProductのフィルターも無視して、Country毎の合計値を計算しています。
  (フィルターペインからビジュアルごとにフィルターを設定した場合でも同じく無視する。)

このALLEXCEPTを利用することで、各カテゴリー内で各要素の構成比率を計算することが可能です。

比率_カテゴリー内_ = DIVIDE([ProfitのSUM],[ProfitのSUM(ALLSELECTED)])*100
//Country毎に各Productの構成比率を計算。

Productでフィルターをかけても計算結果に変更はありません。

ALLEXCEPT関数を使用した記事：

あわせて読みたい

[Power BI] マトリクスの小計に平均値を表示(空白を0として計算) このページでは、マトリックスのビジュアルに空白を含めた平均値を計算する方法を紹介します。通常の数値計算のほか、項目をカウント数の平均値を表示する方法も記載し…

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関数はスライサーなどの外部フィルターを無視することができないので、注意が必要。