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

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

U Power Pivotu, DAX je srce tabličnih modela: Koristi se za stvaranje izračunatih stupaca i mjera koje hrane pivot tablice i grafikoneOmogućuje vam igranje s kontekstom filtera, rad s datumima, rukovanje pogreškama i usporedbe u el tiempo I još mnogo toga. Pogledajmo pobliže kako se sve ovo uklapa u DAX modele u Power Pivotu i kako to iskoristiti bez pretjeravanja.

Što 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 je funkcija definirati prilagođene izračune koji se primjenjuju na tablice i stupce u vašem Power Pivot modelu.

Iako vizualno može nalikovati Excel formule, DAX je dizajniran za rad s relacijskim podacima i dinamičkim agregacijama.To znači da se besprijekorno integrira s tablicama povezanim odnosima, pivot tablicama i složenim modelima gdje se kontekst filtera mijenja ovisno o poljima koja koristite u retcima, stupcima ili rezačima.

Unutar Power Pivot modela možete koristiti DAX za Dvije glavne vrste objekata: izračunati stupci i mjereOba koegzistiraju unutar istog modela, ali svaki ima drugačiju svrhu i drugačije se ocjenjuje, 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 filtriranja i "vremenske inteligencije"kao i funkcije koje kao rezultat vraćaju potpune tablice. Ove potonje funkcije jedna su od glavnih razlika u usporedbi s Excelom, gdje formule ne vraćaju tablice 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 kombinirati operatore, funkcije, reference stupaca i tablica itd., baš kao što biste to učinili prilikom izrade složene formule u proračunskoj tablici.

Međutim, postoje važne nijanse. DAX nikada ne radi s referencama tipa A1:C10Umjesto toga, referenca se odnosi na cijele stupce ili tablice, na primjer 'Prodajni' o 'Kalendar'To ima puno smisla u tabličnom modelu, ali vas prisiljava da promijenite način razmišljanja ako dolazite iz isključivo "ćelijskog" svijeta Excela.

Power Pivot ima traka s formulama slična onoj u Excelu što uvelike pojednostavljuje stvaranje izraza. Sadrži automatsko dovršavanje za funkcije, tablice i stupce: dok počinjete tipkati naziv tablice ili stupca, pojavljuje se padajući popis s valjanim opcijama, smanjujući sintaktičke pogreške i štedeći vrijeme.

Za pisanje imena tablica, jednostavno počnite tipkati i pustite da se izvršava. Samodovršavanje predlaže odgovarajuća imenaZa stupce možete otvoriti zagradu i odabrati stupac u trenutnoj tablici ili upisati naziv tablice nakon čega slijede zagrade i odabrati s popisa.

Međutim, za razliku od Excela, Power Pivot ne zatvara automatski zagrade Ne podudaraju se s njima za vas. 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čunati stupci i mjere

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

Izračunati stupci u Power Pivotu

Izračunati stupac je novo polje koje dodajete postojećoj tablici u modeluUmjesto uvoza te vrijednosti iz izvora podataka, definirate DAX formulu koja se procjenjuje redak po redak. Rezultat se pohranjuje u stupac za svaki redak u tablici.

Izračunati stupci se primjenjuju ravnomjerno na sve retke: Ne možete imati različitu formulu za svaki redak.Za razliku od Excela, gdje dijelove možete ručno povlačiti i ispuštati, u Power Pivotu se izraz koji definirate automatski procjenjuje za cijeli stupac i ponovno izračunava kada se podaci ažuriraju ili se prisilno izvrši ponovni izračun modela.

Ova vrsta stupca može se temeljiti na drugim izračunatim stupcima ili u mjeramaMeđutim, preporučuje se da se isti naziv ne koristi i za mjeru i za stupac kako bi se izbjegla zabuna prilikom referenciranja. Vrlo je dobra ideja uvijek koristiti punu referencu stupca (Tablica) kako bi se izbjeglo slučajno referenciranje mjere s istim nazivom.

Izračunati stupci idealni su kada vam je potrebno dodatni atributi koje želite koristiti u retcima, stupcima, filterima ili rezačima pivot tablica ili kao ključevi za odnose. Zamislite, na primjer, stupac "Margina" izračunat kao - koji zatim možete koristiti za grupiranje ili filtriranje.

Mjere ili izračunata polja

Mjere su, sa svoje strane, izračuni koji se procjenjuju u kontekstu pivot tablice ili vizualizacijeNe pohranjuju se po retku, već se preračunavaju u hodu za svaku kombinaciju filtera, redaka i stupaca koja je aktivna u izvješću.

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

