Erweiterte Tabellen in LuckyTemplates

In diesem Tutorial werfen wir einen Blick auf ein ganz bestimmtes Szenario, das Sie sicher alle bereits bei der Arbeit mit DAX kennengelernt haben – die erweiterte Tabelle . Erweiterte Tabellen können Ihre Berechnung durcheinander bringen, wenn Sie nicht wissen, wie sie tatsächlich funktionieren. Das vollständige Video dieses Tutorials können Sie unten in diesem Blog ansehen.

Für dieses Beispiel verwenden wir die Contoso-Datenbank, die die Verkaufstabelle , die Kundentabelle , die Datumstabelle , die Produkttabelle , die Produktkategorietabelle und die Produktunterkategorietabelle enthält .

Auf der linken Seite haben wir eine Eins-zu-viele-Beziehung zwischen den Produkt-, Kategorie-, Unterkategorie- und Verkaufstabellen. Wir haben auch eine Eins-zu-Viele-Beziehung zwischen Kunde und Vertrieb sowie Terminen und Verkäufen.

Inhaltsverzeichnis

• Beispiel 1
• Beispiel #2
• Rote Verkäufe Nr. 1
• Rote Verkäufe Nr. 2
• Rote Verkäufe Nr. 3
• Erweiterte Tabellen überprüfen
• Definieren erweiterter Tabellen
• Aufteilen der Kategoriespalte und Verwenden erweiterter Tabellen
• Red Sales 2 erklären
• Red Sales erklären 3
• Verwenden von DAX Studio zum Überprüfen erweiterter Tabellen
• Abschluss

Beispiel 1

Nehmen wir an, wir möchten einen Bericht aus dieser Kategoriespalte in der Kategorietabelle erstellen und eine Kennzahl verwenden, die berechnet, wie viele Kunden in der Kundentabelle vorhanden sind. Wir möchten die Nummer für den Tisch des Kunden nach Kategorie aufteilen.

Wenn ich der Kategorietabelle einen Filter hinzufüge, gelangt dieser Filter zur Unterkategorietabelle, dann zur Produkttabelle und schließlich zur Verkaufstabelle und filtert diese. Dieser Filter kann die Tabelle des Kunden jedoch nicht filtern, es sei denn, wir aktivieren die bidirektionale Filterung.

Kehren wir zum Bericht zurück, um diese Berechnung zu korrigieren. Wir können diese Berechnung in die CALCULATE-Funktion einschließen und dann „Sales“ schreiben. Wir können sehen, dass wir dieses Mal nicht für jede Zelle des Bildes dieselbe Zahl wiederholen.

Um zu überprüfen, ob diese Berechnung korrekt ist, können wir den Kundenschlüssel aus der Verkaufstabelle in die Aggregation einbringen und dann eine Zählung (Distinct) darüber durchführen.

Sie können sehen, dass wir für jede Zeile denselben Wert zurückgeben. Durch die Verwendung der Verkaufstabelle innerhalb der CALCULATE-Funktion konnten wir die Berechnung korrigieren.

Beispiel #2

Kommen wir zum nächsten Beispiel. Hier wollen wir die Verkaufsmenge der roten Produkte im Jahr 2007, 2008 oder 2009 ermitteln. Je nach Auswahl des Slicers möchte ich auch ein Jahr zurückgehen.

Wenn ich beispielsweise 2009 auswähle, möchte ich die roten Umsätze für 2008 melden. Wenn ich 2008 auswähle, möchte ich auch die roten Umsätze für 2007 melden.

Rote Verkäufe Nr. 1

Die Berechnung unseres Gesamtumsatzes ist grundsätzlich eine Summe der Verkaufsdaten. Im Zeilenkontext multiplizieren wir die Menge mit dem Nettopreis.

Erstellen wir eine neue Kennzahl und nennen sie Red Sales. Wir schreiben CALCULATE und dann Total Sales. Wir werden die Verkaufstabelle Produkte gleich Rot ist.

Dann schreiben wir über die Daten in der Datumsspalte.

Wenn wir diese Berechnung in eine Kartenvisualisierung übertragen, erhalten wir eine Leerstelle.

Rote Verkäufe Nr. 2

Was passiert hier? Versuchen wir, diese Berechnung umzuschreiben und zu sehen, ob wir ein Ergebnis erhalten. Wir erstellen eine neue Kennzahl, benennen sie als „Red Sales 2“ und verwenden den ersten Teil der Berechnung für „Red Sales 1“.

Wir werden ein weiteres CALCULATE über das erste CALCULATE einleiten.

