Power BI – modyfikacja relacji z funkcją CROSSFILTER

Bardzo często filtracja krzyżowa jest tym czego oczekujemy od nowoczesnego narzędzia przeznaczonego do budowy modelu analitycznego. W Power BI i ogólnie rzecz biorąc w modelu tabelarycznym opcja ta była dostępna już od dawna pod postacią różnych technik jak na przykład specjalnego kodu DAX lub jako np. ustawienie kierunku relacji. Jedną z najbardziej pożytecznych funkcji pomocnych przy tego typu scenariuszach jest jest bez wątpienia CROSSFILTER, który pozwala ustalić zachowanie przepływu filtrów. Myślę, że znajomość tejże przyda się każdemu pracującemu z modelami analitycznymi dlatego też chciałbym zaprosić Was do lektury niniejszego artykułu gdzie przedstawmy ją na przykładach.

Jako nasz zestaw testowy wykorzystamy cztery tabele pochodzące z hurtowni danych AdventureWorksDW:

• FactInternetSales
• DimProduct
• DimDate
• DimSalesTerritory

Cały model, który na podstawie tych tabel stworzyłem w Power BI przedstawia się następująco:

Na powyższym zrzucie ekranowym można zauważyć, że tabele wymiarów są połączone z tabelą faktów relacjami jednokierunkowymi (filtracja przepływa od wymiaru do faktu). Przypuśćmy, że mamy taki scenariusz, iż chcemy przeanalizować ile produktów sprzedawanych jest w poszczególnych regionach. Produkty znajdują się oczywiście w tabeli DimProduct, a regiony/państwa w DimSalesTerritory. Ze względu na fakt, iż jeden wiersz w DimProduct odpowiada jednemu produktowi aby policzyć liczbę produktów możemy po prostu zliczyć liczbę wierszy wykorzystując COUNTROWS:

Number Of Products = COUNTROWS(DimProduct)

Mając do dyspozycji tą prostą miarę możemy na warstwie wizualizacji w Power BI stworzyć tabelę zawierającą Number Of Products i Region (atrybut SalesTerritoryCountry):

Cóż to? Nie tego się spodziewaliśmy! Uzyskany rezultat jest efektem zdefiniowanych przez nas relacji – filtracja przebiega tak jak poniżej czyli jak wspomniałem wcześniej od wymiaru do faktu:

Rozwiązaniem tej sytuacji jest zmiana kiedunku relacji między FactInternetSales, a DimProduct na dwukierunkową (Cross filter direction: Both):

Po tej zmianie gdy jeszcze raz spojrzymy na stworzoną przez nas wcześniej tabelę to możemy zauważyć, że miara działa w prawidłowy sposób:

Ten prosty zabieg rozwiązał nasz problem ale czy tak naprawdę sprawa jest zamknięta? To zależy od przypadku, czasem po prostu nie warto zmieniać całej relacji aby działała jedna miara. Zachowanie relacji możemy zmienić tylko w tej konkretnej mierze używając wspomnianego wcześniej CROSSFILTER. Funkcja ta przyjmuje następującą formę składającą się z trzech parametrów:

CROSSFILTER (kolumna, kolumna2, kierunek relacji)

Gdzie zwyczajowo:

• kolumna – kolumna po stronie wiele relacji
• kolumna2 – kolumna po stronie jeden relacji
• kierunek relacji – BOTH, NONE lub ONE

Oczywiście każda z wymienionych w parametrach kolumn musi być częścią istniejącej relacji pomiędzy tabelami – nie możemy za jej pomocą w tworzyć wirtualnych relacji.

Po modyfikacji miara wygląda w następujący sposób:

Number Of Products =
CALCULATE (
    COUNTROWS ( DimProduct );
    CROSSFILTER ( FactInternetSales[ProductKey]; DimProduct[ProductKey]; BOTH )
)

Aby sprawdzić jej działanie miary kolejny raz zmieniłem opisywaną wcześniej relację na jednokierunkową i jak widać poniżej otrzymałem dokładnie taki sam rezultat jakbym miał relację dwukierunkową:

Jak to zazwyczaj bywa nie jest to jedyny możliwy sposób osiągnięcia tego efektu, możemy go również uzyskać przekazując całą tabelę faktów jako filtr:

Number Of Products = CALCULATE(COUNTROWS(DimProduct);FactInternetSales)

Oczywiście użycie CROSSFILTER nie sprowadza się jedynie do zmiany relacji z SINGLE na BOTH, ale również na odwrót. Dobrą praktyką jest to, żeby w miarę możliwości używać CROSSFILTER zamiast zmieniać całą relację – szczególnie gdy modyfikacja zachowania jest wyjątkiem, a nie regułą.

Funkcja ta jest również pomocna w scenariuszach wiele do wielu, właściwie powyższy przykład obrazuje coś podobnego ale dla przykładu stworzyłem bardziej oczywisty przykład. W przykładzie wykorzystamy trzy uproszczone tabele wpisując dane bezpośrednio do Power BI (używając opcji Enter Data):

Do dyspozycji mamy produkty (products) i zamówienia(orders) gdzie granulatem pierwszej z nich jest pojedynczy produkt, a drugiej pojedyncze zamówienie. Zakładamy, że jedno zamówienie może zawierać wiele produktów. W tym uproszczonym scenariuszu mamy więc relację wiele do wielu, a co za tym idzie musimy posiadać tabelę mostkową (bridge), którą nazwałem ProductOrder :

Po ułożeniu danych model przedstawia się w następujący sposób:

Analogicznie jak w wyżej przedstawionym przykładzie chcemy policzyć ile produktów jest w każdym zamówieniu:

Number Of Products = COUNTROWS(Products)

I podobnie jak poprzednio otrzymamy błędny wynik ze względu na kierunek relacji:

Analogicznie jak poprzednio jesteśmy w stanie to naprawić poprzez użycie CROSSFILTER:

Number Of Products =
CALCULATE (
    COUNTROWS ( Products );
    CROSSFILTER ( OrderProduct[ProductID]; Products[ProductID]; BOTH )
)

Jak możecie zauważyć wykorzystanie CROSSFILTER jest kolejnym sposobem na obsłużenie wiele do wielu. Bardzo użyteczna funkcja, którą moim zdaniem warto rozpoznać ze względu na ogromne możliwości jakie oferuje.