Kurs Power BI: DAX CALCULATE i kontekst filtrów

W skrócieCALCULATE to funkcja DAX, która zmienia kontekst filtra obliczenia: dodaje, nadpisuje albo usuwa filtry. Bez niej DAX kończy się na prostych sumach. Na danych Nordvelli liczymy udział procentowy z ALL i REMOVEFILTERS, poznajemy przejście kontekstu i budujemy ranking RANKX.

W rozdziale o podstawach DAX zbudowaliśmy pierwsze miary Nordvelli, na przykład Łączna sprzedaż, i zobaczyliśmy, że ta sama miara daje inny wynik w każdym wierszu macierzy. To zasługa kontekstu filtra, czyli zestawu warunków, które w danej komórce raportu ograniczają dane widziane przez miarę. Teraz sięgamy po funkcję, która ten kontekst potrafi świadomie zmienić. CALCULATE to najważniejsza funkcja całego języka DAX i (razem z bliźniaczą CALCULATETABLE) jedyna, która potrafi dodać, nadpisać lub usunąć filtr dla całego obliczenia w środku. Bez niej DAX kończy się na prostych sumach. Z nią liczysz udział kategorii w całości sprzedaży, sprzedaż bez zwrotów, ranking sklepów i porównania, których zwykła agregacja nigdy Ci nie da.

Anatomia CALCULATE: jak modyfikuje kontekst

Co chcemy osiągnąć. Zarząd Nordvelli zadaje pytania, na które zwykła suma nie odpowie: jaki procent sprzedaży robi kategoria, ile sprzedają produkty premium, który sklep jest na którym miejscu. Wszystkie te miary opierają się na funkcji CALCULATE, więc chcemy najpierw zrozumieć, jak zmienia ona kontekst filtra.

Zacznijmy od tego, co CALCULATE robi mechanicznie, bo wiele błędów w DAX bierze się z niezrozumienia tego jednego kroku. Funkcja przyjmuje jako pierwszy argument wyrażenie (zwykle gotową miarę), a po nim dowolną liczbę argumentów filtrujących. Zanim policzy wyrażenie, CALCULATE bierze kontekst filtra przyniesiony z zewnątrz (wiersz macierzy, wybór we fragmentatorze, filtr strony) i nakłada na niego wszystkie argumenty filtrujące. Dopiero na tak zmienionym kontekście oblicza wyrażenie.

Trzy rodzaje zmian, jakie może wprowadzić argument filtrujący, warto od razu nazwać po polsku, bo do nich będziemy wracać przez cały rozdział:

• Dodanie filtra, gdy kontekst zewnętrzny nie dotyka danej kolumny. Przykład: karta KPI bez fragmentatora, a Ty w CALCULATE zawężasz do jednej marki.
• Nadpisanie filtra, gdy kontekst już filtruje tę samą kolumnę. Argument CALCULATE wygrywa i zastępuje wybór użytkownika. Tak liczysz na przykład sprzedaż stałego roku odniesienia niezależnie od tego, co ktoś kliknął we fragmentatorze.
• Usunięcie filtra funkcjami ALL lub REMOVEFILTERS. To klucz do wszystkich udziałów procentowych, bo mianownik musi widzieć całość, a nie tylko bieżący wiersz.

Najprostsza forma CALCULATE ma jeden argument filtrujący zapisany jako warunek na kolumnie. Zbudujemy miarę, która liczy sprzedaż z pominięciem zwrotów. W tabeli faktów mamy kolumnę CzyZwrot (0 dla normalnej sprzedaży, 1 dla zwrotu).

Krok 1. Miary tej lekcji pokażemy na trzech nowych stronach raportu. Na pasku stron na dole kliknij zielony plus. Kliknij dwukrotnie zakładkę nowej strony, wpisz CALCULATE i naciśnij Enter.

Krok 2. Kliknij w panelu Dane prawym przyciskiem na tabelę FaktSprzedaz i wybierz Nowa miara.

Krok 3. Wpisz w pasku formuł poniższą formułę i zatwierdź ją klawiszem Enter albo znacznikiem po lewej stronie paska:

Sprzedaż bez zwrotów =
CALCULATE(
    [Łączna sprzedaż],
    FaktSprzedaz[CzyZwrot] = 0
)

Krok 4. Miara jest zaznaczona, a na wstążce widać kartę Narzędzia miar. Kliknij w pole Format, wpisz #,0 "zł" i naciśnij Enter, tak jak przy Łącznej sprzedaży w lekcji 07. Ten sam format ustaw każdej mierze kwotowej z tej lekcji.

Krok 5. Kliknij puste miejsce strony, w panelu Wizualizacje wybierz Karta i przeciągnij do obszaru Wartość obie miary: Łączna sprzedaż i Sprzedaż bez zwrotów. Zobaczysz, że Sprzedaż bez zwrotów jest niższa od Łączna sprzedaż, bo CALCULATE dołożył warunek, którego zwykła miara nie miała. To właśnie dodanie filtra. Zapamiętaj skrót myślowy: pisząc warunek na kolumnie jako argument CALCULATE, w rzeczywistości mówisz DAX-owi, żeby najpierw z całej tabeli wybrał tylko te wiersze, a potem policzył wyrażenie.

Udział procentowy: ALL, REMOVEFILTERS i DIVIDE

Co chcemy osiągnąć. Chcemy pokazać przy każdej kategorii produktów Nordvelli jej udział w sprzedaży całej sieci, a potem udział kategorii wewnątrz jej regionu.

Najczęstsze pytanie zarządu Nordvelli brzmi: która kategoria produktów odpowiada za jaką część naszej sprzedaży? Zwykła suma tego nie powie, bo w macierzy z podziałem na kategorie każdy wiersz widzi tylko swoją kategorię i nie zna całości. Potrzebujemy mianownika, który ignoruje filtr wiersza. To zadanie dla funkcji ALL, która zdejmuje filtry z tabeli lub kolumny, oraz jej czytelniejszego rodzeństwa REMOVEFILTERS.