Bringen wir diese Kennzahl in die Matrix ein und sehen wir uns die Ergebnisse an. Wenn wir 2008 auswählen, erhalten wir 51.947. Wenn wir 2009 auswählen, erhalten wir 24.343. Wenn wir schließlich das Jahr 2010 auswählen, erhalten wir 39.724.

Rote Verkäufe Nr. 3

Es gibt eine andere Möglichkeit, diese Berechnung zu schreiben. Wir schreiben eine neue Kennzahl, nennen sie Red Sales 3 und verwenden dann die Funktion CALCULATE.

Wir werden den Gesamtumsatz berechnen und schreiben, dass die Farbe der Produkte gleich Rot ist. Verwenden Sie dann die Funktion SAMEPERIODLASTYEAR für die Daten.

Wenn wir Kennzahl Nr. 3 in die Kartenansicht ziehen, können Sie sehen, dass diese beiden Kartenwerte denselben Wert zurückgeben, was richtig ist.

Bei unserer ersten Berechnung stimmt jedoch etwas nicht, da wir ein Leerzeichen und nicht den korrekten Wert zurückgeben.

Mal sehen, was hier tatsächlich passiert. Nachdem wir nun einige Berechnungen in erweiterten Tabellen gesehen haben, wollen wir die Theorie dahinter verstehen.

Bevor Sie verstehen, was eine erweiterte Tabelle ist, müssen Sie verstehen, dass alle Tabellen, die wir hier haben, Basistabellen genannt werden .

Wann werden diese Tabellen zu erweiterten Tabellen? Sobald Sie eine n:1-Beziehung zwischen einer Tabelle und einer anderen Tabelle erstellen, wird die Basistabelle zur erweiterten Tabelle.

Erweiterte Tabellen überprüfen

Aber wie können wir überprüfen, ob tatsächlich eine Tabellenerweiterung stattfindet? Nun, Sie können das entsprechende Schlüsselwort für jede Tabelle verwenden. Wenn Sie von einer Seite auf die Spalte zugreifen können, wissen Sie, dass eine Tabellenerweiterung stattfinden kann.

Gehen wir zur Verkaufstabelle und erstellen eine neue berechnete Spalte.

Nehmen wir an, wir möchten die Produktfarbe für diesen bestimmten Produktschlüssel aus der Produkttabelle abrufen. Wir verwenden RELATED, wodurch nur die Liste der Spalten in IntelliSense angezeigt wird, die tatsächlich aus der Verkaufstabelle erweitert werden können.

Wir können sehen, dass die Kundentabelle und die Verkaufstabelle eine Viele-zu-Eins-Beziehung haben. Wir können auch die Liste der Kundentabellenspalten, Datumstabellenspalten und Produktspalten sehen.

Wenn wir Produkte[Farbe] auswählen, können wir mithilfe des Schlüsselworts RELATED eine neue Spalte in der Verkaufstabelle erstellen. RELATED gibt uns nur Zugriff auf die Spalten der Tabelle, auf die die Basistabelle tatsächlich erweitert werden kann.

Wenn wir die Art dieser Beziehung von Viele-zu-Eins auf Viele-zu-Viele ändern, wird diese Berechnung nicht mehr funktionieren.

Ändern wir die Art dieser Beziehung in eine Viele-zu-Viele-Beziehung. Unten sehen wir, dass wir ein Warnsymbol erhalten.

Wenn wir zur Verkaufstabelle zurückkehren, sehen wir die Fehlermeldung, dass die Spalte „Products[Color]“ entweder nicht existiert oder keine Beziehung zu einer Tabelle hat.

Im aktuellen Kontext ist die Warnung, die wir erhalten, nicht sehr benutzerfreundlich. Im Grunde bedeutet dies, dass die Verkaufstabelle nicht zur Produkttabelle erweitert werden kann, da wir nicht auf nur einen einzigen Wert für dieses bestimmte Zeilenelement zugreifen können.

Und da wir die Viele-zu-Viele-Beziehung verwenden, findet die Tabellenerweiterung nicht statt und RELATED funktioniert nicht.

Kehren wir zur Diagrammansicht zurück und korrigieren diese Berechnung. Wir ändern die Art der Beziehung in eine Viele-zu-Eins-Beziehung und aktivieren diese Beziehung, damit die Berechnung funktioniert.

Definieren erweiterter Tabellen

Bevor wir uns mit den Berechnungen befassen, die wir bereits im Bericht durchgeführt haben, wiederholen wir noch einmal die Definition der erweiterten Tabelle.

