#6 FILTER w DAX - jak efektywnie filtrować dane w Power BI

#6 FILTER w DAX - jak efektywnie filtrować dane w Power BI

Wstęp: funkcja FILTER w DAX

FILTER to jedna z funkcji tablicowych, co oznacza, że jej rezultatem jest tabela. Jest to jedna z najczęściej używanych funkcji DAX, jednak rzadko pojawia się samodzielnie. Zazwyczaj stosuje się ją w połączeniu z innymi funkcjami jako element pośredni. Ilekroć w naszym kodzie potrzebujemy odwołać się do tabeli lub kolumny w tabeli, korzystamy z funkcji tablicowych, takich jak FILTER. 

Jej składnia funkcji to dwa obowiązkowe elementy:

FILTER ( Table, FilterExpression )

Tabela, z której pobieramy dane, oraz filtr, jaki na nią nakładamy (filtrów może być więcej, o czym za chwilę), to kluczowe elementy funkcji FILTER. Aby zrozumieć istotę jej działania, warto pamiętać, że FILTER służy do pobierania danych. Wbrew temu jak sugeruje nazwa, nie jest to tylko "pojemnik" na filtry. FILTER przechowuje w sobie tabelę i zwraca jej przefiltrowaną wersję, którą możemy wykorzystać w dalszych kalkulacjach.

Wiem, że brzmi to dość skomplikowanie, więc posłużę się przykładem z SQL, aby to zobrazować. Oto jak wyglądałby kod SQL naśladujący funkcję FILTER:

Jak widzisz, FILTER nie jest zamiennikiem SQL-owego WHERE, jak mogłoby się wydawać, lecz bardziej przypomina całą kwerendę z SELECT na czele. Jeśli masz wątpliwości, spójrz jeszcze raz na składnię funkcji FILTER – najpierw wybieramy tabelę, a następnie nakładamy na nią warunki. Taka logika wynika z jej budowy.

Jak działa FILTER?

Tabela utworzona przez FILTER jest wykorzystywana w kolejnych etapach obliczeń, co przypomina tworzenie podzapytań w SQL-u, aby na wybranych danych wykonywać dalsze kalkulacje. Na potwierdzenie, że FILTER przechowuje dane, spójrz na poniższy przykład. Jeśli umieścimy samą funkcję FILTER w mierze, otrzymamy błąd. Komunikat wyraźnie mówi, że nasza kalkulacja zawiera dane tabelaryczne, podczas gdy miara oczekuje, aby jej rezultat był pojedynczą wartością (np. agregacją).

Co innego, gdybyśmy chcieli umieścić ten sam kod w tabeli kalkulowanej. Jak sama nazwa wskazuje, jest to tabela, więc oczekuje się tam danych w formie tabelarycznej. W takim przypadku FILTER zadziała bez problemu.

Zawartość tabeli stworzonej w ten sposób, np. TABLE_1, można porównać do odwołania (reference) w Power Query. To sytuacja, w której pobieramy fragment jednej tabeli, wybieramy z niej interesujące nas dane i tworzymy nową tabelę za pomocą FILTER. Ponieważ jest to odwołanie, zmiany w danych źródłowych będą miały wpływ na tabelę kalkulowaną. Na przykład: 

  • Jeśli po odświeżeniu danych z tabeli Customer w kolumnie City znikną wiersze z nazwą „London”, to tabela TABLE_1 również się nie odwoła, ponieważ nie będzie miała z czego utworzyć danych.
  • Jeśli w tabeli Customer pojawi się nowa kolumna, np. Region, to w naszej tabeli kalkulowanej także się pojawi.

Co istotne, choć dane w nowo utworzonej tabeli kalkulowanej są w pewnym sensie takie same co w tabeli źródłowej, to nasza nowo stworzona tabela nie jest automatycznie włączona w istniejący model danych. Zauważ, jak „pływa” swobodnie w modelu, bez powiązań z innymi tabelami. Oczywiście możemy z niej normalnie korzystać, mimo to nic nie stoi na przeszkodzie, aby jednak stworzyć dla niej relacje i włączyć ją w schemat modelu danych.

Co więcej, mamy elastyczność – nie musimy powielać relacji oryginalnej tabeli. Możemy je zmodyfikować lub w ogóle nie tworzyć żadnych. Obie możliwości są często wykorzystywane w pracy analityka, w zależności od potrzeb analizy.

Podobny kod (z drobnymi zmianami) moglibyśmy również użyć w DAX Query. Dzięki temu możliwe będzie wywołanie podglądu danych. Trzeba jednak pamiętać o funkcji EVALUATE, która w DAX Query jest wymagana do określenia elementów, które mają zostać zwrócone w zapytaniu.

Operatory logiczne i odwołania do tabel

Czas na kolejny krok wtajemniczenia w temat funkcji FILTER. Na pierwszy rzut oka, patrząc na składnię, wydaje się, że funkcja ta przyjmuje jedynie dwa parametry, jednak w rzeczywistości jest nieco bardziej elastyczna. Kolejne warunki filtrowania możemy dodawać, używając operatorów && (działa jak AND) i || (działa jak OR). Oto przykład:

Korzystając z operatorów, koniecznie zwróć uwagę na kolejność argumentów filtrowania, a w razie potrzeby używaj nawiasów, aby odpowiednio zaplanować hierarchię warunków.

Pamiętaj również, że do filtrowania możemy używać tylko kolumn, które w tabeli, którą wskazujemy istnieją. Dlatego nie możemy odwoływać się do kolumn z innych tabel – takie wywołanie nie zadziała:

Przy czym wcale nie musimy brać z tabeli Customer wszystkich jej kolumn. Jeśli interesuje nas tylko kolumna City, możemy ją łatwo wskazać. W takim przypadku tracimy dostęp do innych kolumn tej tabeli, więc nie będziemy mogli się do nich odwołać podczas filtrowania, co ostatecznie jeśli nie potrzebujemy ich wszystkich może być dobrym rozwiązaniem, aby pozbyć się zbędnego balastu. Wyboru kolumn możemy dokonać na przykład za pomocą funkcji ALL. Zobacz na przykładzie poniżej:

Tłumacząc działanie kodu: Za pomocą funkcji ALL (która również jest funkcją tablicową) tworzymy nową dwukolumnową tabelę Customer. Następnie używamy jej jako obiektu w funkcji FILTER, którą następnie filtrujemy. Dzięki temu otrzymujemy nową tabelę składającą się z kolumn City i CustomerKey, zawierającą tylko wiersze, które spełniły warunki filtrowania.

Zauważ, że mimo iż sama funkcja FILTER nie zmienia kolumn, a jedynie wiersze w zwracanej przez siebie tabeli, to poprzez odpowiednie zagnieżdżenie funkcji ALL możemy zmienić także te elementy. Podobnie, jeśli chodzi o dodawanie nowych kolumn, które w oryginalnej tabeli Customer nie istnieją, możemy to zrobić za pomocą funkcji ADDCOLUMNS.

Zastosowanie FILTER w miarach

Sporo przykładów dotyczących tabel i DAX Query, a przecież mówiłem, że funkcja FILTER częściej używana jest w miarach. To jak to w końcu jest? Rzeczywiście, w raportach za pomocą DAX tworzy się przede wszystkim miary, ale sam dość często korzystam z tabel. Na etapie tworzenia raportu i pisania kodu dobrze jest zwizualizować sobie dane. Dużo łatwiej zrozumieć, co się dzieje w naszym kodzie, gdy możemy spojrzeć na jego rezultat.

Ale co dalej, jak przenieść ten kod do miary, skoro samo umieszczenie funkcji FILTER powoduje błąd w mierze? Musimy użyć funkcji, która zagreguje nasze dane. Może to być coś prostego, jak zliczenie liczby wierszy w przefiltrowanej tabeli lub zliczenie unikatowych wartości w określonej kolumnie tej tabeli, ale możliwości jest znacznie więcej. 

