Calculate – na podstawie opowieści o generale i żołnierzach

Pracując z językiem DAX w  Power BI Desktop lub innych programach powinniśmy znać i przede wszystkim rozumieć jak działa funkcja Calculate. Można by powiedzieć, że jest to bardzo prosta funkcja ponieważ oczekuje od nas tylko jednego argumentu, którym musi być inna miara lub zagregowana wartość (chociażby za pomocą zagnieżdżenia innej funkcji). Pozostałe argumenty filtra są opcjonalne (informuje nas o tym nawias kwadratowy). Ale to właśnie te argumenty filtra stanowią całą moc obliczeniową funkcji Calculate ponieważ za ich pomocą możemy nadawać nowe konteksty wyliczeń, usuwać częściowo lub całkowicie te konteksty oraz wykonywać szereg innych wyliczeń w Power BI. Ale tą myśl rozwiniemy troszeczkę później.

Aby Wam przedstawić i zaprezentować możliwości Calculate chciałbym przytoczyć pewną opowieść o generale i żołnierzach, która wydarzyła się w pewnym królestwie.

Był sobie bardzo władczy generał, który chciał rządzić królestwem, mieć decydujące zdanie w każdym sporze, decydować kiedy i w jaki sposób będą wykonywane wszystkie prace.

Niestety sam nie miał wystarczającej mocy aby to osiągnąć więc postanowił powołać do pomocy swoich oddanych żołnierzy. Były ich setki – każdy z nich zawsze chętny do pomocy i wykonania polecenia, które otrzyma od generała. Z ich pomocą władza generała w królestwie była niepodważalna. Był prawdziwym przywódcą i miał decydujące zdanie w każdym konflikcie, który zaistniał. Potrafił zawsze narzucić swoje pomysły i rozwiązania. Jeżeli była taka potrzeba to potrafił także zignorować całkowicie lub częściowo głos innych w królestwie. Z pomocą swoich żołnierzy potrafił odbudować mosty i udrożnić nieprzejezdne drogi. Generał dbał o właściwe funkcjonowanie królestwa z pomocą żołnierzy.

Zastanawiacie się pewnie co ta opowieść ma wspólnego z Calculate i Power BI?

Na bardzo abstrakcyjnym poziomie zrozumienia tematu generała można porównać do funkcji Calculate – która to także ma przywódcze cechy. Żołnierze z opowieści to argumenty filtra funkcji Calculate, które zmieniają zasady obliczeń w DAX, natomiast królestwo to Wasz model danych, w którym rządzi Calculate.

Tak więc jak można wywnioskować z tego wstępu za pomocą funkcji Calculate jesteśmy w stanie igrać z kontekstami w Power BI poprzez ich nadawanie, usuwanie czy szeroko pojęte modyfikacje.

Calculate należy dobrze zrozumieć ponieważ właściwie użyta w odpowiednich miejscach ma wielką moc, natomiast użyta w nieodpowiedni sposób w pewnych przypadkach może nam znacznie skomplikować „raportowe życie”. Zrozumienie prostoty tej funkcji i zarazem jej poziomu skomplikowania jest kluczowym elementem do świadomego korzystania z języka DAX.

Przejdźmy zatem do poważnych rozważań o Calculate.

Poniżej składnia funkcji:

<wyrażenie> jest to miejsce gdzie podstawiamy wyrażenie, które będzie obliczone na podstawie nowego kontekstu. Kontekst ten zostanie nadany przez kolejne argumenty filtra.

Warto wiedzieć, że Calculate nie współpracuje z kolumnami – jako wyrażenie podstawiamy funkcję agregującą jak np. SUM(), COUNT() lub inną miarę.

<filtr1>,<filtr2>,,,,, – to opcjonalne argumenty, które zwracają nowe tabele w silniku odpowiedzialnym za działanie Power BI. Za ich pomocą zmieniamy konteksty i Calculate zaczyna działać trochę inaczej.

Można tu przytoczyć fragment naszej opowieści gdzie generał (Calculate) z pomocą swoich żołnierzy (filtry) potrafił narzucać swoje zdanie.

Pierwszym elementem na którym się skupimy będzie omówienie co i kiedy w raporcie jest ważniejsze. Stworzyłem kilka prostych miar, które mają na celu zobrazowanie tej sytuacji.

Miara $ Zysk to proste sumowanie kolumny. (Na obrazku poniżej zaznaczone kolorem zielonym)

$ Zysk =
SUM ( FactInternetSales[Zysk] )

Miara $ Zysk v1 wylicza także po prostu zyski. Użyłem do tego funkcji Calculate chociaż nie było to konieczne. Przykład ten obrazuje, że zastosowanie Calculate bez filtrów nie wnosi za wiele do naszych obliczeń a więc wyniki są identyczne jak przy prostym sumowaniu kolumny – to jej filtry są jej żołnierzami.

