Kontext in DAX-Formeln
Mithilfe des Kontexts können Sie eine dynamische Analyse ausführen, in der sich die Ergebnisse einer Formel ändern können, um die aktuelle Zeilen- oder Zellenauswahl sowie verknüpfte Daten widerzuspiegeln. Es ist sehr wichtig, dass Sie den Kontext verstehen und anwenden, um leistungsstarke, dynamische Analysen erstellen und Probleme in Formeln beheben zu können.
In diesem Abschnitt werden die verschiedenen Kontexttypen definiert: Zeilenkontext, Abfragekontext und Filterkontext. Es wird erläutert, wie der Kontext für Formeln in berechneten Spalten und in PivotTables ausgewertet wird.
Der letzte Teil dieses Abschnitts enthält Links zu ausführlichen Beispielen, die veranschaulichen, wie sich die Ergebnisse von Formeln je nach Kontext ändern.
Einführung in den Kontext
Formeln in PowerPivot können durch die in einer PivotTable angewendeten Filter (durch Beziehungen zwischen Tabellen) sowie durch in Formeln verwendete Filter beeinflusst werden. Durch den Kontext wird die Durchführung dynamischer Analysen ermöglicht. Ein Verständnis des Kontexts ist wichtig, um Formeln erstellen und ggf. Fehler beheben zu können.
Es gibt verschiedene Typen von Kontext: Zeilenkontext, Abfragekontext und Filterkontext.
Stellen Sie sich den Zeilenkontext als "die aktuelle Zeile" vor. Wenn Sie eine berechnete Spalte erstellt haben, besteht der Zeilenkontext aus den Werten in jeder einzelnen Zeile und den Werten in den Spalten, die mit der aktuellen Zeile verknüpft sind. Es gibt auch einige Funktionen (EARLIER und EARLIEST), die einen Wert aus der aktuellen Zeile abrufen und dann beim Ausführen eines Vorgangs über eine gesamte Tabelle diesen Wert verwenden.
Der Abfragekontext verweist auf die Teilmenge von Daten, die implizit für jede Zelle in einer PivotTable erstellt wird, abhängig von der Zeilen- und Spaltenüberschrift.
Der Filterkontext ist der Satz von Werten, der in jeder Spalte zulässig ist. Die Grundlage dafür sind Filtereinschränkungen, die auf die Zeile angewendet wurden oder die durch Filterausdrücke innerhalb der Formel definiert sind.
Im folgenden Thema werden die verschiedenen Kontexttypen ausführlicher erläutert: Kontext in DAX-Formeln.
Zurück zum Anfang
Zeilenkontext
Wenn Sie in einer berechneten Spalte eine Formel erstellen, enthält der Zeilenkontext für diese Formel die Werte aller Spalten in der aktuellen Zeile. Wenn die Tabelle mit einer anderen Tabelle verknüpft ist, enthält der Kontext auch alle Werte aus der anderen Tabelle, die mit der aktuellen Zeile verknüpft sind.
Angenommen, Sie erstellen die berechnete Spalte =[Freight] + [Tax], die zwei Spalten aus der gleichen Tabelle addiert. Diese Formel verhält sich wie Formeln in einer Excel-Tabelle, die automatisch auf Werte in der gleichen Zeile verweist. Beachten Sie, dass sich Tabellen von Bereichen unterscheiden: Sie können mit der Bereichsschreibweise nicht auf einen Wert aus einer Zeile vor der aktuellen Zeile verweisen, und Sie können nicht auf jeden beliebigen einzelnen Wert in einer Tabelle oder Zelle verweisen. Sie müssen immer mit Tabellen und Spalten arbeiten.
Der Zeilenkontext folgt automatisch den Beziehungen zwischen Tabellen, um zu bestimmen, welche Zeilen in verknüpften Tabellen der aktuellen Zeile zugeordnet sind.
In der folgenden Formel wird z. B. mit der RELATED-Funktion auf Grundlage des Lands, in das die Bestellung ausgeliefert wurde, ein Steuerwert aus einer verknüpften Tabelle abgerufen. Der Steuerwert wird mit dem Wert für Region in der aktuellen Tabelle ermittelt. Die Region wird in der verknüpften Tabelle nachgeschlagen, und anschließend wird der Steuersatz für diese Region aus der verknüpften Tabelle abgerufen.
= [Freight] + RELATED('Region'[TaxRate])
Mit dieser Formel wird einfach der Steuersatz für die aktuelle Region aus der Tabelle Region abgerufen. Sie müssen den Schlüssel zum Verknüpfen der Tabellen nicht kennen oder angeben.
Mehrere Zeilenkontexte
Darüber hinaus umfasst DAX Funktionen, die eine Tabelle durchlaufen, um Berechnungen durchzuführen. Diese Funktionen können über mehrere aktuelle Zeilen und aktuelle Zeilenkontexte verfügen. Programmiertechnisch gesehen können Sie Formeln erstellen, die über eine innere und äußere Schleife rekursieren.
Angenommen, die Arbeitsmappe enthält eine Products-Tabelle und eine Sales-Tabelle. Sie können z. B. die gesamte Vertriebstabelle durchlaufen, die mit Transaktionen verschiedener Produkte gefüllt ist, um die größte Bestellmenge für jedes Produkt in einer beliebigen Transaktion zu ermitteln.
In Excel erfordert diese Berechnung eine Reihe von Zwischenzusammenfassungen, die neu erstellt werden müssten, wenn die Daten geändert werden. Als erfahrener Excel-Benutzer könnten Sie Arrayformeln erstellen, um die Aufgabe zu erledigen. Alternativ könnten Sie in einer relationalen Datenbank geschachtelte untergeordnete SELECT-Anweisungen schreiben.
Mit DAX können Sie jedoch eine einzelne Formel erstellen, die den richtigen Wert zurückgibt, und die Ergebnisse werden automatisch bei jedem Hinzufügen von Daten zu den Tabellen aktualisiert.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Eine ausführliche exemplarische Vorgehensweise zu dieser Formel finden Sie unter EARLIER-Funktion (DAX).
Zusammengefasst speichert die EARLIER-Funktion den Zeilenkontext des Vorgangs, der dem aktuellen Vorgang vorausging. Die Funktion speichert zu jedem Zeitpunkt zwei Kontextsätze im Arbeitsspeicher: Ein Kontextsatz stellt die aktuelle Zeile für die innere Schleife der Formel dar, und ein weiterer Kontextsatz stellt die aktuelle Zeile für die äußere Schleife der Formel dar. DAX übermittelt automatisch Werte zwischen den zwei Schleifen, sodass komplexe Aggregate erstellt werden können.
Abfragekontext
Der Abfragekontext verweist auf die Teilmenge von Daten, die implizit für eine Formel abgerufen werden. Wenn Sie ein Measure oder ein anderes Wertfeld in eine Zelle einer PivotTable ablegen, werden die Zeilen- und Spaltenüberschriften, Datenschnitte und Berichtsfilter vom PowerPivot-Modul untersucht, um den Kontext zu ermitteln. Anschließend führt PowerPivot die notwendigen Berechnungen durch, um alle Zellen in der PivotTable auszufüllen. Der Datensatz, der abgerufen wird, ist der Abfragekontext für jede Zelle.
Da sich der Kontext je nach Position, an der Sie die Formel platzieren, ändern kann, ändern sich auch die Ergebnisse der Formel je nachdem, ob Sie sie in einer PivotTable mit vielen Gruppierungen und Filtern oder in einer berechneten Spalte ohne Filter und mit wenig Kontext verwenden.
Angenommen, Sie erstellen diese einfache Formel, die die Werte in der Profit-Spalte der Sales-Tabelle addiert: =SUM('Sales'[Profit]). Wenn Sie diese Formel in einer berechneten Spalte innerhalb der Sales-Tabelle verwenden, sind die Ergebnisse der Formel für die gesamte Tabelle identisch, da der Abfragekontext für die Formel immer dem gesamten Dataset der Sales-Tabelle entspricht. Die Ergebnisse enthalten somit den Gewinn für alle Regionen, alle Produkte, alle Jahre usw.
In der Regel möchten Sie jedoch nicht Hunderte Male das gleiche Ergebnis anzeigen, sondern den Gewinn für ein bestimmtes Jahr, ein bestimmtes Land, ein bestimmtes Produkt oder eine Kombination daraus abrufen, um das Gesamtergebnis zu ermitteln.
Sie können den Kontext in einer PivotTable auf einfache Weise ändern, indem Sie Spalten- und Zeilenüberschriften oder Datenschnitte hinzufügen bzw. entfernen. Sie können eine Formel wie oben in einem Measure erstellen und dieses in eine PivotTable ziehen. Jedes Mal, wenn Sie der PivotTable Spalten- oder Zeilenüberschriften hinzufügen, wird der Abfragekontext geändert, in dem das Measure ausgewertet wird. Slice- und Filterungsvorgänge beeinflussen ebenfalls den Kontext. Daher wird die gleiche Formel, in einer PivotTable verwendet, für jede Zelle in einem anderen Abfragekontext ausgewertet.
Filterkontext
Der Filterkontext wird hinzugefügt, wenn Sie Filtereinschränkungen für den in einer Spalte oder einer Tabelle zulässigen Satz von Werten angeben, indem Sie in einer Formel Argumente verwenden. Der Filterkontext wird zusätzlich zu anderem Kontext wie Zeilenkontext oder Abfragekontext angewendet.
Beispielsweise werden in einer PivotTable die Werte für jede Zelle anhand der Zeilen- und Spaltenüberschriften berechnet, wie im vorangehenden Abschnitt zum Abfragekontext beschrieben. Innerhalb der Measures oder berechneten Spalten, die Sie der PivotTable hinzufügen, können Sie jedoch Filterausdrücke angeben, um die von der Formel verwendeten Werte zu steuern. Sie können die Filter auch selektiv für bestimmte Spalten löschen.
Weitere Informationen zum Erstellen von Filtern in Formeln finden Sie unter FILTER-Funktion (DAX).
Ein Beispiel dazu, wie Filter gelöscht werden können, um Gesamtergebnisse zu erzeugen, finden Sie unter ALL-Funktion (DAX).
Beispiele zum selektiven Löschen und Anwenden von Filtern innerhalb von Formeln finden Sie unter ALLEXCEPT-Funktion (DAX).
Daher sollten Sie die Definition von Measures oder Formeln überprüfen, die in einer PivotTable verwendet werden, damit Sie beim Interpretieren der Formelergebnisse den Filterkontext kennen.
Bestimmen des Kontexts in Formeln
Wenn Sie eine Formel erstellen, überprüft PowerPivot für Excel zuerst die allgemeine Syntax und vergleicht dann die von Ihnen bereitgestellten Namen der Spalten und Tabellen mit möglichen Spalten und Tabellen im aktuellen Kontext. Wenn PowerPivot die in der Formel angegebenen Spalten und Tabellen nicht findet, wird ein Fehler angezeigt.
Der Kontext wird, wie in den vorangehenden Abschnitten beschrieben, anhand der verfügbaren Tabellen in der Arbeitsmappe, der Beziehungen zwischen den Tabellen und der angewendeten Filter bestimmt.
Wenn Sie z. B. gerade einige Daten in eine neue Tabelle importiert und keine Filter angewendet haben, ist der ganze Satz von Spalten in der Tabelle Teil des aktuellen Kontexts. Wenn Sie mehrere Tabellen haben, die durch Beziehungen verknüpft sind, und eine PivotTable verwenden, die nach dem Hinzufügen von Spaltenüberschriften und Verwenden von Datenschnitten gefiltert wurde, schließt der Kontext die verknüpften Tabellen und alle auf die Daten angewendeten Filter ein.
Der Kontext ist ein leistungsstarkes Konzept, das die Fehlerbehebung in Formeln allerdings auch erschweren kann. Sie sollten daher mit einfachen Formeln und Beziehungen beginnen, um die Funktionsweise des Kontexts zu verstehen, und dann mit einfachen Formeln in PivotTables experimentieren. Der folgende Abschnitt enthält auch einige Beispiele für Formeln, in denen verschiedene Kontexttypen zur dynamischen Rückgabe von Ergebnissen verwendet werden.
Beispiele für Kontext in Formeln
Die RELATED-Funktion erweitert den Kontext der aktuellen Zeile, um Werte in einer verknüpfte Spalte einzuschließen. Auf diese Weise können Sie Suchvorgänge ausführen. Das Beispiel in diesem Thema veranschaulicht die Interaktion des Filter- und Zeilenkontexts.
Mit der FILTER-Funktion können Sie die Zeilen angeben, die in den aktuellen Kontext eingeschlossen werden sollen. Zudem wird anhand der Beispiele in diesem Thema veranschaulicht, wie Filter in andere Funktionen, die Aggregate ausführen, eingebettet werden.
Die ALL-Funktion legt den Kontext in einer Formel fest. Sie können Filter, die als Ergebnis des Abfragekontexts angewendet werden, mithilfe der ALL-Funktion außer Kraft setzen.
Mit der ALLEXCEPT-Funktion können Sie alle Filter bis auf den angegebenen Filter entfernen. Beide Themen enthalten Beispiele, die Sie durch das Erstellen von Formeln führen, um komplexe Kontexte besser verstehen zu können.
Mit den Funktionen EARLIER und EARLIEST können Sie Tabellen durchlaufen, indem Berechnungen durchgeführt werden, während von einer inneren Schleife auf einen Wert verwiesen wird. Wenn Sie mit dem Konzept der Rekursion und inneren und äußeren Schleifen vertraut sind, werden Sie die Leistungsfähigkeit zu schätzen wissen, die die Funktionen EARLIER und EARLIEST bereitstellen. Wenn Sie mit diesen Konzepten nicht vertraut sind, sollten Sie die Schritte in dem Beispiel sorgfältig durchführen, um den inneren und äußeren Kontext beim Durchführen von Berechnungen zu verstehen.
Referenzielle Integrität
In diesem Abschnitt werden einige erweiterte Konzepte hinsichtlich fehlender Werte in PowerPivot-Tabellen erläutert, die von Beziehungen verbunden werden. Dieser Abschnitt ist möglicherweise nützlich, wenn Sie Arbeitsmappen mit mehreren Tabellen und komplexen Formeln verwenden und Hilfe benötigen, um die Ergebnisse besser zu verstehen.
Falls Sie noch keine Erfahrung mit relationalen Datenkonzepten haben, empfiehlt es sich, zuerst das Einführungsthema Übersicht über Beziehungen zu lesen.
Referenzielle Integrität und PowerPivot-Beziehungen
In PowerPivot muss zwischen zwei Tabellen keine referenzielle Integrität erzwungen werden, um eine gültige Beziehung zu definieren. Stattdessen wird eine leere Zeile auf der 1-Seite jeder 1:n-Beziehung erstellt und zur Verarbeitung aller nicht übereinstimmenden Zeilen aus der verknüpften Tabelle verwendet. Sie verhält sich praktisch wie ein äußerer Join in SQL.
Wenn Sie in PivotTables Daten auf der 1-Seite der Beziehung gruppieren, werden alle nicht übereinstimmenden Daten auf der n-Seite der Beziehung gruppiert und mit einer leeren Zeile als Überschrift in Summen aufgenommen. Die leere Überschrift entspricht in etwa dem unbekannten Element.
Grundlegendes zum unbekannten Element
Wenn Sie mit mehrdimensionalen Datenbanksystemen gearbeitet haben, z. B. SQL Server Analysis Services, sind Sie wahrscheinlich bereits mit dem Konzept des unbekannten Elements vertraut. Wenn Ihnen der Begriff neu ist, erklärt das folgende Beispiel, was das unbekannte Element ist und wie es sich auf Berechnungen auswirkt.
Angenommen, Sie erstellen eine Berechnung, die die monatlichen Verkäufe für jede Filiale addiert, in einer Spalte der Tabelle Sales fehlt jedoch ein Wert für den Filialnamen. Wenn die Tabellen für Store und Sales über den Filialnamen miteinander verbunden sind, welche Ergebnisse würde die Formel dann wohl haben? Wie sollte die PivotTable die Umsatzzahlen gruppieren oder anzeigen, die sich nicht auf eine vorhandene Filiale beziehen?
Dies ist ein allgemeines Problem in Data Warehouses, bei denen umfangreiche Tabellen mit Faktendaten logisch mit Dimensionstabellen verknüpft werden müssen, die Informationen zu Filialen, Regionen und anderen Attributen enthalten, die zum Kategorisieren und Berechnen von Fakten verwendet werden. Alle neuen Fakten, die nicht mit einer vorhandenen Entität verbunden sind, werden vorübergehend dem unbekannten Element zugeordnet, um das Problem zu beheben. Daher werden nicht verknüpfte Fakten in einer PivotTable unter einer leeren Überschrift gruppiert.
Behandlung von leeren Werten im Vergleich zur leeren Zeile
Leere Werte unterscheiden sich von den leeren Zeilen, die zur Unterstützung des unbekannten Elements hinzugefügt werden. Der leere Wert ist ein Spezialwert, der zur Darstellung von NULLEN, leeren Zeichenfolgen und anderen fehlenden Werten verwendet wird. Weitere Informationen zu leeren Werten sowie zu anderen DAX-Datentypen finden Sie unter In PowerPivot-Arbeitsmappen unterstützte Datentypen.