User-Defined Functions w DAX w Power BI

Tworzenie powtarzających się i skomplikowanych obliczeń w DAX może być czasochłonne i podatne na błędy, szczególnie przy większych zbiorach danych. Jedno z nowszych narzędzi w DAX, User-Defined Functions (UDF), pozwala nam definiować własne funkcje w modelu danych. Dzięki nim możemy uniknąć wielokrotnego powtarzania tych samych obliczeń, co znacząco przyśpiesza czas tworzenia raportów oraz zwiększy wydajność modelu danych. W tym artykule przedstawimy, jak używać narzędzia User-Defined Functions oraz pokażemy, w jakich sytuacjach ich zastosowanie może się okazać korzystne.

User-Defined Functions to stosunkowo nowe narzędzie w DAX, które nie jest domyślnie włączone w Power BI. Aby uzyskać do niego dostęp, należy aktywować funkcje eksperymentalne.

Otwieramy Power BI Desktop i przechodzimy do Plik → Opcje i ustawienia → Opcje:

Następnie wybieramy sekcję Funkcje w wersji zapoznawczej i zaznaczamy opcję Funkcje języka DAX zdefiniowane przez użytkownika i zaznaczamy OK:

Aby korzystać z UDF, musimy zrestartować Power BI Desktop.

Definiowanie funkcji języka DAX przez użytkownika działa podobnie do funkcji w tradycyjnych językach programowania – mogą przyjmować argumenty, wykonują zdefiniowane obliczenia i zwracają wynik, który możemy wykorzystać w miarach, kolumnach obliczeniowych i raportach. Dużą zaletą UDF jest to, że po zmianie definicji funkcji wszystkie miejsca, w których jest używana, automatycznie uwzględniają wprowadzone zmiany.

Aby zdefiniować funkcję, przechodzimy do widoku zapytań DAX, który jest dostępny po włączeniu funkcji eksperymentalnych:

W zakładce zapytania definiujemy nową funkcję, korzystając ze składni DEFINE FUNCTION:

DEFINE
	FUNCTION NazwaFunkcji = (parametr1: typ, parametr2: typ, …) =>
		/* logika funkcji */

Aby wyświetlić wynik naszej funkcji, używamy funkcji EVALUATE:

EVALUATE
ROW(„WynikFunkcji”, NazwaFunkcji(argument1, argument2, …)

Wynikiem będzie jedno-wierszowa tabela z kolumną „WynikFunkcji”, w której znajduje się wynik funkcji dla podanych argumentów.

Najpierw zdefiniujemy funkcję, która dodaje dwie liczby:

DEFINE
	FUNCTION Dodaj = (a: numeric, b: numeric) =>
		a + b

Aby sprawdzić działanie funkcji, wywołamy ją w widoku zapytań języka DAX, używając funkcji EVALUATE:

EVALUATE
ROW("WynikDodawania", Dodaj(5, 7))

Gdy przygotowana funkcja, będzie użyteczna także w modelu danych, możemy ją dodać do wszystkich innych, wbudowanych funkcji DAX.

Aby to zrobić, klikamy Aktualizowanie modelu ze zmianami, a następnie Aktualizuj model:

Nasza funkcja została dodana do modelu i możemy z niej korzystać:

Przy definiowaniu funkcji DAX każdy parametr może mieć określony typ danych (string, numeric, int64 itp.), który określa nam, jakiego rodzaju wartości parametr może przyjmować, dzięki czemu możemy uniknąć błędów związanych z wprowadzeniem niewłaściwego typu wartości, np. wprowadzania tekstu tam, gdzie oczekiwana jest liczba.

Parametry dzielą się również na dwa główne rodzaje:

• Parametr typu value oznacza, że funkcja przyjmuje pojedynczą, konkretną wartość skalarną. Typ value jest odpowiedni dla wartości które są stałe lub zostały już obliczone, np. daty, liczby, teksty czy wartości logiczne.
• Parametr typu expr w funkcjach DAX oznacza, że do funkcji można przekazać wyrażenie DAX, które zostanie obliczone dopiero w momencie wywołania funkcji.
• Oznacza to, że funkcja będzie dynamicznie reagować na zmieniające się dane i kontekst wizualizacji.

DEFINE
    FUNCTION CenaPromocyjna = (cena: numeric, rabat: numeric) =>
        IF(ISBLANK(rabat), cena*0.9, cena* (1-rabat))
EVALUATE
ROW(
    "Rabat_domyslny", CenaPromocyjna(100, BLANK()),
    "Rabat_podany", CenaPromocyjna(100, 0.2)
)

W tym przykładzie, jeśli przekażemy BLANK() jako rabat, funkcja automatycznie użyje wartości 0.9 jako domyślnego rabatu.

Przy definiowaniu funkcji i parametrów należy pamiętać o regułach nazewnictwa, podobnych jak w standardowym DAX:

• Nazwy mogą zawierać jedynie cyfry, litery, znak podkreślenia (spacje i inne znaki specjalne są niedozwolone)
• Nazwa nie może zaczynać się od cyfry
• Nazwa nie może używać istniejących nazw funkcji DAX, np. IF, SUM, DIVIDE, itd.

Dodatkowo, nazwy funkcji:

• Mogą zawierać kropki (nazwa nie może zaczynać się ani kończyć kropką, ani zawierać dwóch kropek obok siebie)
• Muszą być unikalne w obrębie modelu (funkcja SumaSprzedazy nie może istnieć dwukrotnie).

Mamy przykładowe dane załadowane do modelu Power BI:

Chcemy stworzyć raport, w którym przedstawimy nazwę produktu, cenę netto oraz cenę brutto, którą wyliczymy definiując funkcję DAX.

Przechodzimy do widoku zapytań w języku DAX:

Definiujemy funkcję, która będzie obliczać cenę brutto (zakładając VAT 23%):

Możemy sprawdzić czy nasza funkcja działa poprawnie, wyświetlając wyniki w zapytaniu:

DEFINE
    FUNCTION Cena_Brutto = (netto: numeric) =>
        netto * 1.23
EVALUATE
ADDCOLUMNS(
    'Dane',
    "CenaBrutto", Cena_Brutto('Dane'[Cena sprzedaży (netto)])
)

Jak widać, nasza funkcja działa prawidłowo.

Dodajemy naszą funkcję do modelu:

Tworzymy wizualizację w postaci Tabeli i dodajemy do niej pola Produkt i Cena sprzedaży (netto):

Chcemy dodać kolumnę obliczeniową Cena sprzedaży (brutto) do tabeli ‘Dane’. Aby to zrobić, przechodzimy do zakładki Modelowanie, a następnie wybieramy Nowa kolumna:

Chcemy dodać kolumnę obliczeniową Cena sprzedaży (brutto) do tabeli ‘Dane’. Aby to zrobić, przechodzimy do zakładki Modelowanie, a następnie wybieramy Nowa kolumna:

Tworzymy wyrażenie DAX, korzystając z naszej funkcji:

Cena sprzedaży (brutto) = Cena_Brutto(Dane[Cena sprzedaży (netto)])

Zauważ, że po zapisaniu Power BI zgłasza błąd. Dzieje się tak dlatego, że funkcje UDF domyślnie zwracają typ Variant (dowolny typ danych), a kolumny obliczeniowe wymagają konkretnego typu danych.

Problem ten można obejść na dwa sposoby:

DEFINE
    FUNCTION Cena_Brutto = (netto: numeric) =>
        VALUE(netto * 1.23)

Gdy zmieniliśmy typ danych naszego wyniku funkcji, możemy dodać kolumnę obliczeniową do wizualizacji:

Gotowe!

Mamy przykładowe dane załadowane do modelu Power BI:

Chcemy stworzyć funkcję, która w miejscach, gdzie nie podano wartości VAT, użyje wartości domyślnej – 23%.

Przechodzimy do widoku zapytań w języku DAX i tworzymy nową funkcję, która za pomocą funkcji ISBLANK() ustawi domyślną wartość 23% dla argumentu vat:

Następnie za pomocą funkcji EVALUATE() wywołamy funkcję, by sprawdzić jej działanie:

DEFINE
    FUNCTION CenaBrutto = (cena_netto: numeric, vat: numeric) =>
        IF( ISBLANK(vat), cena_netto*1.23, cena_netto*(1+vat) )
EVALUATE
ADDCOLUMNS(
    Vat,                           -- Twoja tabela
    "Cena Brutto", 
    CenaBrutto(Vat[Cena netto], Vat[Vat])
)

Jak widać, funkcja zadziałała. Pola, w kolumnie Vat, które były puste, domyślnie zostały potraktowane jako wartość 0,23.

Do tego przykładu wykorzystamy dane z przykładu 1.

Chcemy stworzyć wizualizację, w której dla każdego produktu przedstawimy Poziom sprzedaży – „Niski” lub „Wysoki” w zależności od tego, czy jego sumaryczna sprzedaż (netto) przekroczyła próg – 15 000 zł.

Tworzymy miarę sumującą sprzedaż danego produktu:

SumaSprzedazy = SUM(Dane[Cena sprzedaży (netto)])

Następnie definiujemy funkcję, która będzie oceniać poziom sprzedaży, w zależności od progu:

Tworzymy nową miarę która wykorzysta naszą funkcję:

Tworzymy wizualizację w postaci tabeli i dodajemy do niej pole Produkt, miarę SumaSprzedazy oraz miarę PoziomSprzedazy:

Nasz raport jest gotowy:

W tym przykładzie również posłużymy się danymi z przykładu pierwszego.

Chcemy zdefiniować funkcję, która pozwoli nam tworzyć miary, wykonujące konkretne obliczenia dla danego produktu, np. zliczy ilość sprzedanych komputerów, czy obliczy sumę sprzedaży monitorów.

Tworzymy dwie miary w modelu – zliczanie transakcji i sumowanie wartości sprzedaży:

SumaSprzedazy = SUM(Dane[Cena sprzedaży (netto)])

IloscSprzedazy = COUNT(Dane[Produkt])

Następnie przechodzimy do widoku zapytań w języku DAX i definiujemy nową funkcję, która wykona obliczenia w zależności od podanej miary oraz nazwy produktu:

Chcemy teraz sprawdzić, czy nasza funkcja działa prawidłowo. Za pomocą funkcji EVALUATE() sprawdzimy, czy wyświetli prawidłowe wartości:

Gdyby argument wejściowy został opisany typem np. numeric, uzyskane wyniki byłyby zaskakujące. Przy liczbie komputerów zamiast 14, pojawiłby się wynik wszystkich wierszy w tabeli (99). „Miara” ma być argumentem przeliczanym, stąd też typ expr.

DEFINE
    FUNCTION ObliczProdukt = (miara: expr, produkt) =>
        CALCULATE(
            miara,
            Dane[Produkt] = produkt
        )
EVALUATE
ROW(
    "Suma_Komputer", ObliczProdukt([SumaSprzedazy], "Komputer"),
    "Liczba_Komputer", ObliczProdukt([IloscSprzedazy], "Komputer")
)

Jak widać, nasza funkcja działa poprawnie.

Chcemy wyświetlić ile razy sprzedano słuchawki oraz jaka była sumaryczna wartość sprzedaży głośników.

Tworzymy miary „specjalne” dla słuchawek i głośników:

IloscSluchawki = ObliczProdukt([IloscSprzedazy], „słuchawki”)

SumaGlosniki = ObliczProdukt([SumaSprzedazy], „głośniki”)

Następnie tworzymy wstawiamy karty w raporcie, by wyświetlić otrzymane wartości:

Nasz raport jest gotowy:

Jeśli wrócimy do widoku zapytań i chcemy zmienić, bądź przetestować przygotowane funkcje, warto to wyklikać: przycisk prawy myszy na funkcji, a następnie polecenie Szybkie zapytania -> Oblicz, Zdefiniuj i oblicz. Dla kilku funkcji warto wybrać Zdefiniuj wszystkie funkcje w tym modelu:

W efekcie uzyskamy:

Pozostaje nam do testów podmienić w wierszu 9 i 10 nazwy argumentów na właściwe wartości.

Podsumowując, User-Defined Functions (UDF) w DAX to narzędzie, które znacznie zwiększa możliwości Power BI. Pozwalają tworzyć elastyczne, dynamiczne obliczenia i dzielić kod na łatwe do zarządzania części, co ułatwia testowanie i utrzymanie „w czystości” modeli danych.

Funkcje użytkownika pozwalają uprościć złożone kalkulacje, zachowując wydajność i czytelność modeli – szczególnie w dużych projektach z wieloma miarami. Ich rozwój sprawia, że w przyszłości Power BI będzie jeszcze bardziej elastyczny dla zaawansowanych analiz.