Online Magazine
Data Analysis Expressions DAX – ein Use Case
Bestellungen von Kunden zusammenzählen – das dürfte eigentlich nicht so schwierig sein, oder? Aber was passiert, wenn mehr Bestellungen berücksichtigt werden müssen, als im ausgewählten Zeitraum sichtbar sind? Schauen wir uns die Problemstellung und mögliche Lösungen an.
von Salvatore Cagliari
Ein Kunde kam mit den folgenden zwei Anliegen auf mich zu:
1. Ich möchte alle Bestellungen zählen, einschliesslich der offenen Bestellungen während eines bestimmten Zeitraums.
2. Ich möchte meine Kunden auf der Grundlage der Anzahl der Bestellungen im Laufe der Zeit in Kategorien einteilen.
Diese beiden Anforderungen schienen auf den ersten Blick einfach zu sein, aber nach einigem Nachdenken musste ich tiefer über möglichen Lösungen nachdenken.
Um Lösungen zu finden, müssen wir den Filterkontext so manipulieren, dass er mehr Zeilen enthält, als im aktuellen Filterkontext sichtbar sind.
Data Model
Das Datenmodell, das ich benutzt habe, sah wie folgt aus:
Abbildung 1 - Datenmodell (Für diesen Artikel habe ich das Contoso-Datenmodell verwendet (Quelle und Lizenzinformationen findest du am Ende des Textes).
Die Tabelle Online-Verkäufe hat drei Beziehungen zur Tabelle Datum:
- Bestelldatum zu Datum
- Fälligkeitsdatum zu Datum (inaktiv)
- Versanddatum zu Datum (inaktiv)
Noob Hack
Der erste Schritt bestand darin, eine Wahrheitstabelle für das gewünschte Ergebnis zu definieren:
Abbildung 2 - Wahrheitstabelle für offene Bestellungen.
Wie du sehen kannst, wurde jede Bestellung, die vor dem Beginn des Beobachtungszeitraums angelegt, aber während des Beobachtungszeitraums versandt worden war, als offen definiert. Das musste geändert werden.
Zusätzlich mussten alle Bestellungen, die während des Beobachtungszeitraums erstellt wurden, in die Zählung einbezogen werden.
Um diese Anforderungen zu erfüllen, musste ich zwei Measures erstellen:
1. Einfache Zählung der Bestellungen
Diese Measure zählt die Bestellnummern in der Faktentabelle.
Da jede Bestellung mehrere Bestellzeilen hat (eine pro Produkt in der Bestellung), musste ich die Funktion DISTINCTCOUNT() für diese Measure verwenden:
|
|
Diese Measure lieferte die Anzahl der Bestellungen im aktuellen Filterkontext.
2. Anzahl der aktiven Orders
Diese Measure war ein wenig komplizierter, da ich alle offenen Bestellungen einbeziehen musste, deren "Order Date" vor dem Beginn des beobachteten Zeitraums liegt (der aktuelle Filterkontext für die Datumstabelle):
|
|
Ich benutzte die Measure [Online Order Count], um die Bestellungen zu zählen.
Dann habe ich die aktive Beziehung zur Tabelle Datum mit CROSSFILTER() deaktiviert.
Diese Funktion sorgte dafür, dass der Filterkontext für diese Berechnung nicht angewendet wurde. Alternativ kannst du auch ALL('Datum') verwenden, um den aktuellen Filterkontext für die Tabelle Datum zu entfernen.
Dann verwendete ich die Variable FirstActualDate, die am Anfang der Measure definiert ist, um nur Bestellungen einzuschliessen, die vor dem aktuellen Zeitraum/Filterkontext erstellt, aber noch nicht versandt wurden.
Eine Kennzahl zu schreiben, um alle Aufträge im aktuellen Filterkontext und alle offenen Aufträge zu erhalten, wie in der obigen Wahrheitstabelle definiert, hätte zu einer sehr komplexen Kennzahl geführt. Daher beschloss ich, die letzte Zeile der Kennzahl zu ändern, um die Kennzahl [Anzahl der Online-Bestellungen] und die neue Kennzahl zusammenzuführen und so das richtige Ergebnis zu erhalten:
|
|
So konnte ich alle Bestellungen auf einfache Weise in das Ergebnis einbeziehen.
RIESIGE DATENMENGEN? KEIN PROBLEM!
Mit künstlicher Intelligenz die Luft messen - so geht's.
Mit einem Multi-Speed-Ansatz in der Cloud - hier den Input lesen.
Pro Hack
Der folgende Fall ist einiges komplexer.
Die genaue Anforderung war: Jeder Kunde muss beobachtet und nach der Anzahl der Bestellungen in den letzten 180 Tagen kategorisiert werden (die folgenden Anforderungen sind an den Contoso-Datensatz aus den ursprünglichen Anforderungen meines Kunden angepasst).
1. Wenn ein Kunde weniger als 20 Bestellungen aufgegeben hat, dann muss er als "zu kontaktieren" kategorisiert werden.
2. Wenn ein Kunde zwischen 20 und 35 Bestellungen getätigt hat, dann muss er als "normaler Kunde" kategorisiert werden.
3. Wenn ein Kunde zwischen 36 und 50 Bestellungen aufgegeben hat, muss er als "guter Kunde" eingestuft werden.
4. Wenn ein Kunde mehr als 50 Bestellungen getätigt hat, muss er als "sehr guter Kunde" eingestuft werden.
Mein Kunde legte Wert darauf, diese Einstufung für jeden Tag und jeden Kunden zu berechnen.
Der einzelne Kunde ist in diesem Stadium nicht so wichtig. Entscheidend ist aber, dass man die Entwicklung aller Kunden im Laufe der Zeit beobachtet.
Mein erster Gedanke war es, den Abstand zwischen den Bestellungen der einzelnen Kunden zu kennen und auf dieser Basis die Kategorie jedes Kunden zu berechnen.
Der einfachste Ansatz, um dieses Ergebnis zu erhalten, ist die Erstellung einer berechneten Spalte. Ein weiterer besteht darin, eine Measure zu erstellen und diese Informationen zur Laufzeit der Measure zu berechnen.
Jeder Ansatz hat Vor- und Nachteile. Ich war mir sicher, dass die berechnete Spalte die Lösung unterstützen könnte, da jeder Kunde im Laufe der Zeit zwischen den Kategorien wechseln würde. Die endgültige Lösung war jedoch eine Maßnahme, da ein Kunde in einem Monat 100 Bestellungen aufgeben kann, in den folgenden drei Monaten jedoch nur zehn, wodurch er im Laufe der Zeit in eine andere Kategorie wechseln würde.
Auf dem Weg zur Lösung in DAX
Ich brauchte 3 Versuche, um dieses Problem zu lösen. Zuerst habe ich den Kontextübergang verwendet, um das Datum der vorherigen Bestellung für jeden Kunden und jede Bestellung zu erhalten. Um mehr über Kontextübergänge zu erfahren, lies diesen Artikel.
Der nächste Schritt war die Erstellung der Kennzahlen – eine Kennzahl für jede Kategorie. Mehrere Kennzahlen sind die einzige Möglichkeit, die Kunden dynamisch zu zählen. Die berechnete Spalte habe ich verwendet, um ein Ergebnis zu generieren. Da die Kennzahl nur eine Zahl zurückgab, wenn eine Bestellung vorlag, und der Kunde eine Zahl pro Zeitraum benötigte, war es leider unmöglich, mit diesem Ansatz das richtige Ergebnis zu erhalten.
Mein zweiter Versuch, diese Herausforderung zu lösen, führte zu einer Measure, die mehr als fünf Minuten brauchte, um das Ergebnis für die ersten beiden Kategorien zu liefern, was inakzeptabel war. Der Grund für diese langwierige Antwort war die von mir verwendete Funktion GENERATE().
Ich versuchte, eine Zeile mit jeder Kombination aus jedem Kunden und jeder Zeile in der Datumstabelle zu erzeugen, und verwendete die resultierende Tabelle in einer FILTER()-Funktion, um die benötigten Zeilen herauszufiltern. Um dies zu erreichen, habe ich die Spalte OrderCount im Ergebnis von SUMMARIZE() mit Hilfe des Kontextübergangs ( CALCULATE ( DISTINCTCOUNT('Online Sales'[Sales Order Number]) ) ).
Während ich mit dem Contoso-Datensatz mit 19'000 Kunden ein Ergebnis erhielt, hätte es mit den Daten meines Kunden nicht funktioniert: Er hatte über 1,4 Millionen Zeilen in seiner Kundentabelle. Ausserdem war das Ergebnis eigentlich falsch.
Auf der Suche nach einem neuen Ansatz musste ich nicht unbedingt eine Tabelle mit einer Zeile für jedes Datum und jeden Kunden erstellen, um die Bestellungen zu zählen. Schließlich hatte ich immer einen Datumskontext, wenn ich einen Bericht auf der Grundlage einer Datumshierarchie erstellte.
Daher habe ich das Problem in zwei Fragen aufgeteilt:
- Wie viele Bestellungen wurden in den letzten 180 Tagen erstellt?
- Wie viele Bestellungen hat jeder Kunde in den letzten 180 Tagen gemacht?
Anhand dieser Informationen konnte ich die Kunden in Kategorien einteilen.
Die erste Measure bestand darin, die Bestellungen der letzten 180 Tage zu zählen:
|
|
Ich habe eine Tabelle mit allen Daten vor dem eigentlichen Filterkontext – 180 Tage – erstellt. In einem zweiten Schritt habe ich CALCULATETABLE() und SUMMARIZE() verwendet, um eine Liste aller Bestellungen über diesen Zeitraum zu erhalten.
Nachdem ich die Zeilen des zweiten Schritts gezählt hatte, verwendete ich diese Informationen, um die Bestellungen pro Kunde zu zählen. Für diese Berechnung habe ich die folgende Kennzahl für die erste Kategorie erstellt:
|
|
Da ich jeden Kunden berücksichtigen musste, musste ich durch die Kundentabelle iterieren und die Anzahl der Bestellungen für jeden Kunden überprüfen.
Schauen wir uns diese Measure von aussen nach innen an:
- COUNTROWS() zählt die Anzahl der Zeilen der Tabelle mit der Anzahl der Bestellungen pro Kunde.
- CALCULATETABLE() erzeugt eine Tabelle.
a. Innerhalb von CALCULATETABLE() verwendete ich VALUES('Customer[CustomerKey]), um alle Kunden zu erhalten.
b. Dann verwendete ich FILTER(), um die Kunden-Tabelle nach der Anzahl der Bestellungen pro Kunde zu filtern.
Da FILTER() ein Iterator ist, habe ich Context Transition genutzt, um die Anzahl der Bestellungen der letzten 180 Tage für jeden Kunden zu ermitteln. Die Measure [Orders last 180 Days] ermittelt den aktuellen Kunden als Filterkontext und berechnet die Anzahl der Bestellungen für jeden Kunden.
Für alle Zeiträume vor der ersten Bestellung und nach der letzten Bestellung musste ich NOT ISBLANK([Bestellungen letzte 180 Tage]) verwenden, um Zeiträume ohne Bestellungen auszuschließen. Ohne diesen Ausschluss hätte ich die Anzahl aller Kunden für alle Zeiträume erhalten, auch ohne Bestellungen.
Das Ergebnis war eine Liste der Kunden mit mehr als 20 Bestellungen in den letzten 180 Tagen. Die Zeilen in dieser Liste waren die Eingabe für COUNTROWS():
Abbildung 3 - Ergebnis der endgültigen Lösung (Abbildung des Autors).
Dieser Ansatz hat die folgenden zwei positiven Punkte:
- Ich habe für jeden Kunden einen Bericht erstellt, da der Filterkontext pro Kunde die Kundentabelle vorfiltert. Auf diese Weise erhalte ich das richtige Ergebnis pro Kunde.
- Die Berechnung des Ergebnisses war in sehr kurzer Zeit abgeschlossen.
Abbildung 4 - Query Performance der Lösung (Abbildung des Autors).
Die Storage-Engine hat den grössten Teil der Arbeit erledigt (blauer Teil). Obwohl die Engine 27 Abfragen zu erledigen hat, können diese parallelisiert und in sehr kurzer Zeit ausgeführt werden. Ich habe diese Lösung dann auf die Power-BI-Datei meines Kunden mit 1,4 Millionen Einträgen in seiner Kundentabelle übertragen.
Das erste Visual brauchte mehr als eine Minute, um das Ergebnis anzuzeigen, doch sobald ich den Filter auf das Auftragsjahr gesetzt hatte, schloss die Power BI hat die Berechnung des Ergebnisses in weniger als einer Sekunde ab.
Ich muss mir nun die Laufzeiten ansehen und mit meinem Kunden besprechen, ob das in Ordnung ist oder ob ich die Lösung weiter optimieren muss.
LESSONS LEARNED
Während die Lösung für die erste Herausforderung relativ einfach war, erwies sich die zweite Herausforderung als viel schwieriger.
Der Grund, warum ich mich mit der Lösung des zweiten Problems schwer tat, war, dass ich anfing, in der gleichen Weise zu denken, in der mein Kunde das Problem definiert hatte. Sobald ich es aus einer neuen Perspektive betrachtete, fiel mir die Lösung ein. Ich brauchte die Herausforderung nur in kleinere Teile zu zerlegen, und es funktionierte.
Rückblickend empfehle ich, nicht schon bei der Besprechung des Problems mit dem Kunden nach einer Lösung zu suchen. Vervollständigen Sie zunächst die Definition der Herausforderung. Dann lehnen Sie sich ein paar Minuten zurück und betrachten Sie die Aufgabe, nachdem Sie in den "DAX-Modus" geschaltet haben.
Referenz
Ich verwendete den Contoso-Beispieldatensatz. Lade den ContosoRetailDW-Datensatz kostenlos von Microsoft hier herunter.
Die Contoso-Daten können unter der MIT-Lizenz frei verwendet werden, wie hier beschrieben.
Ich habe das Dataset vergrössert, damit die DAX-Engine besser arbeiten kann. Die Tabelle "Online Sales" enthält 71 Millionen Zeilen (statt 12,6 Millionen Zeilen), und die Tabelle "Retail Sales" enthält 15,5 Millionen Zeilen (statt 3,4 Millionen Zeilen).
HIER FINDEST DU WEITERE ARTIKEL UNSERER DATA & AI EXPERT*INNEN:
