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.