*  Takie zastosowanie Calculate też ma swoje uzasadnienie w określonych przypadkach. Gdy potrzebujesz wywołać przejście kontekstu a filtr nie propaguje się przez relację można skorzystać z tej funkcjonalności. Jest to temat zdecydowanie wykraczający poza ramy tego artykułu więc jeżeli jesteś zainteresowany tym zagadnieniem doczytaj w dedykowanych artykułach.

$ Zysk v1 =
CALCULATE ( SUM ( FactInternetSales[Zysk] ) )

Gdy potrzebuje już narzucić w obliczeniu pewne warunki to w filtrze Calculate „zaszyje w kodzie” odwołanie do kolumny lub użyje innej funkcji jako tzw. modyfikatora.

Miara $ Zysk Czerwony obrazuje te założenie. W filtrze odwołałem się tylko do tabeli wymiarów, z której zwracam produkty w kolorze czerwionym. W tym momencie Calculate zwróci wyniki zysków ale tylko dla produktów spełniających podane kryteria.

(Na obrazku poniżej zaznaczone kolorem czerwonym)

$ Zysk Czerwony =
CALCULATE ( [$ Zysk], DimProdukt[Kolor] = „Czerwony” )

W tym momencie dochodzimy do pierwszego bardzo ważnego punktu zrozumienia Calculate. Zwróć uwagę, że zarówno w kodzie odwołaliśmy się do kolumny Kolor z tabeli DimProduct oraz użyliśmy tej samej kolumny do zbudowania kontekstu na macierzy w raporcie. Nastąpił zatem konflikt na raporcie ponieważ w tym momencie co jest ważniejsze?

Za przykład weźmy wiersz tabeli z kolorem Niebieskim. W tym kontekście oczekujemy wyniku zysku dla produktów w kolorze niebieskim czyli 889 656 zł, natomiast wyniki miary $ Zysk Czerwony przypisał sumę zysków dla koloru czerwonego. Ten przykład dobrze obrazuje, że w momencie wykorzystania tych samych kolumn na raporcie i w kodzie Calculate zawsze narzuci „swoje zdanie” i będzie to ważniejsze – podobnie jak generał z pomocą żołnierzy z naszej opowieści.

Funkcja Calculate będzie miała decydujące zdanie przy rozstrzyganiu sporów gdy użyjemy tej samej kolumny. Zrozumienie tego aspektu jest kluczowe do dalszych bardziej zaawansowanych analiz w Power BI. To wiele wyjaśnia np. przy wykorzystaniu funkcji Time Intelligence gdy będziemy się odwoływać do kolumny z datami na raporcie budując kontekst roku i miesiąca w tabeli oraz wykorzystując tą samą kolumnę z datami w argumencie filtra w Calculate. Wtedy Calculate narzuci swój kontekst i pokaże wyniki, które zaszyjecie w jej kodzie.

Nie będzie problemu jeżeli użyjecie innej kolumny jak np. Nazwy Kategorii jak na poniższym obrazku. Wtedy zostaną uwzględnione wszystkie filtry – czyli ten wewnętrzny Calculate z kolorem czerwonym i zewnętrzny z nazwą produktu.

Kolejnym przykładem nadania kontekstu będzie wspomniany przykład z wykorzystaniem analizy czasowej. Na poziomie 3 kwartału danego roku miara $ Zysk PY zwraca analogiczny okres poprzedniego roku (odpowiedzialna za to jest funkcja SAMEPERIODLASTYEAR()).

Analizując ten przypadek możemy wytłumaczyć to w ten sam sposób. W Calculate i na raporcie wykorzystaliśmy kolumnę z datami więc Calculate narzuca swoje zdanie i wyniki pokazują kontekst z funkcji.