Ukupna prodaja = SUM(Prodaja)

Ova mjera, postavljena u području vrijednosti U pivot tablici svaka se ćelija 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 tablice.

Mjere ne čine ništa dok se ne koriste u izvješću. Pohranjuju se s modelom podataka i pojavljuju se u popisu polja pivot tablica. tako da ih svaki korisnik knjige može koristiti. Temeljni su za fleksibilne agregatne izračune, kao što su omjeri, postoci doprinosa, kumulativni iznosi, usporedbe između razdoblja itd.

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

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

Prvi DAX ne radi s pojedinačnim ćelijama ili rasponimaCijeli stupci ili tablice uvijek se 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 rasponu datuma, DAX vraća stvarne vrijednosti tipa datuma i vremenaIako Excel obično predstavlja datume kao serijske brojeve, ta je razlika u većini slučajeva transparentna, ali važno ju je imati na umu prilikom kombiniranja modela ili uvoza podataka iz drugih sustava.

Druga ključna točka je ta Mnoge nove DAX funkcije vraćaju potpune tablice (na primjer, FILTER, ALL, VALUES itd.) ili prihvaćaju tablice kao argumente. Excel, s druge strane, nema koncept funkcije "koja vraća tablicu" u istom smislu, iako formule polja postoje.

Konačno, u DAX-u se pretpostavlja da Sve vrijednosti u stupcu dijele isti tip podatakaAko su tipovi podataka pomiješani, mehanizam podataka će prisiliti pretvorbu cijelog stupca 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 tablice

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

Jedna važna nova značajka u usporedbi s klasičnim Excelom je tip podataka tabliceMnoge DAX funkcije prihvaćaju cijelu tablicu kao argument i kao rezultat vraćaju drugu tablicu. Na primjer, FILTER uzima tablicu i uvjet te vraća tablicu samo s retcima koji zadovoljavaju uvjet.

Kombiniranje funkcija koje vraćaju tablice s agregacijske funkcije kao što su SUMX, AVERAGEX ili MINXMogu se izraditi vrlo sofisticirani izrač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 tablica i stvoriti odnose između njih. (na primjer, Prodaja s proizvodima, Prodaja s kalendarom, Prodaja s kupcima itd.). Ti su odnosi osnova za DAX formule kako bi se mogle prebacivati ​​iz jedne tablice u drugu.

Kada su tablice povezane, Možete pisati formule koje zbrajaju vrijednosti iz povezane tablice i koristite ih u tablici iz koje pišete izraz. Također možete kontrolirati koji retci sudjeluju u izračunu primjenom filtara na određene stupce.

Važno je obratiti pažnju Svi retci u Power Pivot tablici moraju imati isti broj stupacaSvaki stupac mora održavati dosljedan tip podataka u svim svojim retcima. Ako ključevi odnosa imaju neusklađene vrijednosti (praznine, osirotele vrijednosti itd.), formule za pretraživanje i pivot tablice mogu vratiti neočekivane rezultate.

Drugi temeljni koncept je kontekstU DAX-u se prvenstveno koriste pojmovi kontekst retka i kontekst filtera. Kontekst retka je "trenutni" redak na kojem se procjenjuje izračunati stupac ili iterator; kontekst filtera je skup aktivnih filtera (iz pivot tablice, rezača, odnosa, funkcija poput CALCULATE itd.).

Igranjem s funkcijama poput CALCULATE, ALL, ALLEXCEPT ili FILTER, možete Izmijenite kontekst filtera kako biste promijenili način na koji se mjera procjenjuje.To omogućuje, na primjer, izračunavanje postotka prodaje proizvoda u odnosu na ukupnu prodaju ili usporedbu učinka odjela s tvrtkom 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 funkcionira. Važno je razlikovati osvježavanje podataka od ponovnog izračuna formula.koji su povezani, ali neovisni procesi.

