Erweiterte Filterkriterien in DAX: Der IN-Operator

Wenn Du regelmäßig Abfragen in SQL schreibst, bist Du sicher mit der Möglichkeit vertraut, Deine Abfragen, mit Hilfe des IN-Operators innerhalb der WHERE-Clause in SQL auf bestimmte Werte oder auch auf das Ergebnis von Unterabfragen, zu filtern.

Seit November 2016 steht der IN-Operator auch in DAX zur Verfügung und erweitert so z.B. Deine Möglichkeiten zum Filtern von Tabellenausdrücken in Power BI, Excel 2016 (ab der Version 1701) oder SSAS Tabular 2017.

In diesem Beitrag werfen wir gemeinsam einen Blick darauf, wie und wo Du den IN-Operator in DAX einsetzen kannst.

Verdeutlichen wir uns die Funktionsweise des IN-Operators in SQL bzw. in DAX zunächst anhand eines einfachen Beispiels und betrachten die folgende Tabelle, die die "Forbes"-Liste der 20 umsatzstärksten Unternehmen weltweit für das Jahr 2015 zeigt:

Die Tabelle links wird unter Verwendung des IN-Operators auf diejenigen Unternehmen eingeschränkt, die als "Country" zu "Germany" bzw. "Switzerland" gehören (Ergebnistabelle rechts). Nachstehend siehst Du den DAX- bzw. den korrespondierenden SQL-Code mit dem diese Einschränkung erfolgt:

In beiden Fällen wird die Tabelle "Forbes2015" auf Basis der Spalte "Country" über den IN-Operator auf die gewünschten Einträge eingeschränkt. Bitte beachte, dass der DAX-Code in diesem Artikel mit dem DAX-Studio erzeugt wurde. Möchtest Du den Code, z.B. zur Erzeugung einer berechneten Tabelle im Power BI Desktop, verwenden, wäre das einleitende "EVALUATE"-Statement nicht notwendig. Zudem müsstest Du in der deutschen Version des Power BI Desktop das Komma als Parameter-Trennzeichen gegen das Semikolon ersetzen.

Eine Besonderheit des IN-Operators in DAX zeigt bereits dieses einfache Beispiel. Die Filterkriterien "Germany" und "Switzerland" werden über den IN-Operator als ODER-Bedingungen an die Tabelle "Forbes2015" übergeben. Entsprechend werden in der Ergebnistabelle im oberen Beispiel alle Unternehmen gezeigt, die dem Land "Germany" oder "Switzerland" angehören. Üblicherweise werden Bedingungen in DAX-Funktionen wie FILTER() oder auch CALCULATETABLE() über ein logisches UND miteinander verkettet:

In den beiden Code-Beispielen müssen die Filterbedingungen für das Feld "Country" explizit geschrieben werden. Im linken Code-Beispiel wird dabei zwischen den beiden Filterbedingungen das klassische Parametertrennzeichen "," verwendet und auf diese Weise eine UND-Verknüpfungen der Bedingungen erzeugt. Diese würde in unserem Beispiel zu einer leeren Ergebnistabelle führen, weil es kein Unternehmen gibt, das zu "Germany" und zu "Switzerland" gehört.

Im rechten Code-Beispiel wird durch die Verwendung der Double-Pipe ("||"), alternativ über die DAX-Funktion OR(), eine ODER-Verknüpfung der Filterkriterien erzeugt und somit das gleiche Ergebnis erzielt, wie in unserem Eingangsbeispiel. Der IN-Operator führt also bereits in diesem vergleichsweise kurzen DAX-Code zu einer Verringerung des Schreibaufwandes und einer übersichtlicheren Darstellung des Codes.

Analog zu einigen anderen Funktionen in DAX ist auch der IN-Operator nicht der einzige Lösungsweg für eine Problemstellung. Im Grunde ist der IN-Operator nichts weiter als sog. "Syntax-Sugar", also eine vereinfachende Kurzschreibweise für einen alternativen Code-Ausdruck. Der IN-Operator ist eine Kurzschreibweise für die Verwendung der DAX-Funktion CONTAINSROW(). Marco Russo hat sich ausführlicher mit diesem Aspekt des IN-Operators beschäftigt. Wenn Du mehr darüber erfahren möchtest, findest Du seinen Artikel unter folgendem Link: The IN operator in DAX

