数式内でのデータのフィルター処理
数式内にフィルターを作成して、計算で使用するソース データの値を制限できます。 これを行うには、数式への入力としてテーブルを指定してから、フィルター式を定義します。 定義したフィルター式を使用してデータを要求するクエリが実行され、ソース データのサブセットのみが返されます。 フィルターは、データの現在のコンテキストに応じて、ユーザーが数式の結果を更新するたびに動的に適用されます。 ここでは、Data Analysis Expressions (DAX) 式内にフィルターを作成する方法について説明します。
数式内でのテーブルのフィルターの作成
入力としてテーブルを受け取る数式にフィルターを適用できます。 テーブル名を入力する代わりに、指定するテーブルの行のサブセットを定義する FILTER 関数を使用します。 そのサブセットは、カスタム集計などの演算を実行する別の関数に渡されます。
たとえば、再販業者に関する注文情報が保存されているデータのテーブルがあり、各再販業者の販売高を計算するとします。 ただし、より高値の製品を複数ユニット販売した再販業者だけの売上高を示す必要があります。 次の数式は、DAX サンプル ブックに基づいており、フィルターを使用してこの計算を作成する方法の一例です。
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
数式の最初の部分は、PowerPivot 集計関数の 1 つで、引数としてテーブルを受け取ります。 SUMX により、テーブル全体の合計が計算されます。
数式の次の部分の FILTER(table, expression) では、SUMX で使用するデータを指定しています。 SUMX には、テーブルまたは結果としてテーブルになる式を指定する必要があります。 ここでは、テーブルのすべてのデータを使用する代わりに FILTER 関数を使用して、テーブルのどの行を使用するかを指定しています。
フィルター式は 2 つの部分で構成されています。最初の部分は、フィルターを適用するテーブルの名前を指定しています。 次の部分は、フィルター処理の条件として使用する式を定義しています。 この例では、6 ユニット以上かつ 100 ドルを超える製品を販売した再販業者をフィルター処理しています。 && 演算子は論理 AND 演算子であり、フィルター選択されたサブセットに属する行は両方の条件が true でなければならないことを示しています。
数式の最後の部分は SUMX 関数が合計する対象の値を指定しています。 この例では、売上高だけを使用しています。
テーブルを返す FILTER などの関数は、PowerPivot ブックにテーブルまたは行を直接返すことはできません。必ず、他の関数の内側で使用します。 フィルター処理に使用する FILTER およびその他の関数の詳細と使用例については、「フィルター関数 (DAX)」を参照してください。
.gif "注意") 注 |
|---|
フィルター式は、そのフィルター式が使用されているコンテキストの影響を受けます。 たとえば、メジャー内でフィルターを使用し、そのメジャーをピボットテーブルまたはピボットグラフで使用する場合は、ユーザーがピボットテーブル内で適用した他のフィルターまたはスライサーの影響が、返されるデータのサブセットに及ぶ可能性があります。 コンテキストの詳細については、「DAX の数式のコンテキスト」を参照してください。 |
重複を除外するフィルター
特定の値をフィルター処理するだけでなく、別のテーブルまたは列から一意の値のセットを取得することもできます。 これは、列内の一意の値の数をカウントする場合や、別の演算で一意の値の一覧を使用する場合に便利です。 DAX には、DISTINCT 関数 および VALUES 関数 という、一意の値を返す 2 つの関数が用意されています。
DISTINCT 関数は、関数の引数として指定された単一の列を調べ、一意の値のみが格納された新しい列を返します。
VALUES 関数も一意の値の一覧を返しますが、さらに不明なメンバーも返します。 これは、リレーションシップで結合されている 2 つのテーブルの値を使用する場合に、一方のテーブルに存在しない値がもう一方のテーブルに存在しているときに便利です。 不明なメンバーの詳細については、「DAX の数式のコンテキスト」を参照してください。
これらの関数はどちらも値が格納された列全体を返すので、これらの関数を使用して値の一覧を取得し、その一覧を別の関数に渡すことができます。 たとえば、次の式では、一意の製品キーを使用することで、特定の再販業者が販売した特定の製品の一覧を取得し、さらに COUNTROWS 関数を使用することで、その一覧の製品をカウントできます。
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
フィルターに対するコンテキストの影響
DAX 数式をピボットテーブルまたはピボットグラフに追加する場合、数式の結果がコンテキストの影響を受ける可能性があります。 PowerPivot テーブルで作業している場合、コンテキストとは現在の行とその値です。 ピボットテーブルまたはピボットグラフで作業している場合、スライスまたはフィルター処理などの操作で定義される、データ セットまたはデータのサブセットがコンテキストになります。 ピボットテーブルまたはピボットグラフのデザインも、それぞれのコンテキストに影響を及ぼします。 たとえば、地域別および年別に売上をグループ化するピボットテーブルを作成した場合、これらの地域および年に該当するデータのみがピボットテーブルに表示されます。 このため、ピボットテーブルに追加するすべてのメジャーは、列および行見出し、およびメジャー式のすべてのフィルターのコンテキストで計算されます。
詳細については、「DAX の数式のコンテキスト」を参照してください。
フィルターの削除
複雑な数式を扱う場合、現在適用されているフィルターを正確に把握する必要がある場合や、数式のフィルター部分を変更する必要が生じる場合があります。 DAX には、フィルターを削除し、現在のフィルターのコンテキストとして保持する列を制御する関数がいくつか用意されています。 ここでは、これらの関数が数式の結果に及ぼす影響の概要について説明します。
ALL 関数を使用した全フィルターの無効化
ALL 関数を使用すると、適用済みのすべてのフィルターを無効にし、集計などの演算を実行する関数にテーブルのすべての行を渡すことができます。 ALL の引数としてテーブルではなく 1 つ以上の列を使用すると、ALL 関数はコンテキスト フィルターを無視して、すべての行を返します。
| 注 |
|---|
リレーショナル データベースの用語に置き換えると、ALL はすべてのテーブルの左外部自然結合の生成であると考えることができます。 |
たとえば、Sales テーブルと Products テーブルがあり、現在の製品の売上の合計をすべての製品の売上で除算する数式を作成するとします。 数式をメジャーで使用する場合は、ピボットテーブルのユーザーがスライサーを使用して、行に格納された製品名で特定の製品をフィルター処理する可能性があることを考慮する必要があります。 このため、フィルターまたはスライサーに関係なく正確な値の分母を計算するには、すべてのフィルターを無効にする ALL 関数を追加する必要があります。 次の数式は、適用済みのフィルターの影響を ALL を使用して無効にする方法の一例です。
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
数式の最初の部分である SUM (Sales[Amount]) では、分子を計算しています。
SUM では現在のコンテキストが考慮されます。つまり、計算列に数式を追加する場合は、行のコンテキストが適用されます。また、ピボットテーブルにメジャーとして数式を追加する場合は、そのピボットテーブルに適用されているフィルター (フィルター コンテキスト) が適用されます。
数式の次の部分では、分母を計算しています。 ALL 関数は、Products テーブルに適用されている可能性があるすべてのフィルターを無効にします。
例を含む詳細については、「ALL 関数」を参照してください。
ALLEXCEPT 関数を使用した特定のフィルターの無効化
ALLEXCEPT 関数も既存のフィルターを無効にしますが、一部のフィルターを有効なままにすることを指定できます。 ALLEXCEPT 関数の引数で指定する列は、フィルター処理をそのまま適用する列です。 ほとんどの列のフィルターを無効にするがすべてではない場合は、ALL よりも ALLEXCEPT が適しています。 ALLEXCEPT 関数は、さまざまな列に対してフィルター処理が実行される可能性のあるピボットテーブルを作成する場合に、数式で使用する値を制御するときに特に便利です。 ピボットテーブルでの ALLEXCEPT の使用例などの詳細については、「ALLEXCEPT 関数」を参照してください。