DAX modeli u Power Pivotu: formule, kontekst i najbolje prakse

Ako radite s podacima u Excelu i PowerPivotPrije ili kasnije, ukrstit ćete se s DAX-om. Iako naziv „Izrazi za analizu podataka“ zvuči tehnički i pomalo zastrašujućeRealnost je da, uz dobro razumijevanje osnova, to postaje vrlo praktičan i moćan alat za modeliranje informacija.

U Power Pivotu, DAX je srž tabelarnih modela: Koristi se za kreiranje izračunatih kolona i mjera koje opskrbljuju pivot tabele i grafikoneOmogućava vam da se igrate s kontekstom filtera, radite s datumima, rješavate greške i pravite poređenja u el tiempo I još mnogo toga. Pogledajmo detaljnije kako se sve ovo uklapa u DAX modele u Power Pivotu i kako to iskoristiti bez pretjeravanja.

Šta je DAX i zašto je ključan u Power Pivot modelima?

DAX (Izrazi za analizu podataka) je, prije svega, jezik formula dizajniran za modele podatakanije jezik programiranje Klasični. Njegova funkcija je definiranje prilagođenih izračuna koji se primjenjuju na tabele i kolone u vašem Power Pivot modelu.

Iako vizuelno može ličiti na excel formule, DAX je dizajniran za rad s relacijskim podacima i dinamičkim agregacijama.To znači da se besprijekorno integrira s tablicama povezanim relacijama, pivot tablicama i složenim modelima gdje se kontekst filtera mijenja ovisno o poljima koja koristite u redovima, stupcima ili slicerima.

Unutar Power Pivot modela, možete koristiti DAX za Dva glavna tipa objekata: izračunate kolone i mjereOba koegzistiraju unutar istog modela, ali svako ima drugačiju svrhu i drugačije se procjenjuje, nešto što bi trebalo biti vrlo jasno kako se ne bi miješali koncepti.

Osim toga, DAX uključuje tekstualne, datumske i vremenske, logičke, matematičke, funkcije filtera i "vremenske inteligencije"kao i funkcije koje kao rezultat vraćaju kompletne tabele. Ove posljednje funkcije su jedna od glavnih razlika u odnosu na Excel, gdje formule ne vraćaju tabele kao takve, već najviše nizove unutar raspona ćelija.

Pregled DAX formula i trake formula

DAX formule slijede strukturu vrlo sličnu onoj u Excelu: Počinju znakom jednakosti (=), a zatim se piše izraz ili funkcija sa svojim argumentimaMožete kombinovati operatore, funkcije, reference kolona i tabela itd., baš kao što biste to radili prilikom kreiranja složene formule u tabeli.

Međutim, postoje važne nijanse. DAX nikada ne radi s referencama tipa A1:C10Umjesto toga, referenca se odnosi na cijele kolone ili tabele, na primjer 'Prodaja' o 'Kalendar'Ovo ima mnogo smisla u tabelarnom modelu, ali vas prisiljava da promijenite način razmišljanja ako dolazite iz svijeta Excela koji je isključivo "ćelija po ćeliju".

Power Pivot ima traka za formule slična onoj u Excelu što znatno pojednostavljuje kreiranje izraza. Sadrži automatsko dovršavanje za funkcije, tabele i kolone: ​​kada počnete kucati naziv tabele ili kolone, pojavljuje se padajuća lista s važećim opcijama, što smanjuje sintaktičke greške i štedi vrijeme.

Da biste napisali imena tabela, jednostavno počnite tipkati i pustite da se izvršava. Automatsko dovršavanje predlaže odgovarajuća imenaZa kolone možete otvoriti zagrade i odabrati kolonu u trenutnoj tabeli ili upisati naziv tabele, a zatim zagrade i odabrati s liste.

Međutim, za razliku od Excela, Power Pivot ne zatvara automatski zagrade Ne odgovara vam. Vaša je odgovornost osigurati da su funkcije ispravno oblikovane, s ispravnim brojem argumenata i potpunim zagradama, inače se formula ne može spremiti ili koristiti.

Gdje se koriste DAX formule: izračunate kolone i mjere

