Implizite Measures gibt es sowohl in Excel Power Pivot als auch in Power BI. Was sind Implizite Measures und warum sollte man sie vermeiden?

Power Pivot in Excel

Sicherlich hast du in Excel schon herkömmliche Pivot-Tabellen angelegt. Man markiert einen Datenbereich und wählt dann Pivot-Tabelle einfügen. Dann erscheint ein Platzhalter, in dem die Pivot-Tabelle angezeigt werden soll. Außerdem erscheint eine Feldliste mit den Spalten aus dem gewählten Datenbereich.

Herkömmliche Pivot Tabelle in Excel

Betrachtet man das Feld Verkäufe sieht man, dass Excel entschieden hat die Werte zu summieren. Das kann man leicht über die Wertfeldeinstellungen ändern.

Kommen die Daten nicht aus einem Datenbereich, sondern aus dem Excel-Datenmodell, sieht die Feldliste ein wenig anders aus. Die Feldliste besteht aus Tabellen, die man aufklappen kann, um an die Felder zu gelangen. Man erhält aber weiterhin einen Platzhalter für die Pivot-Tabelle und kann auch hier Felder direkt in den Bereich Werte einfügen. Die Werte werden nach wie vor summiert, sofern es sich um summierbare Datentypen handelt.

Power BI

In Power BI verhält sich das ähnlich. Die Oberfläche ist anders als in Excel Power Pivot. Auf der rechten Seite findet man eine Feldliste mit allen Feldern. In Power BI arbeitet man mit Visuals - um beim aktuellen Beispiel zu bleiben nehmen wir das Matrix Visual. Auch in Power BI kann ich ein summierbares Feld direkt in die Werte einfügen und Power BI summiert das Ergebnis.

Wozu dann DAX?

In unseren Grundlagen-Trainings kommen wir irgendwann zu Einführung in DAX. Natürlich mit einfachen Funktionen. Beispielsweise:

Dieses neue Measure kann ich jetzt in einer Pivot-Tabelle verwenden. Als Ergebnis erhält man exakt das gleiche Ergebnis, wie wenn man das Feld Sales[Total] in der Pivot-Tabelle verwendet hätte. Wozu der umständliche Weg über Measures?

Nähern wir uns dem Problem über das Format

Möchte man in Power BI das Measure Summe Verkäufe Total als Währung ohne Nachkommastellen formatieren, wählt man das Feld Sales[Total] und formatiert dieses. Irgendwie geht man bei Excel auch so vor. Man wählt die Wertfeldeinstellungen und kann dort das Format ändern.

Möchten wir aber nicht die Summe der Aufträge, sondern die Anzahl, nehmen wir das Feld Sales[SalesOrderId] und stellen die Aggregation auf Anzahl. Du kannst übrigens auch jedes andere Feld verwenden. Es ist völlig unerheblich, ob du die Auftragsnummer, das Datum oder auch hier Sales[Total] verwendest - solange du die Aggregation auf Anzahl stellst erhälst du immer das gleiche Ergebnis. In Power BI sieht das dann beispielsweise so aus:

Implizites Measure *Anzahl Aufträge* in Power BI

Möchten wir jetzt Tausender-Trennzeichen zur einfacheren Lesbarkeit haben, haben wir keine Chance. Man kann zwar dem Feld SalesOrderId ein Tausender-Trennzeichen verpassen - es hat aber keine Auswirkung. Wenn die Auftragsnummer alphanumerisch wäre (also Text und Zahlen), dann geht das so ohnehin nicht.

Implizite Measures

In beiden Fällen, also der Summe der Verkäufe als auch der Anzahl Aufträge handelt es sich um Implizite Measures. In Wirklichkeit wird nicht das Feld verwendet, sondern es wird intern ein Measure angelegt, das dem Benutzer verborgen bleibt. Das Format dieses Measures wird abgeleitet aus dem zugrundeliegenden Feld. Im Fall der Summe der Verkäufe passt das gut. Im Fall der Anzahl Verkäufe passt das nicht.

In Excel Power Pivot kann man implizite Measures tatsächlich sichbar machen. Nachdem man ein Feld in einer Pivot-Tabelle aggregiert (oder auch in Pivot-Charts), wird im Datenmodell ein solches implizites Measure angelegt. Wechselt man ins Datenmodell kann man dort im Menü unter Erweitert die Option Implizite Measures anzeigen aktivieren:

Implizites Measure anzeigen in Excel Power Pivot

