Niveau: dazwischenliegend
Ich habe in der Vergangenheit einige Artikel geschrieben, die eingebettete Verweise auf DAX-Abfragen und DAX-Tabellenfunktionen enthielten. Heute schreibe ich einen Artikel, der führt die DAX-Abfragesprache von Grund auf ein, damit Sie lernen, wie Sie sie verwenden und wie Sie damit ein Power BI-Superstar werden können.
Was ist eine Abfragesprache??
Eine Abfragesprache ist eine Programmiersprache, mit der Sie eine Anfrage an eine Datenbank senden und dann Daten in Form einer Tabelle empfangen können. Wenn Sie nur ein paar hundert Datenzeilen haben, das ist nicht so sinnvoll. Und, Zweitens, hat Millionen von Datenzeilen, es wird sehr nützlich.
Der wichtigste Unterschied zwischen DAX bei der Verwendung in Kennzahlen und berechneten Spalten und DAX als Abfragesprache besteht darin, dass die Abfragesprache gibt immer eine Tabelle zurück. Die resultierende Tabelle besteht aus einer oder mehreren Spalten, keine oder mehr Zeilen und Daten in keiner oder mehreren Tabellenzellen.
Warum benötige ich eine Abfragesprache?
Beim Erstellen eines Datenmodells in Power BI oder Power Pivot für Excel, Sie laden im Grunde tabellarische Daten in eine dedizierte Berichtsdatenbank zum ausdrücklichen Zweck der Analyse und / oder Berichte. Sobald es geladen ist, Sie (der Autor des Berichts) Sie verbringen normalerweise Stunden damit, das Datenmodell zu erstellen, damit es zur Wertschöpfung Ihres Unternehmens verwendet werden kann / Job. Schließlich, wenn das alles erledigt ist, Die gängigste Methode zur Verwendung Ihres neuen Datenmodells besteht darin, Berichte und Pivot-Tabellen zu erstellen, die einem Benutzer Daten auf leicht verständliche Weise präsentieren.
Dann, zeigt ein Beispiel für einen interaktiven Power BI-Bericht, der von einem Power BI-Datenmodell ausgeführt wird.
Und hier ist eine Pivot-Tabelle, die auf einem Excel Power Pivot-Datenmodell ausgeführt wird.
Beide oben genannten Visualisierungen sind Zusammenfassungen der zugrunde liegenden Daten und in beiden Fällen in Wirklichkeit, kann keine der zugrunde liegenden Daten selbst sehen, nur die zusammenfassung. Die oben genannten Visualisierungstools sind hervorragend und äußerst nützlich. aber trotzdem, Sie sind im Allgemeinen nicht die besten Tools, wenn Sie die zugrunde liegenden Daten selbst sehen oder aus irgendeinem Grund sehr große Datensätze extrahieren möchten.
Nachdem Sie all diesen Aufwand in den Aufbau Ihres Datenmodells investiert haben, werde es in vollen Zügen nutzen wollen. Power Pivot und Power BI können viele Millionen Datenzeilen verarbeiten (im Gegensatz zu herkömmlichem Excel) Ja, Daher, das “alter Weg” nur auf das zugrunde liegende Diagramm zu schauen ist in dieser glücklichen neuen Welt nicht wirklich praktikabel.
Zeig mir die zugrunde liegenden Daten!
Wenn Sie die Pivot-Tabelle nehmen, die ich oben gezeigt habe, Die meisten Excel-Benutzer würden wissen, dass sie auf jede Wertzelle doppelklicken können (sagen wir das 31,6% repräsentiert den Verkauf von Mountainbikes für Menschen zwischen 30 Ja 40 Jahre alt). Wenn Sie auf eine herkömmliche Pivot-Tabelle wie diese doppelklicken, es passiert etwas Magisches: eine Kopie der zugrunde liegenden Daten wird aus der Quelle extrahiert und erscheint auf magische Weise in einem neuen Blatt in Excel. Sobald Sie mit dem Betrachten der Kopie der Daten fertig sind, Sie können das neue Blatt löschen und sind wieder da, wo Sie angefangen haben: Das leben ist gut.
Wenn Sie auf eine herkömmliche Pivot-Tabelle wie diese doppelklicken, Sie senden effektiv eine Abfrage an die zugrunde liegende Datenbank und die Antwort auf die Abfrage ist eine Datentabelle.
Aber funktioniert es mit Power Pivot??
Bestimmt, ja und nein je nachdem was du suchst. Anders sieht es aus, wenn Sie über ein Excel Power Pivot-Datenmodell verfügen oder mit einer externen SSAS-Datenquelle verbunden sind. Durch meine Erfahrung, die Ergebnisse sind bestenfalls inkonsistent und, im schlimmsten Fall, Sie liefern möglicherweise nicht das, was Sie erwarten. Aber die gute Nachricht ist, dass mit DAX als Abfragesprache, Sie können ganz einfach jede gewünschte Abfrage schreiben und genau die Teilmenge der Daten extrahieren, die Sie benötigen. Sie müssen sich nicht mehr darauf verlassen, dass Sie auf Ihre Pivot-Tabelle doppelklicken, um Ihre Daten abzufragen.
Zeit, DAX als Abfragesprache zu lernen
Gut, genug Gerede, Es ist an der Zeit, DAX als Abfragesprache ernst zu nehmen. Sie haben Zeit und Mühe investiert, um Ihr Datenmodell in Power BI oder Power Pivot zu erstellen, und jetzt, mit Recht, Sie möchten eine Teilmenge Ihrer Daten extrahieren, damit Sie sie sich ansehen können. Lasst uns beginnen.
Wählen Sie zuerst das entsprechende Werkzeug aus
Es gibt 3 allgemeine Ansätze, mit denen Sie eine Datentabelle aus Ihrem fabelhaften Datenmodell extrahieren können.
- Verwenden Sie ein dediziertes Tool wie DAX Studio oder SQL Server Management Studio. Ich habe hier einen Artikel geschrieben, in dem beschrieben wird, wie DAX Studio installiert und verwendet wird.
- Schreiben Sie einen “neuer Tisch” und Power BI Desktop. ich schrieb a Artikel zur Verwendung dieser Funktion hier.
- Benutze einen “Bretttrick” Ich habe von Marco Russo gelernt, eine Tabelle direkt in Excel zu extrahieren. Ich werde diesen Trick später in dieser Artikelserie behandeln..
In diesem Blogartikel werde ich DAX Studio verwenden, aber Sie können Power BI "Neue Tabelle" verwenden, wenn Sie fortfahren möchten und nicht haben (oder willst du) DAX-Studio. Eigentlich, wenn du diese Dinge lernen willst, dann sollte ich immer noch einen der beiden Ansätze verwenden. Durch meine Erfahrung, Sie können neue Konzepte nicht einfach durch Lesen lernen. Aber trotzdem, Beachten Sie, dass die Verwendung von Power BI und Excel als Abfragetool als vorübergehende Extraktion der Daten betrachtet werden sollte. Nachdem Sie sich die gewünschten Daten angesehen haben, Normalerweise würde ich die Tabelle fallen lassen, damit Sie nicht am Ende redundante Daten speichern (Sie können sie bei Bedarf jederzeit wieder extrahieren).
Beginnen wir mit der Bewertungsaussage
Alle DAX-Abfragen müssen mit der EVALUATE-Anweisung beginnen. Aber trotzdem, es gibt eine ausnahme. Wenn Sie die Taste verwenden “Neue Tabelle” von Power BI, um eine Abfrage zu schreiben, Lassen Sie diese EVALUATE-Anweisung einfach weg. Dies ist sicherlich ein weiteres Beispiel für “Syntax Zucker” wo Entwickler den Benutzer vor einigen der verwirrendsten Syntaxen schützen. Wenn Sie meine Abfragen unten in Power BI verfolgen und testen, Lassen Sie beim Schreiben der Abfragen einfach die EVALUATE-Anweisung weg. Was ist mehr, wenn Sie Ihre DAX Studio-Abfrage jemals in eine Tabelle in Power BI verschieben möchten, Sie können die EVALUATE-Anweisung zu diesem Zeitpunkt entfernen.
DAX Studio Anbindung an die Datenbank
Wenn ich DAX Studio betreibe, Ich werde aufgefordert, mich mit meiner Datenbank zu verbinden. In diesem Fall, Ich habe eine Instanz von Power BI Desktop mit einem Adventure Works-Datenmodell, das auf meinem PC ausgeführt wird, und DAX Studio bietet mir die Möglichkeit, eine Verbindung dazu herzustellen. Sie können auch eine Verbindung mit Excel Power Pivot-Datenmodellen und tabellarischen SSAS-Servern herstellen.
Für eine ausführlichere Beschreibung der DAX Studio-Benutzeroberfläche, Lies meinen anderen Beitrag, den ich oben erwähnt habe. Andererseits, lass uns darauf eingehen.
Eine vorhandene Tabelle zurückgeben
Die einfachste DAX-Abfrage, die Sie eingeben können, besteht darin, einfach eine vollständige Kopie einer vorhandenen Tabelle zurückzugeben.
EVALUATE
Customers
Wenn ich diese Abfrage in DAX Studio ausführe (was zeigt als 1 dann) und drücken Sie dann F5, um auszuführen, die komplette Tabelle wird zurückgegeben (was zeigt als 2). Insgesamt 18,484 Datenzeilen (gezeigt als 3).
Dies ist an sich nicht allzu nützlich, da Sie diese Tabelle wahrscheinlich bereits auf einem Quellsystem haben.
Liste einzigartiger Werte
In DAX gibt es viele Funktionen, die Tabellen anstelle von Skalarwerten zurückgeben. Ich habe dieses Thema letzte Woche ausführlich behandelt, sodass Sie es hier lesen können, wenn Sie möchten..
Lassen Sie mich jetzt die ALL-Funktion sehen.
AUSWERTEN ALLE(Produkte[Kategorie])
Wenn ich diese Tabellenfunktion als Abfrage ausführe, Ich kann wirklich “beobachten” die Ergebnisse der Konsultation. In diesem Fall, Die Abfrage gibt eine einspaltige Tabelle zurück, die aus 4 Reihen, Alle einzigartigen Produktkategorien.
Dann, mal WERTE sehen.
EVALUATE
VALUES(Produkte[Kategorie])
In diesem Fall, Die Tabellenfunktion Werte gibt genau das gleiche Ergebnis zurück.
Werfen wir nun einen Blick auf DISTINCT.
Evaluate
DISTINCT(Produkte[Kategorie])
Und das Gleiche hier noch einmal.
Alle drei Tabellenfunktionen geben in diesem Fall dieselbe Tabelle zurück. In Wirklichkeit, Jeder macht unterschiedliche Dinge in verschiedenen Filterszenarien. Dies ist eines der besten Dinge an DAX-Abfragen und das ist, dass Sie “inspizieren” Was passiert mit diesen Tabellenfunktionen in verschiedenen Szenarien?. Mehr dazu später.
Eine zweispaltige Tabelle
Die ALL-Funktion kann dauern 1 oder mehr Spalten als Eingabe (oder ein Tisch). Im folgenden Beispiel, Ich habe ALL verwendet, um eine Tabelle aller Produktkategorie- und Produktunterkategorie-Kombinationen zu materialisieren. Es gibt 37 einzigartige Kombinationen insgesamt.
Eine gefilterte Tabelle
Die meisten Leute, die DAX kennen, werden über die FILTER-Funktion Bescheid wissen. Die Filter-Funktion gibt eine gefilterte Kopie einer Tabelle zurück. zum Beispiel, siehe Beispiel unten:
EVALUATE
FILTER(Produkte, [Gesamtumsatz] > 200000)
Dies ist eine viel interessantere Abfrage, da sie eine Tabelle zurückgibt, die alle Produkte enthält, die mehr als $ 200,000 Ständig im Vertrieb. Es gibt 49 dieser Produkte.
Ändern des Filterkontexts
Wenn Sie eine DAX-Kennzahl oder eine berechnete Spalte eingeben, Es gibt nur eine Möglichkeit, den Kontext des Filters zu ändern, nämlich die Verwendung der CALCULATE-Funktion. CALCULATE gibt einen Skalarwert zurück, nachdem Sie Filter angewendet haben. CALCULATETABLE ist ein Geschwister von CALCULATE. Wie Sie wahrscheinlich erraten können, CALCULATETABLE gibt eine Tabelle zurück, nachdem Sie Filter angewendet haben.
Diese nächste Abfrage gibt genau das gleiche Ergebnis wie die letzte Abfrage zurück.
EVALUATE
CALCULATETABLE(Produkte, FILTER(Produkte, [Gesamtumsatz] > 200000))
Der Hauptunterschied bei der Verwendung von CalculateTable im Vergleich zur Verwendung von Filter besteht darin, dass CalculateTable eine andere Tabelle an die Filterfunktion zurückgeben kann.
EVALUATE
CALCULATETABLE(Der Umsatz, FILTER ( Produkte, [Gesamtumsatz] > 200000))
Die obige Abfrage verwendet dieselbe FILTER-Funktion wie zuvor, aber anstatt eine Tabelle mit den Produkten zurückzugeben, die mehr als verkauft haben $ 200,000, gibt alle tatsächlichen Verkaufstransaktionen zurück, die dazu beigetragen haben, dass sich diese Produkte qualifizieren (siehe Ergebnisse unten).
Tabellenfunktionen kombinieren
Jetzt, da Sie sehen, wie einige der Standardtabellenfunktionen in einer Abfrage funktionieren, mal sehen, wie man sie in einer einzigen Abfrage kombiniert.
EVALUATE
CALCULATETABLE(
WERTE(Produkte[Unterkategorie]),
FILTER(Produkte, [Gesamtumsatz] > 50000)
)
Diese Abfrage wendet zuerst einen Filter auf alle Produkte an, die mehr als verkauft haben $ 50,000 (Linie 4 dann) und gibt dann eine Liste eindeutiger Produktunterkategorien zurück, die diese Produkte enthalten.
Eine interessante Sache an den Ergebnissen der vorherigen Abfrage ist, dass die “Helme” stehen auf der Liste (die einzige Unterkategorie, die nicht für Fahrräder gedacht ist). Um zu sehen, was die genauen Produkte sind, wo es verkauft wurde, es geht einfach darum, eine weitere Abfrage wie folgt zu schreiben.
EVALUATE
CALCULATETABLE(
WERTE(Produkte[Produktname]),
FILTER(Produkte, [Gesamtumsatz] > 50000),
Produkte[Unterkategorie]="Helme"
)
Beachten Sie, wie ich die ursprüngliche Abfragestruktur wiederverwenden konnte, um die Liste der Produktnamen mit nur einer kleinen Änderung an der Abfrage zurückzugeben.
Nächste Woche gibt es mehr
Das ist ein tolles Thema, zu groß für einen einzelnen Blogpost. Diese Woche habe ich das Thema DAX-Abfragen vorgestellt, Ich habe gezeigt, wie Abfragen geschrieben werden können, um Tabellen zurückzugeben, und wie Tabellenfunktionen kombiniert werden können, um Daten abzufragen. Nächste Woche werde ich einige neue spezielle Abfragefunktionen vorstellen, die die Fähigkeiten noch mehr erweitern.
