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]))

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

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

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

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

DAXのALLSELECTED

ALLSELECTED

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

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

ALLSELECTED – DAX 関数 – Microsoft Learn

「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」でも全カテゴリの合算値が出力されます。

次にカテゴリー別でない集計をします。
例えばカラー別。
「Sales_by_ALLSELECTED2」は、カラーに対してはフィルターが適用されるので、今度は色別の合計値が並びます。
「Sales_by_ALL0」も同様で、カラーのフィルターは有効なので同じ合計値が返されます。

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 Amount」（の列）を「Sales_by_ALL2」（の列）で割れば算出できます。
１つの式で作ると以下のように記述できます。

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

その結果です。

月別売上の割合を集計をする場合、年と月の列を用意したほうがわかりやすいです。

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」ではその年の中での各月の割合を算出できます。