Wenn Sie über ein Datenmodell mit einem Sternschema verfügen, wird die Faktentabelle auf alle Tabellen im Datenmodell erweitert, wenn zwischen den Dimensionen und der Faktentabelle eine Viele-zu-Eins-Beziehung besteht.

Wenn Sie über ein Snowflake-Schema verfügen, werden die Produktunterkategorietabelle und die Kategorietabelle zur Basistabelle erweitert, in diesem Fall zur Produkttabelle. Die Verkaufstabelle ist die Basistabelle, die auf alle anderen Tabellen erweitert wird.

Im Hintergrund enthält die Verkaufstabelle alle Spalten in einer einzigen Tabelle. Beachten Sie, dass die Tabellenerweiterung nur ein logisches Konzept ist und daher nicht zu einer Erweiterung und Vergrößerung Ihres Datenmodells führt.

Die erweiterte Tabelle kommt nur ins Spiel, wenn Sie sich auf eine Tabelle beziehen (die die Basistabelle ist) und wenn Sie eine Viele-zu-Eins-Beziehung zu anderen Tabellen haben.

Aufteilen der Kategoriespalte und Verwenden erweiterter Tabellen

Versuchen wir, die Berechnungen zu korrigieren, die wir bereits in der Berichtsansicht durchgeführt haben. In diesem Beispiel schneiden wir die Kategoriespalte aus der Produktkategorietabelle auf und versuchen zu zählen, wie viele Kunden es gibt.

Was machen wir also eigentlich hier? Wir verwenden die erweiterte Verkaufstabelle als Referenz. Sobald der Filterkontext einen Wert aus der Kategorietabelle enthält, gelangt dieser Filter von den Unterkategorieprodukten zur Verkaufstabelle und direkt zu den Verkäufen.

Da „Sales“ eine erweiterte Tabelle ist und wir diese Referenz innerhalb der CALCULATE-Funktion verwenden, enthält die Sales-Tabelle auch die Spalte der Kundentabelle. Wenn wir einen Filter auf die Verkaufstabelle anwenden, filtern wir indirekt auch die Kundentabelle.

Kehren wir zu unseren roten Umsatzberechnungen zurück und versuchen zu verstehen, was tatsächlich passiert. Wir beginnen mit der Blindberechnung. Wenn wir diese Kennzahl auswählen, können Sie sehen, dass wir einen verschachtelten Code schreiben. Wir haben CALCULATE, dann den Gesamtumsatz, FILTER über die Verkäufe und SAMEPERIODLASTYEAR.

Lassen Sie uns diese Berechnung Schritt für Schritt aufschlüsseln. Zuerst müssen wir den externen Filterkontext identifizieren, der aus der Berechnung hervorgeht.

Wir haben im Slicer für die Kalenderjahreszahlen das Jahr 2008 ausgewählt.

Aus diesem Filterkontext wird die Verkaufstabelle ausgewertet. Die Verkaufstabelle enthält nur die Zeile für das Kalenderjahr 2008 und die Produktfarbe ist rot. Wir haben hier zwei Filter: einen, der vom Filterkontext erstellt wird, und einen, der vom Filter erstellt wird .

Das SAMEPERIODLASTYEAR wird im Filterkontext ausgewertet, wobei das Jahr 2008 ist. Es wird eine Liste der Daten im Jahr 2008 empfangen und diese Daten im Jahr 2008 auf 2007 verschieben. Die dadurch zurückgegebene Tabelle enthält nur die Termine für 2007.

Sobald diese beiden Vorgänge abgeschlossen sind, bereitet die CALCULATE-Funktion den Filterkontext vor und wendet diese beiden Filter auf den Filterkontext an. Wenn wir das anwenden, haben wir einen Filterkontext, bei dem die Produktfarbe Rot entspricht, und in der Jahresspalte haben wir einen Filter für 2007 und 2008.

Für dieses Datenmodell gibt es also keine einzelne Transaktion, die in zwei verschiedenen Jahren existiert. Wenn CALCULATE versucht, diese beiden Filter in einer Und- Bedingung zusammenzuführen , wird angezeigt, dass das Jahr 2008 und 2007 sein sollte und dass die Produktfarbe Rot sein sollte.

Wenn wir die Verkaufstabelle mit dem Filterkontext des Jahres 2008 filtern, filtern wir indirekt auch die Datumstabelle. Die Verkaufsstabilität hat eine Beziehung von n:1 zur Datumsstabilität.

Erstellen wir eine neue Berechnung, um zu ermitteln, wie viele Zeilen die Datumstabelle für die zugehörigen Daten enthält. Wir werden CALCULATE und dann der Datumstabelle schreiben. Dann werden wir die gesamte Verkaufstabelle FILTERN und sagen, dass die ZUGEHÖRIGEN Daten in der Kalenderjahrnummer 2008 sein sollten.

