Power BI – wykluczające i uzupełniające slicery

Każdy z nas zdaje sobie sprawę jak istotną funkcjonalnością Power BI są slicery. Pozwalają one przefiltrować pozostałe wizualizacje i są tym co użytkownicy nazywają “filtrami” i z całą pewnością nie budzą żadnych zastrzeżeń co do zastosowania. Standardowo każdy slicer pełni rolę filtra działającego na podstawie funkcji logicznej AND, a wybrane wartości są przekazywane jako obowiązujące do pozostałych wizualizacji. Co jeśli jednak chciałbym zrobić coś bardziej ciekawego i chciałbym zaznaczać te wartości na slicerze, które mają zostać odfiltrowane na wizualizacjach? Oczywiście mogę to zrobić! Tą prostą technikę z wykorzystaniem tabel wyliczanych chciałbym Wam przedstawić w ramach tego artykułu – zapraszam do lektury.

Nasz przykładowy raport wygląda tak jak zostało to przedstawione na poniższym zrzucie ekranowym. Prosty wykres kolumnowy pokazujący wartość sprzedaży dla poszczególnych kolorów produktów oraz slicer pozwalający wybrać kolory, które mają być widoczne na wykresie.

Jak więc w takim razie zrobić tak, aby kolor wybrany na slicerze był wykluczony z wykresu? Rozwiązań tego typu problemu jest całkiem sporo – ja powiem o tylko jednym z nich. Pierwszym krokiem potrzebnym do implementacji tej logiki jest stworzenie tabeli wyliczanej, która będzie zawierać unikalną listę kolorów. Stworzę ją przechodząc na widok danych i ze wstążki wybierając zakładkę Modelling i tam New Table:

 

W kolejnym kroku musimy podać zapytanie w DAX, które zwróci nam unikalną listę wartości – jest ono bardzo proste i tak naprawdę sprowadza się do wywołania funkcji VALUES. Całą tabelę nazwałem Excluded Colors ukryłem ją w widoku raportu klikając na nią prawym przyciskiem myszy wybierając z menu kontekstowego opcję Hide in report view.

Excluded Columns = VALUES('DimProduct'[Color])

To właśnie na tej jednokolumnowej tabeli będzie opierał się mój slicer. Mając przygotowaną tabelę mogę przejść do budowania miary sumującej wartość sprzedaży. Miara ta musi mieć zawartą w sobie taką logikę, aby odfiltrować sprzedaż produktów o kolorach wybranych w mojej nowo powstałej tabeli. Mogę to zrobić z wykorzystaniem standardowej funkcji EXCEPT, która nie robi nic innego jak po prostu wybiera ze zbioru wskazanego jako pierwszy argument te wartości, których brak w zbiorze wskazanym jako drugi argument:

SumOfSalesAmount =
CALCULATE (
    SUM ( 'FactInternetSales'[SalesAmount] );
    EXCEPT ( VALUES ( DimProduct[Color] ); VALUES ( 'Excluded Columns'[Color] ) )
)

Teraz nie pozostaje nic innego jak stworzyć nowy slicer na podstawie przygotowanej wcześniej Excluded Columns i wykres z wykorzystaniem miary SumOfSalesAmount posiadający na osi X atrybut Color z DimProduct. To co może zaskakiwać na samym początku to fakt, że wykres jest kompletnie pusty podczas gdy na slicerze nic nie jest wybrane:

Dzieje się tak dlatego, że domyślnie gdy nic nie jest zaznaczone to do miary przekazywana jest pełna lista kolorów, a co za tym idzie wszystkie zostaną odfiltrowane i nie ma koloru dla którego coś zostałoby obliczone. Oczywiście jeśli takie zachowanie nie jest dla nas użyteczne możemy to zmienić dodając prostą filtrację (pomijając fakt, że funkcje warunkowe w takim miejscu nie są nigdy dobrą rzeczą):

SumOfSalesAmount =
IF (
    ISFILTERED ( 'Excluded Columns'[Color] );
    CALCULATE (
        SUM ( 'FactInternetSales'[SalesAmount] );
        EXCEPT ( VALUES ( DimProduct[Color] ); VALUES ( 'Excluded Columns'[Color] ) )
    );
    CALCULATE ( SUM ( 'FactInternetSales'[SalesAmount] ) )
)

My pozostaniemy bez dodatkowej filtracji i możemy zobaczyć, że nasz raport działa dokładnie tak jak chcieliśmy i mamy fajnie działającą logikę wykluczającą:

Możemy tak to zostawić jeśli wiemy, że jedna z wartości musi zostać odfiltrowana. Jeśli chcemy aby użytkownik miał możliwość wyświetlenia wszystkich wartości to albo stworzymy powyższą miarę z instrukcją warunkową albo np. dodajmy dodatkową wartość do naszej tabeli kalkulowanej, która będzie miała za zadanie wyświetlenie wszystkich wartości na wykresie. Możemy to osiągnąć budując tabelę używając następującego wyrażenia:

 

Excluded Columns =
UNION (
    CROSSJOIN ( VALUES ( 'DimProduct'[Color] ); ROW ( "Sorting Order"; 2 ) );
    ROW ( "Color"; "Don't exclude"; "Sorting Order"; 1 )
)

Powyższe wyrażenie doda wpis “Don’t exclude’ jako jedną z wartości dostępnych do wyboru, ponadto dodałem kolumnę Sorting Order, która odpowiada za sortowanie wartości. Wszystkie kolory otrzymają wartość sortowania 2 natomiast nowy wpis wartość 1 przez co zostanie on umieszczony na samej górze slicera, a pozostałe ułożą się alfabetycznie. W tym momencie nasza wizualizacja jest nieco bardziej przyjazny dla użytkowników końcowych. Słowem uzupełnienia wspomnę, że aby wskazać kolumnę jako wartość sortowania innej kolumny wystarczy na zakładce Modelling wybrać Sort By Column mając zaznaczoną kolumnę, którą chcemy sortować:

W tym momencie jeśli tylko chcemy możemy mieć dwa slicery na kolorze. Jeden z nich będzie miał standardową logikę włączającą, a drugi przygotowaną przez nas logikę wykluczającą. Pójdźmy o krok dalej i spróbujmy przygotować raport gdzie będziemy mieli dwa slicery jeden na atrybucie Class, a drugi na atrybucie Size. Standardowo tego typu konstrukcja po zaznaczeniu wartości w każdym z nich będzie przedstawiać np. wartość sprzedaży gdzie Class=’H’ oraz Size=40. Spróbujmy tak przygotować nasz raport aby umożliwiał on zaprezentowanie logiki gdzie Class=’H’ lub Size=40. Standardowy przykład zamiany logiki ORAZ na LUB.

Do tematu podejdę w standardowy sposób i znów stworzę sobie tabelę kalkulowaną. Zamiast tworzyć dwie tabele dla każdego z atrybutów stworzyłem jedną używając funkcji CROSSJOIN (oba atrybuty użyte w tym przykładzie nie są zbyt liczebne więc iloczyn kartezjański jest dobrym wyjściem, jeśli mamy do czynienia z dużą tabelą o wysokiej selektywności warto sprawdzić rozmiar powstałego słownika i pomyśleć o rozdzieleniu na dwie osobne tabele):

SizesAndClasses = CROSSJOIN(VALUES('DimProduct'[Class]);VALUES(DimProduct[Size]))

Oczywiście podobnie jak w poprzednim przypadku tak i teraz jest to tabela techniczna, która nie powinna być widoczna dla użytkowników. Mając już tabelę muszę dodać miarę, w której zawarta będzie cała logika – wygląda ona następująco:

SumOfSalesAmnt =
CALCULATE (
    SUM ( 'FactInternetSales'[SalesAmount] );
    FILTER (
        'DimProduct';
        OR (
            [Size] IN ( VALUES ( 'SizesAndClasses'[Size] ) );
            [Class] IN ( VALUES ( 'SizesAndClasses'[Class] ) )
        )
    )
)

W tym momencie raport zachowuje się w pożądany sposób np. zaznaczając kolejno Size=48 oraz Class=M otrzymamy sprzedaż wszystkich towarów o rozmiarze 48 bez względu na klasę plus sprzedaż wszystkich towarów o klasie M bez względu na rozmiar:

Oczywistym jest fakt, że podobne struktury możemy stworzyć bazując na znacznie większej ilości slicerów i z bardziej wyrafinowanymi miarami niż suma. Oczywiście metoda ta jak każda inna nie jest pozbawiona wad. Ze względu na fakt, iż tabele z wartościami wyświetlanymi w slicerach nie są połączone relacjami dlatego też nie zostaną ograniczone przez selekcje w innych polach. Mniejszym problemem (ale jednak problemem) jest duplikacja danych ale mimo wszystko będzie to miało znaczenie tylko w przypadku atrybutów o naprawdę dużej ilości unikalnych wierszy.

Znając tą prostą technikę jesteśmy osiągnąć naprawdę ciekawe efekty,a w gruncie rzeczy nie powinna ona nikomu sprawić większych problemów. Być może z czasem doczekamy się wbudowanej obsługi logiki opartej na LUB, na ten moment musimy zadowolić się funkcjonalnościami dostępnymi z poziomu DAX.