U Power Pivot modelu možete pisati DAX formule u izračunate kolone i unutra mjerenja (također se nazivaju izračunata polja u kontekstu pivot tabela). Iako dijele isti jezik, ponašaju se na vrlo različite načine.

Izračunate kolone u Power Pivotu

Izračunata kolona je novo polje koje dodajete postojećoj tabeli u modeluUmjesto uvoza te vrijednosti iz izvora podataka, definirate DAX formulu koja se procjenjuje red po red. Rezultat se pohranjuje u kolonu za svaki red u tabeli.

Izračunate kolone se primjenjuju ravnomjerno na sve redove: Ne možete imati različitu formulu za svaki red.Za razliku od Excela, gdje dijelove možete ručno prevlačiti i ispuštati, u Power Pivotu se izraz koji definirate automatski procjenjuje za cijelu kolonu i ponovo izračunava kada se podaci ažuriraju ili se prisilno izvrši ponovno izračunavanje modela.

Ova vrsta kolone može biti zasnovana na druge izračunate kolone ili u mjeramaMeđutim, preporučuje se da se ne koristi isti naziv i za mjeru i za kolonu kako bi se izbjegla zabuna prilikom referenciranja. Vrlo je dobra ideja uvijek koristiti punu referencu kolone (Tabela) kako bi se izbjeglo slučajno referenciranje mjere s istim nazivom.

Izračunate kolone su idealne kada vam je potrebno dodatni atributi koje želite koristiti u redovima, kolonama, filterima ili slicerima pivot tabela ili kao ključevi za odnose. Zamislite, na primjer, kolonu "Margina" izračunatu kao - koju zatim možete koristiti za grupiranje ili filtriranje.

Mjere ili izračunata polja

Mjere su, sa svoje strane, proračuni koji se evaluiraju u kontekstu pivot tabele ili vizualizacijeNe pohranjuju se po redovima, već se preračunavaju u hodu za svaku kombinaciju filtera, redova i kolona koja je aktivna u izvještaju.

Una tipično mjerenje Moglo bi biti nešto jednostavno kao:

Ukupna prodaja = SUM(Prodaja)

Ova mjera, postavljena u području Vrijednosti U pivot tabeli, svaka ćelija se procjenjuje prema kontekstu (na primjer, po godini, po proizvodu, po regiji...). Isti izračun vraća različite rezultate ovisno o primijenjenim filterima. i dizajn pivot tabele.

Mjere ne rade ništa dok se ne koriste u izvještaju. Pohranjuju se s modelom podataka i pojavljuju se u listi polja pivot tabela. tako da ih svaki korisnik knjige može koristiti. Oni su fundamentalni za fleksibilne agregatne proračune, kao što su omjeri, procenti doprinosa, kumulativni iznosi, poređenja između perioda itd.

Ključne razlike između DAX funkcija i Excel funkcija

Iako mnoge DAX funkcije podsjećaju na Excel funkcije po imenu i općem ponašanju, Oni nisu jednostavno zamjenjivi.Postoje važne razlike koje utiču na način na koji se formule konstruišu u Power Pivot modelu.

Prvo, DAX ne radi s pojedinačnim ćelijama ili rasponimaČitave kolone ili tabele se uvijek koriste kao referenca. To vas prisiljava da razmišljate više u smislu skupova podataka nego pojedinačnih stavki, što se bolje uklapa u pristup relacijskog modela podataka.

U tom rasponu datuma, DAX vraća stvarne vrijednosti tipa datuma i vremenaIako Excel obično predstavlja datume kao serijske brojeve, ova razlika je u većini slučajeva transparentna, ali je važno imati je na umu prilikom kombinovanja modela ili uvoza podataka iz drugih sistema.

Još jedna ključna tačka je to Mnoge nove DAX funkcije vraćaju potpune tabele (na primjer, FILTER, ALL, VALUES, itd.) ili prihvataju tabele kao argumente. Excel, s druge strane, nema koncept funkcije "koja vraća tabelu" u istom smislu, iako formule niza postoje.