Um die Nutzung des IN-Operators in DAX zu ermöglichen wurde eine Syntax eingeführt, die in Power BI vorher vornehmlich in der Sprache M für Power Query Verwendung fand. Die Nutzung von geschweiften Klammern ("{}").

Seit dieser Neuerung gibt es in DAX sog. "Table-Constructor" und "Row-Constructor", mit deren Hilfe es möglich ist "anonyme" Tabellen zu definieren, also Tabellen, die nicht auf der Abfrage einer physisch im Datenmodell existierenden Tabelle basieren.

Die geschweiften Klammern "{}" leiten dabei den "Table-Constructor" ein. In ihm kann eine Liste von Werten angegeben werden, die die Tabelleninhalte darstellen. Im vorherigen Abschnitt waren dies z.B. die Länder "Germany" und "Switzerland", also {"Germany","Switzerland"}, durch die eine einspaltige Tabelle mit diesen Einträgen erzeugt wurde.

Zur Erzeugung einer mehrspaltigen Tabelle kannst Du zusätzlich den sog. "Row-Constructor" verwenden. Dieser wird durch einfache Klammern "()" eingeleitet. Die folgenden Beispiele zeigen die Syntax: 

Das rechte Beispiel zeigt, wie Du durch die Verwendung des "Row-Constructors" innerhalb des "Table-Constructors" weitere Spalten erzeugen kannst, in denen jedem "Country" z.B. ein "Sector" zugeordnet wird.

Das Voranstellen des Schlüsselwortes IN ermöglicht es Dir nun, diese Syntax innerhalb von Boolean-Bedingungen, also Wahr/Falsch-Vergleichsoperationen, zum Filtern Deiner Tabellen zu verwenden.

Neben einer "starren" Liste von Werten ist es ebenfalls möglich DAX-Ausdrücke und Spaltenbezüge physisch vorhandener Tabellen in "Table-" bzw. "Row-Constructors" zu verwenden, um die Filterkriterien zu bestimmen:

Beachten musst Du allerdings, dass der verwendete DAX-Ausdruck immer genau einen Wert zurückliefert. Der Versuch im rechten Beispiel die TOP 5 Sales-Werte aus der Tabelle "Forbes2015" im "Table-Constructor" zu verwenden wird vom DAX-Studio mit einer entsprechenden Fehlermeldung quittiert, während die Rückgabe eines einzigen Wertes im linken Beispiel zum Erfolg führt.

Bisher haben wir uns nur mit der Möglichkeit beschäftigt, eine einzelne Tabellenspalte mit Hilfe des IN-Operators zu filtern. Natürlich kannst Du den IN-Operator aber auch zur Filterung mehrerer Spalten verwenden. Dazu nutzt Du die Syntax des "Row-Constructors", die wir soeben kennengelernt haben (blauer Bereich im nachfolgenden Beispiel) und stellst dem IN-Operator eine Auflistung der zu filternden Spalten voran (grün unterlegter Bereich):

Die Darstellung zeigt, dass Du eine Reihe von syntaktischen Regeln befolgen musst, damit das Filtern über mehrere Spalten zum gewünschten Ergebnis führt. Im Einzelnen sind dies:

Im obigen Beispiel "Falsche Spaltenreihenfolge" ist die vierte Regel verletzt, weil die Spalte "Sector" in der Ausgangstabelle vor der Spalte "Country" positioniert ist. Entsprechend führt die Variante "Falsche Spaltenreihenfolge" zu einem leeren, die Variante "Richtige Spaltenreihenfolge" hingegen zum korrekten Ergebnis. Dabei werden die einzelnen "Row-Constructor", wie eingangs beschrieben, durch ein logisches ODER verknüpft, während die Kriterien in den Spalten des "Row-Constructors" durch ein logisches UND verkettet sind. Der obige Code liest sich vereinfacht ausgedrückt also ungefähr so:

"Filtere die Tabelle "Forbes2015" auf die Datensätze, die dem Country "United States" und dem Sector "Energy" oder dem Country "China" und dem Sector "Energy" zuzuordnen sind."

Für die Überprüfung der UND-Verkettung, also z.B. "Country"=China und "Sector"="Energy", wird bei der Filterung mehrerer Spalten entsprechend ein Zugriff auf den Row-Context der zu filternden Tabelle benötigt, der im obigen Fall über die Iterator-Funktion FILTER() erzeugt wird. In anderen Funktionen (z.B. CALCULATETABLE()) ist dies also nicht möglich, weil kein entsprechender Row-Context erzeugt wird.

