Top N Filter – eine Aufgabe, drei Lösungen

Eine häufige Anforderung ist es, nur diejenigen Kategorien anzuzeigen, die wesentlich zum Ergebnis beitragen. Oft sind das die „Top 10 Kunden“ oder die „Top 5 Produkte“. Um an einem einfachen Dataset die unterschiedlichen Lösungsmöglichkeiten zu untersuchen, habe ich dem Dachs ein paar Kollegen an die Seite gestellt und ein kleines Datenmodell gebaut, das die Population verschiedener Tierarten enthält.

Die Ausgangsdaten

Die nachfolgende Matrix zeigt unsere Ausgangsdaten: Es handelt sich um 12 Tiere, die in 3 Arten (Insekten, Säugetiere, Vögel) unterteilt sind. Eine Zählung in unserem Revier hat ergeben, dass es erstaunlich viele Spechte (11), Dachse (10) und Schmetterlinge (7) gibt. Andere Tiere wie Hasen, Mücken und Schnepfen sind hier selten und tragen kaum zur Tierpopulation bei.

Mit diesen Daten möchte ich nachfolgend drei unterschiedliche Lösungen demonstrieren. Somit ist diese einfach klingende Anforderung „zeige mir die Top N Elemente“ ein wunderbares Beispiel für Flexibilität von Power BI. Denn je nach Anforderungen, unserem eigenem Kenntnisstand und der zur Verfügung stehenden Zeit können wir auf unterschiedlichen Wegen das Ziel erreichen.

Ansatz 1: Die schnelle, einfache Lösung „out of the box“

Der einfachste Weg, unsere Darstellung auf die häufigsten Tiere einzuschränken, ist die Benutzung des „Top N Filters“ in Power BI. Dieser Filter wird für das Visual konfiguriert und erlaubt die Auswahl eines Measures, nach dessen Wert die Namen gefiltert werden sollen. In unserem Fall ist es das Measure „Population“. Schon zeigt die Matrix nur noch die drei Tiere mit der höchsten Population an, die anderen nicht.

Ergebnis: Der Top N Filter in Power BI schränkt die Tiernamen auf die obersten 3, gemessen an der Population, ein. Oft ist das eine gute und schnelle Lösung.

Praxistipp: Wenn Sie den Filter verändern, zum Beispiel wenn Sie die Anzahl der Elemente ändern, dann vergessen Sie nicht, danach auf „Filter anwenden“ zu klicken! Ich spreche hier aus bitterer Erfahrung 🙂

Ansatz 2: Top N Measure erstellen

Kniffliger wird es, wenn wir für jede Tierart (also Insekten, Säugetiere, Vögel) jeweils die Top 3 Tiere innerhalb der Art darstellen möchten. Mit den Bordmitteln von Power BI habe ich keinen Weg gefunden. Falls das doch irgendwie geht, freue ich mich über Hinweise!

Die erste Lösung, die ich hier vorschlagen möchte, besteht in einem Measure, das die Top 3 im jeweiligen Filterkontext ermittelt. Nur für Tiere, welche zu den Top 3 im Filterkontext des Visuals gehören, liefert dieses Measure die Population, ansonsten BLANK().

Um die Demo einfach zu halten, ist die Zahl 3 hier fest im Code hinterlegt. Aber wenn Sie Ihren Anwendern mehr Flexibilität geben möchte, dann lässt sich dies mit einem Was-wäre-wenn Parameter bewerkstelligen. Dann können die Anwender über einen Datenschnitt die Anzahl anzuzeigender Top-Tiere wählen. In der beigefügten Demo-Datei finden Sie hierzu ein Beispiel.

Top 3 Tiere nach Population =
VAR AnzahlN = 3
VAR GefilterteTiere =
    CALCULATETABLE ( VALUES ( D_Tiere ), ALLSELECTED ( D_Tiere[Name] ) )
VAR Rang =
    RANKX ( GefilterteTiere, [Population] )
