Wie im ersten Teil dieser 3-teiligen Blogbeitragsserie zu sehen war, ist die Berechnung des Alters zu einem definierten Stichtag auf Basis einer Calculated Column einfach und intuitiv möglich. Im zweiten Teil haben wir gezeigt, daß die Berechnung in einer Calculated Column nicht durch eine User Interaktion beeinflußt werden kann - eine Dynamisierung also nicht möglich ist. In diesem dritten und abschließendem Teil wird die Lösung vorgestellt, wie mit einem Measure auf Basis einer virtuellen Tabelle die Dynamisierung erreicht wird und damit auch gleich Zeitreihenauswertungen möglich werden. Die dabei verwendete Methode der virtuellen Tabelle kann für sehr viele fortgeschrittene Fragestellungen in DAX angewendet werden.

Vielen Dank an die Teilnehmer des diesjährigen Power BI Camps, die diesen Praxisfall intensiv und mit Begeisterung diskutiert haben und denen ich die Lösung der dynamischen Berechnung versprochen habe und dieses Versprechen hiermit einlöse. :)

1. Grundsätzliche Berechnung auf Basis eines Measures

Wir verwenden den Disconnected Table zur Selektion des Stichtags aus Teil 2 dieser Blogbeitragsserie auch in der finalen Lösung, wir waren diesbezüglich bereits auf einem richtigen Weg. Vereinfachend führen wir die Berechnung des Durchschnittsalters vorerst mit allen Mitarbeitern durch.

Als ersten Versuch verwenden wir das DAX-Statement aus der statischen Berechnung in der Calculated Column (siehe Teil 1) jetzt als Measure. Lediglich müssen wir das Geburtsdatum mit einer Aggregatfunktion umschließen, da Measures per definition für die Berechnung auf allen Datenebenen ausgelegt sein müssen. Naheliegend ist die Verwendung der AVERAGE()-Funktion, da wir ein Durchschnittsalter ermitteln möchten:

Ф Alter (dynamisch v0 - nur 1 Stichtag) = 
// v0 = die Mitarbeiter-Tabelle wird ungefiltert in die Altersberechnung einbezogen

ROUNDDOWN(
    (
        [Stichtag Selektiert] 
        - AVERAGE('Dimension Mitarbeiter'[Geburtsdatum])
    )
    /365,25;
    0
)

Das Ergebnis dieses simplen Measures ist verblüffend gut und reagiert auch bereits dynamisch auf die Slicer Selektion für den Berechnungsstichtag:

Misson Completed? Nein, leider noch nicht. Die Berechnung ist auf Mitarbeiterebene bereits richtig, aber die Aggregationen auf die Total-Ebene und auch die Teilergebnisse bspw. für männlich/weiblich sind bei näherer Betrachtung falsch. Erstens führt die Ermittlung eines durchschnittlichen Geburtsdatums nicht zum gleichen Ergebnis wie die Verdurchschnittung des Alters und zweitens fließt die ungefilterte Mitarbeitertabelle mit allen Mitarbeitern in die Durchschnittsberechnung ein.

Bevor wir diese Themen lösen, betrachten wir den sehr positiven Effekt des Measures, ohne weiteres auch gleich Zeitreihen visualisieren zu können. Dabei entpuppt sich die Verwendung unseres Measures Stichtag Selektiert als starke Einschränkung, da dieses die DAX-Funktion SELECTEDVALUE() verwendet (siehe Teil 2) und damit auf die Einfachselektionen ausgelegt bzw. begrenzt ist. Bei einer Mehrfachselektion für den Stichtag - eine solche tritt jedenfalls auf der Total-Ebene in der folgenden Visualisierung ein - wird der hinterlegte Default Wert (hier: 30.04.2019) für die Berechnung verwendet:

Diese Berechnung ist für die User dieses Reports nicht nachvollziehbar und damit irreführend. Die Ermittlung des Stichtags kann aber sehr leicht durch eine einfache MAX()-Abfrage auf die Stichtagstabelle dynamisiert werden:

Ф Alter (dynamisch v0 - Zeitreihe) = 
// v0 = die Mitarbeiter-Tabelle wird ungefiltert in die Altersberechnung einbezogen

ROUNDDOWN(
    (
        MAX('Selektion Stichtag'[Date]) 
        - AVERAGE('Dimension Mitarbeiter'[Geburtsdatum])
    )
    /365,25;
    0
)

Jetzt wird auf Total-Ebene das Alter für den höchsten selektierten Stichtag ermittelt - eine vermutlich für die meisten User logische Vorgehensweise:

