Filtern von Daten in Formeln
Sie können Filter für Formeln erstellen, um die Werte der Quelldaten einzuschränken, die in Berechnungen verwendet werden. Dazu geben Sie eine Tabelle als Eingabe für die Formal an und definieren dann einen Filterausdruck. Mit diesem Filterausdruck werden die Daten abgefragt, und nur eine Teilmenge der Quelldaten wird zurückzugeben. Der Filter wird abhängig vom aktuellen Kontext der Daten jedes Mal dynamisch angewendet, wenn Sie die Ergebnisse der Formel aktualisieren. In diesem Abschnitt wird beschrieben, wie Sie Filter in Data Analysis Expressions-Formeln (DAX) erstellen.
Erstellen eines Filters für eine in einer Formel verwendeten Tabelle
Sie können Filter in Formeln anwenden, die eine Tabelle als Eingabe verwenden. Statt einen Tabellennamen einzugeben, definieren Sie mithilfe der FILTER-Funktion eine Teilmenge der Zeilen in der angegebenen Tabelle. Anschließend wird diese Teilmenge an eine andere Funktion für Vorgänge wie z. B. benutzerdefinierte Aggregationen übergeben.
Beispiel: Angenommen, Sie möchten anhand einer Tabelle mit Bestellinformationen zu Wiederverkäufern berechnen, wie viel jeder Wiederverkäufer verkauft hat. Allerdings soll der Betrag der Verkäufe nur für Wiederverkäufer angezeigt werden, die mehrere Einheiten höherwertiger Produkte verkauft haben. Die folgende Formel zeigt auf Grundlage der DAX-Beispielarbeitsmappe ein Beispiel dafür, wie Sie diese Berechnung mit einem Filter erstellen können:
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
Der erste Teil der Formel gibt eine der PowerPivot-Aggregationsfunktionen an, die eine Tabelle als Argument verwendet. Mit SUMX wird die Summe für eine Tabelle berechnet.
Der zweite Teil der Formel, FILTER(table, expression), teilt SUMX mit, welche Daten verwendet werden sollen. SUMX erfordert eine Tabelle bzw. einen Ausdruck, der eine Tabelle ausgibt. Hier verwenden Sie die FILTER-Funktion, um anzugeben, welche Zeilen der Tabelle verwendet werden, anstatt alle Daten in der Tabelle zu verwenden.
Der Filterausdruck verfügt über zwei Teile: im ersten Teil wird die Tabelle genannt, auf die der Filter angewendet wird. Vom zweiten Teil wird ein Ausdruck zu Verwendung als Filterbedingung definiert. In diesem Fall werden die Wiederverkäufer gefiltert, die mehr als fünf Einheiten und Produkte verkauft haben, die mehr als 100 US-Dollar gekostet haben. Der &&-Operator ist ein logischer AND-Operator, von dem angegeben wird, dass beide Teile der Bedingung für die Zeile den Wert "True" aufweisen müssen, damit die Zeile zur gefilterten Teilmenge gehört.
Vom dritten Teil der Formel wird der SUMX-Funktion mitgeteilt, welche Werte summiert werden sollen. In diesem Fall wird nur der Betrag der Verkäufe verwendet.
Beachten Sie, dass Funktionen wie FILTER, die eine Tabelle zurückgeben, die Tabelle oder die Zeilen nicht direkt an die PowerPivot-Arbeitsmappe zurückgeben, sondern stets in einer anderen Funktion eingebettet sind. Weitere Informationen zur FILTER-Funktion und zu anderen Filterfunktionen sowie weitere Beispiele finden Sie unter Filterfunktionen (DAX).
.gif "Hinweis") Hinweis |
|---|
Der Filterausdruck ist u. a. vom Kontext abhängig, in dem er verwendet wird. Wenn Sie beispielsweise in einem Measure einen Filter verwenden und das Measure in einer PivotTable oder einem PivotChart verwendet wird, unterliegt die Teilmenge der Daten, die zurückgegeben werden, möglicherweise zusätzlichen Filtern oder Datenschnitten, die der Benutzer in der PivotTable angewendet hat. Weitere Informationen zum Kontext finden Sie unter Kontext in DAX-Formeln. |
Filter, die Duplikate entfernen
Zusätzlich zum Filtern nach bestimmten Werten können Sie einen eindeutigen Satz von Werten in einer anderen Tabelle oder Spalte zurückgeben. Dies kann hilfreich sein, wenn Sie die Anzahl der eindeutigen Werte in einer Spalte zählen oder eine Liste von eindeutigen Werten für andere Vorgänge verwenden möchten. DAX stellt zwei Funktionen zum Zurückgeben von unterschiedlichen Werten bereit: DISTINCT-Funktion (DAX) und VALUES-Funktion (DAX).
Die DISTINCT-Funktion untersucht eine einzelne Spalte, die Sie als Argument für die Funktion angeben, und gibt eine neue Spalte zurück, die nur die unterschiedlichen Werte enthält.
Die VALUES-Funktion gibt auch eine Liste eindeutiger Werte, aber auch das unbekannte Element zurück. Dies ist nützlich, wenn Sie Werte von zwei Tabellen verwenden, die durch eine Beziehung miteinander verknüpft sind, und ein Wert in einer Tabelle fehlt und in der anderen vorhanden ist. Weitere Informationen zum unbekannten Element finden Sie unter Kontext in DAX-Formeln.
Von beiden dieser Funktionen wird eine komplette Spalte mit Werten zurückgegeben. Daher werden die Funktionen verwendet, um eine Liste mit Werten abzurufen, die anschließend an eine andere Funktion übergeben wird. Sie können beispielsweise mithilfe der folgenden Formel anhand des individuellen Product Keys eine Liste der unterschiedlichen Produkte abrufen, die von einem bestimmten Wiederverkäufer verkauft wurden und anschließend die in der Liste enthaltenen Produkte mit der COUNTROWS-Funktion zählen:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Wie sich der Kontext auf Filter auswirkt
Wenn Sie einer PivotTable oder einem PivotChart eine DAX-Formel hinzufügen, kann sich der Kontext auf die Ergebnisse der Formel auswirken. Wenn Sie in einer PowerPivot-Tabelle arbeiten, ist der Kontext die aktuelle Zeile mit den darin enthaltenen Werten. Wenn Sie in einer PivotTable oder einem PivotChart arbeiten, ist der Kontext der Satz oder die Teilmenge an Daten, die durch Vorgänge wie z. B. das Aufteilen in Slices oder das Filtern definiert wird. Durch die Konzeption der PivotTable oder des PivotCharts wird auch ein eigener Kontext erzeugt. Wenn Sie z. B. eine PivotTable erstellen, in der Verkäufe nach Bereich und Jahr gruppiert werden, werden nur die Daten in der PivotTable angezeigt, die für jene Bereiche und Jahre gelten. Daher werden alle Measures, die Sie der PivotTable hinzufügen, im Kontext der Spalten- und Zeilenüberschriften sowie aller Filter in der Measureformel berechnet.
Weitere Informationen finden Sie unter Kontext in DAX-Formeln.
Entfernen von Filtern
Beim Arbeiten mit komplexen Formeln möchten Sie u. U. genau wissen, welche Filter derzeit verwendet werden, oder den Filterteil der Formel ändern. DAX stellt mehrere Funktionen bereit, die Ihnen das Entfernen von Filtern ermöglichen und steuern, welche Spalten als Teil des aktuellen Filterkontexts beibehalten werden. Dieser Abschnitt bietet eine Übersicht darüber, wie sich diese Funktionen auf Ergebnisse in einer Formel auswirken.
Überschreiben aller Filter mit der ALL-Funktion
Sie können mithilfe der ALL-Funktion zuvor angewendete Filter überschreiben und alle Zeilen in der Tabelle für die Funktion zurückgeben, die den Aggregatvorgang oder einen anderen Vorgang ausführt. Wenn Sie statt einer Tabelle eine oder mehrere Spalten als Argumente für ALL, verwenden, gibt die ALL-Funktion alle Zeilen zurück und ignoriert alle Kontextfilter.
| Hinweis |
|---|
Wenn Sie mit der Terminologie für relationale Datenbanken vertraut sind, betrachten Sie die ALL-Funktion als Funktion, von der der natürliche linke äußere Join aller Tabellen generiert wird. |
Angenommen, Sie haben die Tabellen Sales und Products und möchten eine Formel erstellen, die die Summe der Verkäufe für das aktuelle Produkt geteilt durch die Verkäufe aller Produkte berechnet. Wenn die Formel allerdings in einem Measure verwendet wird, verwendet der Benutzer der PivotTable unter Umständen einen Datenschnitt, um anhand der Produktnamen der Spalten nach einem bestimmten Produkt zu filtern. Sie müssen daher die ALL-Funktion hinzufügen, um alle Filter außer Kraft zu setzen und den echten Wert des Nenners unabhängig von Filtern oder Datenschnitten zu ermitteln. Die folgende Formel ist ein Beispiel für die Verwendung von ALL zum Überschreiben der Auswirkungen vorheriger Filter:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
Der erste Teil der Formel, SUM (Sales[Amount]), berechnet den Zähler.
Die Summe berücksichtigt den aktuellen Kontext. Dies bedeutet, dass der Zeilenkontext angewendet wird, wenn Sie die Formel in eine berechnete Spalte einfügen. Wenn Sie dagegen die Formel in eine PivotTable als Measure einfügen, werden alle in der PivotTable (dem Filterkontext) angewendeten Filter angewendet.
Der zweite Teil der Formel berechnet den Nenner. Die ALL-Funktion setzt alle Filter außer Kraft, die möglicherweise auf die Products-Tabelle angewendet wurden.
Weitere Informationen und ausführliche Beispiele finden Sie unter ALL-Funktion (DAX).
Überschreiben bestimmter Filter mit der ALLEXCEPT-Funktion
Die ALLEXCEPT-Funktion überschreibt auch vorhandene Filter. Sie können jedoch angeben, dass einige der vorhandenen Filter beibehalten werden sollen. Über die Spaltenargumente für die ALLEXCEPT-Funktion geben Sie an, welche Spalten weiterhin gefiltert werden sollen. Wenn Sie Filter nur für bestimmte Spalten außer Kraft setzen möchten, ist die ALLEXCEPT-Funktion besser geeignet als die ALL-Funktion. Die ALLEXCEPT-Funktion ist besonders nützlich, wenn Sie PivotTables erstellen, in denen möglicherweise nach vielen unterschiedlichen Spalten gefiltert wird, und Sie die Werte steuern möchten, die in der Formel verwendet werden. Weitere Informationen und ein ausführlicheres Beispiel für die Verwendung von ALLEXCEPT in einer PivotTable finden Sie unter ALLEXCEPT-Funktion (DAX).