RETURN
    IF ( Rang > 0 && Rang <= AnzahlN, [Population], BLANK () )

Die Verwendung von ALLSELECTED()

Bemerkenswert an diesem Measure ist die Variable „GefilterteTiere“. Diese enthält alle Tiere in dem gerade wirksamen Filterkontext des Visuals. In der nachfolgend abgebildeten Matrix ist das die Tierart „Insekten“, „Säugetiere“ oder „Vögel“. Ein ALLSELECTED() alleine liefert aber nicht die gewünschte Menge, erst durch die VALUES() Funktion im Kontext von ALLSELECTED() wird dies erreicht. Dies passt zum Hinweis in dax.guide, dass ALLSELECTED() nicht als Tabellen-Funktion, sondern nur als Modifizierer in CALCULATE() eingesetzt werden sollte. („ALLSELECTED can be used as a table function, even though it is not a best practice.“)

Zwischensumme: ja oder nein?

Ein genauer Blick auf das Ergebnis (Tabelle oben) zeigt, dass dieses Measure nicht nur auf Ebene der Tiere einen Wert liefert, sondern auch eine Zwischensumme für jede Kategorie. Und die Summe aller Werte je Kategorie ist teilweise kleiner als diese Zwischensumme. Das liegt daran, dass die Zwischensumme über alle Tiere in der Kategorie gerechnet wird und nicht nur über die Top-Tiere. Das kann für einige Anwendungen das gewünschte Verhalten sein, für andere nicht. Um die Zwischensummen auszublenden, so wie im folgenden Bild zu sehen, habe ich das Measure um ein IF() erweitert. Die Bedingung, ob das Measure angezeigt werden soll, wird mit ISINSCOPE() ermittelt. Diese Funktion liefert „wahr“, wenn die Spalte D_Tiere[Name] im Filterkontext vorkommt. Das trifft hier zu, wenn das Measure auf Ebene der Tiernamen berechnet wird. Aber die Bedingung trifft nicht zu auf Ebene der Tierart (also auf Ebene der Zwischensummen).

Top 3 Tiere nach Population ohne Zwischensumme =
IF (
    ISINSCOPE ( D_Tiere[Name] ),
    VAR AnzahlN = 3
    VAR GefilterteTiere =
        CALCULATETABLE ( VALUES ( D_Tiere ), ALLSELECTED ( D_Tiere[Name] ) )
    VAR Rang =
        RANKX ( GefilterteTiere, [Population] )
    RETURN
        IF ( Rang > 0 && Rang <= AnzahlN, [Population], BLANK () )
)

Wie groß ist der Rest?

Noch fehlt uns die Berechnung von „andere“, um den Rest je Tierart anzeigen zu können. Das ist dann nicht mehr ganz so einfach, weil wir in mehreren Schritten vorgehen müssen:

1. Die Dimensionstabelle „D_Tiere“ benötigt je Tierart den Pseudo-Namen „andere“. Dies habe ich in der angehängten PBIX Datei mit Power Query gelöst, alternativ hätte ich auch mit einer berechneten Tabelle im Modell arbeiten können.
2. In Variablen werden zunächst alle Bestandteile zurechtgelegt:
   • WertTopTiere – Summe der Population aller Top N Tiere
   • WertAlleTiere – Summe der Population über alle Tiere in der aktuellen Tierart
   • WertAndere – die Differenz der beiden vorherigen
3. Abhängig davon, ob das Measure für ein Top N Tier, für „andere“ oder auf Ebene der Tierart angezeigt werden soll, wird dann der Wert der entsprechenden Variablen angezeigt.

Top 3 Tiere nach Population und andere =
VAR AnzahlN = 3
VAR GefilterteTiere =
    CALCULATETABLE ( VALUES ( D_Tiere ), ALLSELECTED ( D_Tiere[Name] ) )
VAR Rang =
    RANKX ( GefilterteTiere, [Population] )
VAR IstAndere =
    SELECTEDVALUE ( D_Tiere[Name] ) = "andere"