Nun wollen wir die Thematik der fehlenden Filterung der Mitarbeitertabelle lösen. Ein naheliegender Gedanke wäre, unsere Berechnung mit der CALCULATE() Funktion zu umschließen - CALCULATE() kann stark vereinfacht mit der Funktion SUMMEWENNS() in Excel verglichen werden - und die Mitarbeiterdimension mit der FILTER() Funktion auf die relevanten Datensätze zu filtern. Dazu machen wir einen einfachen Versuch, indem wir die Altersberechnung auf die weiblichen MitarbeiterInnen einschränken möchten:

Ф Alter (dynamisch v0 - Versuch Filter) = 
// v0 = die Mitarbeiter-Tabelle wird ungefiltert in die Altersberechnung einbezogen

CALCULATE(
    ROUNDDOWN(([Stichtag Selektiert] - AVERAGE('Dimension Mitarbeiter'[Geburtsdatum]))/365,25;0);
    FILTER(
        'Dimension Mitarbeiter';
        'Dimension Mitarbeiter'[MA Geschlecht] = "weiblich"
    )
)

Das Ergebnis ist aber nicht wie erwartet: die männlichen Mitarbeiter werden nicht weggefiltert sondern mit einem fixen Alter von 119 Jahren ausgewiesen:

Wie kommt es dazu? Nun ja, der Filter Context für das Measure liefert auf der Ebene eines oder mehrerer (oder auch aller) männlichen Mitarbeiter ein BLANK() für das Geburtsdatum, da aus dem Visual die Filterbedingung "männlicher Mitarbeiter" mit der Filterbedingung "Geschlecht = weiblich" aus dem CALCULATE() kombiniert wird. Das Ergebnis ist eine Filtertabelle mit 0 Datensätzen, die Aggregation des Geburtsdatums einer leeren Tabelle liefert ein BLANK(). Damit wird das Alter beim Stichtag 30.04.2019 mit 119 Jahren ermittelt, da BLANK() in diesem Fall gleichgesetzt wird mit 0 und das dem Datum 31.12.1899 entspricht.

2. Berechnung auf Basis einer virtuellen Tabelle mit vereinfachter Filterung

Wir müssen also umdenken und einen anderen Weg für die dynamische Ermittlung des Durchschnittsalters einschlagen. Zur Vorbereitung reichern wir die Mitarbeiterdimension um die beiden Felder Beschäftigt Von und Beschäftigt Bis an. Vereinfachend schließen wir damit vorläufig den Fall aus, daß Mitarbeiter auch mehrmals aus- und wiedereintreten können.

Hier die DAX-Statements für die beiden Calculated Columns:

Beschäftigt von = 
CALCULATE(
    MIN(Faktentabelle[Datum]);
    Faktentabelle[Kennzahl] = "Aktiv"
)

Beschäftigt bis = 
VAR varMAX =
CALCULATE(
    MAX(Faktentabelle[Datum]);
    Faktentabelle[Kennzahl] = "Aktiv"
)
RETURN
// Bedingung für laufendes (unvollständiges) Jahr und Zukunfsjahr 2020 -> damit die Altersberechnung bis Ende 2020 stattfindet
IF(varMAX = [Aktueller Stichtag];DATE(2020;12;31);varMAX) 

Aus dieser physischen Tabelle wird jetzt mittels Measure dynamisch das Alter nur der zum jeweiligen Zeitpunkt aktiven Mitarbeiter ermittelt und jetzt auch korrekt verdurchschnittet:

Wie funktioniert das? Im Measure wird eine sogenannte virtuelle Tabelle erzeugt - aus Übersichtlichkeitsgründen haben wir dafür die Variable varTabelleAktiveMitarbeiter verwendet. Diese virtuelle Tabelle - und das ist der Clou - wird je Berechnungsdurchlauf des Measures dynamisch über die Variable varStichtag auf den jeweiligen Stichtag entsprechend dem Filter Context des Datenpunkts im Visual gefiltert. Die virtuelle Tabelle wird um die Calculated Column varColumn_Alter angereichert und diese Column wird dann mittels der Iterator-Funktion AVERAGEX() auf das jeweilige Aggregationsniveau der Measure Berechnung verdurchschnittet (der Iterator wird weniger aus logischen als aus Syntax Gründen eingesetzt):

Ф Alter (dynamisch v1) = 
// v1 = Filterung auf aktive Mitarbeiter  in MA-Dimension mittels Hilfsfelder "Beschäftigt von" und "Beschäftigt bis" = näherungsweise Berechnung

VAR varStichtag = 
    MAX('Selektion Stichtag'[Date])