Konačno, u DAX-u se pretpostavlja da Sve vrijednosti u koloni dijele isti tip podatakaAko su tipovi podataka pomiješani, mehanizam za obradu podataka će prisiliti konverziju cijele kolone u tip koji najbolje odgovara svim zapisima, što ponekad može dovesti do iznenađenja ako izvori podataka nisu temeljito provjereni.

Tipovi podataka u DAX-u i tip tabele

Kada uvozite informacije u Power Pivot model, Podaci se pretvaraju u jedan od tipova podataka koje podržava mehanizam. (brojevi, tekst, logičke vrijednosti, datumi i vremena, valuta itd.). Ova vrsta podataka određuje koje su operacije važeće i kako će se formule evaluirati.

Jedna važna nova funkcija u poređenju sa klasičnim Excelom je tip podataka tabeleMnoge DAX funkcije prihvataju cijelu tabelu kao argument i vraćaju drugu tabelu kao rezultat. Na primjer, FILTER uzima tabelu i uslov, a vraća tabelu samo sa redovima koji ispunjavaju uslov.

Kombinovanje funkcija koje vraćaju tabele sa agregacijske funkcije kao što su SUMX, AVERAGEX ili MINXMogu se napraviti visoko sofisticirani proračuni koji rade na dinamički definiranim podskupovima podataka. To rezultira prilagođenim agregacijama koje se prilagođavaju filterima aktivnim u bilo kojem trenutku.

Odnosi, kontekst i relacijski model u Power Pivotu

Prozor Power Pivot je mjesto gdje se gradi relacijski model podataka. Tamo možete uvesti više tabela i kreirati relacije između njih. (na primjer, Prodaja s proizvodima, Prodaja s kalendarom, Prodaja s kupcima itd.). Ovi odnosi su osnova za DAX formule kako bi mogle prelaziti iz jedne tabele u drugu.

Kada su tabele povezane, Možete pisati formule koje sabiraju vrijednosti iz povezane tabele i koristite ih u tabeli iz koje pišete izraz. Također možete kontrolirati koji redovi učestvuju u izračunu primjenom filtera na određene kolone.

Važno je obratiti pažnju Svi redovi u Power Pivot tabeli moraju imati isti broj kolonaSvaka kolona mora održavati konzistentan tip podataka u svim svojim redovima. Ako ključevi relacija imaju neusklađene vrijednosti (praznine, osirotele vrijednosti itd.), formule za pretraživanje i pivot tabele mogu vratiti neočekivane rezultate.

Još jedan fundamentalni koncept je kontekstU DAX-u se prvenstveno koriste termini kontekst reda i kontekst filtera. Kontekst reda je "trenutni" red na kojem se procjenjuje izračunata kolona ili iterator; kontekst filtera je skup aktivnih filtera (iz pivot tabele, slicera, relacija, funkcija poput CALCULATE itd.).

Igranjem s funkcijama poput CALCULATE, ALL, ALLEXCEPT ili FILTER, možete Izmijenite kontekst filtera da biste promijenili način na koji se mjera procjenjuje.Ovo omogućava, na primjer, izračunavanje procenta prodaje proizvoda u odnosu na ukupnu prodaju ili poređenje učinka odjeljenja sa kompanijom u cjelini bez filtera.

Ažuriranje podataka i ponovni izračun DAX formule

U modelu koji koristi složene formule ili velike količine podataka, ključno je razumjeti kako ažuriranje funkcioniše. Važno je razlikovati osvježavanje podataka od ponovnog izračunavanja formula.koji su povezani, ali nezavisni procesi.

