Datenanalyseausdrücke (DAX) in Power Pivot
Data Analysis Expressions (DAX) klingt zunächst etwas einschüchternd, aber lassen Sie sich nicht vom Namen täuschen. DAX-Grundlagen sind wirklich recht einfach zu verstehen. Das Wichtigste zuerst: DAX ist KEINE Programmiersprache. DAX ist eine Formelsprache. Sie können DAX verwenden, um benutzerdefinierte Berechnungen für berechnete Spalten und Kennzahlen (auch als berechnete Felder bezeichnet) zu definieren. DAX enthält einige der Funktionen, die in Excel-Formeln verwendet werden, sowie zusätzliche Funktionen, die für die Arbeit mit relationalen Daten und die dynamische Aggregation entwickelt wurden.
DAX-Formeln verstehen
DAX-Formeln sind Excel-Formeln sehr ähnlich. Um eines zu erstellen, geben Sie ein Gleichheitszeichen ein, gefolgt von einem Funktionsnamen oder Ausdruck und allen erforderlichen Werten oder Argumenten. Wie Excel bietet DAX eine Vielzahl von Funktionen, die Sie verwenden können, um mit Zeichenfolgen zu arbeiten, Berechnungen mit Datums- und Uhrzeitangaben durchzuführen oder bedingte Werte zu erstellen.
DAX-Formeln unterscheiden sich jedoch in den folgenden wichtigen Punkten:
Wenn Sie Berechnungen zeilenweise anpassen möchten, enthält DAX Funktionen, mit denen Sie den aktuellen Zeilenwert oder einen verwandten Wert verwenden können, um Berechnungen durchzuführen, die je nach Kontext variieren.
DAX enthält einen Funktionstyp, der anstelle eines einzelnen Werts eine Tabelle als Ergebnis zurückgibt. Diese Funktionen können verwendet werden, um Eingaben für andere Funktionen bereitzustellen.
Zeitintelligenzfunktionen in DAX ermöglichen Berechnungen mit Datumsbereichen und vergleichen die Ergebnisse über parallele Zeiträume hinweg.
Verwendung von DAX-Formeln
Sie können Formeln in Power Pivot entweder in berechneten Spalten oder in berechneten Feldern erstellen .
Berechnete Spalten
Eine berechnete Spalte ist eine Spalte, die Sie einer vorhandenen Power Pivot-Tabelle hinzufügen. Anstatt Werte in die Spalte einzufügen oder zu importieren, erstellen Sie eine DAX-Formel, die die Spaltenwerte definiert. Wenn Sie die Power Pivot-Tabelle in eine PivotTable (oder ein PivotChart) einschließen, kann die berechnete Spalte wie jede andere Datenspalte verwendet werden.
Die Formeln in berechneten Spalten ähneln weitgehend den Formeln, die Sie in Excel erstellen. Anders als in Excel können Sie jedoch nicht für verschiedene Zeilen einer Tabelle unterschiedliche Formeln erstellen; Stattdessen wird die DAX-Formel automatisch auf die gesamte Spalte angewendet.
Wenn eine Spalte eine Formel enthält, wird der Wert für jede Zeile berechnet. Die Ergebnisse werden für die Spalte berechnet, sobald Sie die Formel erstellen. Spaltenwerte werden nur neu berechnet, wenn die zugrunde liegenden Daten aktualisiert werden oder wenn eine manuelle Neuberechnung verwendet wird.
Sie können berechnete Spalten erstellen, die auf Kennzahlen und anderen berechneten Spalten basieren. Vermeiden Sie es jedoch, denselben Namen für eine berechnete Spalte und eine Kennzahl zu verwenden, da dies zu verwirrenden Ergebnissen führen kann. Wenn Sie auf eine Spalte verweisen, verwenden Sie am besten einen vollständig qualifizierten Spaltenverweis, um zu vermeiden, dass versehentlich eine Kennzahl aufgerufen wird.
Ausführlichere Informationen finden Sie unter Berechnete Spalten in Power Pivot .
Mittel
Eine Kennzahl ist eine Formel, die speziell für die Verwendung in einer PivotTable (oder PivotChart) erstellt wird, die Power Pivot-Daten verwendet. Measures können auf standardmäßigen Aggregationsfunktionen wie COUNT oder SUM basieren, oder Sie können mithilfe von DAX Ihre eigene Formel definieren. Eine Kennzahl wird im Wertebereich einer PivotTable verwendet. Wenn Sie berechnete Ergebnisse in einem anderen Bereich einer PivotTable platzieren möchten, verwenden Sie stattdessen eine berechnete Spalte.
Wenn Sie eine Formel für eine explizite Kennzahl definieren, geschieht nichts, bis Sie die Kennzahl zu einer PivotTable hinzufügen. Wenn Sie die Kennzahl hinzufügen, wird die Formel für jede Zelle im Wertebereich der PivotTable ausgewertet. Da für jede Kombination aus Zeilen- und Spaltenüberschriften ein Ergebnis erstellt wird, kann das Ergebnis für die Kennzahl in jeder Zelle unterschiedlich sein.
Die Definition der von Ihnen erstellten Kennzahl wird mit ihrer Quelldatentabelle gespeichert. Es wird in der Liste PivotTable-Felder angezeigt und steht allen Benutzern der Arbeitsmappe zur Verfügung.
Ausführlichere Informationen finden Sie unter Measures in Power Pivot .
Erstellen von Formeln mithilfe der Formelleiste
Power Pivot bietet wie Excel eine Bearbeitungsleiste, um das Erstellen und Bearbeiten von Formeln zu vereinfachen, sowie eine AutoVervollständigen-Funktion, um Tipp- und Syntaxfehler zu minimieren.
So geben Sie den Namen einer Tabelle ein Beginnen Sie mit der Eingabe des Namens der Tabelle. Formula AutoComplete bietet eine Dropdown-Liste mit gültigen Namen, die mit diesen Buchstaben beginnen.
So geben Sie den Namen einer Spalte ein Geben Sie eine Klammer ein und wählen Sie dann die Spalte aus der Liste der Spalten in der aktuellen Tabelle aus. Beginnen Sie für eine Spalte aus einer anderen Tabelle mit der Eingabe der ersten Buchstaben des Tabellennamens und wählen Sie dann die Spalte aus der Dropdown-Liste „AutoComplete" aus.
Weitere Einzelheiten und eine exemplarische Vorgehensweise zum Erstellen von Formeln finden Sie unter Erstellen von Formeln für Berechnungen in Power Pivot .
Tipps zur Verwendung von AutoComplete
Sie können Formel-AutoVervollständigen mitten in einer vorhandenen Formel mit verschachtelten Funktionen verwenden. Der Text unmittelbar vor der Einfügemarke wird verwendet, um Werte in der Dropdown-Liste anzuzeigen, und der gesamte Text nach der Einfügemarke bleibt unverändert.
Definierte Namen, die Sie für Konstanten erstellen, werden nicht in der Dropdown-Liste „AutoVervollständigen" angezeigt, Sie können sie aber dennoch eingeben.
Power Pivot fügt die schließende Klammer von Funktionen nicht hinzu und passt Klammern nicht automatisch an. Sie sollten sicherstellen, dass jede Funktion syntaktisch korrekt ist oder Sie die Formel nicht speichern oder verwenden können.
Verwenden mehrerer Funktionen in einer Formel
Sie können Funktionen verschachteln, was bedeutet, dass Sie die Ergebnisse einer Funktion als Argument einer anderen Funktion verwenden. Sie können bis zu 64 Funktionsebenen in berechneten Spalten verschachteln. Die Verschachtelung kann jedoch die Erstellung oder Fehlerbehebung von Formeln erschweren.
Viele DAX-Funktionen sind ausschließlich für die Verwendung als verschachtelte Funktionen konzipiert. Diese Funktionen geben eine Tabelle zurück, die als Ergebnis nicht direkt gespeichert werden kann; es sollte als Eingabe für eine Tabellenfunktion bereitgestellt werden. Beispielsweise erfordern die Funktionen SUMX, AVERAGEX und MINX alle eine Tabelle als erstes Argument.
Hinweis: Innerhalb von Kennzahlen gibt es einige Einschränkungen für das Verschachteln von Funktionen, um sicherzustellen, dass die Leistung nicht durch die vielen Berechnungen beeinträchtigt wird, die durch Abhängigkeiten zwischen Spalten erforderlich sind.
Vergleich von DAX-Funktionen und Excel-Funktionen
Die DAX-Funktionsbibliothek basiert auf der Excel-Funktionsbibliothek, aber die Bibliotheken weisen viele Unterschiede auf. In diesem Abschnitt werden die Unterschiede und Ähnlichkeiten zwischen Excel-Funktionen und DAX-Funktionen zusammengefasst.
Viele DAX-Funktionen haben denselben Namen und dasselbe allgemeine Verhalten wie Excel-Funktionen, wurden jedoch so geändert, dass sie andere Arten von Eingaben akzeptieren, und geben in einigen Fällen möglicherweise einen anderen Datentyp zurück. Im Allgemeinen können Sie DAX-Funktionen nicht in einer Excel-Formel verwenden oder Excel-Formeln in Power Pivot ohne einige Änderungen verwenden.
DAX-Funktionen verwenden niemals eine Zellreferenz oder einen Bereich als Referenz, sondern DAX-Funktionen verwenden stattdessen eine Spalte oder Tabelle als Referenz.
DAX-Datums- und Uhrzeitfunktionen geben einen datetime-Datentyp zurück. Im Gegensatz dazu geben Excel-Datums- und Uhrzeitfunktionen eine Ganzzahl zurück, die ein Datum als fortlaufende Zahl darstellt.
Viele der neuen DAX-Funktionen geben entweder eine Wertetabelle zurück oder führen Berechnungen basierend auf einer Wertetabelle als Eingabe durch. Im Gegensatz dazu hat Excel keine Funktionen, die eine Tabelle zurückgeben, aber einige Funktionen können mit Arrays arbeiten. Die Möglichkeit, auf einfache Weise vollständige Tabellen und Spalten zu referenzieren, ist ein neues Feature in Power Pivot.
DAX stellt neue Nachschlagefunktionen bereit, die den Array- und Vektor-Nachschlagefunktionen in Excel ähneln. Die DAX-Funktionen erfordern jedoch, dass eine Beziehung zwischen den Tabellen hergestellt wird.
Es wird erwartet, dass die Daten in einer Spalte immer denselben Datentyp haben. Wenn die Daten nicht denselben Typ haben, ändert DAX die gesamte Spalte in den Datentyp, der alle Werte am besten aufnimmt.
DAX-Datentypen
Sie können Daten aus vielen verschiedenen Datenquellen, die möglicherweise unterschiedliche Datentypen unterstützen, in ein Power Pivot-Datenmodell importieren. Wenn Sie die Daten importieren oder laden und die Daten dann in Berechnungen oder in PivotTables verwenden, werden die Daten in einen der Power Pivot-Datentypen konvertiert. Eine Liste der Datentypen finden Sie unter Datentypen in Datenmodellen .
Der Tabellendatentyp ist ein neuer Datentyp in DAX, der als Eingabe oder Ausgabe für viele neue Funktionen verwendet wird. Beispielsweise nimmt die FILTER-Funktion eine Tabelle als Eingabe und gibt eine andere Tabelle aus, die nur die Zeilen enthält, die die Filterbedingungen erfüllen. Durch die Kombination von Tabellenfunktionen mit Aggregationsfunktionen können Sie komplexe Berechnungen über dynamisch definierte Datensätze durchführen. Weitere Informationen finden Sie unter Aggregationen in Power Pivot .
Formeln und das relationale Modell
Das Power Pivot-Fenster ist ein Bereich, in dem Sie mit mehreren Datentabellen arbeiten und die Tabellen in einem relationalen Modell verbinden können. Innerhalb dieses Datenmodells sind Tabellen durch Beziehungen miteinander verbunden, wodurch Sie Korrelationen mit Spalten in anderen Tabellen erstellen und interessantere Berechnungen erstellen können. Beispielsweise können Sie Formeln erstellen, die Werte für eine zugehörige Tabelle summieren, und diesen Wert dann in einer einzelnen Zelle speichern. Oder Sie können Filter auf Tabellen und Spalten anwenden, um die Zeilen aus der zugehörigen Tabelle zu steuern. Weitere Informationen finden Sie unter Beziehungen zwischen Tabellen in einem Datenmodell .
Da Sie Tabellen mithilfe von Beziehungen verknüpfen können, können Ihre PivotTables auch Daten aus mehreren Spalten enthalten, die aus unterschiedlichen Tabellen stammen.
Da Formeln jedoch mit ganzen Tabellen und Spalten arbeiten können, müssen Sie Berechnungen anders gestalten als in Excel.
Im Allgemeinen wird eine DAX-Formel in einer Spalte immer auf den gesamten Satz von Werten in der Spalte angewendet (nie auf nur wenige Zeilen oder Zellen).
Tabellen in Power Pivot müssen immer die gleiche Anzahl von Spalten in jeder Zeile haben, und alle Zeilen in einer Spalte müssen denselben Datentyp enthalten.
Wenn Tabellen durch eine Beziehung verbunden sind, müssen Sie sicherstellen, dass die beiden Spalten, die als Schlüssel verwendet werden, größtenteils übereinstimmende Werte aufweisen. Da Power Pivot keine referenzielle Integrität erzwingt, ist es möglich, nicht übereinstimmende Werte in einer Schlüsselspalte zu haben und dennoch eine Beziehung zu erstellen. Das Vorhandensein leerer oder nicht übereinstimmender Werte kann sich jedoch auf die Ergebnisse von Formeln und das Erscheinungsbild von PivotTables auswirken. Weitere Informationen finden Sie unter Suchen in Power Pivot-Formeln .
Wenn Sie Tabellen mithilfe von Beziehungen verknüpfen, erweitern Sie den Bereich oder Kontext , in dem Ihre Formeln ausgewertet werden. Beispielsweise können Formeln in einer PivotTable von beliebigen Filtern oder Spalten- und Zeilenüberschriften in der PivotTable beeinflusst werden. Sie können Formeln schreiben, die den Kontext manipulieren, aber der Kontext kann auch dazu führen, dass sich Ihre Ergebnisse auf eine Weise ändern, die Sie möglicherweise nicht vorhersehen. Weitere Informationen finden Sie unter Kontext in DAX-Formeln .
Aktualisieren der Ergebnisse von Formeln
Datenaktualisierung und Neuberechnung sind zwei separate, aber verwandte Vorgänge, die Sie verstehen sollten, wenn Sie ein Datenmodell entwerfen, das komplexe Formeln, große Datenmengen oder Daten enthält, die aus externen Datenquellen stammen.
Beim Aktualisieren von Daten werden die Daten in Ihrer Arbeitsmappe mit neuen Daten aus einer externen Datenquelle aktualisiert. Sie können Daten in von Ihnen festgelegten Intervallen manuell aktualisieren. Oder, wenn Sie die Arbeitsmappe auf einer SharePoint-Website veröffentlicht haben, können Sie eine automatische Aktualisierung aus externen Quellen planen.
Bei der Neuberechnung werden die Ergebnisse von Formeln aktualisiert, um Änderungen an den Formeln selbst und an den zugrunde liegenden Daten widerzuspiegeln. Die Neuberechnung kann sich auf folgende Weise auf die Leistung auswirken:
Bei einer berechneten Spalte sollte das Ergebnis der Formel immer für die gesamte Spalte neu berechnet werden, wenn Sie die Formel ändern.
Für eine Kennzahl werden die Ergebnisse einer Formel erst berechnet, wenn die Kennzahl im Kontext der PivotTable oder des PivotChart platziert wird. Die Formel wird auch neu berechnet, wenn Sie eine Zeilen- oder Spaltenüberschrift ändern, die sich auf Filter für die Daten auswirkt, oder wenn Sie die PivotTable manuell aktualisieren.
Fehlerbehebung bei Formeln
Fehler beim Schreiben von Formeln
Wenn Sie beim Definieren einer Formel einen Fehler erhalten, enthält die Formel möglicherweise einen Syntaxfehler , einen Semantikfehler oder einen Berechnungsfehler .
Syntaxfehler sind am einfachsten zu beheben. Sie beinhalten typischerweise eine fehlende Klammer oder ein Komma. Hilfe zur Syntax einzelner Funktionen finden Sie in der DAX-Funktionsreferenz.
Der andere Fehlertyp tritt auf, wenn die Syntax korrekt ist, aber der Wert oder die Spalte, auf die verwiesen wird, im Kontext der Formel keinen Sinn ergibt. Solche semantischen und Rechenfehler können durch eines der folgenden Probleme verursacht werden:
Die Formel bezieht sich auf eine nicht vorhandene Spalte, Tabelle oder Funktion.
Die Formel scheint korrekt zu sein, aber wenn die Daten-Engine die Daten abruft, findet sie einen Typenkonflikt und löst einen Fehler aus.
Die Formel übergibt eine falsche Anzahl oder einen falschen Parametertyp an eine Funktion.
Die Formel verweist auf eine andere Spalte, die einen Fehler aufweist, und daher sind ihre Werte ungültig.
Die Formel bezieht sich auf eine Spalte, die nicht verarbeitet wurde, d. h. sie enthält Metadaten, aber keine tatsächlichen Daten, die für Berechnungen verwendet werden können.
In den ersten vier Fällen kennzeichnet DAX die gesamte Spalte, die die ungültige Formel enthält. Im letzten Fall graut DAX die Spalte aus, um anzuzeigen, dass sich die Spalte in einem unverarbeiteten Zustand befindet.
Falsche oder ungewöhnliche Ergebnisse beim Rangieren oder Sortieren von Spaltenwerten
Beim Rangieren oder Sortieren einer Spalte, die den Wert NaN (Not a Number) enthält, erhalten Sie möglicherweise falsche oder unerwartete Ergebnisse. Wenn beispielsweise eine Berechnung 0 durch 0 dividiert, wird ein NaN-Ergebnis zurückgegeben.
Dies liegt daran, dass die Formel-Engine das Sortieren und Rangieren durch Vergleichen der numerischen Werte durchführt; NaN kann jedoch nicht mit anderen Zahlen in der Spalte verglichen werden.
Um korrekte Ergebnisse sicherzustellen, können Sie bedingte Anweisungen mit der IF-Funktion verwenden, um auf NaN-Werte zu testen und einen numerischen 0-Wert zurückzugeben.
Kompatibilität mit tabellarischen Modellen von Analysis Services und dem DirectQuery-Modus
Im Allgemeinen sind DAX-Formeln, die Sie in Power Pivot erstellen, vollständig mit tabellarischen Analysis Services-Modellen kompatibel. Wenn Sie Ihr Power Pivot-Modell jedoch zu einer Analysis Services-Instanz migrieren und das Modell dann im DirectQuery-Modus bereitstellen, gibt es einige Einschränkungen.
Einige DAX-Formeln geben möglicherweise andere Ergebnisse zurück, wenn Sie das Modell im DirectQuery-Modus bereitstellen.
Einige Formeln können Validierungsfehler verursachen, wenn Sie das Modell im DirectQuery-Modus bereitstellen, da die Formel eine DAX-Funktion enthält, die für eine relationale Datenquelle nicht unterstützt wird.
Weitere Informationen finden Sie in der Dokumentation zur tabellarischen Modellierung von Analysis Services in der SQL Server 2012-Onlinedokumentation.
No comments:
Post a Comment