Krok 1. Najpierw zbudujmy sam mianownik, żeby zobaczyć różnicę na własne oczy. Utwórz miarę pomocniczą (prawy przycisk na FaktSprzedaz, Nowa miara, format #,0 "zł"):

Sprzedaż wszystkich kategorii = CALCULATE([Łączna sprzedaż], ALL(DimProdukty))

Krok 2. Kliknij puste miejsce strony pod kartą i w panelu Wizualizacje wybierz Macierz. Do obszaru Wiersze przeciągnij DimProdukty[Kategoria], do obszaru Wartości Łączną sprzedaż i Sprzedaż wszystkich kategorii. Żeby liczby były czytelne, przejdź do Formatuj element wizualny, rozwiń Siatka, potem Opcje, i ustaw Globalny rozmiar czcionki na 14. Zobaczysz coś, co na pierwszy rzut oka dziwi: kolumna Łączna sprzedaż zmienia się wiersz po wierszu (każda kategoria ma swoją wartość), a kolumna Sprzedaż wszystkich kategorii pokazuje w każdym wierszu tę samą liczbę, sumę całości. Tak właśnie działa ALL: usuwa filtr kategorii narzucony przez wiersz, więc CALCULATE liczy na pełnej tabeli produktów.

Krok 3. Mając mianownik, udział jest już prosty. Najpierw dodaj miarę Sprzedaż całości, która zdejmuje wszystkie filtry naraz (czym różni się od ALL, wyjaśniamy za chwilę):

Sprzedaż całości =
CALCULATE([Łączna sprzedaż], REMOVEFILTERS())

Zobacz też: Szkolenie Power BI - Desktop, DAX i Online + Copilot

Krok 4. Teraz sam udział. Nie dziel wprost operatorem, tylko funkcją DIVIDE. Jej trzeci argument to wynik zastępczy: gdy mianownik jest pusty albo równy zero, miara zwróci 0 zamiast błędu:

Udział w sprzedaży % =
DIVIDE(
    [Łączna sprzedaż],
    [Sprzedaż całości],
    0
)

Krok 5. Ustaw tej mierze format procentowy z jednym miejscem po przecinku: w polu Format na karcie Narzędzia miar wpisz 0.0% i naciśnij Enter.

Krok 6. Przeciągnij Udział w sprzedaży % do obszaru Wartości macierzy. Teraz macierz mówi wprost, ile procent tortu bierze każda kategoria. Zwróć uwagę, że użyliśmy REMOVEFILTERS zamiast ALL: w roli modyfikatora CALCULATE robią dokładnie to samo, ale REMOVEFILTERS czyta się jednoznacznie jako usuń filtry, więc rekomendujemy go w nowych raportach. ALL zostaw tam, gdzie potrzebujesz z niego tabeli wierszy, na przykład w RANKX w dalszej sekcji.

Odczyt biznesowy jest natychmiastowy: AGD/RTV i Komputery razem to 69% obrotu sieci, więc dowolna decyzja o marżach czy promocjach w tych dwóch kategoriach waży najwięcej. Wiersz sumy pokazuje 100%, co jest dobrym testem poprawności: gdyby suma nie schodziła do 100%, znaczyłoby, że mianownik nie zdejmuje wszystkich potrzebnych filtrów.

ALLEXCEPT: udział wewnątrz grupy

Czasem nie chcesz udziału w całości, tylko wewnątrz jakiegoś podziału. Pytanie z Nordvelli: jaki udział ma każda kategoria wewnątrz swojego regionu? Wtedy mianownik ma zdjąć filtr kategorii, ale zachować filtr regionu. Zamiast wypisywać, co zostawić, użyj ALLEXCEPT, które usuwa filtry ze wszystkich kolumn tabeli poza wskazanymi:

Udział w regionie % =
DIVIDE(
    [Łączna sprzedaż],
    CALCULATE(
        [Łączna sprzedaż],
        ALLEXCEPT(FaktSprzedaz, DimSklepy[Region])
    ),
    0
)

W praktyce mianownik liczy sprzedaż całego regionu, a licznik sprzedaż bieżącej kategorii w tym regionie, więc gdy w wierszach macierzy ustawisz Region, a pod nim Kategorię jako drugi poziom, każda kategoria pokaże swój udział lokalnie. To ważne rozróżnienie: udział w całej sieci i udział wewnątrz regionu to dwie różne liczby dla tej samej kategorii, a wybór między nimi zależy wyłącznie od tego, co zdejmiesz z filtrów.

Krok 7. Nowej mierze ustaw format 0.0%. Kliknij puste miejsce strony po prawej i wstaw drugą macierz. Do obszaru Wiersze przeciągnij DimSklepy[Region], a pod nim DimProdukty[Kategoria]. Do Wartości przeciągnij Łączną sprzedaż, Udział w sprzedaży % i Udział w regionie %. Rozwiń region Centrum plusem przy jego nazwie.

FILTER: warunki, których zwykły argument nie udźwignie

Co chcemy osiągnąć. Chcemy policzyć sprzedaż produktów premium Nordvelli, czyli droższych niż 2000 zł, i inne miary z warunkami, których prosty argument CALCULATE nie wyrazi.

Krok 1. Dodaj kolejną stronę i nazwij ją FILTER i RANKX (zielony plus na pasku stron, potem dwuklik na zakładce). Miary z tej sekcji i z sekcji o rankingu zakładasz jak wcześniej: prawy przycisk na FaktSprzedaz, Nowa miara, format #,0 "zł".

Prosty warunek na jednej kolumnie zapisujesz wprost w CALCULATE. Gdy jednak warunek dotyczy wyniku agregacji albo łączy kilka kolumn w nietrywialny sposób, potrzebujesz funkcji FILTER. FILTER to iterator: przechodzi tabelę wiersz po wierszu i zwraca tylko te, które spełniają warunek, a tę zawężoną tabelę CALCULATE traktuje jak filtr. Policzmy sprzedaż produktów premium, czyli takich, których cena katalogowa netto przekracza 2000 zł:

Sprzedaż premium =
CALCULATE(
    [Łączna sprzedaż],
    FILTER(
        DimProdukty,
        DimProdukty[CenaNetto] > 2000
    )
)

Krok 2. Wstaw macierz: do obszaru Wiersze przeciągnij DimProdukty[Marka], do Wartości Łączną sprzedaż i Sprzedaż premium.

Ta miara sensownie zadziała w macierzy po marce, mieście czy miesiącu, bo FILTER dobiera zbiór produktów spełniających cenę, a reszta kontekstu (wybrany rok, sklep) dalej obowiązuje. Prawdziwa siła FILTER pokazuje się jednak przy warunkach na agregacie. Załóżmy, że zarząd chce sprzedaż tylko tych marek, które w bieżącym kontekście przekroczyły 100 mln złotych obrotu. Takiego warunku nie zapiszesz jako prostego wyrażenia na kolumnie, bo dotyczy on sumy, a nie pojedynczego wiersza:

Sprzedaż marek powyżej 100 mln =
CALCULATE(
    [Łączna sprzedaż],
    FILTER(
        VALUES(DimProdukty[Marka]),
        [Łączna sprzedaż] > 100000000
    )
)

Krok 3. Przeciągnij nową miarę do Wartości tej samej macierzy.

Tu dzieje się rzecz kluczowa: wewnątrz FILTER wywołujemy miarę Łączna sprzedaż dla każdej marki z osobna. To możliwe wyłącznie dzięki mechanizmowi, który opisujemy w następnej sekcji. Bez niego warunek liczyłby sumę wszystkich marek naraz i albo przepuszczał wszystkie, albo żadnej.

Przejście kontekstu (context transition)

Co chcemy osiągnąć. Gdy dopiszemy do tabeli sklepów Nordvelli kolumnę z sumą sprzedaży sklepu, naiwna formuła pokaże w każdym wierszu tę samą kwotę całej sieci. Chcemy rozumieć, dlaczego tak się dzieje i jak CALCULATE to naprawia, bo na tym mechanizmie opiera się ranking sklepów w następnej sekcji.

To pojęcie odróżnia osobę, która kopiuje formuły DAX, od osoby, która je rozumie. W DAX istnieją dwa rodzaje kontekstu. Kontekst filtra to zbiór warunków ograniczających dane (o nim mówiliśmy do tej pory). Kontekst wiersza to znajomość jednego, aktualnie przetwarzanego wiersza, jaki mają iteratory (FILTER, SUMX) oraz kolumny obliczeniowe. Problem w tym, że miara sama z siebie nie widzi kontekstu wiersza. Miara reaguje tylko na kontekst filtra.

Dokładnie tu wchodzi CALCULATE. Ma ono działanie, które łatwo przeoczyć: wywołane wewnątrz kontekstu wiersza wykonuje przejście kontekstu, czyli zamienia bieżący wiersz na równoważny kontekst filtra. Co więcej, każda miara jest wewnętrznie owinięta w niejawne CALCULATE, więc gdy piszesz w FILTER po prostu [Łączna sprzedaż], DAX robi z tego CALCULATE([Łączna sprzedaż]) i przejście kontekstu dzieje się automatycznie. Dlatego miara z poprzedniej sekcji liczyła sprzedaż osobno dla każdej marki, a nie hurtem.

Zobaczmy to na kolumnie obliczeniowej, bo tam różnica jest namacalna. Chcemy w tabeli DimSklepy dodać kolumnę z sumą sprzedaży danego sklepu.

Krok 1. Kliknij w panelu Dane prawym przyciskiem na tabelę DimSklepy i wybierz Nowa kolumna.

Krok 2. Naiwna wersja bez CALCULATE zawiedzie. Wpisz ją i zatwierdź, żeby zobaczyć, co się stanie:

Sprzedaż sklepu (zła) =
SUM(FaktSprzedaz[WartoscNetto])
// ŹLE: w każdym wierszu ta sama liczba, sprzedaż całej sieci,
// bo SUM nie widzi kontekstu wiersza z DimSklepy

Krok 3. Poprawna wersja owija agregację w CALCULATE, co wymusza przejście kontekstu i sprawia, że każdy wiersz DimSklepy widzi tylko swoją sprzedaż. Dodaj drugą kolumnę w tej samej tabeli (prawy przycisk na DimSklepy, Nowa kolumna):

Sprzedaż sklepu =
CALCULATE(SUM(FaktSprzedaz[WartoscNetto]))
// DOBRZE: CALCULATE zamienia kontekst wiersza (bieżący sklep)
// na kontekst filtra, więc suma dotyczy tylko tego sklepu

Krok 4. Przejdź do widoku Dane (druga ikona na pasku po lewej) i wybierz w panelu Dane tabelę DimSklepy. Obie nowe kolumny są na końcu tabeli.

Kolumna z lewej powtarza sumę całej sieci w każdym wierszu, co jest bezużyteczne. Kolumna z prawej, dzięki jednemu CALCULATE, rozkłada sprzedaż na sklepy. To jest cały sekret przejścia kontekstu w jednym obrazku. Reguła praktyczna: gdy w kolumnie obliczeniowej albo w iteratorze chcesz, żeby agregacja odnosiła się do bieżącego wiersza, owiń ją w CALCULATE. Kolumnę Sprzedaż sklepu (zła) zostawiamy w pliku efektu obok poprawnej, żeby było z czym porównać. W raporcie dla zarządu taką kolumnę usuwasz (prawy przycisk na kolumnie, Usuń z modelu).

RANKX: ranking sklepów

Co chcemy osiągnąć. Chcemy dać zarządowi Nordvelli ranking sklepów: numer pozycji każdego sklepu obok jego sprzedaży, bez sztucznego numeru w wierszu sumy.

Skoro rozumiemy już usuwanie filtrów i przejście kontekstu, ranking jest naturalnym połączeniem obu. Zarząd Nordvelli chce zestawienia sklepów od najlepszego do najgorszego pod względem sprzedaży, z numerem pozycji obok wartości. Funkcja RANKX iteruje po podanej tabeli, dla każdego jej wiersza liczy wyrażenie i porównuje z wartością bieżącego wiersza, zwracając numer pozycji. Zacznijmy od wersji, która wygląda rozsądnie, a nie działa.

Krok 1. Na stronie FILTER i RANKX utwórz miarę:

Ranking sklepu (bez ALL) =
RANKX(
    VALUES(DimSklepy[Nazwa]),
    [Łączna sprzedaż]
)

Krok 2. Obok macierzy marek wstaw drugą macierz: do obszaru Wiersze przeciągnij DimSklepy[Nazwa], do Wartości Łączną sprzedaż i Ranking sklepu (bez ALL). Tej mierze nie ustawiaj formatu złotówkowego, to numer pozycji.

Każdy sklep jest pierwszy. W wierszu macierzy filtr zostawia jeden sklep, więc VALUES(DimSklepy[Nazwa]) zwraca listę z jedną pozycją i RANKX porównuje sklep sam ze sobą. Poprawna wersja owija kolumnę w ALL.

Krok 3. Utwórz miarę z rankingiem liczonym po wszystkich sklepach. Komentarze po // możesz wkleić razem z formułą, Power BI je pominie:

Ranking sklepu =
IF(
    HASONEVALUE(DimSklepy[Nazwa]),   // wiersz sumy nie ma jednego sklepu
    RANKX(
        ALL(DimSklepy[Nazwa]),       // po wszystkich sklepach, bez filtra wiersza
        [Łączna sprzedaż],           // wartość rankingowana
        ,                            // (pominięte: bierze bieżącą wartość)
        DESC,                        // malejąco, 1 = największa sprzedaż
        DENSE                        // bez luk w numeracji przy remisach
    )
)

Owinięcie w ALL zdejmuje filtr wiersza, dając RANKX pełną listę sklepów do porównania. Wewnątrz RANKX każde wywołanie [Łączna sprzedaż] przechodzi kontekst do konkretnego sklepu, więc liczby są rozdzielone poprawnie. Widzisz, jak dwa pojęcia z tego rozdziału spotykają się w jednej formule.

Krok 4. Przeciągnij Ranking sklepu do Wartości tej samej macierzy i kliknij nagłówek kolumny Ranking sklepu, żeby posortować po niej. Jeśli strzałka przy nagłówku wskazuje w dół, kliknij jeszcze raz: pozycja 1 ma być na górze. Dostajesz gotowe zestawienie zarządcze:

Ostatni argument, Dense, decyduje o zachowaniu przy remisach. Dense numeruje bez luk (dwa remisy na pozycji 3, kolejny sklep dostaje 4), a domyślne Skip zostawia dziurę (kolejny dostaje 5). W rankingach zarządczych najczęściej chcesz Dense, żeby lista pozycji była ciągła. Wiersz sumy nie ma pojedynczego sklepu do porównania, więc bez zabezpieczenia RANKX pokazałby tam dziwną pozycję. Dlatego całość jest owinięta w IF z HASONEVALUE: w wierszu sumy miara zwraca pusto i macierz wygląda tak, jak na zrzucie wyżej.

Kolejność filtrów i KEEPFILTERS

Co chcemy osiągnąć. Miara sprzedaży jednej kategorii, na przykład Komputerów, potrafi w macierzy kategorii Nordvelli pokazać tę samą kwotę we wszystkich wierszach. Chcemy rozumieć, skąd bierze się ten efekt, i kontrolować go funkcją KEEPFILTERS.

Na koniec pułapka, która potrafi kosztować godziny debugowania. Argumenty filtrujące w CALCULATE działają na kontekście przyniesionym z zewnątrz, a nie kolejno jeden na drugim. Dlatego kolejność między prostymi warunkami nie ma znaczenia, bo wszystkie stosują się do tego samego kontekstu wejściowego. Znaczenie ma za to to, czy dany warunek nadpisuje, czy zawęża istniejący filtr. Domyślnie prosty warunek na kolumnie nadpisuje wybór użytkownika w tej kolumnie, co bywa zaskoczeniem.

Krok 1. Dodaj trzecią stronę i nazwij ją KEEPFILTERS. Zbudujemy na niej macierz z wierszami po DimProdukty[Kategoria] i miarę, która ma pokazać sprzedaż tylko Komputerów:

Sprzedaż komputery =
CALCULATE(
    [Łączna sprzedaż],
    DimProdukty[Kategoria] = "Komputery"
)

Zaskoczenie: ta miara w wierszu Telefony pokaże sprzedaż Komputerów, a nie pusto. Argument nadpisał filtr wiersza, więc niezależnie od tego, którą kategorię zawiera wiersz, wynik dotyczy Komputerów. Jeśli chcesz zachowanie przecięcia (pokaż Komputery tylko tam, gdzie kontekst i tak dotyczy Komputerów, a w innych wierszach pusto), owiń warunek w KEEPFILTERS:

Sprzedaż komputery (przecięcie) =
CALCULATE(
    [Łączna sprzedaż],
    KEEPFILTERS(DimProdukty[Kategoria] = "Komputery")
)

Krok 2. Wstaw macierz: do obszaru Wiersze przeciągnij DimProdukty[Kategoria], do Wartości Łączną sprzedaż, Sprzedaż komputery i Sprzedaż komputery (przecięcie).

Kolumna środkowa pokazuje efekt nadpisania: te same 130,5 mln zł w każdym wierszu, co jest błędem logicznym raportu. Kolumna prawa z KEEPFILTERS zachowuje przecięcie z kontekstem wiersza, więc wartość pojawia się tylko przy Komputerach, a pozostałe kategorie są puste, dokładnie tak, jak oczekuje osoba czytająca raport. Zapamiętaj tę różnicę: domyślne nadpisywanie jest wygodne przy stałych wartościach odniesienia, ale gdy chcesz respektować to, co użytkownik już wybrał, sięgaj po KEEPFILTERS.

Krok 3. Obok macierzy wstaw Fragmentator z polem DimProdukty[Kategoria] i zaznacz w nim Telefony.

Masz teraz komplet narzędzi kontekstu filtra: CALCULATE, ALL i REMOVEFILTERS do udziałów, FILTER do warunków złożonych, przejście kontekstu do miar w iteratorach i kolumnach, RANKX do rankingów oraz KEEPFILTERS do świadomej kontroli nad wyborem użytkownika. W następnym rozdziale przejdziemy do iteratorów SUMX i AVERAGEX oraz miar ze zmiennymi VAR. CALCULATE wróci w rozdziale o funkcjach czasu (Time Intelligence), gdzie posłuży do porównań rok do roku, sprzedaży narastająco od początku roku i średnich kroczących, czyli tego, co najczęściej trafia na pulpity zarządcze Nordvelli.

✕