Sowohl bei der Filterung einer Spalte, als auch der mehrerer Spalten stehen Dir mit dem IN-Operator weitere Möglichkeiten zur Verfügung. Analog zum IN-Operator in SQL kannst Du auch in DAX nicht nur die Datensätze ausgeben, die Deinen Filterkriterien entsprechen, sondern auch alle Datensätze, die den gewählten Filterkriterien nicht entsprechen. Dies erreichst Du über die Verwendung der Funktion NOT():

Im Beispielcode wurde der Fall des vorangegangenen Abschnitts aufgegriffen. Grün hinterlegt steht dabei die gleiche Filterbedingung, die wir schon im letzten Abschnitt kennengelernt haben. Eingefasst wird diese nun durch die Funktion NOT() (gelb hinterlegt), die die Filterlogik umkehrt und so das rechts stehende Ergebnis erzeugt, in dem alle Datensätze gezeigt werden, die den angegebenen Kriterien nicht entsprechen. Am einfachsten lässt sich dies anhand der Spalte "Rank" überprüfen, in der die Einträge mit dem Rang 3, 4, 6 , 11 und 17 fehlen und damit genau die Datensätze, die im vorherigen Abschnitt noch die Ergebnistabelle gebildet haben.

Zudem haben wir in den bisherigen Beispielen immer feste Wertelisten als Filterkriterien im IN-Operator verwendet. Alternativ ist es natürlich auch möglich die Kriterien aus einem Tabellen-Ausdruck zu übergeben. Dies ist immer dann nützlich, wenn die Inhalte anderer Tabellen als Filterkriterien in Deinem DAX-Statement genutzt werden sollen. Ein gesonderter "Table-Constructor" ist in solchen Szenarien nicht mehr notwendig:

 Im oberen Codebeispiel wird über die Tabellenfunktion VALUES() die Spalte "Sector" aus einer Tabelle "FilterKriterien" ausgelesen und zur Filterung unserer Beispieltabelle "Forbes2015" verwendet. Im unteren Beispiel werden die Einträge der Spalten "FilterSector" und "FilterCountry" aus der Tabelle "FilterKriterien" über die Tabellenfunktion SELECTCOLUMNS() abgefragt und als Filterkriterien übergeben. Entsprechend ergibt sich oben als Ergebnis eine Tabelle mit allen Unternehmen aus den Sektoren "Energy" oder "Financials", während unten die Einträge ausgegeben werden, die den Kriterien "Energy" und "France" oder "Financials" und "United States" entsprechen.

Grundsätzlich kannst Du den IN-Operator überall dort verwenden, wo Du Boolean-Vergleichsoperationen durchführst. Dies ist, wie in unseren Beispielen auch, sehr oft beim Filtern von Tabellenausdrücken mit FILTER() oder CALCULATETABLE() der Fall.

Es ist aber genauso möglich den IN-Operator z.B. in Vergleichsoperationen in logischen Verzweigungen, wie IF() oder SWITCH(), zu verwenden, weil natürlich auch dort Wahr/Falsch-Vergleichsprüfungen vorgenommen werden. Soheil Bakhshi nutzt den IN-Operator in einem seiner Beiträge in SWITCH(), um in einer berechneten Spalte in Power BI neue Produktgruppen zu erzeugen und zeigt auf, dass die Gruppierungsfunktionalität in Power BI im Hintergrund im Grunde genau die gleiche Syntax verwendet. Soheils Artikel findest Du hier.

Ein anderes Einsatzgebiet für den IN-Operator kann auch die Verwendung innerhalb Deiner Row-Level-Security in Power BI oder SSAS Tabular sein. Die dort definierten Kriterien und Bedingungen für den Datenzugriff können in manchen Fällen durch den Einsatz des IN-Operators deutlich übersichtlicher gestaltet werden.

Ich hoffe ich konnte Dir die Verwendung des IN-Operators in DAX etwas näher bringen. Wenn Dir der Artikel gefallen hat, würde ich mich freuen, wenn Du auch bei meinen nächsten Artikeln wieder reinschaust.

Vielleicht teilst Du den Artikel auch mit anderen und besuchst mich auf meinem YouTube-Kanal. Dort findest Du noch mehr Inhalte zu Power BI, Excel, DAX und M! Ich würde mich freuen!

Bis dahin!

Viele Grüße aus Hamburg

Uwe