Dieser Abschnitt enthält Links zu Beispielen, die die Verwendung von DAX-Formeln in den folgenden Szenarien veranschaulichen.
- Durchführen komplexer Berechnungen
- Arbeiten mit Text und Datumsangaben
- Bedingte Werte und Prüfung auf Fehler
- Verwenden von Zeitintelligenz
- Rangfolge und Vergleich von Werten
Inhalt dieses Artikels
Erste Schritte
Besuchen Sie das DAX-Ressourcencenter-Wiki , in dem Sie alle Arten von Informationen zu DAX finden, einschließlich Blogs, Beispiele, Whitepaper und Videos, die von branchenführenden Fachleuten und Microsoft bereitgestellt werden.
Szenarien: Durchführen komplexer Berechnungen
DAX-Formeln können komplexe Berechnungen durchführen, die benutzerdefinierte Aggregationen, Filterung und die Verwendung bedingter Werte umfassen. Dieser Abschnitt enthält Beispiele für die ersten Schritte mit benutzerdefinierten Berechnungen.
Erstellen von benutzerdefinierten Berechnungen für eine PivotTable
CALCULATE und CALCULATETABLE sind leistungsfähige, flexible Funktionen, die zum Definieren berechneter Felder nützlich sind. Mit diesen Funktionen können Sie den Kontext ändern, in dem die Berechnung ausgeführt wird. Sie können auch den Typ der Aggregation oder der auszuführenden mathematischen Operation anpassen. Beispiele finden Sie in den folgenden Themen.
Anwenden eines Filters auf eine Formel
An den meisten Stellen, an denen eine DAX-Funktion eine Tabelle als Argument verwendet, können Sie stattdessen eine gefilterte Tabelle übergeben, indem Sie entweder die FILTER-Funktion anstelle des Tabellennamens verwenden oder einen Filterausdruck als eines der Funktionsargumente angeben. Die folgenden Themen enthalten Beispiele für das Erstellen von Filtern und für den Einfluss von Filtern auf die Ergebnisse von Formeln. Weitere Informationen finden Sie unter Filtern von Daten in DAX-Formeln.
Mit der FILTER-Funktion können Sie Filterkriterien mithilfe eines Ausdrucks angeben, während die anderen Funktionen speziell für das Herausfiltern von leeren Werten konzipiert sind.
Selektives Entfernen von Filtern zum Erstellen eines dynamischen Verhältnisses
Durch das Erstellen dynamischer Filter in Formeln können Sie Fragen wie die folgenden problemlos beantworten:
- Wie hoch war der Beitrag des aktuellen Produktumsatzes zum Gesamtumsatz des Jahres?
- Wie viel hat dieser Geschäftsbereich im Vergleich zu anderen Geschäftsbereichen zum Gesamtgewinn aller Geschäftsjahre beigetragen?
Formeln, die Sie in einer PivotTable verwenden, können vom PivotTable-Kontext beeinflusst werden, aber Sie können den Kontext selektiv ändern, indem Sie Filter hinzufügen oder entfernen. Das Beispiel im Thema ALL zeigt Ihnen, wie Sie dies tun können. Um das Verhältnis der Umsätze für einen bestimmten Wiederverkäufer zu den Umsätzen für alle Wiederverkäufer zu ermitteln, erstellen Sie ein Measure, das den Wert für den aktuellen Kontext dividiert durch den Wert für den ALL-Kontext berechnet.
Das Thema ALLEXCEPT enthält ein Beispiel für das selektive Löschen von Filtern in einer Formel. In beiden Beispielen wird gezeigt, wie sich die Ergebnisse je nach Entwurf der PivotTable ändern.
Weitere Beispiele für die Berechnung von Verhältnissen und Prozentsätzen finden Sie in den folgenden Themen:
Verwenden eines Werts aus einer äußeren Schleife
Zusätzlich zur Verwendung von Werten aus dem aktuellen Kontext in Berechnungen kann DAX einen Wert aus einer vorherigen Schleife verwenden, um eine Reihe verwandter Berechnungen zu erstellen. Das folgende Thema enthält eine exemplarische Vorgehensweise zum Erstellen einer Formel, die auf einen Wert aus einer äußeren Schleife verweist. Die Funktion FRÜHER unterstützt bis zu zwei Ebenen von geschachtelten Schleifen.
Weitere Informationen zum Zeilenkontext und verwandten Tabellen sowie zur Verwendung dieses Konzepts in Formeln finden Sie unter Kontext in DAX-Formeln.
Szenarien: Arbeiten mit Text und Datumsangaben
Dieser Abschnitt enthält Links zu DAX-Referenzthemen, die Beispiele für häufige Szenarien enthalten, die das Arbeiten mit Text, das Extrahieren und Erstellen von Datums- und Uhrzeitwerten oder das Erstellen von Werten basierend auf einer Bedingung umfassen.
Erstellen einer Schlüsselspalte durch Verkettung
Power Pivot lässt keine zusammengesetzten Schlüssel zu. Wenn Sie zusammengesetzte Schlüssel in der Datenquelle haben, müssen Sie sie daher möglicherweise in einer einzigen Schlüsselspalte kombinieren. Das folgende Thema enthält ein Beispiel für das Erstellen einer berechneten Spalte basierend auf einem zusammengesetzten Schlüssel.
Compose eines Datums basierend auf Datumsteilen, die aus einem Textdatum extrahiert wurden
Power Pivot verwendet zum Arbeiten mit Datumsangaben den Datentyp "Datum/Uhrzeit" von SQL Server. Wenn Ihre externen Daten Datumsangaben enthalten, die anders formatiert sind, z. B. wenn Ihre Datumsangaben in einem regionalen Datumsformat geschrieben sind, das von der Power Pivot-Daten-Engine nicht erkannt wird, oder wenn Ihre Daten ganzzahlige Ersatzschlüssel verwenden, müssen Sie möglicherweise eine DAX-Formel verwenden, um die Datumsteile zu extrahieren und die Teile dann zu einem gültigen Datum zusammenzusetzen/ Zeitdarstellung.
Wenn Sie beispielsweise über eine Spalte mit Datumsangaben verfügen, die als ganze Zahl dargestellt und anschließend als Textzeichenfolge importiert wurden, können Sie die Zeichenfolge mithilfe der folgenden Formel in einen Datums-/Uhrzeitwert konvertieren:
=DATUM(RECHTS([Wert1];4);LINKS([Wert1];2);TEIL([Wert1];2))
| Wert1 | Result |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Die folgenden Themen enthalten weitere Informationen zu den Funktionen zum Extrahieren und Verfassen von Datumsangaben.
Definieren eines benutzerdefinierten Datums- oder Zahlenformats
Wenn die Daten Datumsangaben oder Zahlen enthalten, die nicht in einem der standardmäßigen Textformate von Windows dargestellt werden, können Sie ein benutzerdefiniertes Format definieren, um sicherzustellen, dass die Werte korrekt verarbeitet werden. Diese Formate werden beim Konvertieren von Werten in Zeichenfolgen oder aus Zeichenfolgen verwendet. Die folgenden Themen enthalten auch eine detaillierte Liste der vordefinierten Formate, die für die Arbeit mit Datumsangaben und Zahlen verfügbar sind.
- Vordefinierte numerische Formate für die FORMAT-Funktion
- Benutzerdefinierte numerische Formate für die FORMAT-Funktion
- Vordefinierte Datums- und Uhrzeitformate für die FORMAT-Funktion
- Benutzerdefinierte Datums- und Uhrzeitformate für die FORMAT-Funktion
Ändern von Datentypen mithilfe einer Formel
In Power Pivot wird der Datentyp der Ausgabe durch die Quellspalten bestimmt, und Sie können den Datentyp des Ergebnisses nicht explizit angeben, da der optimale Datentyp von Power Pivot bestimmt wird. Sie können jedoch die impliziten Datentypkonvertierungen von Power Pivot verwenden, um den Ausgabedatentyp zu ändern.
- Um ein Datum oder eine Zahlenzeichenfolge in eine Zahl zu konvertieren, multiplizieren Sie mit 1,0. Die folgende Formel berechnet beispielsweise das aktuelle Datum minus 3 Tage und gibt dann den entsprechenden ganzzahligen Wert aus.
=(HEUTE()-3)*1,0 - Um einen Datums-, Zahlen- oder Währungswert in eine Zeichenfolge zu konvertieren, verketten Sie den Wert mit einer leeren Zeichenfolge. Die folgende Formel gibt beispielsweise das heutige Datum als Zeichenfolge zurück.
=""& HEUTE()
Die folgenden Funktionen können ebenfalls verwendet werden, um sicherzustellen, dass ein bestimmter Datentyp zurückgegeben wird:
Konvertieren von reellen Zahlen in ganze Zahlen
- RUNDEN
- OBERGRENZE
-
UNTERGRENZE
Konvertieren von reellen Zahlen, ganzen Zahlen oder Datumsangaben in Zeichenfolgen - FEST
-
FORMAT Function
Konvertieren von Zeichenfolgen in reelle Zahlen oder Datumsangaben - WERT
- DATWERT
- ZEITWERT
Szenario: Bedingte Werte und Testen auf Fehler
Wie Excel verfügt DAX über Funktionen, mit denen Sie Werte in den Daten testen und basierend auf einer Bedingung einen anderen Wert zurückgeben können. Sie können beispielsweise eine berechnete Spalte erstellen, die Wiederverkäufer je nach Jahresumsatz entweder als "Bevorzugt" oder " Wert" bezeichnet. Funktionen, die Werte testen, sind auch nützlich, um den Bereich oder Typ von Werten zu überprüfen, um zu verhindern, dass unerwartete Datenfehler Berechnungen unterbrechen.
Erstellen eines Werts basierend auf einer Bedingung
Sie können verschachtelte WENN-Bedingungen verwenden, um Werte zu testen und neue Werte bedingt zu generieren. Die folgenden Themen enthalten einige einfache Beispiele für die bedingte Verarbeitung und bedingte Werte:
Testen auf Fehler in einer Formel
Im Gegensatz zu Excel können Sie in einer Zeile einer berechneten Spalte keine gültigen Werte und in einer anderen Zeile keine ungültigen Werte haben. Das heißt, wenn in einem beliebigen Teil einer Power Pivot-Spalte ein Fehler auftritt, wird die gesamte Spalte mit einem Fehler gekennzeichnet, sodass Sie Formelfehler, die zu ungültigen Werten führen, immer korrigieren müssen.
Wenn Sie beispielsweise eine Formel erstellen, die durch Null dividiert, erhalten Sie möglicherweise das Unendlichkeitsergebnis oder einen Fehler. Einige Formeln schlagen auch fehl, wenn die Funktion bei der Erwartung eines numerischen Werts einen leeren Wert findet. Während Sie Ihr Datenmodell entwickeln, empfiehlt es sich, die Fehler erscheinen zu lassen, damit Sie auf die Meldung klicken und das Problem beheben können. Wenn Sie Arbeitsmappen veröffentlichen, sollten Sie jedoch eine Fehlerbehandlung integrieren, um zu verhindern, dass unerwartete Werte zu Berechnungen führen.
Um zu vermeiden, dass in einer berechneten Spalte Fehler zurückgegeben werden, verwenden Sie eine Kombination aus logischen und Informationsfunktionen, um auf Fehler zu testen und immer gültige Werte zurückzugeben. Die folgenden Themen enthalten einige einfache Beispiele dafür, wie Sie dies in DAX tun können:
Szenarien: Verwenden von Zeitintelligenz
Die DAX-Zeitintelligenzfunktionen umfassen Funktionen, mit denen Sie Datumsangaben oder Datumsbereiche aus Ihren Daten abrufen können. Sie können dann diese Datumsangaben oder Datumsbereiche verwenden, um Werte über ähnliche Zeiträume hinweg zu berechnen. Die Zeitintelligenzfunktionen umfassen auch Funktionen, die mit Standarddatumsintervallen arbeiten, damit Sie Werte über Monate, Jahre oder Quartale hinweg vergleichen können. Sie können auch eine Formel erstellen, die Werte für das erste und letzte Datum eines angegebenen Zeitraums vergleicht.
Eine Liste aller Zeitintelligenzfunktionen finden Sie unter Zeitintelligenzfunktionen (DAX). Tipps zur effektiven Verwendung von Datums- und Uhrzeitangaben in einer Power Pivot-Analyse finden Sie unter Datumsangaben in Power Pivot.
Kumulierte Umsätze berechnen
Die folgenden Themen enthalten Beispiele für das Berechnen von Schließ- und Eröffnungssalden. Mit den Beispielen können Sie laufende Salden über verschiedene Intervalle wie Tage, Monate, Quartale oder Jahre erstellen.
- CLOSINGBALANCEMONTH-Funktion, CLOSINGBALANCEQUARTER-Funktion, CLOSINGBALANCEYEAR-Funktion
- Funktion OPENINGBALANCEMONTH, Funktion OPENINGBALANCEQUARTER, Funktion OPENINGBALANCEYEAR
Vergleichen von Werten im Zeitverlauf
Die folgenden Themen enthalten Beispiele für das Vergleichen von Summen über verschiedene Zeiträume hinweg. Die von DAX unterstützten Standardzeiträume sind Monate, Quartale und Jahre.
- PREVIOUSMONTH-Funktion, PREVIOUSQUARTER,PREVIOUSYEAR-Funktion
- TOTALMTD-Funktion, TOTALQTD-Funktion, TOTALYTD
- PARALLELPERIOD-Funktion
Berechnen eines Werts über einen benutzerdefinierten Datumsbereich
Beispiele zum Abrufen von benutzerdefinierten Datumsbereichen, z. B. die ersten 15 Tage nach dem Start einer Werbeaktion, finden Sie in den folgenden Themen.
Wenn Sie Zeitintelligenzfunktionen verwenden, um einen benutzerdefinierten Satz von Datumsangaben abzurufen, können Sie diesen Satz Datumsangaben als Eingabe für eine Funktion verwenden, die Berechnungen ausführt, um benutzerdefinierte Aggregate über Zeiträume hinweg zu erstellen. Im folgenden Thema finden Sie ein Beispiel dafür, wie Sie dies tun können:
-
Hinweis
Wenn Sie keinen benutzerdefinierten Datumsbereich angeben müssen, aber mit Standardbuchhaltungseinheiten wie Monaten, Quartalen oder Jahren arbeiten, empfehlen wir, Berechnungen mit den dafür vorgesehenen Zeitintelligenzfunktionen wie TOTALQTD, TOTALMTD, TOTALQTD usw. durchzuführen.
Szenarien: Rangfolge und Vergleich von Werten
Um nur die ersten n Elemente in einer Spalte oder PivotTable anzuzeigen, haben Sie mehrere Möglichkeiten:
- Sie können die Features in Excel verwenden, um einen Top-Filter zu erstellen. Sie können auch eine Reihe von oberen oder niedrigsten Werten in einer PivotTable auswählen. Im ersten Teil dieses Abschnitts wird beschrieben, wie Sie nach den obersten 10 Elementen in einer PivotTable filtern. Weitere Informationen finden Sie in der Excel-Dokumentation.
- Sie können eine Formel erstellen, die Werte dynamisch in eine Rangfolge bringt, und dann nach den Rangwerten filtern oder den Rangfolgewert als Datenschnitt verwenden. Im zweiten Teil dieses Abschnitts wird beschrieben, wie Sie diese Formel erstellen und diese Rangfolge dann in einem Datenschnitt verwenden.
Jede Methode hat Vor- und Nachteile.
- Der Excel-Top-Filter ist einfach zu verwenden, aber er dient ausschließlich zu Anzeigezwecken. Wenn sich die Daten ändern, die der PivotTable zugrunde liegen, müssen Sie die PivotTable manuell aktualisieren, um die Änderungen anzuzeigen. Wenn Sie dynamisch mit Ranglisten arbeiten müssen, können Sie DAX verwenden, um eine Formel zu erstellen, die Werte mit anderen Werten in einer Spalte vergleicht.
- Die DAX-Formel ist leistungsfähiger; Wenn Sie außerdem den Rangfolgewert zu einem Datenschnitt hinzufügen, können Sie einfach auf den Datenschnitt klicken, um die Anzahl der angezeigten Top-Werte zu ändern. Die Berechnungen sind jedoch rechenintensiv, und diese Methode ist möglicherweise nicht für Tabellen mit vielen Zeilen geeignet.
Nur die ersten zehn Elemente in einer PivotTable anzeigen
So zeigen Sie die oberen oder unteren Werte in einer PivotTable an
|
|---|
Dynamisches Sortieren von Artikeln mithilfe einer Formel
Das folgende Thema enthält ein Beispiel für die Verwendung von DAX zum Erstellen einer Rangfolge, die in einer berechneten Spalte gespeichert wird. Da DAX-Formeln dynamisch berechnet werden, können Sie immer sicher sein, dass die Rangfolge korrekt ist, auch wenn sich die zugrunde liegenden Daten geändert haben. Da die Formel in einer berechneten Spalte verwendet wird, können Sie außerdem die Rangfolge in einem Datenschnitt verwenden und dann die Top 5, Top 10 oder sogar Top 100 Werte auswählen.