$ Zysk PY =
CALCULATE ( [$ Zysk], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

Na tym etapie warto mieć świadomość, że w Calculate można użyć wielu filtrów, które są od siebie niezależne. Oznacza to, że każdy filtr jest wykonywany osobno a jego wyniki są przekazane jako tabele dla Calculate. Nastepnie Calculate mając już przefiltrowane wyniki na tych danych oblicza swoje pierwsze wyrażenie czyli podstawioną miarę lub zagnieżdżoną funkcję. Można tu odnaleźć dużo podobieństwa do naszej opowieści gdzie każdy żołnierz wykonał powierzone mu zadanie i wtedy generał bez trudu mógł podjąć decyzję.

Poniżej przykładowy kod wykorzystania kilku filtrów w Calculate.

Miara #Liczba transakcji v1 oblicza ile było transakcji w kategorii Rowery dla produktów w kolorze czerwonym lub czarnym ale tylko wybranych krajach.

Zwróć uwagę, że w kodzie zostały użyte operator logiczny ||  oraz IN, które są alternatywą dla użycia funkcji OR.

# Liczba transakcji v1 =
CALCULATE (
    COUNTROWS ( FactInternetSales ),
    DimKategoria[Nazwa Kategorii] = „Rowery”,
    DimProdukt[Kolor] = „Czerwony”
        || DimProdukt[Kolor] = „Czarny”,
    ’DimObszarSprzedaży'[Kraj] IN { „Francja”, „Niemcy”, „Wielka Brytania” }
)

Nadszedł czas na omówienie jak za pomocą Calculate zignorować kontekst w Power BI czyli innymi słowy mówiąc jak zrobić obliczenia na wszystkich wierszach tabeli. Aby napisać takie obliczenie przyda nam się wiedza dotycząca rodziny funkcji ALL. Są to funkcje filtra, które różnią się działaniem w zależności od kontekstu użycia.

Chcąc zobrazować ten przykład poniżej miara ALL test pokazująca, że użycie tej funkcji jako filtru Calculate zwróci wszystkie wiersze tabeli niezależnie od istniejącego kontekstu (w tym przypadku nazwy krajów na tabeli) oraz miara % Zysk udział sprzedaży, która wykonuje proste dzielenie przez właśnie zablokowany kontekst na tabeli DimObszarSprzedaży czyli zwraca wszystkie wiersze tabeli niezależnie od istniejącego kontekstu.

ALL test =
CALCULATE ( [$ Zysk], ALL ( 'DimObszarSprzedaży’ ) )

% Zysk udział sprzedaży =
DIVIDE ( [$ Zysk], CALCULATE ( [$ Zysk], ALL ( 'DimObszarSprzedaży’ ) ) )

Jak można zauważyć bez właściwego użycia Calculate wraz z jej filtrami trudno byłoby tworzyć zaawansowane miary w Power BI. W tym miejscu ponownie można odwołać się do generała, który jeżeli potrzebował to z pomocą swoich żołnierzy mógł ignorować inne zdanie i narzucić swoje w zależności od potrzeby.

Ostatnim przykładem użycia Calculate będzie wyjaśnienie jak do tego wszystkiego o czym rozmawiamy ma się odbudowa mostów i udrożnianie nieprzejezdne dróg przez wojsko generała w stosunku do naszego modelu danych. Otóż Calculate z użyciem odpowiednich funkcji użytych jako jej filtrów potrafi także uruchomić nieaktywną relację (udrożnić przejście dla obliczenia) oraz pozwolić wykonać obliczenie w kierunku niezgodnym z modelem.

Poniżej wycinek modelu danych z trzema relacjami między tabelą DimDate i FactInternetSales. W modelu danych dozwolona jest tylko jedna aktywna relacja oraz warto aby zachować jednostronny kierunek filtrowania krzyżowego.

Więcej na temat DAX możesz dowiedzieć się na naszych kursach

W jaki sposób możemy uruchomić inną relację gdy dla przykładu potrzebujemy na jednym wykresie pokazać ile transakcji zostało złożonych w danym okresie a ile zostało wysłanych.

Z pomocą ponownie przyjdzie nam Calculate, która powoła jako swojego żołnierza funkcję USERELATIONSHIP(). Funkcja ta jest odpowiedzialna za aktywowanie relacji jednak najważniejsze w tym momencie jest to, że kolejny raz zmieniamy kontekst wykonania za pomocą Calculate.

Poniżej przykład

# Liczba transakcji data dostawy =
CALCULATE (
    [# Liczba transkacji],
    USERELATIONSHIP ( DimDate[Date], FactInternetSales[Data dostawy] )
)

Gdybyśmy potrzebowali obliczyć ilu klientów dokonało u nas zakupów w danym okresie czasu to nie mamy „udrożnionej drogi” między tabelą faktów a tabelą klientów – dane przepływają w kierunku od 1:* a nie odwrotnie.

I po raz kolejny możemy zmienić założenie za pomocą Calculate wraz z mała pomocą funkcji CROSSFILTER.

Przy użyciu pierwszej miary # Liczba klientów v1 nie zdołamy tego zrobić ale gdy użyjemy bohaterki tego artykułu obliczenie rzeczywiście będzie poprawnie wykonane.

# Liczba klientów v1 =
COUNTROWS ( DimKlient )

# Liczba klientów v2 =
CALCULATE (
    COUNTROWS ( DimKlient ),
    CROSSFILTER ( DimKlient[Klucz klienta], FactInternetSales[Klucz klienta], BOTH )
)

Podsumowując z „dużym przymrużeniem oka” można powiedzieć, że istnieje duże prawdopodobieństwo, że inżynierowie Micorsoft-u także znają opowieść o generale i żołnierzu ponieważ projektując Power BI mogli się wzorować na tym😊

A tak poważnie to warto zapamiętać najważniejsze elementy opisane w tym artykule. Calculate zawsze nadpisze wyniki jeżeli odwołacie się do tych samych kolumn na raporcie i kodzie. Jest to kluczowe do zrozumienia i świadomego korzystania z DAX.

Za pomocą tej funkcji usunięcie całościowo lub selektywnie konteksty, uruchomicie relację i kierunki filtrów w modelu.

Jest to najważniejsza funkcja w DAX więc warto poświęcić jej trochę czasu aby dobrze rozumieć co się dzieje tak naprawdę w Waszych obliczeniach.