Trzecim przykładem jaki chciałbym Wam przedstawić, jest scenariusz pozwalający wyświetlić wartość “All” na slicerze oraz na wykresie. Nauczony doświadczeniem wiem, że użytkownicy czasem chcą wykorzystać Power BI w statyczny sposób. Często zdarza się, że model w narzędziu przygotowany jest pod konkretny raport czy nawet wizualizację i wtedy też musimy kombinować jak coś zaimplementować i tak też jest w tym konkretnym przypadku. Przypuśćmy, że chcemy osiągnąć efekt taki jak na poniższym zrzucie ekranowym:

Co widzimy na powyższym zrzucie ekranowym? Dedykowaną pozycję na slicerze oraz na wykresie, która odpowiada za sumę całej sprzedaży. Pozostałe wartości odpowiadają poszczególnym okresom rocznym. Aby coś takiego osiągnąć znów możemy wykorzystać tabele kalkulowane. Najpierw stwórzmy sobie tabelę bazując na poniższym zapisie DAX:

Calendar Years =
UNION (
    ADDCOLUMNS (
        VALUES ( 'DimDate'[CalendarYear] );
        "ReferenceKey"; 'DimDate'[CalendarYear];
        "Sort Column"; 2
    );
    ADDCOLUMNS (
        CROSSJOIN ( ROW ( "Total"; "All Years" ); VALUES ( 'DimDate'[CalendarYear] ) );
        "Sort Column"; 1
    )
)

Powyższy zapis pozwoli na wyliczenie następującej tabeli:

Pomiędzy tą tabelą, a tabelą kalendarza DimDate zachodzi relacja wiele do wielu. W takim przypadku potrzebujemy tabeli mostkowej aby połączyć obie tabele – jej definicja prezentuje się następująco:

CalendarYearBridge = VALUES('DimDate'[CalendarYear])

Wszystkie tabele należy połączyć relacjami. Aby uprościć cały scenariusz użyjemy relacji dwukstronnych choć mogliśmy również użyć odpowiedniego wyrażenia DAX. Schemat połączeń wygląda następująco:

• Calendar Years[ReferenceKey]=CalendarYearBridge[CalendarYear]
• CalendarYearBridge[CalendarYear]=DimDate[CalendarYear]

Mając tak zamodelowane relacje możemy przejść do tworzenia wykresów. Mamy dwie możliwości:

• stworzyć wykres z określoną miarą i atrybutem CalendarYear z tabeli wyliczanej Calendar Years
• stworzyć wykres z określoną miarą i atrybutem CAlendarYear z DimDate.

Różnica jest taka, że w przypadku użycia atrybutu z tabeli wyliczanej będziemy mieli pole “All Years”, które jest traktowane jak każda inna wartość. W przypadku gdy na wykresie wyświetlimy atrybut z DimDate to po zaznaczeniu “All Years” po prostu wybrane zostaną wszystkie lata (kolumny z “All Years” nie będzie widać ponieważ takiej wartości w DimDate po prostu nie ma). Zachowanie to możecie zaobserwować na poniższej grafice:

W przypadku dolnego wykresu zaznaczenie “All Years” pełni taką samą rolę jak opcja “Select All”, ale zachowanie to jest całkiem inne dla wykresu powyżej. Zamiast dwóch wykresów możemy również użyć Bookmarków i przełączać się między nimi dynamicznie bądź np. na jednym wykresie pokazać “All years”, a na drugim poszczególne składniki. Wszystko tak naprawdę zależy od naszej inwencji.

W górnym wykresie wyróżniłem kolorem wartość dla All Years – osiągnąłem to ustawiając we właściwościach pod opcją Data Colors wybrany przeze mnie kolor:

Całkiem ciekawy efekt. Oczywiście cały czas podkreślam, że ten sam efekt można osiągnąć na kilka sposobów, a ogólnie rzecz biorąc wydajność każdego rozwiązania zależy od wielu czynników jak wielkość modelu, dostępność zasobów czy selektywność atrybutu filtrującego. Warto wiedzieć, że operacje na zbiorach w miarach DAX wykorzystujące szybki Storage Engine z całą pewnością będą lepiej działały niż zestaw instrukcji warunkowych, które bardzo mocno obciążają wolny, jednowątkowy Formula Engine. Pamiętajmy, że jeżeli coś nie jest dostępne “z pudełka” to w wielu przypadkach możemy to zaimplementować w mniej lub bardziej wyrafinowany sposób w modelu z wykorzystaniem DAX. Jedną z kluczowych w moim mniemaniu funkcjonalności, które to umożliwiają są właśnie tabele wyliczane, które stały się nieodłączonym elementem niemal każdego bardziej wyszukanego modelu w Power BI.