VAR WertTopTiere =
    -- Summe der Population über die TopN Tiere 
    SUMX (
        TOPN ( AnzahlN, GefilterteTiere, [Population] ),
        [Population]
    )
VAR WertAlleTiere =
    -- Summe der Population über alle Tiere in der aktuellen Tierart
    CALCULATE (
        [Population],
        REMOVEFILTERS ( D_Tiere[Name] )
    )
VAR WertAndere = 
    -- Population aller Tiere in der Tierart, die nicht zu den TopN gehören
    WertAlleTiere - WertTopTiere
VAR WertAktuellesTier =
    IF ( Rang > 0 && Rang <= AnzahlN, [Population], BLANK () )

RETURN
    IF ( IstAndere, WertAndere, WertAktuellesTier )

Ergebnis: Mit einem „Top N“ Measure lassen sich auch komplexere Anforderungen exakt umsetzen.

Marco Russo und Alberto Ferrari haben diesen Lösungsansatz genauer untersucht. Ihr sehr detailreicher Artikel ist hier zu finden: Filtering the top products alongside the other products in Power BI. Dieser Artikel zeigt auch Optimierungstechniken, die ich hier nicht berücksichtigt habe, um die Struktur des DAX Codes möglichst einfach zu halten. Daher wäre meine Empfehlung, meinen Artikel als Einstieg zu verwenden und bei Bedarf mit dem Artikel von Russo und Ferrari die Lösung weiter zu optimieren.

Ansatz 3: Ein Measure zum Filtern des Visuals

Die dritte Lösungsvariante, die ich hier vorstellen möchte, ist die Erstellung eines einzigen Measures, das Sie für beliebig viele andere Measures verwenden können, um diese nach TopN zu filtern. Es ist so etwas wie die Synthese aus den ersten beiden Ansätzen. Schauen wir uns das an einem Beispiel an:

TopN Visual Filter =
IF (
    ISINSCOPE ( D_Tiere[Name] ),
    RANKX (
        CALCULATETABLE (
            VALUES ( D_Tiere[Name] ),
            ALLSELECTED ( D_Tiere[Name] )
        ),
        [Population]
    )
)

Dieses Measure liefert den Rang nach Population, wenn nach dem Namen gefiltert wird. Das ist nur dann der Fall, wenn wir uns innerhalb einer Tierart befinden.

Die Zwischensummen schon wieder…

Die beschriebene Lösung zeigt nun als Zwischensummen die Summe der sichtbaren Tiere. Alle Tiere, die nicht unter die Top 3 fallen, sind in der Zwischensumme auch nicht sichtbar. Um in den Zwischensummen die Summe aller Tiere zu sehen, können wir auch ein Measure erstellen, das oberhalb der Ebene „Name“ die Filterung nach „Name“ entfernt:

Population mit Zwischensumme ungefiltert = 
IF (
    ISINSCOPE ( D_Tiere[Name] ),
    [Population],
    CALCULATE (
        [Population],
        ALL ( D_Tiere[Name] )
    )
)

Das neue Measure zeigt nun als Zwischensummen die Population über alle Tiere derselben Art.

Zusammenfassung

Wie so oft in der Praxis gibt es für die einfache Frage „Zeige mir die Top N Elemente einer Menge“ mehrere Antworten. Welche Lösung die richtige Antwort ist, hängt von den genauen Anforderungen ab. Hier haben wir uns drei unterschiedliche Lösungen angesehen:

• der Filtertyp „Top N“ im Visual
• mehrere individuelle Measures
• ein einziges Measure, das als Filter im im Visual dient

Diese lassen sich je nach Anforderungen natürlich noch weiter ausgestalten. Und ich hoffe, dass Sie hier viele Anregungen gefunden haben. Mir war es wichtig, hier die drei unterschiedlichen Ansätze darzustellen. Und wenn Sie weitere Lösungsansätze teilen möchten, dann freue ich mich über Kommentare.

Hier noch die Power BI Datei zum Download: