Beziehungen and Suchvorgänge in Formeln
Eine der leistungsstärksten Funktionen in PowerPivot für Excel ist die Möglichkeit, Beziehungen zwischen Tabellen zu erstellen und dann die verknüpften Tabellen zu verwenden, um nach verknüpften Daten zu suchen oder diese zu filtern. Sie rufen verknüpfte Werte aus Tabellen ab, indem Sie die DAX-Formelsprache (Data Analysis Expressions) verwenden, die mit PowerPivot für Excel bereitgestellt wird. DAX verwendet ein relationales Modell und kann entsprechende Werte in einer anderen Tabelle oder Spalte daher einfach und genau abrufen.
Sie können Formeln, die Suchvorgängen durchführen, als Teil einer berechneten Spalte oder als Teil eines Measures zur Verwendung in einer PivotTable oder einem PivotChart erstellen. Weitere Informationen finden Sie in den folgenden Themen:
Erstellen einer berechneten Spalte
In diesem Abschnitt werden die DAX-Funktionen für Suchvorgänge mit einigen Anwendungsbeispielen beschrieben.
Hinweis |
---|
Je nach Typ des Suchvorgangs oder der Suchformel, den bzw. die Sie verwenden möchten, müssen Sie zuerst eine Beziehung zwischen den Tabellen erstellen. Weitere Informationen zur Erstellung von Beziehungen finden Sie unter Beziehungen zwischen Tabellen. |
Grundlegendes zu Suchfunktionen
Die Möglichkeit, übereinstimmende oder verknüpfte Daten in einer anderen Tabelle zu suchen, ist besonders nützlich, wenn die aktuelle Tabelle lediglich einen Bezeichner enthält und die eigentlich benötigten Daten (wie Produktpreis, Name o. Ä.), in einer verknüpften Tabelle gespeichert sind. Diese Möglichkeit ist ebenfalls hilfreich, wenn mehrere Zeilen in einer anderen Tabelle mit der aktuellen Zeile bzw. dem aktuellen Wert verknüpft sind. Beispielsweise können Sie auf einfache Weise alle Verkäufe abrufen, die an eine bestimmte Region, ein Geschäft oder einen Vertriebsmitarbeiter gebunden sind.
Im Gegensatz zu Excel-Suchfunktionen wie VLOOKUP (SVERWEIS), die auf Arrays basiert, oder LOOKUP (VERWEIS), bei der der erste von mehreren übereinstimmenden Werten abgerufen wird, werden in DAX vorhandene Beziehungen zwischen per Schlüssel verknüpften Tabellen nachverfolgt, um einen einzelnen verknüpften Wert mit genauer Übereinstimmung abzurufen. DAX kann auch eine Tabelle von Datensätzen abrufen, die sich auf den aktuellen Datensatz beziehen.
Hinweis |
---|
Wenn Sie mit relationalen Datenbanken vertraut sind, können Sie sich die Suchvorgänge in PowerPivot wie eine geschachtelte untergeordnete SELECT-Anweisung in Transact-SQL vorstellen. |
Weitere Informationen zu dem in PowerPivot verwendeten relationalen Modell finden Sie unter Übersicht über Beziehungen.
Abrufen eines einzelnen verknüpften Werts
Die RELATED-Funktion gibt einen einzelnen Wert aus einer anderen Tabelle zurück, der mit dem aktuellen Wert in der aktuellen Tabelle verknüpft ist. Sie geben die Spalte an, die die gewünschten Daten enthält. Die Funktion folgt vorhandenen Beziehungen zwischen Tabellen, um den Wert aus der angegebenen Spalte in der verknüpften Tabelle abzurufen. In einigen Fällen muss die Funktion einer Kette von Beziehungen folgen, um die Daten abzurufen.
Beispiel. Angenommen, Sie verfügen über ein Excel-Dokument, in dem aktuelle Lieferungen aufgeführt sind. Die Liste enthält jedoch nur eine Mitarbeiter-, eine Auftrags- und eine Versenderidentifikationsnummer, sodass die Liste schwer zu lesen ist. So rufen Sie zusätzliche gewünschte Informationen ab: Konvertieren Sie die Liste in eine verknüpfte PowerPivot-Tabelle, und erstellen Sie anschließend Beziehungen zur Employee-Tabelle und zur Reseller-Tabelle, um das EmployeeID-Feld dem EmployeeKey-Feld und das ResellerID-Feld dem ResellerKey-Feld zuzuweisen.
Fügen Sie zwei neue berechnete Spalten mit den folgenden Formeln hinzu, um die Suchinformationen in der verknüpften Tabelle anzuzeigen:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Vor Suchvorgang |
Nach Suchvorgang |
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
|
Die Funktion verwendet die Beziehungen zwischen der verknüpften Tabelle und der Employees-Tabelle sowie der Resellers-Tabelle, um jeweils den richtigen Namen für die einzelnen Zeilen im Bericht abzurufen. Sie können die verknüpften Werte auch für Berechnungen verwenden. Weitere Informationen und Beispiele finden Sie unter RELATED-Funktion (DAX).
Abrufen einer Liste mit verknüpften Werten
Die RELATEDTABLE-Funktion verfolgt eine vorhandene Beziehung und gibt eine Tabelle zurück, die alle übereinstimmenden Zeilen aus der angegebenen Tabelle enthält. Angenommen, Sie möchten ermitteln, wie viele Bestellungen von den einzelnen Wiederverkäufern dieses Jahr aufgegeben wurden. In diesem Fall können Sie eine neue berechnete Spalte in der Resellers-Tabelle erstellen, die folgende Formel enthält. Mit dieser Formel werden in der ResellerSales_USD-Tabelle die den jeweiligen Wiederverkäufern zugehörigen Datensätze gesucht, und für jeden Wiederverkäufer wird die jeweilige Anzahl aufgegebener Bestellungen ermittelt. Die Tabellen sind Teil der DAX-Beispielarbeitsmappe. Weitere Informationen zu Beispieldaten finden Sie unter Abrufen von Beispieldaten für PowerPivot.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
In dieser Formel ruft die RELATEDTABLE-Funktion zuerst den ResellerKey-Wert für jeden Wiederverkäufer in der aktuellen Tabelle ab. Sie müssen die ID-Spalte nicht in der Formel angeben, da von PowerPivot die vorhandene Beziehung zwischen den Tabellen verwendet wird. Von der RELATEDTABLE-Funktion werden dann alle Zeilen aus der ResellerSales_USD-Tabelle abgerufen, die sich auf die jeweiligen Wiederverkäufer beziehen, und es werden alle Zeilen gezählt. Liegt keine direkte oder indirekte Beziehung zwischen den zwei Tabellen vor, werden alle Zeilen aus der Tabelle ResellerSales_USD abgerufen.
Für Modular Cycle Systems in unserer Beispieldatenbank gibt es vier Bestellungen in der Verkaufstabelle, weshalb die Funktion 4 zurückgibt. Für Associated Bikes liegen keine Verkäufe vor, deshalb wird von der Funktion eine leere Menge zurückgegeben.
Reseller |
Datensätze in der Verkaufstabelle für diesen Wiederverkäufer |
Modular Cycle Systems |
Reseller IDSalesOrderNumber
445SO53494
445SO71872
445SO65233
445SO59000
|
Associated Bikes |
|
Hinweis |
---|
Da die RELATEDTABLE-Funktion eine Tabelle zurückgibt, nicht einen einzelnen Wert, muss sie als Argument in einer Funktion verwendet werden, die Vorgänge für Tabellen ausführt. Weitere Informationen finden Sie unter RELATEDTABLE-Funktion (DAX). |
Siehe auch
Konzepte
Andere Ressourcen
Key Concepts in DAX