Funkcja EARLIER w DAX – kilka słów o zagnieżdżaniu kontekstów

DAX posiada kilka koncepcji, które są nieco trudniejsze w zrozumieniu – jedną z nich bez wątpienia jest funkcja EARLIER i jej pochodna tj. EARLIEST, którą chciałbym omówić w ramach dzisiejszego artykułu – zapraszam do lektury.

Iteratory w modelach tabelarycznych to znany i lubiany (chyba) koncept. Wszyscy wiemy, że przykładem typowego iteratora są funkcję z sufiksem “X” jak np. SUMX, która wykonuje pewne działanie wiersz po wierszu (np. iloczyn ilość * cena), a następnie sumuje otrzymane rezultaty. To na co rzadziej zwraca się uwagę to fakt, że iteratory tworzą nowy kontekst wiersza w tabeli wskazanej jako pierwszy parametr funkcji iterującej.  W momencie gdy taki iterator umieścimy np. w kolumnie kalkulowanej to  powstaje coś na kształt zagnieżdżonych kontekstów wiersza – przyjrzyjmy się temu nieco bliżej. Za przykład posłuży scenariusz w którym istnieje potrzeba stworzenia rankingu produktów pod względem wysokości ich ceny tj. im droższy produkt tym wyżej jest w rankingu.W pierwszym kroku stworzona zostanie formuła w kolumnie kalkulowanej, która filtruje produkty w taki sposób aby pozostały tylko te, których cena jest mniejsza niż podana wartość– aby to osiągnąć użyjemy funkcji FILTER po to aby później policzyć wiersze spełniające warunek funkcją COUNTROWS.

=
COUNTROWS ( FILTER ( 'DimProduct', 'DimProduct'[DealerPrice] > 10 ) )

W każdej z kolumn pojawi się ta sama liczba odzwierciedlająca liczbę produktów z ceną większą niż 10. Pożądanym efektem jest to aby zamiast 10 podstawić cenę dla bieżącego wiersza – dzięki temu policzona zostanie liczba produktów która ma większą cenę czyli nic innego jak miejsce bieżącego rekordu w rankingu cen bo jeżeli 5 produktów ma wyższą cenę niż bieżący produkt oznacza to, iż zajmuje on 6 miejsce w rankingu. FILTER sam w sobie możemy traktować jako iterator i wpisując w jego warunku zamiast 10 frazę

'DimProduct'[DealerPrice]

co daje nam dosyć kuriozalny efekt:

=
COUNTROWS ( FILTER ( 'DimProduct', 'DimProduct'[DealerPrice] > 'DimProduct'[DealerPrice] ) )

w powyższym przykładzie używając zapisu DimProduct[DealerPrice] nie odnosimy się do kontekstu bieżącego wiersza kolumny kalkulowanej tylko kontekstu funkcji FILTER, która jest aktualnie zagnieżdżona w kontekście bieżącego wiersza! Jak więc zatem dostać się do kontekstu kolumny kalkulowanej – pomijając kontekst narzucony przez FILTER? DAX udostępnia metodę na dostanie się do zewnętrznego kontekstu z poziomu zagnieżdżonego, a jest nią funkcja EARLIER. Dlatego też poniższy zapis porównuje bieżącą cenę jaką „widzi” FILTER z aktualnie przetwarzanym wierszem kolumny kalkulowanej.

=
COUNTROWS (
    FILTER (
        'DimProduct',
        'DimProduct'[DealerPrice] > EARLIER ( 'DimProduct'[DealerPrice] )
    )
)

Jak widać kilka produktów ma najwyższą cenę 2146 dolarów i dla nich w nowopowstałej kolumnie RANK nie wyświetla się żadna liczba – dzieje się tak dlatego, że żaden wiersz nie ma większej ceny dlatego FILTER zwraca BLANK. Dla większej przejrzystości do rezultatu można dodać 1

=
COUNTROWS (
    FILTER (
        'DimProduct',
        'DimProduct'[DealerPrice] > EARLIER ( 'DimProduct'[DealerPrice] )
    )
)+1

Taki wynik może już być zadowalający – co jednak gdy potrzeba stworzyć taki ranking, który produkty które mają np. drugie miejsce miały obliczony ranking 2 zamiast 6? Wystarczy spojrzeć na miarę i pomyśleć skąd wzięła się 6 – odpowiedź jest bardzo prosta ponieważ 5 produktów ma wyższą cenę. Obserwując rezultat można dojść do wniosku, że wszystkie 5 cen to tak naprawdę ta sama cena, to może w jakikolwiek sposób można by usunąć duplikaty z liczenia? Oczywiście, że tak! Do dyspozycji pozostają funkcję ALL, DISTINCT albo VALUES, które są funkcjami tablicowymi zwracającymi unikalną listę wartości z kolumny przekazanej jako parametr:

=
COUNTROWS (
    FILTER (
        VALUES('DimProduct'[DealerPrice]),
        'DimProduct'[DealerPrice] > EARLIER ( 'DimProduct'[DealerPrice] )
    )
)+1

Teraz wygląda to już naprawdę dobrze prawda? Konstrukcja EARLIER nie jest zbyt intuicyjna ale po jej zrozumieniu może być przydatna. Pamiętajmy o niej w momencie gdy używamy zagnieżdżonych kontekstów wykonania. Jeżeli mimo wszystko nie przypadła Wam ona do gustu to w najnowszych wersjach silnika tabelarycznego (Power BI i SSAS) podobny efekt można uzyskać używając zmiennych:

=
VAR CurrentDealerPrice = 'DimProduct'[DealerPrice]
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'DimProduct'[DealerPrice] ),
            'DimProduct'[DealerPrice] > CurrentDealerPrice
        )
    )
        + 1

Bieżąca wartość wiersza jest przechowywana w zmiennej i nie potrzeba odwoływać się do kontekstu zewnętrznego przy użyciu EARLIER – osobiście to podejście najbardziej mi się spodobało i staram się jego właśnie używać gdy zachodzi taka potrzeba.

Omawiana funkcja posiada również drugi opcjonalny parametr, który mówi o ile kontekstów należy się cofnąć, podobną funkcją jest EARLIEST, która cofa się do najbardziej zewnętrznego kontekstu jednakże w praktyce bardzo rzadko spotyka się zagnieżdżenie więcej niż dwóch kontekstów wiersza. Samo rozwiązanie problemu rankingu zapożyczyłem od Alberto Ferrariego, który obok Marco Russo jest niebywałym autorytetem w dziedzinie modeli tabelarycznych – tak więc jeśli ktoś chce dowiedzieć się czegoś więcej na te tematy zapraszam na ich stronę sqlbi.com – ponadto w najbliższym czasie na seequality pojawi się kilka dodatkowych opisów funkcjonalności DAX, które mam nadzieję okażą się dla was przydatne.