Debuggowanie miary w DAX z użyciem zmiennych
Zanim zaczniemy optymalizować kod w DAX i świadomie używać dobrych praktyk zacznijmy od wyjaśnienia sobie tych kilku trudnych słów. Zmienna czyli co to takiego? Dlaczego w ogóle miałbym z tego korzystać?
Wyobraźcie sobie taką sytuację. Jesteście na najdłuższej ulicy w Waszym mieście – dla przykładu niech to będzie Piotrkowska w Łodzi. Potrzebujecie zdobyć informację gdzie na tej ulicy znajduję się bank, apteka, sklep z cukierkami i restauracja. Jan – wasz przełożony do Was mówi abyście przespacerowali się od początku do końca ulicy i poszukali apteki. Zrobiliście sobie niezły spacer ale oczywiście zadanie wykonane. Wracacie z informacją, że apteka jest w punkcie X.
Następnie dostajecie polecenie aby znowu się przespacerować tą samą ulicą i poszukać banku.
Przy kolejnym poleceniu jak się łatwo domyślić będzie trzeba powtórzyć ten sam spacer, tą samą ulicą aby odszukać sklep z cukierkami oraz restaurację.
Po czwartym powrocie ze spaceru bolą was nogi ale wszystkie cele zostały znalezione. W tym momencie zastanawiacie się dlaczego Jan od razu nie kazał Wam poszukać przy pierwszej wyprawie wszystkich potrzebnych lokalizacji to zrobilibyście to znacznie szybciej i nie musielibyście cztery razy pokonać tej samej trasy.
I właśnie na takim ogólnym poziomie zrozumienia będzie zmienna w DAX. Napisanie kodu, który się wykona tylko jeden raz na początku, a następnie wiele razy z niego później skorzystacie w obliczeniach. Czy silnik odpowiedzialny za działanie DAX też nie będzie z tego bardziej zadowolony, jeżeli nie będzie musiał powtarzać tych samym obliczeń w wielu miejscach?
Warto więc korzystać ze zmiennych aby poprawić wydajność waszych obliczeń oraz zwiększyć czytelność kodu.
Jeżeli udało mi się Was zainteresować tym przykładem to czas przejść do meritum.
Tym, co wprowadza zmienne do wyrażenia jest słowo kluczowe VAR. Jest to skrót od słowa variable i definiuje ono zmienną. Kolejnym krokiem jest nazwa zmiennej. Dobrą praktyką jest nazwanie zmiennych od znaku specjalnego __ (podkreślnik) ale nie jest to wymagane. Warto wiedzieć, że nazwa zmiennej nie może posiadać polskich znaków i spacji. Definicję kończymy drugim słowem kluczowym RETURN – po którym zostaje zwrócony wynik.
Warte zapamiętania jest także, że w wyrażeniu możemy zadeklarować wiele zmiennych a więc słów kluczowych VAR będzie kilka ale zawsze na końcu wpisujemy RETURN tylko jeden raz.
Poniżej przykład zadeklarowania zmiennej, która oblicza wartości sprzedaży tylko we Francji powiększone o 10 %.
$ Sales Amount Francja +10 % =
VAR __Sales_Amount_FRANCJA =
CALCULATE (
SUM ( FactInternetSales[Wartość sprzedaży] ),
’DimObszarSprzedaży'[Kraj] = „Francja”
)
RETURN
__Sales_Amount_FRANCJA * 1.1
Zmienne są definiowane zazwyczaj na początku definicji miary, a następnie wykorzystywane często wielokrotnie w jej kodzie. To poniekąd już wyjaśnia przykład z Waszym spacerem ulicą Piotrkowską.
Zmienne mogą przechowywać wartości skalarne czyli liczby lub całe tabele, które później możecie wykorzystać do obliczeń chociażby w funkcjach iteracyjnych.
Często także gdy formuły są złożone zmienne są stosowane do podziału pisania kodu w celu poprawy jego czytelności.
Zmienne ułatwiają również debuggowanie formuły.
Gdy potrzebujecie przetestować fragment kodu wystarczy po wyrażeniu RETURN zwrócić nazwę zmiennej w celu sprawdzenia poprawności wyników, które powinna zwrócić. Następnie gdy jesteście przekonani, że wszystko jest wyliczane poprawnie możecie ją wykorzystać w dalszych obliczeniach zagnieżdżając zmienną w kolejnych blokach kodu.
Aby w prosty sposób zobrazować ten przykład zadeklarowałem trzy proste zmienne. Nie wyliczają one nic skomplikowanego. Ich celem jest zaprezentowanie jak w prosty sposób można dokonać weryfikacji wyników pojedynczych zmiennych lub sprawdzić błędy.
Pierwsza zmienna to proste dodawanie, druga mnożenie a trzecia wynik końcowy.
Gdy po słowie return zwrócę wynik to oczywiście oczekuję wyniku dodania pierwszej i drugiej zmiennej jak poniżej.
# Zmienna testowa =
VAR __Pierwsza_Zmienna = 2 + 2
VAR __Druga_Zmienna = __Pierwsza_Zmienna * 10
VAR __Wynik = __Pierwsza_Zmienna + __Druga_Zmienna
RETURN
__Wynik
Natomiast gdybym nie był przekonany czy moje zmienne wyliczają prawidłowe wyniki, które później zagnieżdżam w kodzie tworząc skomplikowany kod DAX mogę bez większego problemu przetestować pojedyncze zmienne zwracając po return odpowiednią zmienną jak poniżej.
# Zmienna testowa =
VAR __Pierwsza_Zmienna = 2 + 2
VAR __Druga_Zmienna = __Pierwsza_Zmienna * 10
VAR __Wynik = __Pierwsza_Zmienna + __Druga_Zmienna
RETURN
__Pierwsza_Zmienna
# Zmienna testowa =
VAR __Pierwsza_Zmienna = 2 + 2
VAR __Druga_Zmienna = __Pierwsza_Zmienna * 10
VAR __Wynik = __Pierwsza_Zmienna + __Druga_Zmienna
RETURN
__Druga_Zmienna
Gdyby podczas pisania miary DAX „nie do końca” chciał z nami współpracować zwracając nieoczekiwane wyniki to wtedy testując zmienne zdecydowanie łatwiej będzie nam znaleźć gdzie popełniliśmy błąd. Poniżej prosty przykład błędnie wpisanego operatora porównania. Wynikiem tej zmiennej jest test logiczny prawda lub fałsz, który później próbuje pomnożyć przez wartość liczbową. Przykład może nie jest bardzo praktyczny ale dobrze obrazujący jak łatwo wyłapać potencjalne błędy w kodzie. W przypadku nie używania zmiennych byłoby to zdecydowanie trudniejsze.
# Zmienna testowa =
VAR __Pierwsza_Zmienna = 2 > 2
VAR __Druga_Zmienna = __Pierwsza_Zmienna * 10
VAR __Wynik = __Pierwsza_Zmienna + __Druga_Zmienna
RETURN
__Pierwsza_Zmienna
Jeżeli rozumiemy już w jaki sposób i do czego wykorzystać zmienne w DAX to zobaczmy troszeczkę ambitniejszy przykład.
Poniżej miara, która oblicza procentową dynamikę wzrostów i spadków MbM dla wartości sprzedaży w kategorii Rowery. W pierwszym przykładzie z użyciem zmiennych, a w drugim bez ich użycia. Prawda, że pierwszy kod jest bardziej przyjazny dla nas? A przy okazji DAX też jest bardziej zadowolony bo nie musiał obliczać kilka razy tego samego. Zmienna __SalesAmountRowery oraz __PM jest obliczana tylko raz ale wykorzystaliśmy ją wielokrotnie. Przy dużych zbiorach danych będzie miało to wpływ na wydajność Waszego modelu danych.
% MbM KPI Sales Amount Rowery =
VAR __SalesAmountRowery =
CALCULATE (
SUM ( FactInternetSales[Wartość sprzedaży] ),
DimKategoria[Nazwa Kategorii] = „Rowery”
)
VAR __PM =
CALCULATE (
CALCULATE (
SUM ( FactInternetSales[Wartość sprzedaży] ),
DimKategoria[Nazwa Kategorii] = „Rowery”
),
DATEADD ( DimDate[Date], -1, MONTH )
)
VAR __Diffrent = __SalesAmountRowery – __PM
VAR __out =
IF ( HASONEVALUE ( DimDate[Nazwa miesiąca] ), DIVIDE ( __Diffrent, __PM ) )
RETURN
__out
% MbM KPI Sales Amount Rowery bez zmiennych =
IF (
HASONEVALUE ( DimDate[Nazwa miesiąca] ),
DIVIDE (
CALCULATE (
SUM ( FactInternetSales[Wartość sprzedaży] ),
DimKategoria[Nazwa Kategorii] = „Rowery”
)
– CALCULATE (
CALCULATE (
SUM ( FactInternetSales[Wartość sprzedaży] ),
DimKategoria[Nazwa Kategorii] = „Rowery”
),
DATEADD ( DimDate[Date], -1, MONTH )
),
CALCULATE (
CALCULATE (
SUM ( FactInternetSales[Wartość sprzedaży] ),
DimKategoria[Nazwa Kategorii] = „Rowery”
),
DATEADD ( DimDate[Date], -1, MONTH )
)
)
)
W miejscach gdzie wykorzystacie ją ponownie nie odbywa się już żadne obliczenia. To trochę tak jakby DAX miał gdzieś zapisane na kartce wyniki i do nich sobie sięga i podstawia w odpowiednie miejsce. Taki efekt może być także bardzo przydatny w bardziej rozbudowanych koncepcjach języka DAX chociażby gdy nie chcecie wywołać przejścia kontekstu. Użycie zmiennych nie spowoduję obliczeń tylko podstawi wartość już wyliczoną a w pewnych sytuacjach możemy tego potrzebować. Ale to już temat na zupełnie inną bajkę😊
Podsumowując wykorzystanie zmiennych przy tworzeniu obliczeń jest bardzo dobrą praktyką w pracy z Power BI Desktop ale także w Power Pivot i innych dodatkach jak Tabular Editor czy DAX Studio. Znacznie ułatwi to pracę Wam jako twórcą raportów ale także innych użytkownikom, którzy będą potrzebować zrozumieć czy poprawić kod w DAX. Dzięki wykorzystaniu zmiennych obliczenia będą działały szybciej, natomiast czytelność kodu będzie znacznie większa i bardziej elegancka jak na Power BI-owego eksperta przystało. Możliwość debuggowania kodu wpłynie także na poprawę jakości i pozwoli wyeliminować potencjalne błędy.
Także jedyne co mi pozostało to to życzyć udanej współpracy z VAR i RETURN.