Power BI – DAX KEEPFILTERS vs FILTER

Niektórzy specjaliści mówią, że język DAX jest kluczem do sukcesu raportów w Power BI. Z całą pewnością się zgadzam z tym stwierdzeniem ponieważ nawet jak mamy najlepszy i najbardziej dopracowany model to bez odpowiedniej implementacji miar trudno osiągnąć jakikolwiek sukces. O tym, że funkcja CALCULATE ze względu na swój charakter nadpisujący kontekst filtra jest najpotężniejsza funkcją dostępną w tym języku słyszeliśmy już niejednokrotnie. Kontekst ten można nadpisać jednak na wiele sposobów i dziś chciałbym Wam przedstawić kilka z nich tj. poprzez wykorzystanie CALCULATE właśnie w połączeniu z KEEPFILTERS oraz z popularnym FILTER – zapraszam serdecznie do lektury.

Na samym początku zdefiniujemy sobie nasz zbiór testowy, którym dziś będzie tabela FactInternetSales oraz DimProduct z AdventureWorksDW:

Do testów wykorzystamy tabelę przedstawiającą sumę wartości sprzedaży (FactInternetSales[SalesAmount]) po kolorze produktu (DimProduct[Color]):

W pierwszym przykładzie spróbujmy stworzyć miarę, która nadpisze atrybut koloru tj.bez względu na kolor znajdujący się w wierszu podstawi wartość obliczoną dla koloru czarnego. Efekt ten bardzo łatwo możemy osiągnąć używając CALCULATE w najprostszej postaci:

SalesAmountForBlack = CALCULATE(SUM(FactInternetSales[SalesAmount]);DimProduct[Color]="Black")

Otrzymaliśmy tą samą liczbę dla każdego wiersza – dlaczego? Każdy wiersz reprezentuje inny kontekst wykonania miary i każdy z tych kontekstów został nadpisany kolorem Czarnym, w tym również podsumowanie, które w gruncie rzeczy z punktu widzenia języka jest po prostu kolejnym kontekstem wykonania. No dobrze, ale co jeżeli chcielibyśmy nadpisać jedynie kontekst dla Black? W tym scenariuszu znów mamy kilka możliwości, pierwszą z nich jest użycie funkcji FILTER:

SalesAmountFilter = CALCULATE(SUM(FactInternetSales[SalesAmount]);
                              FILTER(DimProduct;[Color]="Black"))

Używając jej w takim scenariuszu nadpisaliśmy kontekst jedynie dla koloru czarnego, jednakże miara ta ma pewien problem. FILTER jest iteratorem i musi przefiltrować tabelę wiersz po wierszu co w niektórych przypadkach może odbić się negatywnie jeśli chodzi o wydajność. Alternatywą dla tego zapisu może być zapis ze wspomnianym wcześniej KEEPFILTERS:

SalesAmountFilter = CALCULATE(SUM(FactInternetSales[SalesAmount]); 
KEEPFILTERS([Color]="Black"))

 

Zapis ten jest dużo wydajniejszy, a daje ten sam efekt. Samą wydajność sprawdzimy sobie w dalszej części. Jest jeszcze trzeci sposób, którym możemy osiągnąć analogiczny efekt, a jest nim użycie FILTER + VALUES, który do iteracji zamiast całej tabeli weźmie unikalne wartości koloru. Efekt jak można było się spodziewać jest dokładnie taki sam:

Powyższe trzy sposoby wcale nie wyczerpują możliwości – są również inne konstrukcje jak np. IF – jednakże to podejście jest nieco inne bo usuwa Total, ale mimo wszystko umieśćmy je w naszym zestawieniu:

SalesAmountKeepFilters =
IF (
    HASONEVALUE ( DimProduct[Color] )
        && VALUES ( DimProduct[Color] ) = "Black";
    SUM ( FactInternetSales[SalesAmount] );
    BLANK ()
)

Aby udowodnić różnice wydajnościowe użyjemy DAX Studio i miernika w postaci ilości zapytań do Storage Engine, który powinien być wystarczający w użytych przykładach. Jako pierwszy bierzemy na tapet przykład z funkcją FILTER i wywołujemy następujące zapytanie (definicja miary podana wyżej):

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('DimProduct'[Color], "IsGrandTotalRowTotal"),
      "SalesAmountValues", 'FactInternetSales'[SalesAmountDateFilter]
    ),
    [IsGrandTotalRowTotal],
    0,
    'DimProduct'[Color],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'DimProduct'[Color]

W dalszym kroku wywołujemy analogiczne zapytanie tym razem z miarą KEEPFILTERS:

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('DimProduct'[Color], "IsGrandTotalRowTotal"),
      "SalesAmountValues", 'FactInternetSales'[SalesAmountKeepfilters]
    ),
    [IsGrandTotalRowTotal],
    0,
    'DimProduct'[Color],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'DimProduct'[Color]

 

Jak na tak proste zapytanie różnica jest bardzo duża, aby zwrócić rezultat przy użyciu FILTER potrzeba było aż czterech zapytań do Storage Engine, gdzie KEEPFILTERS potrzebowało tylko jednego! Warto również zwrócić uwagę na liczbę wierszy procesowanych w przypadku FILTER w każdym kroku. Oczywiście przy małych modelach nie ma to znaczenia ale im większy model tym bardziej odczujemy negatywny wpływ użycia FILTER.

Jeśli chodzi o pozostałe metody to również wypadają one gorzej od KEEPFILTERS ale nadal są lepsze niż FILTER:

Podsumowując można powiedzieć, że KEEPFILTERS jest lekiem na całe zło i funkcja ta powinna być używana niemal w każdej sytuacji. Powyższe przykłady są oczywiście uproszczone jednakże nic nie stoi na przeszkodzie aby przenieść je na nieco bardziej skomplikowany grunt szczególnie, że w przypadku użycia np. wielu filtrów funkcja działa dokładnie tak samo. Mam nadzieję, że nieco rozjaśniłem Wam całą koncepcję i będzie mogli budować nieco wydajniejsze miary z KEEPFILTERS. Oczywiście to tylko jeden z wielu niuansów związanych z DAX, myślę że jeszcze nieraz uda mi się napisać trochę o tzw. tips & tricks z tego zakresu. Na ten moment dziękuję za poświęcony czas na lekturę niniejszego artykułu i pozdrawiam.