In CALCULATE haben wir keinen Filter für die Datumstabelle referenziert. Wir überprüfen lediglich, ob die Datumsangaben in der Kalenderjahrnummer aus dem Jahr 2008 stammen. Idealerweise sollte dieser Filter nicht in der Lage sein, die Datumstabelle zu filtern. Wir verwenden auch die Funktion, die den Filterkontext ignoriert, der vom Slicer kommt.

Erstellen wir eine neue Karte für diese Berechnung. Sie können sehen, dass wir 348 Zeilen zurückgeben.

Wie ist es also möglich, dass wir aus einer Datumstabelle mit 2500 Zeilen nur 348 Zeilen zurückgeben? Wenn wir eine erweiterte Tabelle verwenden, filtern wir indirekt auch die andere Tabelle, die über eine Viele-zu-Eins-Beziehung verbunden ist.

Obwohl wir für das laufende Jahr keinen Filter haben, begrenzen wir dennoch die Anzahl der sichtbaren Zeilen für die Dimensionstabelle, die wir auf der einen Seite haben.

Wenn wir die Verkaufstabelle zurückgeben, geben wir auch eine gefilterte Version der Datumstabelle, der Kundentabelle, der Produkttabelle, der Produktkategorietabelle und der Produktunterkategorietabelle zurück.

Red Sales 2 erklären

Fahren wir mit der nächsten Berechnung fort, nämlich Red Sales 2. Wir beginnen mit der äußeren CALCULATE, denn wenn wir diese Funktion in einem beliebigen Szenario verschachteln, sollte die äußere CALCULATE den Filterkontext für die innere CALCULATE vorbereiten.

Auf dem Slicer wählen wir das Kalenderjahr 2008 aus. Wenn die Funktion SAMEPERIODLASTYEAR das Datum für 2008 erhält, verschiebt sie diese Daten in das Jahr 2007 und dient als FILTER-Kontext für die innere Berechnung. Dieses innere BERECHNEN wird das Jahr 2007 auswerten.

Die FILTER-Funktion filtert die Verkaufstabelle und die Verkaufstabelle wird nur auf die Zeile beschränkt, in der das Jahr 2007 steht. Sobald wir die Zeilen für 2007 haben, überprüfen wir, ob die zugehörigen Produkte gleich rot sind.

Anders als bei der ersten Berechnung geben wir nicht zwei verschiedene Ebenen zurück. Diese beiden unterschiedlichen Ebenen werden nicht auf den FILTER-Kontext angewendet, sodass die von der FILTER-Funktion zurückgegebene Tabelle die gesamte Zeile der Verkaufstabelle enthält.

Sobald dieser Filter angewendet wird, erhalten wir die Verkaufsmenge für das Jahr 2007 sowie die Produkte, die rot sind, während wir in der ersten Berechnung die Jahre 2008 und 2007 zurückgegeben haben.

Dieses Mal haben wir den Filterkontext für die Verkaufstabelle vorbereitet, indem wir die Funktion SAMEPERIODLASTYEAR verwendet und die Funktion CALCULATE in eine andere Funktion CALCULATE geschachtelt haben.

Red Sales erklären 3

Für das dritte Beispiel haben wir eine sehr einfache CALCULATE-Funktion mit zwei Anweisungen: Eine besagt, dass die Produktfarbe gleich Rot ist, und dann die SAMEPERIODLASTYEAR für Datumsangaben.

Die Funktion SAMEPERIODLASTYEAR wird in einem Filterkontext ausgewertet, der nur die Daten von 2007 enthält. Die Farbe der Produkte wendet einen Filterkontext von Rot auf die Produkttabelle an, der wiederum die Verkaufstabelle filtert und nur die Zeilen für enthält die roten Produkte.

Wenn diese beiden Werte im Filterkontext angewendet werden, filtern sie die Verkaufstabelle, nicht jedoch die erweiterte Verkaufstabelle. Sie können sehen, dass wir nirgends im Code auf die erweiterte Verkaufstabelle verwiesen haben.

Dies ist das ideale Szenario, das Sie verwenden sollten, wenn Sie versuchen, auf die erweiterte Tabelle zu verweisen. Wenn Sie die erweiterte Tabelle verwenden, können die Berechnungen sehr kompliziert werden und manchmal können Sie nicht erkennen, warum Ihre Berechnungen ein falsches Ergebnis liefern.