Im Measures-Bereich in Excel Power Pivot wird dadurch ein Measure sichtbar:

Implizites Measure in Excel Power Pivot

Mit einem kleinem Doppelpfeil rechts oben in der Zelle wird das Measure als implizit gekennzeichnet. Im Bearbeitungsbereich kann die Measure nicht geändert werden. Auch wenn implizite Measures nicht sichtbar sind, erscheinen diese immer in der Auswahlliste, wenn DAX-Expressions formuliert werden. Und natürlich: das irritiert, denn man findet diese Measures nicht, wenn man implizite Measures nicht anzeigt.

Implizite Measures in Power BI...

...kann man anders als in Excel Power Pivot nicht sichtbar machen. Man muss einfach wissen, dass jedes Mal, wenn man ein Feld aus dem Datenmodell direkt in einen Bereich eines Visuals einfügt in dem aggregiert wird (Summe, Anzahl, Min, Max etc.), ein implizites Measure angelegt wird.

Warum Fluch?

In Excel Power Pivot ist der einfachste Grund, dass implizite Measures irritieren. Bei Erstellung von DAX-Formeln tauchen ständig Measures auf, auf die man sich beziehen kann, die man nicht angelegt hat. Anfänger verstehen das nicht.

In Power BI kann man mit der Formatierung argumentieren - aber das ist natürlich plakativ. Ein wichtiger Grund ist, dass in einem wartbaren Datenmodell, Measures auf anderen Measures aufbauen. So definiert man beispielsweise ein Measure für die Gesamtsumme der Verkäufe:

Den Vorjahreswert definiert man dann über das bereits verfügbare Measure:

Viele weitere Measures sind jetzt denkbar, wie Vorvorjahr, Differenzen absolut, Vormonat - oder einfach eine Year-To-Date-Variante. Soll jetzt die Berechnung des Umsatzes redefiniert werden, muss in dieser Struktur nur das Measure [Total Sales] geändert werden. Das Datenmodell bleibt damit konsistent.

Power BI mit Excel auswerten

Power BI Datenmodelle werden im Power BI Service online veröffentlicht und die Reporte können in der Organisation und auch außerhalb der Organisation freigegeben werden. Nur wenige wissen, dass via Excel auf dieses Online verfügbare Datenmodell zugegriffen werden kann. Ausgehend vom Datenmodell im Power BI Service kann man eine Verbindung via Excel herstellen:

In Power BI veröffentlichte Datenmodelle in Excel analysieren

Über diese Funktion wird eine Excel-Datei im Browser heruntergeladen (oder eine ODT-Datei mit Verbindungsdaten). Die Excel-Datei ist mit dem Datenmodell verknüpft und es kann direkt mit Pivot-Tabellen oder -Charts auf die Daten im Datenmodell zugegriffen werden.

Implizite Measures werden hier nicht unterstützt. Das bedeutet, dass hier beispielsweise das Feld Sales[Total] nicht direkt in die Werte der Pivot-Tabelle eingefügt werden kann. Es muss ein explizites Measure verfügbar sein. Neben allen Wartbarkeitsgründen ist dieser Grund sicherlich ein ausreichender. Um ein zukunftsfähiges Datenmodell zu erstellen muss man auf impliziten Measures verzichten.

Best Practise

  • Sowohl in Power BI als auch in Excel Power Pivot kann man Spalten ausblenden. Alle Spalten mit Werte, die direkt aggregiert werden könnten (also keine Dimensionsdaten, wie Produktnamen, Versandarten etc.) werden ausgeblendet. Damit kommt ein Nutzer des Datenmodells nicht in Versuchung.
  • In Power BI können in Tabellen Anzeigeordner erstellt werden. Alle Dimensions-Felder und alle Measures werden in verschiedenen Ordner organisiert.
  • Measures gehören immer zu einer Tabelle - aus rein organisatorischen Gründen. Wird die Tabelle versehentlich gelöscht (weil man sich in Power Query vertan hat), sind alle Measures ebenfalls gelöscht. Daher kann es sinnvoll sein, eine leere Abfrage anzulegen - beispielsweise mit dem Namen _measures. Leere Abfragen gibt es so direkt nicht - es wird immer eine Spalte darin vorkommen. Nachdem allerdings das erste Measure dieser neuen Dummy-Tabelle zugeordnet wurde, kann die Spalte der Summy-Tabelle gelöscht werden. Die Tabelle bleibt dann erhalten.