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