Wenn Sie das Konzept erweiterter Tabellen nicht verstehen, können Sie diesen speziellen Code als Modellmaßnahme entwickeln und ihn dann in der Produktion bereitstellen. Andere Benutzer beginnen möglicherweise damit, über dieser Kennzahl andere Kennzahlen zu erstellen, und können möglicherweise nicht herausfinden, warum die Berechnungen nicht funktionieren, weil sie einfach nicht über das entsprechende Maß an Verständnis für die DAX-Sprache verfügen.

Wenn Sie mit DAX arbeiten, versuchen Sie immer, den Filter auf eine einzelne Spalte zu legen, da bei Verwendung einer einzelnen Spalte das Konzept der erweiterten Tabellen nicht angewendet wird. Wenn Sie beispielsweise PRODUCTS [Color] = „Red“ verwenden , erreicht der Filter die Verkaufstabelle, dieser Filter kann jedoch nicht die Tabelle des Kunden erreichen, da wir die erweiterte Verkaufstabelle nicht verwenden.

Bei der Verwendung einer einzelnen Spalte beziehen wir uns nicht auf das Konzept erweiterter Tabellen. Wir wenden lediglich einen Basisfilter auf die Verkaufstabelle an, der sich nicht auf die anderen Tabellen ausbreitet.

Verwenden von DAX Studio zum Überprüfen erweiterter Tabellen

Bevor wir zum Abschluss kommen, verwenden wir DAX Studio, um unseren Code für Red Sales 3 zu überprüfen. Gehen wir zur Registerkarte „Ansicht“, klicken Sie auf „Leistungsanalysator“ und starten Sie dann die Aufzeichnung. Wir aktualisieren das Bild und wählen eine leere Karte aus.

Von den externen Tools aus werden wir DAX Studio starten

Wir können sehen, dass wir im Ergebnisbildschirm unten ein Leerzeichen erhalten.

Wir aktivieren die Server-Timings, um die Abfragen zu verstehen, die hinter den Kulissen generiert werden. Wir gehen zur Registerkarte „Server-Timings“ und erweitern sie, um alles zu sehen.

Wir führen einen JOIN für die Verkaufstabelle auf der linken Seite durch. Wir führen auch einen JOIN für den Produktschlüssel aus der Produkttabelle durch.

In der WHERE-Klausel heißt es, dass die Datumsangaben in der Kalenderjahreszahl dem Jahr 2008 entsprechen und dass die Produktfarbe rot sein sollte.

Dann haben wir eine weitere Bedingung, die „Datum[Datum]“ lautet, gefolgt von einem Datumsbereich. Was sind also diese Daten? Idealerweise müssen diese Daten aus dem Jahr 2008 stammen, sie stammen jedoch aus dem Jahr 2007, wenn wir die Formatierung dieser Zahlen in Excel ändern.

Aus diesem Grund geben wir einen leeren Wert zurück. Wir geben an, dass das Kalenderjahr 2008 sein sollte, und wenden eine UND-Bedingung an, dass die Daten im Jahr 2007 liegen sollten. Dieses Jahr 2007 liegt an der Funktion SAMEPERIODLASTYEAR, die wir in der Berechnung verwendet haben.

Ändern wir die Kennzahl und den Verweis auf Red Sales 2. Sobald wir den Code ausführen, können Sie sehen, dass wir die Daten aus der Datumstabelle abrufen und dann die Datumsspalte nach der Kalenderjahreszahl 2008 filtern.

Dieses Mal können wir ein Ergebnis zurückgeben, weil wir keinen Filter auf die WHERE-Klausel anwenden und sagen, dass die Daten in der Kalenderjahrnummer 2008 entsprechen sollten.

Wo man die versteckte DAX-Falle findet.
Kunden im Laufe der Zeit mit DISTINCTCOUNT in LuckyTemplates zählen.
Neukundenanalyse mit Advanced DAX

Abschluss

Hoffentlich hat dieses Tutorial Ihnen geholfen zu verstehen, was eine erweiterte Tabelle ist und wie sie Ihre Berechnungen durcheinander bringen kann. Erweiterte Tabellen sind nicht wirklich intuitiv und auch nicht leicht zu verstehen.

Für weitere Informationen zu erweiterten Tabellen vergessen Sie nicht, den LuckyTemplates-TV-Kanal zu abonnieren. Wir veröffentlichen ständig eine große Menge an Inhalten von mir selbst und einer Reihe von Inhaltserstellern, die sich alle dafür einsetzen, die Art und Weise zu verbessern, wie Sie LuckyTemplates und die Power Platform nutzen.