// virtuelle Tabelle mit dynamisch gefilterter Mitarbeiterdimensnion
VAR varTabelleAktiveMitarbeiter =
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE(
                'Dimension Mitarbeiter';
                'Dimension Mitarbeiter'[MA Nummer];
                'Dimension Mitarbeiter'[Geburtsdatum];
                'Dimension Mitarbeiter'[Beschäftigt von];
                'Dimension Mitarbeiter'[Beschäftigt bis]
            );
            'Dimension Mitarbeiter'[Beschäftigt von] <= varStichtag;
            'Dimension Mitarbeiter'[Beschäftigt bis] >= varStichtag
        );
        "varColumn_Alter"; ROUNDDOWN( (varStichtag - [Geburtsdatum]) / 365,25 ;0)
    )

RETURN
    // Der Iterator wird verwendet, um die virtuelle Tabelle aus der Variable auswerten zu können (CALCULATE unterstützt die Referenz nicht)
    AVERAGEX(
        varTabelleAktiveMitarbeiter;
        AVERAGE([varColumn_Alter])
        // Referenz auf eine Calculated Column in der virtuellen Tabelle
        // Quelle: https://www.sqlbi.com/articles/table-and-column-references-using-dax-variables/
    )

Die Berechnung in der virtuellen Tabelle können wir sehr einfach transparent und nachvollziehbar machen. Wir brauchen dazu lediglich das DAX-Statement für die virtuelle Tabelle in die New Table Funktion kopieren und einen physischen Calculated Table erzeugen damit wir uns das Berechnungsergebnis ansehen und analysieren können. Die Dynamisierung des Stichtags und die x-fache Berechnung des Measures für alle Datenpunkte des Visuals müssen wir uns vorstellen, da wir - wie im Teil 2 dargelegt - auch im Calculated Table nur einen statischen Berechnungsstichtag einsetzen können:

Das Grundgerüst der virtuellen Tabelle ist ein mit der DAX-Funktion SUMMARIZE() erzeugter 1:1 Auszug der Mitarbeiterdimension mit lediglich den für die Berechnung benötigten Spalten. Diese Tabelle wird mit der DAX-Funktion CALCULATEDTABLE() gefiltert (107 Mitarbeiter per 30.04.2019 statt 253 Mitarbeiter insgesamt) und die Umklammerung mit der DAX-Funktion ADDCOLUMNS() ermöglicht das Hinzufügen beliebig vieler Calculated Columns zu dieser gefilterten Grundtabelle. Hinzugefügt wird lediglich die Calculated Column für die Berechnung des Alters auf Mitarbeiterebene - also genau das, was wir schon aus Teil 1 kennen.

Das DAX Statement des Measures - und damit die virtuelle Tabelle - wird für jeden sichtbaren Datenpunkt im Visual eigenständig mit dem individuellen Filter Context des Datenpunkts berechnet und darauf der Altersdurchschnitt ermittelt:

Das Wichtigste zusammengefasst: das DAX Statement in einem Measure wird also so oft berechnet, wie es Datenpunkte in einem Visual gibt - und zwar mit dem spezifischen Filter Context des jeweiligen Datenpunktes. Eine Calculated Column hingegen wird so oft berechnet, wie es Datensätze in einer Tabelle (physisch oder virtuell) gibt und berücksichtigt per definition keinen Filter Context aus einem Report.

3. Berechnung auf Basis einer virtuellen Tabelle mit perfekter Filterung

Da ein Mitarbeiter / eine Mitarbeiterin mehrmals aus- und wiedereintreten kann, ist die bisherige Berechnung lediglich eine Näherung, da nur das erste Eintrittsdatum und das letzte Austrittsdatum berücksichtigt wurden. In der folgenden Berechnung wird periodengenau ermittelt, ob ein Aktiv-Datensatz in der Faktentabelle für den betreffenden Mitarbeiter vorliegt oder nicht. Die Mitarbeiterin 1035 Romero, Saskia war im Jahr 2013 vorübergehend nicht beschäftigt, in der Berechnung links wird dieser Umstand nicht berücksichtigt, in der finalen Berechnung rechts sehr wohl:

Hier das DAX-Statement für dieses finale Measure:

Ф Alter (dynamisch v2) = 
// v2 = Ermittlung der MA Liste auf Basis der Faktentabelle auch mit allen zwischenzeitlichen Ein-/Austritten = perfekte Berechnung

VAR varStichtag = 
    MAX('Selektion Stichtag'[Date])

VAR varStichtag_Faktenfilter = 
    // Bedingung für Berechnungen nach dem letzten IST-Datensatz in der Faktentabelle: wer zum aktuellen Stichtag beschäftigt ist, gilt auch zukünftig als beschäftigt
    IF(
        varStichtag > [Aktueller Stichtag];
        [Aktueller Stichtag];
        varStichtag
    )
    
