DAX – wstęp do funkcji CALCULATE

Funkcja CALCULATE jest jedną z najważniejszych funkcji dostępnych w języku DAX ponieważ jako jedyna daje nam możliwość zmiany kontekstu filtra. Jak jej używać i dostosowywać miary do własnych potrzeb? Postaram się Wam to przedstawić w ramach niniejszego artykułu.

CALCULATE działa w taki sposób, że tworzy nowy kontekst filtra i następnie przetwarza w nim wyrażenia języka DAX. Składnia omawianej funkcji została przedstawiona poniżej:

Miara:=
CALCULATE( wyrażenie, warunek 1 … warunek N)

Funkcja akceptuje dowolną liczbę parametrów z czego obowiązkowy jest tylko pierwszy z nich odnoszący się do wyrażenia DAX jakie należy wykonać (wyrażenie to może być dowolną instrukcją języka DAX, która zwraca skalarną wartość). Wszystkie pozostałe następujące po pierwszym parametrze wartości są niczym innym jak parametrami filtrującymi. Jako filtr możemy podać zarówno wyrażenie logiczne np. Miasto=”Katowice” jak i zestaw wartości pojedynczej lub wielu kolumn np. zwracanych przez funkcję tablicową.

Schemat działania CALCULATE wygląda następująco:na samym początku odczytywany jest bieżący kontekst filtra i tworzony jest nowy na jego podstawie. Potem następuje tworzenie dla każdego warunku listę aktywnych wartości dla wskazanej kolumny, w przypadku gdy wiele filtrów odnosi się do jednej kolumny to są one łączone operatorem AND, jeżeli kolumna ma na sobie już filtr to jest on zastępowany nowym filtrem, w przypadku braku filtra na tej kolumnie zakładany jest nowy. Może to się wydawać nieco skomplikowane jednakże w rzeczywistości jest to dosyć proste zachowanie szczególnie gdy stworzymy sobie kilka własnych miar wykorzystujących modyfikujących kontekst.

Jak już wspomniałem, jako parametr do CALCULATE może być wstawione np. wyrażenie będące warunkiem logicznym – tak jak zostało to przedstawione poniżej. Miara ta oblicza sumę sprzedanych sztuk dla kategorii produktowej zawierającej rowery:

OrderQtyForBikes :=
CALCULATE (
    SUM ( [OrderQuantity] ),
    'ProductCategory'[EnglishProductCategoryName] = "Bikes"
)

Poniższy zrzut ekranowy przedstawia rezultat działania naszej miary dla każdej z kategorii – dla porównania przedstawiono również miarę obliczającą standardową sumę. Jak widać wartość OrderQtyForBikes jest dla każdego wiersza taka sama – dlaczego? Zastanówmy się jakie w tej sytuacji mamy konteksty:

Dla komórka B2 istnieje kontekst ProductCategory równy Accessories (ze względu na to, że inne atrybuty naszego modelu nie występują na raporcie to nie znaczy, że nie występują w kontekście filtra – po prostu w filtrze występują wszystkie wartości tych atrybutów) – po wyznaczeniu tego kontekstu obliczona została miara. W podobny sposób obliczone zostały komórki B3, B4, B5, B6. Z kolei komórka B7 zawierająca podsumowanie nie jest sumą powyższych wartości co mogłoby wydawać się intuicyjne, lecz posiada ona po prostu inny kontekst wykonania – w tym przypadku są nim wszystkie wartości ze wszystkich kolumn w tym również ze wszystkich kategorii produktów. Używając CALCULATE nadpisaliśmy kontekst dla każdej komórki – zgodnie z algorytmem opisanym wyżej, podczas obliczenia VertiPaq (silnik tabelaryczny w Power Pivot, Power BI, Tabular) odczytał bieżący kontekst, następnie wyznaczył, że dla kolumny ProductCategories został on nadpisany poprzez warunek logiczny i podmienił oryginalny kontekst tym, który jawnie wskazaliśmy w CALCULATE. Rezultatem tych działań jest ta sama liczba dla wszystkich wartości kategorii produktów. Co natomiast by się stało gdybyśmy dorzucili do tabeli przestawnej atrybut z rokiem kalendarzowym? (wyrzucając standardową miarę SumOf OrderQuantity dla czytelności):

Nadpisany został jedynie kontekst dla kategorii – dla roku pozostaje on nietknięty- dlatego, że podczas tworzenia kontekstu VertiPaq kopiuje go do nowopowstałego kontekstu, a następnie nadpisuje kontekst dla kategorii produktu. Tak też dla komórek B3:B8 widzimy sprzedaż sztuk produktów należących do kategorii produktowej Bikes w roku 2005 i tak dalej – całkiem fajne rozwiązanie prawda? Szczególnie, że to dopiero początek możliwości CALULATE.

Tak jak wspomniałem CALCULATE pozwala na podanie wielu warunków logicznych np. poniższy zapis obliczy sumę OrderQuantity dla czerwonych rowerów

OrderQtyForRedBikes :=
CALCULATE (
    SUM ( [OrderQuantity] ),
    'ProductCategory'[EnglishProductCategoryName] = "Bikes",
    'Product'[Color] = "Red"
)

Warunki podane po przecinku przez miarę są traktowane jako koniunkcja czyli jako logiczny AND. Oprócz jawnego wskazania warunku logicznego możemy również podać listę wartości która ma nadpisać kontekst, w przypadku podania funkcji tablicowej wewnętrznie będzie ona przekształcona na listę wartości:

SumOfOrderQuantityAfterMay2008 :=
CALCULATE (
    SUM ( [OrderQuantity] ),
     FILTER ( 'InternetSales', InternetSales[DueDate] >DATE(2008,5,1)) 
)

To co zwraca funkcja FILTER możemy przetestować np. w DAX Studio używając słowa kluczowego EVALUATE:

Tak więc obliczamy wartość naszej miary tylko dla rekordów których DueDate jest większa niż 1 maja 2008. Zapis tego typu oznacza, iż obliczamy naszą miarę dla tych rekordów, które spełniają nasz warunek filtrujący. Dla rekordów, które nie spełniają warunku logicznego podstawiana jest wartość pusta BLANK() tak jak można to zauważyć na poniższym zrzucie ekranowym.

Jeżeli chcemy innego zachowania naszej miary tzn. chcemy aby całkowicie ignorowany był kontekst na konkretnej kolumnie lub tabeli i obliczony została liczba sprzedanych sztuk produktu po 1 maja 2008 (analogicznie jak w przypadku podania warunku w CALCULATE pokazanym wcześniej) możemy użyć słowa kluczowego ALL, które całkowicie ignoruje kontekst w podanej tabeli/kolumnie – tak więc poniższa miara

SumOfOrderQuantityGreaterThan1 :=
CALCULATE (
    SUM ( [OrderQuantity] ),
    FILTER ( ALL ( 'InternetSales' ), InternetSales[DueDate] > DATE ( 2008, 5, 1 ) )
)

daje taki efekt:

Bardzo podobny rezultat jak poprzednio przy mierze OrderQtyForBikes prawda? Dzieje się tak dlatego, że zapis z warunkiem logicznym jest wewnętrznie tłumaczony na zapis z FILTER tzn. zapis:

OrderQtyForBikes :=
CALCULATE (
    SUM ( [OrderQuantity] ),
    'ProductCategory'[EnglishProductCategoryName] = "Bikes"
)

jest tożsamy z następującym zapisem:

OrderQtyForBikes :=
CALCULATE (
    SUM ( [OrderQuantity] ),
    FILTER (
        ALL('ProductCategory'[EnglishProductCategoryName]),
        'ProductCategory'[EnglishProductCategoryName] = "Bikes"
    )
)

W takim razie dlaczego nie możemy zawsze używać krótszego zapisu bez FILTER? Dlatego, że czasem po prostu nie jest to możliwe – VertiPaq musi mieć pewność kontekst której kolumny ma nadpisać, dlatego też zapis logiczny z więcej niż jedną kolumną nie zadziała:

test:=CALCULATE(SUM([SalesAmount]),
'InternetSales'[ProductStandardCost]<'InternetSales'[Margin])

Ale już zapis z FILTER zadziała:

test :=
CALCULATE (
    SUM ( [SalesAmount] ),
    FILTER (
        'InternetSales',
        'InternetSales'[ProductStandardCost] < 'InternetSales'[Margin]
    )
)

To o czym jeszcze nie powiedziałem, a co jest dosyć istotne to fakt, że w DAX możemy zagnieżdżać funkcje. Aby to zobrazować pokażemy zagnieżdżenie CALCULATE, a następnie wprowadzimy sobie funkcję CALCULATETABLE, która działa dokładnie tak samo jak CALCULATE przy czym zamiast wartości skalarnej zwraca tabelę.

Na początku zastanówmy się co zwróci poniższa miara:

OrderQtyForRedOrBlackBikes:=CALCULATE (
    CALCULATE(SUM ( [OrderQuantity] ),
     'Product'[Color] = "Black" ),
    'Product'[Color] = "Red"
)

Z logicznego punktu widzenia mamy trzy możliwości tj.

• zwrócona będzie suma OrderQuantity dla produktów o kolorze czarnym
• zwrócona będzie suma OrderQuantity dla produktów o kolorze czerwonym
• zwrócona będzie suma OrderQuantity dla produktów o kolorze czarnym i dla produktów o kolorze czerwonym
• zwrócona zostanie pusta wartość bo żaden produkt nie może być w tym samym czasie czerwony i czarny

Prawidłową odpowiedzią jest pierwsza z podanych możliwości – ponieważ w przypadku zagnieżdżania CALCULATE najpierw ewaluowane są warunki zewnętrzne, a dopiero potem warunki wewnętrzne.

Podobny zachowanie możemy zaobserwować przy użyciu CALCULATETABLE – gdzie zwrócony zostanie tylko kolor podany jako warunek wewnętrznej funkcji:

 EVALUATE
 DISTINCT (
     SELECTCOLUMNS (
         CALCULATETABLE (
             CALCULATETABLE ( Product, 'Product'[Color] = "Red" ),
             'Product'[Color] = "Black"
         ),
         "Color", 'Product'[Color]
     )
 )

Oczywiście zarówno CALCULATE jak i CALCULATETABLE wiąże się z całą masą innych właściwości języka DAX o których z pewnością poświęcę jeszcze niejeden artykuł. Jeżeli ktoś chce poznać DAX musi poznać funkcję CALCULATE, a najlepszym sposobem aby ją zrozumieć jest po prostu eksperymentowanie do czego serdecznie zachęcam.