V decembrski posodobitvi Power BI-ja je Microsoft DAX jeziku dodal nekaj novih funkcij, katerih se je neuradno prijelo ime “window” funkcije.

Razlog je v tem, da so funkcije podobne SQL window funkcijam. To so funkcije, ki nam odprejo neko »okno« v naše podatke. Pogosto nas zanima, kakšen je odnos vrednosti iz ene vrstice do vrednosti v sosednjih vrsticah. Tipičen primer je spremljanje zaloge po dnevih, kjer nas zanima, koliko se zaloga nekega dne razlikuje od zaloge preteklega dne. Naše okno sta v tem primeru 2 vrstici in zanima nas razlika med njima. 

Nove funkcije pravzaprav DAX-u ne prinašajo nobenih novih zmogljivost. Vse, kar znajo nove window funkcije se je dalo narediti že prej, le da je bila koda običajno dokaj kompleksna. Te nove funkcije so:

• OFFSET,
• INDEX,
• WINDOW,
• ORDERBY,
• PARTITIONBY.

ORDERBY ter PARTITIONBY sta pravzaprav 2 pomožni funkciji, dodani zaradi določenih definicij, ki so potrebne znotraj ostalih novih funkcij. V kategorijo window funkcij spadajo prve tri. Naslednjih nekaj člankov bom namenili opisu window funkcij, začenši s funkcijo OFFSET.

Funkcija OFFSET

Vzemimo primer, omenjen v uvodu. Naša tabela se imenuje »PodatkiOZalogi«. Tabela ima stolpce »ID«, ki vsebuje unikatne identifikatorje, stolpec »Datum«, ki vsebuje datume v formatu YYYYMMDD, ter stolpec »Zaloga«, v katerem spremljamo dnevno stanje zaloge.

Da bomo lahko računali razliko v zalogi med enim in drugim dnevom, pa bi radi dodali stolpec, v katerem bomo prikazovali stanje zaloge dan prej. V prejšnjih različicah Power BI-ja, je bil pristop dokaj kompleksen, še posebej, preden so se pojavile spremenljivke.

Še nekaj mesecev nazaj bi se torej rabili matrati na sledeč način:

ZalogaPrejsnjiDan_OLD =

VAR _TrenutniDanID = PodatkiOZalogi[ID]

VAR _PrejsnjiDanID = CALCULATE(

    MAX(PodatkiOZalogi[ID]),

    FILTER(

        PodatkiOZalogi,

        PodatkiOZalogi[ID] < _TrenutniDanID

    )

)

 

VAR _ZalogaPrejsnjiDan = CALCULATE(

    MAX(PodatkiOZalogi[Zaloga]),

    FILTER(PodatkiOZalogi,

    PodatkiOZalogi[ID] = _PrejsnjiDanID

    )

)

 

RETURN

    _ZalogaPrejsnjiDan

 

Zaradi različnih kontekstov vrstice je definiranih kar nekaj spremenljivk. Najprej je definirana spremenljivka _TrenutniDanID, ki si shrani ID trenutne vrstice. Nato je definirana spremenljivka _PrejsnjiDanID, ki na podlagi prve spremenljivke po celotni tabeli poišče prejšnji največji ID. Na koncu pa je definirana, in kot rezultat vrnjena, spremenljivka _ZalogaPrejsnjiDan, ki vrne podatek iz stolpca „Zaloga“ iz vrstice, ki ima ID enak _PrejsnjiDanID.

Kako pa bi to izgledalo z novo OFFSET funkcijo? Preden začnemo s pisanjem funkcije, se je pomembno zavedati, kaj je rezultat vseh novih window funkcij. Te nam namreč kot rezultat ne vrnejo eno vrednost, ampak nam vrnejo tabelo. Spadajo torej v širšo kategorijo tabelnih funkcij. Iz tega razloga window funkcije pogosto ne bodo dovolj same po sebi, pač pa bodo del obsežnejše funkcije.

ZalogaPrejsnjiDan_NEW =

SELECTCOLUMNS (

    OFFSET (

        -1,

        SUMMARIZE ( PodatkiOZalogi, PodatkiOZalogi[Datum], PodatkiOZalogi[Zaloga] ),

        ORDERBY ( PodatkiOZalogi[Datum], ASC )

    ),

    "ZalogaPrejsnjiDan", [Zaloga]

)

 

Če se v zgornji kodi najprej osredotočimo le na funkcijo OFFSET, smo povedali sledeče:

• Pridobi mi podatke iz ene vrstice višje (-1).
• Ta vrstica naj bo pridobljena iz tabele, ki je sestavljena iz unikatnih kombinacij stolpcev Datum in Zaloga iz tabele PodatkiOZalogi (SUMMARIZE ( PodatkiOZalogi, PodatkiOZalogi[Datum], PodatkiOZalogi[Zaloga] )).
• Novo ustvarjena tabela naj bo pri tem urejena glede na datum naraščajoče. Na ta način bomo dobili ustrezen podatek za zalogo o prejšnjem dnevu – eno vrstico višje je vedno prvi prejšnji datum. Za razvrščanje smo uporabili eno od novih pomožnih funkcij, ORDERBY.

Kot rečeno, funkcija OFFSET ne vrne ene vrednosti, ampak vrne tabelo, ki vsebuje eno vrstico. V našem primeru vračamo vrstico, ki vsebuje stolpce Datum in Zaloga s podatki o prejšnjem dnevu. Stolpec Datum potrebujemo zaradi ustreznega sortiranja, stolpec Zaloga pa vsebuje podatke, ki nas zanimajo.

Funkcijo OFFSET sem iz tega razloga ugnezdil v funkcijo SELECTCOLUMNS, ki mi dovoli, da izberem le določene stolpce iz neke tabele. Tabelo sem izračunal s prvim argumentom funkcije SELECTCOLUMNS, torej s celotno OFFSET funkcijo. Z drugim argumentom sem povedal, kako naj se pridobljeni stolpec imenuje, kar v tem primeru niti ni tako pomembno, s tretjim argumentom pa sem definiral formulo za pridobivanje vrednosti tega novega stolpca. Povedal sem, da želim podatke črpati iz stolpca Zaloga tabele, ki sem jo izračunal s funkcijama OFFSET in SUMMARIZE. Ker je rezultat funkcije OFFSET le ena vrstica, z izbiro enega stolpca te vrstice dobim samo eno vrednost. To pa je rezultat, ki ga lahko zapisujem v izračunani stolpec.

Kot vidimo, je funkcija precej krajša, kot tista, spisana na star način. Scenarijev za uporabo funkcije OFFSET je še veliko, pogosta je uporaba v merah v kombinaciji s funkcijo CALCULATE, česar se lahko lotimo v kakšnem prihodnjem članku.

 KLEMEN VONČINA, MCT, Microsoft Office Specialist Master

Kontakt: klemen.voncina@kompas-xnet.si