Kleine wijzigingen in DAX v2

Een paar weken geleden gaf ik de training Writing PowerPivot and BI Tabular Model Calculations using DAX. Geen hele nieuwe training, maar wat wel nieuw was, was de versie van PowerPivot waarmee ik de training gaf. Dat was namelijk de SQL Server 2012 versie van PowerPivot, ook wel versie 2 genoemd (PowerPivot werd met SQL Server 2008 R2 geïntroduceerd). En tot mijn verrassing, liepen we tegen wat kleine verschillen aan tijdens het doen van de oefeningen. Verschillen die leiden tot fouten in DAX expressies die in versie 1 nog geen fouten gaven.

Het eerste verschil waar we tegenaan liepen, had te maken met het berekenen van een gemiddelde over een periode van 10 werkdagen. In eerste instantie was hiervoor in de tijd tabel een calculated column aangemaakt waarin voor iedere datum de datum 10 werkdagen voor die datum werd bepaald. Vervolgens werd in de draaitabel een measure aangemaakt met de volgende DAX formule:

=IF (
    COUNTROWS (
        VALUES (Calendar[Date])) > 1;
        BLANK ();
        CALCULATE (
            SUM (Sales_SalesOrderDetail[LineTotal]);
            DATESBETWEEN (
                Calendar[Date];
                VALUES (Calendar[FirstDate]);
                VALUES (Calendar[Date]
            )
         )
     ) / 10
)

In deze measure is FirstDate de eerder genoemde calculated column die voor iedere ‘Date’ de datum 10 werkdagen terug bevat. Deze measure houdt rekening met het feit dat je wel een gemiddelde over 10 werkdagen kunt bepalen voor één datum, maar niet zo makkelijk voor een periode van datums, bijv. een hele maand. Om dat toch te doen, werd de measure vervolgens aangepast tot de volgende DAX expressie:

=AVERAGEX(
    Calendar;
    CALCULATE (
        SUM (Sales_SalesOrderDetail[LineTotal]);
        DATESBETWEEN (
            Calendar[Date];
            EARLIER (Calendar[FirstDate]);
            EARLIER(Calendar[Date]
        )
    ) / 10
)

Het idee achter deze berekening is dat je met AVERAGEX over de datums in de huidige filtercontext van de Calendar tabel itereert (bijv. alle dagen in een maand), voor iedere rij het gemiddelde over 10 werkdagen bepaald en dat weer middelt. Eerlijk gezegd had ik nooit eerder nagedacht over het al dan niet noodzakelijk zijn van de EARLIER functie; in PowerPivot versie 1 deed de formule het (al twijfel ik ondertussen wel aan de juiste uitkomst). Maar in PowerPivot versie 2 geeft dezelfde formule de volgende fout:

Eigenlijk wel terecht: EARLIER geeft data uit de vorige rijcontext terug. Maar er is helemaal geen rijcontext vóór de context die door AVERAGEX wordt geïnitieerd. Waarom deze DAX expressie in PowerPivot versie 1 geen fout gaf, is eigenlijk raar. De oplossing is heel eenvoudig. Gewoon de EARLIER functies weghalen…:

=AVERAGEX(
    Calendar;
    CALCULATE (
        SUM (Sales_SalesOrderDetail[LineTotal]);
        DATESBETWEEN (
            Calendar[Date];
            Calendar[FirstDate];
            Calendar[Date]
        )
    ) / 10
)

Immers, voor iedere rij in de filter context op de Calendar tabel wordt de berekening gedaan die als eerste argument aan de CALCULATE functie wordt meegegeven. Die berekening wordt gedaan voor de dan geldende rij in de Calendar tabel en daarvoor kun je, zonder gebruik van de EARLIER functie, de Date en FirstDate kolommen raadplegen. De CALCULATE functie vertaald daarmee de rijcontext op de Calendar tabel in een filtercontext op de Sales_SalesOrderDetail om de som van LineTotal over 10 werkdagen te bepalen. Die wordt, per rij in de Calendar tabel, gedeeld door 10 en het totaal van alle rijen in de oorspronkelijke filtercontext op de Calendar tabel wordt gemiddeld. Precies wat we hebben wilden.

De tweede verrassing waar ik tijdens de bewuste training tegenaan liep, was in de volgende calculated column:

=CALCULATE (
    VALUES(Fixing[Date]);
    FILTER (
        Fixing;
        Fixing[DayNumber] = EARLIER (Fixing[DayNumber]) – 50
&& Fixing[Stock] = EARLIER (Fixing[Stock])
    )
)

Deze expressie wordt gebruikt om voor iedere Date in de tabel Fixing de datum 50 dagen ervoor te bepalen. Het idee is dat de conditie in de FILTER functie altijd één (of geen) datum terug geeft. En dat daarmee de VALUES functie één waarde teruggeeft. Voor de CALCULATE functie is dat ook een vereiste; de functie moet immers één waarde terug geven. En hoewel de VALUES functie in het bewuste voorbeeld écht maar één waarde terug geeft (getest!), geeft bovenstaande expressie in PowerPivot versie 2 de volgende vage foutmelding:

Mijn (onbevestigde) vermoeden is dat DAX in PowerPivot versie 2 iets strikter is geworden. De VALUES functie kan immers meerdere waarden teruggeven, ook al was dit in dit geval niet het geval. Vandaar dat de oplossing in dit geval is een scalaire functie te gebruiken, zoals MIN of MAX:

=CALCULATE (
     MIN(Fixing[Date]);
     FILTER (
         Fixing;
         Fixing[DayNumber] = EARLIER (Fixing[DayNumber]) – 50
&& Fixing[Stock] = EARLIER (Fixing[Stock])
     )
)

Omdat we zeker weten dat er altijd maar één datum is die aan de conditie in de FILTER functie voldoet, kunnen we MIN of MAX gebruiken als alternatief om die ene datum terug te krijgen. Probleem opgelost.

Het mag duidelijk zijn: bij het upgraden van PowerPivot werkboeken die je hebt gemaakt met PowerPivot versie 1 naar versie 2 en waarin je wat ‘geknutseld’ hebt met DAX, kun je voor verrassingen komen te staan. Verrassingen die je even aan het denken kunnen zetten. Al is de oplossing vaak relatief eenvoudig.