Ažuriranje podataka sastoji se od unositi nove zapise u knjigu iz vanjskih izvora (baze podataka(datoteke, online usluge, itd.). Ovo ažuriranje možete pokrenuti ručno kada je potrebno ili ga zakazati ako je knjiga objavljena u SharePoint ili neko drugo kompatibilno okruženje. Često se ovaj proces provodi korištenjem Power Query u Excelu pripremiti i transformirati podatke prije učitavanja u model.

S druge strane, ponovno izračunavanje je proces kojim se DAX formule se ponovo procjenjuju kako bi odražavale promjene u podacima ili u samim izrazima.Za izračunate kolone, ako promijenite formulu, cijela kolona se mora odjednom ponovo izračunati. Za mjere, ponovno izračunavanje se vrši kada se kontekst izmijeni (filteri, polja redova/kolona pivot tabele) ili kada se pivot tabele ručno ažuriraju.

Ovi ponovni izračuni mogu utjecati na performanse, posebno ako se u velikim tabelama koristi mnogo složenih izračunatih kolona ili intenzivnih iterativnih funkcijaStoga je dobra praksa premjestiti većinu logike na mjere umjesto na kolone, kad god je to moguće.

Detekcija i ispravljanje grešaka u DAX formulama

Prilikom pisanja DAX formula, uobičajeno je naići na tri vrste grešaka: sintaktičke greške, semantičke greške i greške u računanjuSvako ima svoje okolnosti i svoj način ispravljanja.

Sintaktičke greške su najjednostavnije: nedostajuće zagrade, pogrešno postavljeni zarezi, pogrešno napisana imena funkcijaitd. Pomoć za automatsko dovršavanje i referenca DAX funkcija vas štede od mnogih ovih zamki.

Semantičke i računske greške se javljaju kada, iako je sintaksa ispravna, Formula radi nešto što nema smisla u kontekstu modela.Na primjer, referenciranje nepostojeće tabele ili kolone, prosljeđivanje pogrešnog broja argumenata funkciji, miješanje nekompatibilnih tipova ili ovisnost o koloni s prethodnim greškama.

U ovim slučajevima, DAX obično označava Cijela kolona je izračunata kao netačna.ne samo određeni red, jer se kolona smatra jedinicom. Ako kolona sadrži samo metapodatke, ali još nije obrađena (nema učitane podatke), prikazat će se sivo, a formule koje zavise od nje neće moći ispravno izvršiti obradu.

Poseban slučaj su vrijednosti NaN (nije broj)Ove vrijednosti se mogu pojaviti, na primjer, prilikom dijeljenja 0 sa 0. Ako kolona sadrži NaN vrijednosti, sortiranje ili klasifikacija ovih vrijednosti može dati čudne rezultate jer se NaN vrijednosti ne mogu uporediti na uobičajen način s drugim brojevima. U takvim slučajevima, preporučljivo je koristiti IF naredbe ili druge logičke funkcije za zamjenu NaN vrijednosti sa 0 ili nekom drugom prihvatljivom numeričkom vrijednošću.

Kompatibilnost s tabelarnim modelima i DirectQuery načinom rada

DAX formule koje kreirate u Power Pivotu su, općenito, kompatibilan sa tabelarnim modelima SQL Server Analysis ServicesTo znači da možete migrirati svoj model na tabelarni server i nastaviti koristiti logiku koju ste već izgradili.

Međutim, kada se tabelarni model implementira u režimu DirectQueryOgraničenja se mogu pojaviti: Neke DAX funkcije nisu direktno podržane na određenim relacijskim bazama podataka. ili mogu vratiti malo drugačije rezultate zbog načina na koji su upiti delegirani.

U ovim scenarijima, važno je pregledati specifičnu dokumentaciju za tabelarni mehanizam i validirati kritične mjere kako bi se potvrdilo da nastavljaju funkcionirati kako se očekuje nakon aktiviranja DirectQueryja.

Praktični scenariji: složeni proračuni sa CALCULATE i filterima

Jedna od prednosti DAX-a je njegova sposobnost izvršavanja složeni proračuni koji se oslanjaju na prilagođene agregacije i dinamičke filtereFunkcije CALCULATE i CALCULATETABLE su ključne za ovu vrstu scenarija.

IZRAČUNAJ omogućava redefinirati kontekst filtera preko kojeg se izraz evaluiraNa primjer, možete zatražiti "zbir prodaje filtriran za određenu godinu, iako pivot tabela prikazuje druge godine" ili "ukupan iznos bez primjene određenih filtera proizvoda".

Svugdje gdje DAX funkcija prihvata tabelu kao argument, Možete proslijediti filtriranu verziju te tabeleOvo se može uraditi korištenjem funkcije FILTER ili specificiranjem uslova unutar funkcije CALCULATE. Ovo omogućava kreiranje mjera koje se prilagođavaju hiljadama kombinacija uslova bez potrebe za kreiranjem međukolona.

Moguće je i selektivno uklanjanje postojećih filtera korištenjem funkcija poput ALL ili ALLEXCEPT. Na primjer, da biste izračunali doprinos određenog preprodavača u odnosu na ukupan broj preprodavača, možete imati mjeru koja dijeli vrijednost u trenutnom kontekstu s vrijednošću u kontekstu „ALL“ (bez filtera po preprodavaču).

U drugim slučajevima, morat ćete koristiti vrijednosti "vanjske petlje"To jest, za pozivanje na prethodni red ili kontekst iteracije. Tu dolaze do izražaja funkcije poput EARLIER, koje omogućavaju do dva nivoa ugniježđenih petlji i vrlo su korisne za kreiranje rangiranja, grupnih ukupnih iznosa ili izračuna koji zavise od konteksta prethodnog reda.

Radim s tekstom, datumima i ključevima u DAX-u.

DAX također nudi mnoge alate za manipulisanje tekstom i datumimaOvo je ključno kada izvori podataka sadrže datume u neobičnim formatima, složene ključeve ili tekstualna polja koja je potrebno pretvoriti u vremenske vrijednosti.

Power Pivot ih ne podržava direktno kompozitni ključevi u odnosimaAko vaš izvor koristi više kolona kao ključeve, u mnogim slučajevima ćete morati kreirati izračunata kolona koja spaja te dijelove u jedan ključ i koristite ga kao relacijsko polje.

Kada datumi dolaze u formatima koje mehanizam ne prepoznaje (na primjer, datum u neobičnom regionalnom formatu ili cijeli broj poput 01032009 uvezen kao tekst), možete kreirati formule poput ove:

=DATE(DESNO(,4), LIJEVO(,2), SREDINA(,3,2))

Sa ovom vrstom izražavanja, Rekonstruišete važeći SQL Server datum iz fragmenata izvučenih iz stringa, što vam zatim omogućava korištenje funkcija vremenske inteligencije bez problema.

Takođe možete promjena tipova podataka pomoću formulaPomnožite sa 1,0 da biste datume ili numeričke nizove pretvorili u brojeve ili ih spojite s praznim nizom da biste broj ili datum pretvorili u tekst. Osim toga, postoje i specifične funkcije za kontrolu tipa povratne vrijednosti (skraćivanje decimala, forsiranje cijelih brojeva itd.).

Uslovne vrijednosti i rukovanje greškama u kolonama i mjerama

Baš kao i u Excelu, DAX uključuje funkcije za vrati rezultate na osnovu uslova i elegantno rješavati greške. Na primjer, možete označiti preprodavače kao „Preferirane“ ili „Vrijednosne“ na osnovu njihovog godišnjeg obima prodaje pomoću ugniježđenih IF naredbi.

Međutim, u izračunatoj koloni, Ne možete si priuštiti da neki redovi imaju greške, a drugi ne.Ako jedan red proizvede grešku, cijela kolona se označava kao netačna. Ovo zahtijeva strožiju kontrolu grešaka nego u konvencionalnoj tabeli.

Da biste spriječili da jednostavno dijeljenje nulom ili praznom vrijednošću uzrokuje pad cijele kolone, preporučuje se umotajte osjetljive operacije u prethodne provjere korištenjem IF i informacionih funkcija, uvijek vraćajući valjanu vrijednost čak i kada je kombinacija podataka neobična.

Prilikom izrade modela, to može biti korisno Neka se greške pojave na početku kako biste ih mogli locirati i ispraviti.Ali kada ga objavite za druge korisnike, važno je osigurati da su formule sigurne i da se u pivot tabelama ili vizualizacijama nikada ne pojavljuje poruka o grešci.

Vremenska inteligencija: kumulativni iznosi, poređenja i prilagođeni periodi

Funkcije vremenske inteligencije su jedna od velikih prednosti DAX-a. Omogućavaju vam rad s rasponima datuma, izračunavanje kumulativnih iznosa, poređenje perioda i generiranje prilagođenih vremenskih prozora. relativno lako, pod uslovom da imate dobro konfigurisanu tabelu kalendara.

Mjere se mogu kreirati kumulativna prodaja po danu, mjesecu, kvartalu ili godiniIzračunajte početne i završne saldo za svaki period ili uporedite prodaju iz jedne godine u prethodnu godinu, iz kvartala u kvartal itd., koristeći specifične vremenske funkcije.

Osim toga, možete se oporaviti prilagođeni skupovi datumakao što su "prvih 15 dana nakon početka promocije" ili "isti period prošle godine", a zatim taj skup proslijediti funkciji koja agregira podatke tokom tog određenog vremenskog perioda.

Funkcije kao što su PARALLELPERIOD i druge povezane s paralelnim periodima Oni olakšavaju poređenje između vremenski pomjerenih intervala.Na primjer, da se analizira da li je kampanja poboljšala rezultate u poređenju s istim vremenskim periodom u drugoj godini.

Rangiranje i poređenje vrijednosti: top N i dinamičko rangiranje

Kada trebaš pokazati samo najrelevantniji elementi (na primjer, 10 najprodavanijih proizvoda), imate dva glavna puta: koristite Excelove funkcije filtriranja na pivot tabeli ili izgradite dinamičko rangiranje pomoću DAX-a.

Excel nudi filtere tipa "Top 10" u pivot tabelama, vrlo jednostavno za postavljanje Za prikaz samo stavki iznad ili ispod datog numeričkog polja. Možete filtrirati po broju stavki, kumulativnom procentu ili zbiru vrijednosti.

Problem s ovim pristupom je taj što Filter je isključivo za prezentaciju.Ako se osnovni podaci promijene, potrebno je ručno ažurirati pivot tabelu da bi se filter ispravno odrazio. Nadalje, to rangiranje ne možete ponovo koristiti kao dio drugih DAX formula.

Alternativa je stvaranje jednog izračunata kolona ili mjera koja dodjeljuje rang svakom elementu pomoću DAX-a. Ova opcija je računski skuplja, ali ima prednosti: rangiranje se dinamički preračunava i može se koristiti u alatima za rezanje podataka, što korisniku omogućava da odabere želi li vidjeti prvih 5, prvih 10, prvih 50 itd.

Međutim, u modelima sa milionima redova, Dinamičko rangiranje može biti nezgrapno. i potrebno je procijeniti da li su troškovi performansi kompenzirani funkcionalnom koristi koju pružaju.

Najbolje prakse pri dizajniranju DAX modela u Power Pivotu

Da bi DAX model u Power Pivotu bio održiv i dobro funkcionisao, nije dovoljno da formule "rade". Preporučljivo je slijediti niz dobrih praksi koje prave veliku razliku u stvarnim projektima.

Jedna preporuka koja se ponavlja je Dajte prioritet mjerama u odnosu na izračunate kolone kada je izračun zapravo dinamička agregacija a ne fiksni atribut. Izračunate kolone zauzimaju memoriju i ponovo se izračunavaju odjednom, dok se mjere procjenjuju samo kada je to potrebno.

Takođe je veoma korisna korištenje varijabli u DAX-u (VAR) Da biste pojednostavili složene formule, izbjegavajte ponavljanje istog izračuna više puta i poboljšajte čitljivost. Ovo poboljšava i performanse i razumijevanje modela kada ga neko drugi pregleda.

Konačno, jasna imena i minimalna interna dokumentacija čine svu razliku. Dajte opisna imena mjerama i kolonamaIzbjegavajte nejasne skraćenice i dokumentirajte najvažnije formule. Ovo smanjuje krivulju učenja za nove korisnike i štedi vam glavobolje kada se vratite modelu mjesecima kasnije.

Savladavanje DAX-a unutar Power Pivot-a ne znači pamćenje svih funkcija, već razumijevanje kako formule interaguju s relacijskim modelom, kontekstom filtera i ažuriranjima podataka. Sa solidnim osnovama u izračunatim kolonama, mjerama, vremenskim funkcijama, rukovanju greškama i dobrim dizajnerskim praksamaVaši tabelarni modeli postaju fleksibilniji, mnogo lakši za analizu i, prije svega, sposobni da odgovore na složena poslovna pitanja jednostavnim prevlačenjem nekoliko polja u pivot tabelu.