VAR varListeAktiveMitarbeiter =
// FILTER() funktioniert, CALCULATETABLE() funktioniert hier nicht
FILTER(
    ADDCOLUMNS(
        ALL('Dimension Mitarbeiter'[MA Nummer];'Dimension Mitarbeiter'[Geburtsdatum]);
        "Ist Aktiv"; 
                CALCULATE(
                    COUNTROWS(Faktentabelle);
                    FILTER(
                        Faktentabelle;
                        Faktentabelle[MA Nummer] = 'Dimension Mitarbeiter'[MA Nummer]
                        && Faktentabelle[Kennzahl] = "Aktiv"
                        && Faktentabelle[Datum] = varStichtag_Faktenfilter
                    )
                );
        "varColumn_Alter";
            ROUNDDOWN( (varStichtag - [Geburtsdatum]) / 365,25 ;0)
    );
    [Ist Aktiv] <> BLANK()
)

RETURN
    // Der Iterator wird verwendet, um die Tabelle aus der Variable auswerten zu können (CALCULATE unterstützt die Referenz nicht)
    AVERAGEX(
        varListeAktiveMitarbeiter;
        AVERAGE([varColumn_Alter])
        // Referenz auf die Calculated Column in der virtuellen Tabelle
        // Quelle: https://www.sqlbi.com/articles/table-and-column-references-using-dax-variables/
    )

Der Aufbau des Measures entspricht dem bereits vorgestellten Muster, lediglich die Ermittlung der virtuellen Tabelle erfolgt nach einem anderen Konzept. Um die Berechnung einfach nachvollziehen zu können, kopieren wir das DAX-Statement für die virtuelle Tabelle wieder in die New Table Funktion um einen physischen Calculated Table erzeugen. Der Stichtag muß wiederum auf ein konkretes Datum fixiert werden:

Die virtuelle Tabelle wird folgendermaßen erzeugt: mit der DAX-Funktion ALL() wird das Grundgerüst geschaffen, nämlich eine 1:1 Kopie der Mitarbeiterdimension mit lediglich den beiden Spalten MA Nummer und Geburtsdatum. Die DAX-Funktion ADDCOLUMNS() ermöglicht das Hinzufügen beliebig vieler Calculated Columns zu dieser Grundtabelle. Die erste Calculated Column Ist Aktiv nutzt das DAX Konzept der Context Transition und gibt die Anzahl der Aktiv-Datensätze aus der Faktentabelle für den jeweiligen Mitarbeiter zum definierten Stichtag an. Die zweite Calculated Column varColumn_Alter ermittelt das Alter des Mitarbeiters zum definierten Stichtag. Die Umklammerung dieser Tabelle mit der DAX-Funktion FILTER() ermöglicht die Filterung auf diejenigen Mitarbeiter, die (zumindest) einen Aktiv-Datensatz haben.

Das Measure funktioniert jetzt fehlerfrei und kann für zahlreiche Visualisierungen des Durchschnittsalters mit oder ohne Zeitachse eingesetzt werden:

4. Disconnected Table vs. Date Dimension

Bisher sieht es so aus, als könnte die Lösung nur mit einem Disconnected Table für die Auswahl der Berechnungstichtage implementiert werden. Das stimmt aber nicht. Anstelle des Disconnected Tables Selektion Stichtag kann genauso gut die Tabelle Dimension Datum für die dynamische Altersberechnung als "Stichtagslieferant" verwendet werden. Aufgrund der 1:n Beziehungen zur Faktentabelle - beide mit unidirektionaler Filterung - stehen die beiden Dimensionstabellen in keiner aktiven Filterbeziehung zueinander, daher ist die Tabelle Dimension Datum aus Sicht der Tabelle Dimension Mitarbeiter ohnehin ein Disconnected Table:

Der Vorteil bei der Verwendung der Date Dimension für die Altersberechnung liegt dann darin, daß die Altersberechnung auf die Periodenfilterung der anderen Measures im Report "synchronisiert", also über die gleiche Periodenselektion gesteuert, werden kann. Der Nachteil liegt darin, daß erstens eine unabhängige Selektion des Berechnungsstichtags dann nicht mehr möglich ist und zweitens mehr Know-How zum Verständnis der Lösung notwendig ist. Daher wurde dieser Blogbeitrag mit dem eigenständigen Disconnected Table aufgebaut, um hier zu verdeutlichen, daß es keine aktiven Beziehungen zwischen den beiden Tabellen geben darf.

Fazit

Das angewendete DAX Pattern ist nicht trivial, aber mit etwas Übung kann das Pattern relativ leicht für sehr viele fortgeschrittene Fragestellungen dieser Art angewendet werden. In der Praxis wird wohl eher zuerst ein pyhsischer Calculated Table (für einen fix definierten Stichtag) entwickelt und dann in einem zweiten Schritt die Dynamisierung als virtuelle Tabelle in einem Measure implementiert.