Power BI – dynamiczna zmiana osi z DAX i M

Jakiś czas temu napisałem artykuł gdzie tłumaczyłem w jaki sposób stworzyć dynamiczną zamianę osi z wykorzystaniem Bookmarków (artykuł znajduje się tutaj). Dziś chciałbym nieco rozszerzyć ten temat i pokazać Wam jak osiągnąć pożądany efekt w prosty sposób przy użyciu języka DAX i transformacji języka M, serdecznie zapraszam do lektury.

Korzystając z AdventureWorksDW przypuśćmy, że chcemy aby na naszym wykresie czy też innej wizualizacji pokazywała się liczba sprzedanych przedmiotów w zależności od trzech atrybutów:

• klasy produktu,
• koloru produktu,
• rozmiaru produktu.

Wymogiem jest to aby użytkownik końcowy mógł wybrać, który z atrybutów ma się pojawić na osi. Mamy zatem zbudować wykres i w zależności od selekcji użytkownika chcemy aby podmieniane były dynamicznie wartości na osi X (załóżmy, że nie wchodzi tutaj w grę implementacja trzech różnych wykresów ani przedstawienie wszystkich atrybutów jednocześnie). W tego typu przypadkach to co musimy zrobić to zbudować atrybut, który będzie zawierał zarówno klasę, kolor jak i rozmiar produktu. Co to oznacza? Myślę, że okaże się to prostsze na konkretnym przykładzie:

Na początku spróbujmy do osiągnięcia naszego celu użyć DAXa – stworzyłem tabelę kalkulowaną na podstawie następującego kodu:

Parameters = 
UNION (
    ADDCOLUMNS (
        SUMMARIZE ( DimProduct; DimProduct[ProductKey]; DimProduct[Color] );
        "Group"; "Color"
    );
    ADDCOLUMNS (
        SUMMARIZE ( DimProduct; DimProduct[ProductKey]; DimProduct[Size] );
        "Group"; "Size"
    );
    ADDCOLUMNS (
        SUMMARIZE ( DimProduct; DimProduct[ProductKey]; DimProduct[Class] );
        "Group"; "Class"
    )
)

Jest to tabela parametrów gdzie najpierw tworzę trzy unikalne zbiory za pomocą funkcji SUMMARIZE składające się z klucza ProductKey oraz poszczególnych interesujących atrybutów (kolor, rozmiar, klasa). W dalszym kroku za pomocą ADDCOLUMNS dodaję kolumnę wyliczaną, tak aby móc zidentyfikować z jaką wartością mam do czynienia. Następnie poszczególne zbiory łącze w jedną tabelę za pomocą funkcji UNION. Zbiór końcowy prezentuje się w następujący sposób:

Jak można zauważyć na powyższym zrzucie ekranowym kolumna Value zawiera wartość atrybutu, Group to nazwa atrybutu a ProductKey to identyfikator produktu do którego odnoszą się pozostałe kolumny. Patrząc na ten zbiór możemy dojść do wniosku, że to co zrobiliśmy to nic innego jak operacja UNPIVOT czyli umieszczenie wybranych kolumn w wierszach i tak też w rzeczywistości jest.

Mając przygotowaną w ten sposób tabelę możemy ją podłączyć do naszego wymiaru DimProduct. Wymiar ten posiada klucz unikalnie identyfikujący każdy wiersz, którym jest ProductKey, nasza tabela parametrów również posiada ProductKey jednakże nie jest on już unikalny ze względu na fakt, iż będzie się on powtarzał 3 razy (wynik operacji union, którą wykonaliśmy wcześniej). Tak więc pomiędzy Product, a Params mamy relację jeden – wiele:

Na powyższym zrzucie ekranowym warto zwrócić uwagę na to, że oznaczyłem kierunek tej relacji na dwustronny. Zrobiłem tak dlatego ponieważ chciałbym aby po filtracji wybranego atrybutu w tabeli parametrów filtrowana był również wymiar produktów. Cały model przedstawia się następująco:

Przechodząc na warstwę raportową można stworzyć wybraną wizualizację korzystając ze stworzonej tabeli parametrów. W moim przypadku jest to wykres kolumnowy gdzie na osi X znajduje się atrybut Value z tabeli parametrów (czyli kolor + rozmiar + klasa), a jako miara znajduje się Suma FactInternetSales[OrderQuantity]. Efekt przedstawia się następująco:

Wygląda obiecująco! Teraz wystarczy dać użytkownikom możliwość wyboru jaki atrybut chcą widzieć na osi X ponieważ teraz widzą wszystkie możliwości. Tworzymy zatem slicer z atrybutem Parameters[Group] i dodatkowo włączamy opcję Single Select tak aby można było zaznaczyć tylko jedną z wartości:

 

Żeby niejako wymusić na użytkowniku wybór na slicerze nasza miara musi sprawdzić czy jedna wartość została wybrana i dopiero wtedy sumować:

Sum Of Order Quantity = 
VAR vCheck =
    CALCULATE (
        IF ( NOT ISBLANK ( SELECTEDVALUE ( Parameters[Group] ) ); TRUE () );
        ALL ( Parameters[Value] )
    )
VAR vResult =
    IF ( vCheck; SUM ( FactInternetSales[OrderQuantity] ) )
RETURN
    vResult

Rezultat wygląda już z całą pewnością zadowalająco (przy braku zaznaczenia lub przy zaznaczeniu z CTRL więcej niż jednego atrybutu wykres nie wyświetla nic):

Podsumowując przedstawioną technikę możemy powiedzieć, że rozwiązanie z dynamiczną osią to nic innego jak unpivot/unia wartości atrybutów, które mają dynamicznie pokazywać się na osi + odpowiednia miara w DAX. Oczywiście nic nie stoi na przeszkodzie aby takową unię wykonać wcześniej np. w SQL lub w Power Query/M. Postaram się to pokazać na podstawie tego drugiego.

Pierwszym krokiem jest podłączenie do źródła danych i pobranie tabeli, która ma stanowić podstawę dla naszej dynamicznej osi. W moim przypadku źródłem była referencja do pobranego wcześniej DimProduct czyli po prostu:

= DimProduct

Dalej przygotujemy nasze dane w taki sposób aby nie zawierały wartości pustych (null) wywołując na wybranych przez nas kolumnach funkcję Table.ReplaceValue:

    #"Replaced Value Color" = Table.ReplaceValue(Source,null,"NA",Replacer.ReplaceValue,{"Color"}),
    #"Replaced Value Size" = Table.ReplaceValue(#"Replaced Value Color",null,"NA",Replacer.ReplaceValue,{"Size"}),
    #"Replaced Value Class" = Table.ReplaceValue(#"Replaced Value Size",null,"NA",Replacer.ReplaceValue,{"Class"}),

Powyższą podmianę wykonaliśmy ponieważ po pierwsze chcieliśmy mieć czyste dane, a po drugie chcemy uniknąć sytuacji gdzie wywołana za chwilę operacja po prostu “wytnie” nam puste wartości co zazwyczaj robi:) Operacją tą jest UNPIVOT gdzie “kotwicą” naszej transformacji będzie ProductKey – w M to bardzo proste i opera się jak zazwyczaj na wywołaniu funkcji którą w tym wypadku jest Table.UnpivotOtherColumns:

= Table.UnpivotOtherColumns(Source, {"ProductKey"}, "Attribute", "Value")

Mamy zatem wszystkie nasze atrybuty w wierszach zamiast w kolumnach. W tym kroku to co musimy zrobić to odfiltrować te atrybuty, które nas nie interesują i nie będą częścią naszej dynamicznej osi (możemy również zrobić to nieco wcześniej, jeszcze przed wykonaniem operacji UNPIVOT).

    #"Filter By attribute list" = Table.SelectRows(#"Unpivot", each List.Contains({"Size","Color","Class"},[Attribute]))

W tym miejscu warto zaznaczyć, że filtracja przez listę wydaje się prosta i przejrzysta, jeśli to możliwe nie używajcie do tego typu operacji filtracji z interfejsu graficznego, który wygeneruje nam po prostu tyle porównań Atrybut=”A” OR Atrybut=”B” ile wybraliśmy wartości co może skutkować przerośniętym i mało czytelnym kodem.

Ostatnim krokiem była zmiana nazwy kolumny:

 #"Rename" = Table.RenameColumns(#"Filter By attribute list",{{"Attribute", "Group"}})

Cały kod M przedstawia się następująco:

let
    Source = DimProduct,
    #"Replaced Value Color" = Table.ReplaceValue(Source,null,"NA",Replacer.ReplaceValue,{"Color"}),
    #"Replaced Value Size" = Table.ReplaceValue(#"Replaced Value Color",null,"NA",Replacer.ReplaceValue,{"Size"}),
    #"Replaced Value Class" = Table.ReplaceValue(#"Replaced Value Size",null,"NA",Replacer.ReplaceValue,{"Class"}),
    #"Unpivot" = Table.UnpivotOtherColumns(#"Replaced Value Class", {"ProductKey"}, "Attribute", "Value"),
    #"Filter By attribute list" = Table.SelectRows(#"Unpivot", each List.Contains({"Size","Color","Class"},[Attribute])),
    #"Rename" = Table.RenameColumns(#"Filter By attribute list",{{"Attribute", "Group"}})
in
    #"Rename"

Otrzymaliśmy zestaw danych, który w modelu stanie się tabelą parametrów i będzie fajnym rozwiązaniem problemu dynamicznej osi. Podłączenia do modelu i wizualizacji nie będę prezentował ponieważ wygląda ona dokładnie tak samo jak pokazałem to wyżej. Mam nadzieję, że podany przeze mnie sposób na budowę tego typu rozwiązania przypadł Wam do gustu i okaże się przydatny.