Poniżej znajdziesz prosty przykład dwóch miar, gdzie jedna z nich ma za zadanie zliczać wiersze w przefiltrowanej tabeli _filteredTable, a druga liczy tylko unikatowe wiersze z tabeli _filteredTable w kolumnie Calendar[Year]. Pamiętaj, że używając _filteredTable nadal odwołujemy się do tabeli Calendar i jej kolumn, ponieważ to jest nasze źródło danych oryginalne źródło danych. Dla lepszego uporządkowania kodu korzystam z warunków (VAR), rozdzielając kod na mniejsze, bardziej czytelne części.

Manipulowanie kontekstem w połączeniu z CALCULATE

Te przykłady są dość proste, ponieważ zarówno filtrowanie, jak i późniejsze obliczenia dotyczą jednej tabeli. Przy czym w Power BI często wykorzystuje się właściwość, że tabele są powiązane ze sobą relacjami. Oznacza to, że filtrujemy jedną tabelę, aby uzyskać określone wyniki w drugiej, kompletnie innej tabeli. Teraz możesz mieć lekkiego „zonka”. Ale jak to? Przecież jeszcze na przykładzie TABLE_1 mówiłem i pokazywałem, że taka tabela nie jest powiązana z modelem. W przypadku miar, bardzo ważny jest kontekst, aby nim manipulować wewnątrz miary w sposób programistyczny trzeba jedynie skorzystać z funkcji CALCULATE. Zobacz na przykładzie:

Wpływ relacji pomiędzy tabelami na wyniki

W tym momencie wykorzystujemy fakt, że tabela Date jest powiązana z tabelą Sales. Wskazujemy za pomocą CALCULATE, że agregacja, która zlicza sumę wartości sprzedaży z tabeli Sales, ma wykonać się tylko dla dat sprzedaży, które mieszczą się w 2012 roku.

Żeby jeszcze bardziej rozłożyć to na części pierwsze, tak aby lepiej zrozumieć, co tutaj się wydarzyło, po raz kolejny odwołajmy się do SQL. Tak wyglądałaby nasza miara, gdybyśmy chcieli odwzorować logikę działania DAX w języku SQL.


Jednak w przypadku DAX nie musimy robić JOIN. Używając CALCULATE, silnik języka DAX sam wie, że tabela Date jest powiązana z tabelą Sales, więc wynik wyliczenia zostanie obliczony tylko dla wierszy spełniających kryteria filtrowania. 

Na sam koniec, żeby nie wprowadzać za dużo zamieszania, w przypadku CALCULATE możesz łączyć warunki filtrowania odnoszące się do wielu tabel jednocześnie. Jednak w tym przypadku nie używamy tych samych operatorów jak wewnątrz FILTER (czyli && oraz ||), po prostu kolejne FILTER wymieniamy po przecinku, tak jak tutaj:

PODSUMOWANIE

Funkcja FILTER w DAX pozwala na precyzyjne filtrowanie danych w tabelach, przechowując wynik w nowej tabeli, która może być używana w dalszych obliczeniach. Choć przypomina SQL-owy WHERE, jej działanie jest bardziej elastyczne i moglibyśmy porównać ją do SELECTa. Z tego powodu funkcja FILTER nie zadziała samodzielnie umieszczona w mierze DAX. Jak wiadomo w miarach musimy pamiętać o agregowaniu wyników, dlatego FILTER zwykle jest jedynie częścią kalkulacji, a razem z funkcją CALCULATE, tworzy nierozerwalny duet. FILTER i CALCULATE w połączeniu z odpowiednimi agregacjami to fundament, na którym opiera się wiele zaawansowanych analiz w Power BI. Dzięki tym funkcjom możemy efektywnie filtrować dane, zarządzać kontekstem filtrów i tworzyć elastyczne miary, które dostosowują się do zmieniających się danych w raporcie.

Sprawdź też inne wpisy na moim blogu: [LINK]