数式内でのリレーションシップと参照
PowerPivot for Excel の最も強力な機能の 1 つに、テーブル間のリレーションシップを作成してから関連テーブルを使用して、関連するデータを参照またはフィルター処理する機能があります。関連する値をテーブルから取得するには、PowerPivot for Excel に用意されている数式言語である Data Analysis Expressions (DAX) を使用します。DAX はリレーショナル モデルを使用しているため、他のテーブルまたは列の関連値や対応する値を簡単かつ正確に取得できます。
参照を実行する数式は、計算列の一部として、またはピボットテーブルやピボットグラフで使用するメジャーの一部として作成できます。詳細については、次のトピックを参照してください。
ここでは、参照用の DAX 関数と、それらの関数の使用方法の例を紹介します。
.gif "注意") 注 |
|---|
使用する参照操作または参照式の種類によって、先にテーブル間のリレーションシップを作成する必要がある場合があります。リレーションシップ作成の詳細については、「テーブル間のリレーションシップ」を参照してください。 |
参照関数について
別のテーブルの一致するデータや関連データを参照する機能が特に役立つのは、現在のテーブルに何らかの識別子だけが含まれており、必要とするデータ (製品の価格、名前、その他の詳細な値など) が関連テーブルに保存されている場合です。また、現在の行や現在の値に関連付けられている行が別のテーブルに複数存在する場合にも役立ちます。たとえば、特定の地域、店舗、または販売員に関連付けられた総売上を簡単に取得できます。
Excel の参照関数には、VLOOKUP のように配列に基づいて参照する方法と、LOOKUP のように一致する複数の値のうちの最初の値を取得する方法がありますが、DAX はこれとは異なり、キーによって結合されたテーブル間の既存のリレーションシップに従って、正確に一致する 1 つの関連値を取得します。また、DAX は現在のレコードに関連付けられているレコードのテーブルを取得することもできます。
| 注 |
|---|
リレーショナル データベースについて熟知している場合、PowerPivot における参照は、Transact-SQL における入れ子にされたサブセレクト ステートメントと同様のものと考えることができます。 |
PowerPivot で使用されるリレーショナル モデルの詳細については、「リレーションシップの概要」を参照してください。
単一の関連する値の取得
RELATED 関数は、別のテーブルから、現在のテーブルの現在の値に関連する単一の値を返します。目的のデータが格納された列が指定されると、関数はテーブル間の既存のリレーションシップを追跡し、関連テーブル内の指定された列から値をフェッチします。場合によっては、関数がデータを取得するために、連鎖したリレーションシップの追跡が必要になることもあります。
たとえば、Excel 形式で本日の出荷の一覧があるとします。ところが、一覧に含まれているのは従業員 ID 番号、注文 ID 番号、および再販業者 ID 番号のみであり、わかりにくいレポートになっています。追加情報を取得するには、この一覧を PowerPivot リンク テーブルに変換し、Employee テーブルと Reseller テーブルへのリレーションシップを作成して、EmployeeID を EmployeeKey フィールドに、ResellerID を ResellerKey フィールドに照合します。
リンク テーブル内の参照情報を表示するには、次の数式を使用して新しい計算列を 2 つ追加します。
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
参照前 |
参照後 |
Order IDEmployeeIDResellerID
100314230445
10031515445
10031676108
EmployeeIDEmployeeReseller
230Kuppa VamsiModular Cycle Systems
15Pilar AckemanModular Cycle Systems
76Kim RallsAssociated Bikes
|
Order IDEmployeeIDResellerIDEmployeeReseller
100314230445Kuppa VamsiModular Cycle Systems
10031515445Pilar AckemanModular Cycle Systems
10031676108Kim RallsAssociated Bikes
|
この関数では、リンク テーブルと Employees テーブルおよび Resellers テーブルとの間のリレーションシップを使用して、レポート内の各行に正しい名前を取得しています。関連する値を計算に使用することもできます。詳細と例については、「RELATED 関数 (DAX)」を参照してください。
一連の関連する値の取得
RELATEDTABLE 関数は、既存のリレーションシップに従って、指定されたテーブルから、一致しているすべての行を取得し、テーブルとして返します。たとえば、各再販業者からの今年の注文数を調べる必要があるとします。Resellers テーブルに、次の数式が格納された新しい計算列を作成できます。この数式は、各再販業者のレコードを ResellerSales_USD テーブル内で参照し、再販業者ごとに個々の注文数をカウントします。これらのテーブルは、DAX サンプル ブックに含まれています。サンプル データの詳細については、「PowerPivot のサンプル データの入手」を参照してください。
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
この数式では、まず RELATEDTABLE 関数が、現在のテーブル内の再販業者ごとに ResellerKey の値を取得します (PowerPivot ではテーブル間の既存のリレーションシップが使用されるため、数式内で ID 列を指定する必要はありません)。次に RELATEDTABLE 関数が、各再販業者に関連付けられているすべての行を ResellerSales_USD テーブルから取得し、行数をカウントします。 2 つのテーブルの間に (直接的にも間接的にも) リレーションシップがない場合は、ResellerSales_USD テーブルの行がすべて取得されます。
サンプル データベースに登録されている Modular Cycle Systems という再販業者の場合は、ResellerSales_USD テーブルに 4 件の注文があるので、関数から 4 が返されます。Associated Bikes という再販業者については売上がないので、関数は空白を返します。
Reseller |
この Reseller に対応する ResellerSales_USD テーブル内のレコード |
Modular Cycle Systems |
Reseller IDSalesOrderNumber
445SO53494
445SO71872
445SO65233
445SO59000
|
Associated Bikes |
|
| 注 |
|---|
RELATEDTABLE 関数が返すのは単一の値ではなくテーブルなので、テーブルに関する演算を実行する関数の引数として使用する必要があります。詳細については、「RELATEDTABLE 関数 (DAX)」を参照してください。 |