このセクションでは、データ分析式 (DAX) の数式内にフィルターを作成する方法について説明します。 式の中でフィルターを作成し、計算に使われるソース データから取得される値を制限できます。 この処理を行うには、式への入力として使うテーブルを指定し、フィルター式を定義します。 指定したフィルター式はデータの照会に使われ、ソース データのサブセットのみを返します。 式の結果を更新するたびに、データの現在のコンテキストに応じて、フィルターが動的に適用されます。
この記事の内容
式で使うテーブルに関するフィルターの作成
入力としてテーブルが使われる式の中で、フィルターを適用することができます。 指定したテーブルの行のサブセットを定義するには、テーブル名を入力する代わりに、FILTER 関数を使います。 このサブセットは別の関数に渡され、カスタム集計などの処理に使われます。
たとえば、販売店の受注情報を含むデータ テーブルを基に、各販売店の売上額を計算するとします。 ただし、表示したいのは、高価な製品を複数販売した販売店の売上額です。 次に示す式は、DAX サンプル ブックを基に、フィルターを使ってこの計算式を作成する例の 1 つです。
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100)、
'ResellerSales_USD'[SalesAmt]
)
数式の最初の部分では、テーブルを引数として受け取る Power Pivot 集計関数の 1 つを指定します。 SUMX はテーブルの合計を計算します。
数式の 2 番目の部分
FILTER(table, expression),は、使用するデータをSUMXに示します。SUMXテーブルまたはテーブルを作成する式が必要です。 ここでは、テーブル内のすべてのデータを使用する代わりに、FILTER関数を使用してテーブルのどの行を使用するかを指定します。
フィルター式は 2 つの部分で構成されます。最初の部分は、フィルターを適用するテーブルに名前を付けます。 第 2 の部分では、フィルター条件として使用する式を定義します。 この場合、5 台以上のユニットを販売したリセラーや、100 ドル以上の商品を販売したリセラーでフィルター処理します。 演算子 && は論理 AND 演算子です。これは、行がフィルター処理されたサブセットに属するためには、条件の両方の部分が True である必要があることを示します。数式の 3 番目の部分は、どの値を合計する必要があるかを
SUMXfunction に指示します。 この場合は、売上高のみを使います。
FILTER のようにテーブルを返す関数は、テーブルや行を直接返すことはなく、常に別の関数に埋め込まれることに注意してください。 その他の例を含め、FILTER やフィルター処理に使用されるその他の関数の詳細については、「 フィルター関数 (DAX)」を参照してください。注
フィルター式は、使われているコンテキストの影響を受けます。 たとえば、フィルターを使っているメジャーが、さらにピボットテーブルまたはピボットグラフで使われている場合、返されるデータのサブセットは、ユーザーがピボットテーブルで適用した他のフィルターまたはスライサーの影響を受けることがあります。 コンテキストの詳細については、「DAX の数式のコンテキスト」を参照してください。
重複を削除するフィルター
特定の値のフィルター処理を行うだけでなく、別のテーブルまたは列の一意の値セットを返すことができます。 この処理は、一意の値が列内にいくつあるかカウントしたり、一意の値のリストを他の演算に使ったりする場合に便利です。 DAX には、DISTINCT 関数および VALUES 関数という、重複しない値を返す関数が 2 つあります。
- DISTINCT 関数は、この関数の引数として指定された 1 つの列を調べて、重複しない値だけが含まれる新しい列を返します。
- VALUES 関数は一意の値のリストも返しますが、不明なメンバーも返します。 これは、リレーションシップによって結合された 2 つのテーブルの値を使用していて、一方のテーブルには存在しない値で、他方のテーブルには存在する値がある場合に便利です。 不明なメンバーの詳細については、「 DAX 数式のコンテキスト」を参照してください。
これらの関数はどちらも、値の列全体を返します。したがって、これらの関数を使用して、値のリストを取得して別の関数に渡します。 たとえば、次の数式を使えば、一意のプロダクト キーを使用して特定のリセラーが販売する個別の製品の一覧を取得し、COUNTROWS 関数を使用してそのリスト内の製品をカウントできます。
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
コンテキストがフィルターに与える影響
DAX 数式をピボットテーブルまたはピボットグラフに追加すると、数式の結果はコンテキストによって影響を受ける場合があります。 Power Pivot テーブルで作業している場合、コンテキストは現在の行とその値です。 ピボットテーブルまたはピボットグラフ内で作業している場合、コンテキストはスライスやフィルター処理などの処理によって定義されるデータのセットまたはサブセットを意味します。 ピボットテーブルまたはピボットグラフでは、設計上の理由から、独自のコンテキストも生じます。 たとえば、売上を地域別および年度別にグループ化するピボットテーブルを作成した場合、これらの地域および年度に該当するデータのみがピボットテーブルに表示されます。 そのため、ピボットテーブルに追加されたメジャーは、列見出しおよび行見出しに加えて、メジャーの数式内のフィルターというコンテキストに基づいて計算されます。
詳細については、「DAX の数式のコンテキスト」を参照してください。
フィルターの削除
複雑な数式を処理する場合は、現在のフィルターの内容を正確に把握したり、あるいは数式のフィルター部分を変更したりしなければならないことがあります。 DAX には、フィルターの削除や、現在のフィルター コンテキストの一部として保持される列の制御を行う関数がいくつか用意されています。 このセクションでは、これらの関数が数式の結果に及ぼす影響について概要を示します。
ALL 関数によるすべてのフィルターの上書き
ALL 関数を使用すると、以前に適用されたフィルターを上書きし、集計またはその他の操作を実行している関数にテーブル内のすべての行を返すことができます。 テーブルではなく 1 つ以上の列を ALL の引数として使用すると、 ALL 関数はすべての行を返し、コンテキスト フィルターを無視します。
注
リレーショナル データベースの用語に精通している場合、 ALL はすべてのテーブルの自然左外部結合を生成すると考えることができます。
たとえば、"売上高" と [商品] というテーブルがあり、現在の商品の売上合計をすべての商品の売上で割った値を計算する数式を作成したいとします。 この数式がメジャーで使用される場合、ピボットテーブルのユーザーは、行に製品名が表示されている特定の製品をフィルター処理するためにスライサーを使用している可能性があることに留意する必要があります。 したがって、フィルターやスライサーに関係なく分母の真値を取得するには、ALL 関数を追加してフィルターをオーバーライドする必要があります。 次の数式は、ALL を使用して以前のフィルターの効果を無効にする方法の一例です。
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
- 数式の最初の部分の SUM (Sales[Amount]) は分子を計算します。
- 合計には現在のコンテキストが反映されます。つまり、計算列に数式を追加した場合は、行コンテキストが適用されます。ピボットテーブルにメジャーとして数式を追加した場合は、ピボットテーブルに適用されているすべてのフィルター (フィルター コンテキスト) が適用されます。
- 数式の 2 番目の部分は分母を計算します。 ALL 関数は、
Productsテーブルに適用される可能性のあるすべてのフィルターをオーバーライドします。
例を含めて、詳細については、「ALL 関数 (DAX)」を参照してください。
ALLEXCEPT 関数による特定のフィルターの上書き
ALLEXCEPT 関数も既存フィルターを上書きしますが、既存フィルターの一部を維持するように指定できます。 ALLEXCEPT 関数の引数として列を指定すると、その列についてはフィルター処理が続けられます。 一部を除くほとんどの列のフィルター処理を上書きする場合は、ALL 関数よりも ALLEXCEPT 関数の方が便利です。 ALLEXCEPT 関数は、さまざまな列に対するフィルター処理ができるピボットテーブルを作成する場合や、数式で使われる値を制御する場合に特に便利です。 ピボットテーブルで LLEXCEPT を使う例を含めて、詳細については、「ALLEXCEPT 関数 (DAX)」を参照してください。