Power BI Desktopの「ALL」「ALLSELECTED」「ALLEXCEPT」

DAXのALL関数

「ALL」「ALLSELECTED」「ALLEXCEPT」は何度使ってもわからなくなります。
Microsoftが提供する小売データのサンプル「AdventureWorks Sales Sample」を使い学習し直しました。

今回使ったデータのリレーション

まず「ALL」から。

---

ALL
テーブルのすべての行、または列のすべての値を返します。適用されている可能性があるフィルターはすべて無視されます。 この関数は、フィルターをクリアし、テーブル内のすべての行に対して計算を作成する場合に便利です。

#構文
ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )  

ALL 関数 (DAX) - Microsoft Learn

---

「ALL」は指定したテーブルもしくは列のフィルターを除去して計算します。
月別に集計した（フィルターされた）売上（#Sales Amount）を集計した例を示します。
「ALL('Sales_Data'[Category])」でカテゴリ列のフィルターを無効にした「メジャー」式を作成し、売上列の隣に配置します。

Sales_by_ALL1 = CALCULATE(SUM(Sales_Data[#Sales Amount]), ALL('Sales_Data'[Category]))

その結果、どの行でも全カテゴリの合計値が表示されます。

ALL('Sales_Data'[Category])でカテゴリ列のフィルターを無効

カテゴリのスライサーで、あるカテゴリだけを選択すると売上（#Sales Amount）はその集計値に変わります。
下の表では「Accessories」（アクセサリー）と「Clothing」（衣料）を選択しています。
「ALL」関数を使った列は、スライサーを使ったフィルターが無視されるので、全カテゴリの合計値のままです。

あるカテゴリを選択して集計をしても「ALL」関数を使った列の値は変わらない

なお、「ALL」では列だけでなくテーブルも指定できます。
上記のカテゴリ別の集計については、「ALL('Sales_Data')」と指定しても同じ結果が得られます。
この場合、'Sales_Dataテーブルのどの列もフィルターを無視します。

Sales_by_ALL0 = CALCULATE(SUM(Sales_Data[#Sales Amount]), ALL('Sales_Data'))

「ALL」では列でなくテーブルも指定できる

DAXのALLSELECTED関数

---

ALLSELECTED
テーブルのすべての行、または列のすべての値を返します。適用されている可能性があるフィルターはすべて無視されます。 この関数は、フィルターをクリアし、テーブル内のすべての行に対して計算を作成する場合に便利です。

#構文
ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )

ALLSELECTED - DAX 関数 - Microsoft Learn

---

「ALLSELECTED」は、指定した（テーブルの中の）列のみフィルターを外します。
分かりやすいように売上テーブルにカテゴリ列を追加します。

ALLSELECTEDに使ったテーブル。カテゴリ列を別のテーブルから追加

「メジャー」を作成し、売上集計表に配置します。
「ALLSELECTED('Sales_Data'[Category])」でカテゴリ列のフィルターを無効にします。

Sales_by_ALLSELECTED2 = CALCULATE(SUM(Sales_Data[#Sales Amount]), ALLSELECTED('Sales_Data'[Category]))

カテゴリ別の集計（= 1行目にカテゴリを置く）を実施します。
「Sales_by_ALLSELECTED2」では、上述の「ALL」を使った関数同様にカテゴリのフィルターが無視されるので、全カテゴリの合算値が出力されます。

Sales_by_ALLSELECTED2列でもカテゴリのフィルターを除去

次にカテゴリー別でない集計をします。
例えば、別のテーブル（Product_Data）のカラー別。
「Sales_by_ALLSELECTED2」は、カラーに対してはフィルターが適用されるので、今度は色別の合計値が並びます。
先ほどのALL('Sales_Data')を使った「Sales_by_ALL1」も同様で、カラーのフィルターは有効なので同じ合計値が返されます。

カラーに対してはフィルターが適用されるので色別の集計値となる

DAXのALLEXCEPT関数

---

ALLEXCEPT
指定した列に適用されているフィルターを除く、テーブル内のすべてのコンテキスト フィルターを削除します。

#構文
ALLEXCEPT(<table>,<column>[,<column>[,…]])  

ALLEXCEPT - DAX 関数 - Microsoft Learn

---

「ALLEXCEPT」は、「ALLSELECTED」と逆に、指定した（テーブルの中の）列以外のフィルターを外します。
先ほどの「ALLSELECTED」を使った式と同様に「ALLEXCEPT('Product_Data', 'Product_Data'[Category]」とカテゴリを指定します。
なお、「ALLEXCEPT」では「<table>,<column>」とテーブル名を最初に指定します。

Sales_by_ALLEXCEPT1 = CALCULATE(SUM(Sales_Data[#Sales Amount]), ALLEXCEPT('Sales_Data',Sales_Data[Category]))

カテゴリはフィルター対象になるので、「Sales_by_ALLEXCEPT1」列にはカテゴリ別の合計値が入ります。

「Sales_by_ALLEXCEPT1」列にはカテゴリ別の集計値が入る

一方カラー別の集計を行うと、カテゴリ以外のフィルターは除去されるので、「Sales_by_ALLEXCEPT1」列にはSales_Data[#Sales Amount]の合計値が表示されます。

カラー別の集計を行うと、カテゴリ以外のフィルターを除去する機能が反映

使い道の例

例えば、あるカテゴリの商品の全体に対する割合は、「#Sales Amount」（の列）を「Sales_by_ALL2」（の列）で割れば算出できます。
１つの式で作ると以下のように記述できます。

メジャーCategory_rate = DIVIDE(SUM(Sales_Data[#Sales Amount]), CALCULATE(SUM(Sales_Data[#Sales Amount]), ALL(Sales_Data[Category])))

その結果です。

「ALL」関数を使いあるカテゴリの商品の全体に対する割合を算出

次に、月別売上の割合を集計します。
この場合、わかりやすいので年と月の列を用意します。

年や月の列を用意（表右側）

ALLで「月」のフィルターを無効にします

Sales_by_ALL3 = CALCULATE(SUM(Sales_Data[#Sales Amount]), ALL('Date'[Month_mm]))

そうすると、「Sales_by_ALL3」列には、月別の合計値でなく、総売上（各月の合計）が並びます。

カテゴリ別の割合を求めた式同様に、月別の割合を算出する式を作成します。

メジャーMonth_rate = DIVIDE(SUM(Sales_Data[#Sales Amount]), CALCULATE(SUM(Sales_Data[#Sales Amount]), ALL('Date'[Month_mm])))

各月の行に、年間合計売り上げに対する割合が表示されます。

月別の割合を算出

「Sales_by_ALL3」と「メジャーMonth_rate」の式では、「年」に対するフィルターは適用されます。
このためスライサーで「年」を指定すると、「Sales_by_ALL3」はその年の総売上げを、「メジャーMonth_rate」ではその年の中での各月の割合を算出できます。

各年の割合を計算