Ažuriranje podataka sastoji se od unositi nove zapise u knjigu iz vanjskih izvora (baza 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 pomoću 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 ponovno procjenjuju kako bi odražavale promjene u podacima ili u samim izrazima.Za izračunate stupce, ako promijenite formulu, cijeli stupac mora se odjednom ponovno izračunati. Za mjere, ponovni izračun se događa kada se kontekst izmijeni (filteri, polja redaka/stupaca zaokretne tablice) ili kada se zaokretne tablice ručno ažuriraju.

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

Otkrivanje i ispravljanje pogrešaka u DAX formulama

Prilikom pisanja DAX formula uobičajeno je naići na tri vrste pogrešaka: sintaktičke pogreške, semantičke pogreške i pogreške u izračunuSvaki 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 spašavaju vas od mnogih od ovih zamki.

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

U tim slučajevima, DAX obično označava Cijeli stupac je izračunat kao netočan.ne samo određeni redak, jer se stupac smatra jedinicom. Ako stupac sadrži samo metapodatke, ali još nije obrađen (nema učitanih podataka), prikazat će se sivo, a formule koje ovise o njemu neće se moći ispravno izračunati.

Poseban slučaj su vrijednosti NaN (Nije broj)Ove vrijednosti mogu se pojaviti, na primjer, pri dijeljenju 0 s 0. Ako stupac sadrži NaN vrijednosti, sortiranje ili klasificiranje tih vrijednosti može dati čudne rezultate jer se NaN vrijednosti ne mogu usporediti 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 s 0 ili nekom drugom prihvatljivom numeričkom vrijednošću.

Kompatibilnost s tabličnim modelima i DirectQuery načinom rada

DAX formule koje stvarate u Power Pivotu su, općenito, kompatibilan s tabličnim modelima SQL Server Analysis ServicesaTo znači da možete migrirati svoj model na tablični poslužitelj i nastaviti koristiti logiku koju ste već izgradili.

Međutim, kada se tablični model implementira u načinu rada DirectQueryOgraničenja se mogu pojaviti: Neke DAX funkcije nisu izravno podržane na određenim relacijskim bazama podataka. ili mogu vratiti malo drugačije rezultate zbog načina delegiranja upita.

U tim scenarijima važno je pregledati specifičnu dokumentaciju za tablični mehanizam i potvrditi kritične mjere kako bi se potvrdilo da nastavljaju funkcionirati kako se očekuje nakon aktiviranja DirectQueryja.

Praktični scenariji: složeni izračuni s funkcijom CALCULATE i filterima

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

IZRAČUN omogućuje redefinirati kontekst filtera preko kojeg se izraz procjenjujeNa primjer, možete zatražiti "zbroj prodaje filtriran za određenu godinu, iako pivot tablica prikazuje druge godine" ili "ukupno bez primjene određenih filtera proizvoda".

Gdje god DAX funkcija prihvaća tablicu kao argument, Možete proslijediti filtriranu verziju te tabliceTo se može učiniti korištenjem FILTER-a ili određivanjem uvjeta unutar CALCULATE-a. To omogućuje izradu mjera koje se prilagođavaju tisućama kombinacija uvjeta bez potrebe za stvaranjem međustupaca.

Također je moguće selektivno uklanjanje postojećih filtera pomoću funkcija poput ALL ili ALLEXCEPT. Na primjer, za izračun doprinosa 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 redak ili kontekst iteracije. Tu dolaze do izražaja funkcije poput EARLIER, koje omogućuju do dvije razine ugniježđenih petlji i vrlo su korisne za stvaranje rangiranja, grupnih ukupnih zbrojeva ili izračuna koji ovise o kontekstu prethodnog retka.

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

DAX također nudi mnoge alate za manipuliranje tekstom i datumimaTo 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 izravno složeni ključevi u odnosimaAko vaš izvor koristi više stupaca kao ključeve, u mnogim slučajevima morat ćete stvoriti izračunati stupac koji spaja te dijelove u jedan ključ i koristiti 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 izraditi formule poput ove:

=INFORMACIJE(DESNO(;4), LIJEVO(;2), SREDINA(;3,2))

S ovom vrstom izražavanja, Rekonstruirate valjani datum SQL Servera iz fragmenata izdvojenih iz niza, što vam zatim omogućuje korištenje funkcija vremenske inteligencije bez problema.

Također možete promjena tipova podataka pomoću formulaPomnožite s 1,0 za pretvaranje datuma ili numeričkih nizova u brojeve ili spojite s praznim nizom za pretvaranje broja ili datuma u tekst. Osim toga, postoje specifične funkcije za kontrolu tipa povrata (skraćivanje decimala, prisilno prikazivanje cijelih brojeva itd.).

Uvjetne vrijednosti i rukovanje greškama u stupcima i mjerama

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

Međutim, u izračunatom stupcu, Ne možete si priuštiti da neki retci imaju greške, a drugi ne.Ako jedan redak proizvede grešku, cijeli stupac se označava kao netočan. To zahtijeva strožu kontrolu pogrešaka nego u konvencionalnoj proračunskoj tablici.

Kako bi se spriječilo da jednostavno dijeljenje s nulom ili prazna vrijednost uzrokuje rušenje cijelog stupca, preporučuje se umotajte osjetljive operacije u prethodne provjere korištenjem IF i informacijskih funkcija, uvijek vraćajući valjanu vrijednost čak i kada je kombinacija podataka neobična.

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

Vremenska inteligencija: kumulativni zbrojevi, usporedbe i prilagođena razdoblja

Značajke vremenske inteligencije jedna su od velikih prednosti DAX-a. Omogućuju vam rad s datumskim rasponima, izračunavanje kumulativnih ukupnih iznosa, usporedbu razdoblja i generiranje prilagođenih vremenskih prozora. s relativnom lakoćom, pod uvjetom da imate dobro konfiguriranu kalendarsku tablicu.

Mjere se mogu kreirati kumulativna prodaja po danu, mjesecu, kvartalu ili godiniIzračunajte početno i završno stanje za svako razdoblje ili usporedite prodaju iz jedne godine u prethodnu godinu, iz tromjesečja u tromjesečje 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 "isto razdoblje prošle godine", a zatim taj skup proslijediti funkciji koja agregira podatke tijekom tog određenog datumskog prozora.

Funkcije poput PARALLELPERIOD i druge povezane s paralelnim periodima Omogućuju usporedbu između vremenski pomaknutih intervala.Na primjer, za analizu je li kampanja poboljšala rezultate u usporedbi s istim vremenskim razdobljem u drugoj godini.

Rangiranje i usporedba vrijednosti: top N i dinamičko rangiranje

Kada trebate pokazati samo najrelevantniji elementi (na primjer, 10 najprodavanijih proizvoda), imate dva glavna puta: koristiti Excelove funkcije filtriranja na pivot tablici ili izgraditi dinamičko rangiranje pomoću DAX-a.

Excel nudi filtere tipa "Top 10" u pivot tablicama, vrlo jednostavno za postavljanje Za prikaz samo stavki iznad ili ispod zadanog numeričkog polja. Možete filtrirati prema broju stavki, kumulativnom postotku ili zbroju vrijednosti.

Problem s ovim pristupom je taj što Filter je isključivo za prezentaciju.Ako se temeljni podaci promijene, morate ručno ažurirati pivot tablicu kako bi se filtar ispravno odrazio. Nadalje, to rangiranje ne možete ponovno upotrijebiti kao dio drugih DAX formula.

Alternativa je stvoriti jedan izračunati stupac ili mjera koja dodjeljuje rang svakom elementu pomoću DAX-a. Ova je opcija računalno skuplja, ali ima prednosti: rangiranje se dinamički preračunava i može se koristiti u alatima za rezanje podataka, što korisniku omogućuje odabir želi li vidjeti prvih 5, prvih 10, prvih 50 itd.

Međutim, u modelima s milijunima redaka, Dinamičko rangiranje može biti nezgrapno. i potrebno je procijeniti jesu li 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 funkcionirao, nije dovoljno da formule "rade". Preporučljivo je slijediti niz dobrih praksi koji čine veliku razliku u stvarnim projektima.

Jedna preporuka koja se ponavlja je dati prioritet mjerama nad izračunatim stupcima kada je izračun zapravo dinamička agregacija a ne fiksni atribut. Izračunati stupci zauzimaju memoriju i ponovno se izračunavaju odjednom, dok se mjere procjenjuju samo kada je potrebno.

Također je vrlo korisno korištenje varijabli u DAX-u (VAR) Kako biste pojednostavili složene formule, izbjegavajte ponavljanje istog izračuna više puta i poboljšajte čitljivost. To pomaže i performansama i razumijevanju modela kada ga netko drugi pregledava.

Konačno, jasna imena i minimalna interna dokumentacija čine svu razliku. Dajte opisne nazive mjerama i stupcimaIzbjegavajte nejasne kratice i dokumentirajte najvažnije formule. To smanjuje krivulju učenja za nove korisnike i štedi vam glavobolje kada se vratite modelu mjesecima kasnije.

Savladavanje DAX-a unutar Power Pivota ne znači pamćenje svih funkcija, već razumijevanje načina na koji formule djeluju s relacijskim modelom, kontekstom filtera i ažuriranjima podataka. S čvrstim temeljima u izračunatim stupcima, mjerama, vremenskim funkcijama, rukovanju pogreškama i dobrim dizajnerskim praksamaVaši tablični modeli postaju fleksibilniji, puno lakši za analizu i, prije svega, sposobni odgovoriti na složena poslovna pitanja jednostavnim povlačenjem nekoliko polja u pivot tablicu.