Konteksty w Power BI

Czy tego chcemy, czy nie, przy pracy z Power BI korzystamy z: filter, row, query context. Warto wiedzieć jak te mechanizmy działają. Dzięki temu można uniknąć wielu niespodziewanych sytuacji w pracy z danymi.
Query context
Zanim zaczniemy tworzyć piękne wizualizacje, mądre DAX-y i odporne na wszystkie błędy instrukcje warunkowe, musimy zidentyfikować nasze dane i się do nich podłączyć. Właśnie w tym miejscu korzystamy z query context.
Power BI oferuje kilkadziesiąt źródeł, do których możemy się podłączyć bezpośrednio m.in. do plików csv czy xlsx, baz danych, serwisów online. Co jest w tym wypadku istotne, często nie potrzebujemy wszystkich danych jakie firma ma do zaoferowania. Jeśli chcemy przygotować raport sprzedażowy za rok 2017-2018, nie musimy ściągać danych od początku istnienia firmy. I właśnie te ograniczenia możemy ustawić w advanced query editor (gdzie korzystamy z query context).
Power BI ma możliwość korzystania z funkcjonalności native query. Co to oznacza? Jeśli łączymy się z serwerem SQL i chcemy korzystać z danych tylko z roku 2019, możemy napisać takie zapytanie w advanced query editor:
Następnie Power BI przetwarza to zapytanie na native query. Oznacza to, że jest ono wysyłane na serwer, gdzie dane są filtrowane i dopiero wtedy ściągane do raportu. Dzięki temu zyskujemy znacznie krótszy czas ściągania danych (jest to zauważalne przy dużych modelach danych), a PBIX przechowuje tylko te dane, które są nam potrzebne.
Trzeba pamiętać o tym, że nie każdy krok w zapytaniu może być przekonwertowany na native query. Jak to sprawdzić? W query editor po prawej stronie, gdzie mamy wylistowane wszystkie kroki po kolei, wystarczy kliknąć prawym przyciskiem myszy na dany krok:
Dobrym przykładem może być split column by delimiter. Jest to operacja wykonywana przez Power BI i w tym wypadku native query nam nie pomoże.
Trzeba pamiętać, że każdy kolejny krok nie jest już konwertowany na native query. Dlatego, warto się zastanowić i wszystkie konwertowalne kroki zrobić na początku.
W przypadku łączenia się bezpośrednio do pliku, czy to cvs czy xlsx, native query nie występuje w ogóle. Mimo wszystko, uważam że warto po podłączeniu do plików napisać takie query, które będzie pokazywać nam tylko te dane, na jakich chcemy pracować.
Filter Context
Kiedy mamy już dane w naszym modelu, możemy przystąpić do pisania measures, które później zostaną użyte przy wizualizacji danych. W tym wypadku niezwykle istotnym jest filter context. Warto wiedzieć co wpływa na naszą measure.
Sprawdźmy to na bardzo prostym przykładzie.
W poniższej tabeli mamy sprzedaż produktów w kilku krajach:
Dodatkowo mamy tabelę z produktami (zawsze warto mieć tabele z wymiarami, ale tutaj już wchodzimy w zagadnienia modelu danych star schema - o tym kiedy indziej):
Tworząc prostą measure widzimy że suma sprzedaży wynosi 100:
W przypadku kiedy dodamy do tabeli nazwę produktu, wartość zostanie rozdzielona między produkty:
To jest pierwszy czynnik w filter context, czyli wszystkie pola jakie wrzucimy do wizualizacji. Power BI automatycznie przelicza naszą measure przez te pola.
Pole Product Name pochodzi z powyższej tabeli wymiarów z produktami. Natomiast suma sprzedaży pochodzi z tabeli sprzedażowej. Skąd Power BI wie jak te wartości przypisać? Sprawdźmy co się dzieje między dwoma tabelami:
Występuje relacja one to many w jednym kierunku. Relacje są również bardzo istotne przy pracy z Power BI, zarówno przy pisaniu measure jak i przy tworzeniu modelu danych. W przypadku kiedy tej relacji zabraknie, będziemy mieli następujący wynik:
Dlaczego? Bo Power BI nie jest w stanie rozdzielić wartości sprzedaży między produkty, dlatego że produkt pochodzi z innej tabeli.
To jest drugi czynnik występujący w filter context, czyli relacje między tabelami.
W DAX występują funkcje, które umożliwiają nam modyfikacje samego filter context, między innymi funkcja CALCULATE. Sprawdźmy tabelę poniżej:
Mamy tę samą liczbę produktów, wykonujemy tę samą operację, czyli sumujemy sprzedaż produktów. Natomiast za każdym razem otrzymujemy inny wynik. Dlaczego?
_M_Sum of Products sold omawialiśmy powyżej.
W przypadku _M_Calculate w/o Filter Products sold, korzystamy z funkcji CALCULATE, która umożliwia nam zmianę/narzucenie filter context. Chcemy zobaczyć sumę sprzedaży Produktu 1 dla wszystkich produktów, żeby np. policzyć % sprzedaży w stosunku do Produktu 1. W tym celu nasza formuła wygląda następująco:
Co zatem robimy? Modyfikujemy filter context (który jest narzucony przez tabelę w postaci Product 1,2,3) w taki sposób, żeby dla każdego produktu pokazać wartość dla Product 1.
A co z ostatnim przykładem gdzie widzimy wartość tylko przy Product 1?
Formuła jest bardzo podobna, tylko w tym przypadku wewnątrz CALCULATE dorzucamy tabelaryczną funkcję FILTER (zwraca tabelę), ponieważ chcemy zobaczyć sumę Produktu 1, ale tylko dla Produktu 1.
To jest trzeci czynnik. który występuje w filter context, czyli modyfikowanie kontekstu z użyciem formuł DAX.
Zwróćmy uwagę, że mesures A i B są do siebie bardzo podobne.
A.
B.
Tak naprawdę, to co robi DAX w przypadku pierwszej measure jest równoznaczne z:
Kolejną funkcją, która ma wpływ na filter context jest ALL.
Zdarzają się sytuacje, kiedy należy użyć funkcji CALCULATE, FILTER czy też połączenia FILTER z ALL. Dodam jeszcze, że funkcja FILTER wykorzystuje dużo więcej pamięci RAM niż CALCULATE.
Row context
Row context jest dosyć intuicyjnym pojęciem, w tym wypadku każdy wiersz jest traktowany osobno.
Dla przykładu, mamy tabelę z dodatkową kolumną Discount, odejmując jedną kolumnę od drugiej otrzymamy wynik dla każdego wiersza:
To jest właśnie row context. Ale co się stanie w przypadku kiedy użyjemy funkcji SUM?
Mamy wartość zsumowaną dla wszystkich wierszy. Dlaczego? Funkcja SUM nie sumuje wartości wiersz po wierszu tylko wartości dla całej kolumny, czyli korzystamy z filter context.
Innymi słowy, Power BI najpierw sumuje Product Price dla całej kolumny, a następnie przypisuje tę wartość do każdego wiersza w tabeli.
Row i filter context mogą być stosowane i modyfikowane w różnych sytuacjach, natomiast nie polecam zaproponowanego wyżej rozwiązania. Funkcję SUM zastosowałbym w measure, ale nie w kalkulowanej kolumnie. Jest taka możliwość i Power BI nie uznaje tego jako błąd, ale trzeba pamiętać, że istnieją dobre i złe praktyki. Dobrą praktyką jest stosowanie funkcji typu SUM, CALCULATE w measure, niekoniecznie w kalkulowanych kolumnach.