Modeli DAX v Power Pivotu: formule, kontekst in najboljše prakse

Če delate s podatki v Excelu in PowerPivotPrej ali slej se boste srečali z DAX-om. Čeprav se ime »Izrazi za analizo podatkov« sliši tehnično in nekoliko zastrašujočeResničnost je, da z dobrim razumevanjem osnov postane zelo priročno in močno orodje za modeliranje informacij.

V Power Pivotu je DAX srce tabelaričnih modelov: Uporablja se za ustvarjanje izračunanih stolpcev in mer, ki napajajo vrtilne tabele in grafikoneOmogoča vam igranje s kontekstom filtrov, delo z datumi, obravnavo napak in primerjave v čas In še veliko več. Oglejmo si podrobneje, kako se vse to ujema z modeli DAX v Power Pivotu in kako to izkoristiti, ne da bi pretiravali.

Kaj je DAX in zakaj je ključen v modelih Power Pivot?

DAX (izrazi za analizo podatkov) je predvsem jezik formul, zasnovan za podatkovne modeleni jezik programiranje Klasična. Njegova funkcija je definirati izračune po meri, ki se uporabijo za tabele in stolpce v vašem modelu Power Pivot.

Čeprav vizualno lahko spominja na Excelove formule, DAX je zasnovan za delo z relacijskimi podatki in dinamičnimi agregacijami.To pomeni, da se brezhibno integrira s tabelami, ki so povezane z relacijami, vrtilnimi tabelami in kompleksnimi modeli, kjer se kontekst filtra spreminja glede na polja, ki jih uporabljate v vrsticah, stolpcih ali razčlenjevalnikih.

Znotraj modela Power Pivot lahko uporabite DAX za Dve glavni vrsti objektov: izračunani stolpci in mereOba sobivata znotraj istega modela, vendar imata vsak drugačen namen in se ocenjujeta drugače, kar bi moralo biti zelo jasno, da ne bi prišlo do mešanja konceptov.

Poleg tega DAX vključuje besedilne, datumske in časovne, logične, matematične, filtrirne in "časovne inteligence" funkcijekot tudi funkcije, ki kot rezultat vrnejo celotne tabele. Slednje funkcije so ena glavnih razlik v primerjavi z Excelom, kjer formule ne vrnejo tabel kot takih, temveč največ polja znotraj obsegov celic.

Pregled formul DAX in vnosne vrstice

Formule DAX imajo zelo podobno strukturo kot Excel: Začnejo se z znakom enačaja (=), nato pa se zapiše izraz ali funkcija z argumenti.Operatorje, funkcije, sklice na stolpce in tabele itd. lahko kombinirate tako, kot bi to storili pri ustvarjanju kompleksne formule v preglednici.

Vendar pa obstajajo pomembne nianse. DAX nikoli ne deluje s sklici tipa A1:C10.Namesto tega se sklicuje na celotne stolpce ali tabele, na primer 'Prodaja' o 'Koledar'To je v tabelarnem modelu zelo smiselno, vendar vas prisili, da spremenite svoj način razmišljanja, če prihajate iz povsem "celično" sveta Excela.

Power Pivot ima vrstica s formulami, podobna tisti v Excelu kar močno poenostavi ustvarjanje izrazov. Ima funkcijo samodokončanja za funkcije, tabele in stolpce: ko začnete tipkati ime tabele ali stolpca, se prikaže spustni seznam z veljavnimi možnostmi, kar zmanjša sintaktične napake in prihrani čas.

Če želite napisati imena tabel, preprosto začnite tipkati in pustite, da se program izvaja. Samodokončanje predlaga ujemajoča se imenaZa stolpce lahko odprete oklepaj in izberete stolpec v trenutni tabeli ali pa vnesete ime tabele, ki mu sledijo oklepaji, in izberete s seznama.

Vendar pa za razliko od Excela Power Pivot ne zapre oklepajev samodejno Ne ujema se z njimi. Vaša odgovornost je, da zagotovite, da so funkcije pravilno oblikovane, s pravilnim številom argumentov in popolnimi oklepaji, sicer formule ni mogoče shraniti ali uporabiti.

Kje se uporabljajo formule DAX: izračunani stolpci in mere

V modelu Power Pivot lahko formule DAX zapišete v izračunani stolpci in ukrepov (v kontekstu vrtilnih tabel imenovana tudi izračunana polja). Čeprav uporabljajo isti jezik, se obnašajo zelo različno.

Izračunani stolpci v Power Pivotu

Izračunani stolpec je novo polje, ki ga dodate obstoječi tabeli v modeluNamesto uvoza te vrednosti iz vira podatkov definirate formulo DAX, ki se ovrednoti vrstico za vrstico. Rezultat se shrani v stolpec za vsako vrstico v tabeli.

Izračunani stolpci se enakomerno uporabijo za vse vrstice: Za vsako vrstico ne morete imeti različne formule.Za razliko od Excela, kjer lahko dele ročno povlečete in spustite, se v Power Pivotu izraz, ki ga definirate, samodejno ovrednoti za celoten stolpec in ponovno izračuna, ko so podatki posodobljeni ali je vsiljen ponovni izračun modela.

Ta vrsta stolpca lahko temelji na drugih izračunanih stolpcih ali v merahVendar pa je priporočljivo, da ne uporabite istega imena za mero in stolpec, da se izognete zmedi pri sklicevanju. Zelo dobro je, da vedno uporabite celotno referenco stolpca (Tabela), da se izognete nenamernemu sklicevanju na mero z istim imenom.

Izračunani stolpci so idealni, kadar potrebujete dodatni atributi, ki jih želite uporabiti v vrsticah, stolpcih, filtrih ali razčlenjevalnikih vrtilnih tabel ali kot ključi za relacije. Pomislite na primer na stolpec »Margin«, izračunan kot –, ki ga lahko nato uporabite za združevanje ali filtriranje.

Mere ali izračunana polja

Ukrepi so pač taki, izračuni, ki se ovrednotijo ​​v kontekstu vrtilne tabele ali vizualizacijeNe shranjujejo se po vrsticah, temveč se sproti preračunavajo za vsako kombinacijo filtrov, vrstic in stolpcev, ki je aktivna v poročilu.

A tipična meritev Lahko bi bilo nekaj tako preprostega, kot je:

Skupna prodaja = SUM(Prodaja)

Ta ukrep, postavljen na območju Vrednosti V vrtilni tabeli se vsaka celica ovrednoti glede na kontekst (na primer po letu, po izdelku, po regiji ...). Isti izračun vrne različne rezultate, odvisno od uporabljenih filtrov. in zasnova vrtilne mize.

Mere ne naredijo ničesar, dokler niso uporabljene v poročilu. Shranjeni so skupaj s podatkovnim modelom in se prikažejo na seznamu polj vrtilnih tabel. tako da jih lahko uporablja vsak uporabnik knjige. So temeljni za prilagodljive agregatne izračune, kot so razmerja, odstotki prispevkov, kumulativne vsote, primerjave med obdobji itd.

Ključne razlike med funkcijami DAX in funkcijami Excela

Čeprav so številne funkcije DAX po imenu in splošnem delovanju podobne funkcijam Excela, Niso preprosto zamenljivi.Obstajajo pomembne razlike, ki vplivajo na to, kako so formule sestavljene v modelu Power Pivot.

prvo, DAX ne deluje s posameznimi celicami ali obsegiKot referenca se vedno uporabljajo celi stolpci ali tabele. To vas sili, da razmišljate bolj v smislu naborov podatkov kot posameznih elementov, kar se bolj ujema s pristopom relacijskega podatkovnega modela.

V tem časovnem obdobju DAX vrne vrednosti tipa realnega datuma in časaČeprav Excel običajno predstavlja datume kot serijske številke, je ta razlika v večini primerov očitna, vendar jo je pomembno upoštevati pri kombiniranju modelov ali uvozu podatkov iz drugih sistemov.

Druga ključna točka je, da Številne nove funkcije DAX vrnejo celotne tabele (na primer FILTER, VSE, VREDNOSTI itd.) ali pa sprejemajo tabele kot argumente. Excel pa nima koncepta funkcije, »ki vrne tabelo« v istem smislu, čeprav obstajajo formule polja.

Končno se v DAX-u predpostavlja, da Vse vrednosti v stolpcu imajo isti podatkovni tipČe so podatkovni tipi mešani, bo podatkovni mehanizem prisilil pretvorbo celotnega stolpca v tip, ki najbolje ustreza vsem zapisom, kar lahko včasih povzroči presenečenja, če viri podatkov niso temeljito preverjeni.

Tipi podatkov v DAX in tip tabele

Ko uvozite podatke v model Power Pivot, Podatki se pretvorijo v enega od tipov podatkov, ki jih podpira mehanizem. (številke, besedilo, logične vrednosti, datumi in časi, valuta itd.). Ta vrsta podatkov določa, katere operacije so veljavne in kako bodo formule ovrednotene.

Ena pomembna nova funkcija v primerjavi s klasičnim Excelom je podatkovni tip tabeleŠtevilne funkcije DAX sprejmejo celotno tabelo kot argument in kot rezultat vrnejo drugo tabelo. Na primer, FILTER vzame tabelo in pogoj ter vrne tabelo samo z vrsticami, ki izpolnjujejo pogoj.

Združevanje funkcij, ki vračajo tabele z agregacijske funkcije, kot so SUMX, AVERAGEX ali MINXZgraditi je mogoče zelo sofisticirane izračune, ki delujejo na dinamično definiranih podmnožicah podatkov. To ima za posledico prilagojene agregacije, ki se prilagajajo filtrom, ki so aktivni v danem trenutku.

Odnosi, kontekst in relacijski model v Power Pivotu

V oknu Power Pivot je zgrajen relacijski podatkovni model. Tam lahko uvozite več tabel in ustvarite relacije med njimi. (na primer Prodaja z izdelki, Prodaja s koledarjem, Prodaja s strankami itd.). Ta razmerja so osnova za formule DAX, da lahko skačejo iz ene tabele v drugo.

Ko so tabele povezane, Lahko napišete formule, ki seštevajo vrednosti iz povezane tabele in jih uporabite v tabeli, iz katere pišete izraz. Z uporabo filtrov za določene stolpce lahko nadzorujete tudi, katere vrstice sodelujejo v izračunu.

Pomembno je posvetiti pozornost Vse vrstice v tabeli Power Pivot morajo imeti enako število stolpcevVsak stolpec mora v vseh svojih vrsticah ohranjati dosleden podatkovni tip. Če imajo ključi relacij neujemajoče se vrednosti (prazne vrednosti, osirotele vrednosti itd.), lahko iskalne formule in vrtilne tabele vrnejo nepričakovane rezultate.

Drug temeljni koncept je kontekstuV jeziku DAX se primarno uporabljata izraza kontekst vrstice in kontekst filtra. Kontekst vrstice je »trenutna« vrstica, v kateri se ovrednoti izračunani stolpec ali iterator; kontekst filtra je nabor aktivnih filtrov (iz vrtilne tabele, razčlenjevalnikov, relacij, funkcij, kot je CALCULATE itd.).

Z igranjem s funkcijami, kot so IZRAČUNAJ, VSE, RAZEN ali FILTER, lahko Spremenite kontekst filtra, da spremenite način vrednotenja mere.To omogoča na primer izračun odstotka prodaje izdelka glede na skupno prodajo ali primerjavo uspešnosti oddelka s podjetjem kot celoto brez filtrov.

Posodobitev podatkov in ponovni izračun formule DAX

V modelu, ki uporablja kompleksne formule ali velike količine podatkov, je ključnega pomena razumevanje delovanja posodobitve. Pomembno je razlikovati med osveževanjem podatkov in ponovnim izračunom formul.ki so povezani, a neodvisni procesi.

Posodobitev podatkov je sestavljena iz v knjigo vnesite nove zapise iz zunanjih virov (podatkovnih baz(datoteke, spletne storitve itd.). To posodobitev lahko po potrebi zaženete ročno ali pa jo načrtujete, če je knjiga objavljena v SharePoint ali drugo združljivo okolje. Pogosto se ta postopek izvaja z uporabo Power Query v Excelu pripraviti in pretvoriti podatke, preden jih naložimo v model.

Po drugi strani pa je preračun postopek, s katerim Formule DAX so ponovno ovrednotene, da odražajo spremembe v podatkih ali v samih izrazih.Pri izračunanih stolpcih je treba v primeru spremembe formule naenkrat preračunati celoten stolpec. Pri merah se preračunavanje izvede, ko se spremeni kontekst (filtri, polja vrstic/stolpcev vrtilne tabele) ali ko se vrtilne tabele ročno posodobijo.

Ti ponovni izračuni lahko vplivajo na delovanje, zlasti če se v velikih tabelah uporablja veliko kompleksnih izračunanih stolpcev ali intenzivnih iterativnih funkcijZato je dobra praksa, da večino logike, kadar koli je to mogoče, premaknete v mere namesto v stolpce.

Zaznavanje in popravljanje napak v formulah DAX

Pri pisanju formul DAX se pogosto pojavijo tri vrste napak: sintaktične napake, semantične napake in računske napakeVsak ima svoje okoliščine in svoj način popravljanja.

Sintaktične napake so najpreprostejše: manjkajoči oklepaji, napačno postavljene vejice, napačno črkovana imena funkcijitd. Pomoč za samodokončanje in referenca funkcij DAX vas rešita pred mnogimi od teh pasti.

Semantične in računske napake se pojavijo, kadar je sintaksa pravilna, Formula naredi nekaj, kar v kontekstu modela nima smisla.Na primer sklicevanje na neobstoječo tabelo ali stolpec, posredovanje napačnega števila argumentov funkciji, mešanje nezdružljivih tipov ali odvisnost od stolpca s prejšnjimi napakami.

V teh primerih DAX običajno označi Celoten stolpec je bil izračunan kot napačen.ne le določene vrstice, ker se stolpec šteje za enoto. Če stolpec vsebuje samo metapodatke, vendar še ni bil obdelan (nima naloženih podatkov), bo prikazan sivo in formule, ki so odvisne od njega, ne bodo mogle pravilno ovrednotiti.

Poseben primer so vrednote NaN (Ni število)Te vrednosti se lahko pojavijo na primer pri deljenju 0 z 0. Če stolpec vsebuje vrednosti NaN, lahko razvrščanje ali razvrščanje teh vrednosti povzroči nenavadne rezultate, ker vrednosti NaN ni mogoče primerjati na običajen način z drugimi števili. V takih primerih je priporočljivo uporabiti stavke IF ali druge logične funkcije, da vrednosti NaN nadomestite z 0 ali drugo obvladljivo številsko vrednostjo.

Združljivost s tabelarnimi modeli in načinom DirectQuery

Formule DAX, ki jih ustvarite v Power Pivotu, so na splošno združljivo s tabelarnimi modeli storitev SQL Server Analysis ServicesTo pomeni, da lahko svoj model preselite na strežnik s tabelami in še naprej uporabljate logiko, ki ste jo že zgradili.

Ko pa je tabelarni model implementiran v načinu DirectQueryPojavijo se lahko omejitve: Nekatere funkcije DAX niso neposredno podprte v določenih relacijskih bazah podatkov. ali pa lahko vrnejo nekoliko drugačne rezultate zaradi načina delegiranja poizvedb.

V teh primerih je pomembno pregledati posebno dokumentacijo za tabelarni mehanizem in potrditi kritične ukrepe da potrdijo, da po aktivaciji DirectQuery še naprej delujejo po pričakovanjih.

Praktični scenariji: kompleksni izračuni z IZRAČUN in filtri

Ena od prednosti DAX-a je njegova sposobnost izvajanja kompleksni izračuni, ki se zanašajo na agregacije po meri in dinamične filtreFunkciji CALCULATE in CALCULATETABLE sta osrednjega pomena za tovrstne scenarije.

IZRAČUN omogoča na novo definirajte kontekst filtra, v katerem se izraz ovrednotiNa primer, lahko zahtevate »vsoto prodaje, filtrirane za določeno leto, čeprav vrtilna tabela prikazuje druga leta« ali »skupni znesek brez uporabe določenih filtrov izdelkov«.

Kjerkoli funkcija DAX sprejme tabelo kot argument, Lahko posredujete filtrirano različico te tabeleTo lahko storite z uporabo funkcije FILTER ali z določitvijo pogojev znotraj funkcije CALCULATE. To omogoča izdelavo mer, ki se prilagajajo tisočim kombinacijam pogojev, ne da bi bilo treba ustvariti vmesne stolpce.

Možno je tudi selektivno odstranite obstoječe filtre z uporabo funkcij, kot sta ALL ali ALLEXCEPT. Na primer, za izračun prispevka določenega prodajalca glede na skupno število prodajalcev lahko uporabite mero, ki vrednost v trenutnem kontekstu deli z vrednostjo v kontekstu »ALL« (brez filtrov po prodajalcu).

V drugih primerih boste morali uporabiti vrednosti "zunanje zanke"To pomeni, da se sklicujemo nazaj na prejšnjo vrstico ali kontekst iteracije. Tukaj pridejo na vrsto funkcije, kot je EARLIER, ki omogočajo do dve ravni ugnezdenih zank in so zelo uporabne za ustvarjanje razvrstitev, skupinskih vsot ali izračunov, ki so odvisni od konteksta prejšnje vrstice.

Delam z besedilom, datumi in ključi v DAX-u.

DAX ponuja tudi veliko orodij za manipuliranje z besedilom in datumiTo je ključnega pomena, kadar viri podatkov vsebujejo datume v nenavadnih oblikah, sestavljene ključe ali besedilna polja, ki jih je treba pretvoriti v časovne vrednosti.

Power Pivot jih neposredno ne podpira. sestavljeni ključi v odnosihČe vaš vir uporablja več stolpcev kot ključe, boste v mnogih primerih morali ustvariti izračunani stolpec, ki te dele združi v en sam ključ in ga uporabite kot relacijsko polje.

Ko so datumi v oblikah, ki jih programski mehanizem ne prepozna (na primer datum v nenavadni regionalni obliki ali celo število, kot je 01032009, uvoženo kot besedilo), lahko ustvarite formule, kot je ta:

=DATA(DESNO(;4), LEVO(;2), SREDINA(;3,2))

S tovrstnim izražanjem, Veljaven datum strežnika SQL Server rekonstruirate iz fragmentov, izvlečenih iz niza., kar vam nato omogoča brez težav uporabo funkcij časovne inteligence.

Lahko tudi spreminjanje podatkovnih tipov z uporabo formulPomnožite z 1,0, da pretvorite datume ali številske nize v številke, ali pa združite s praznim nizom, da številko ali datum pretvorite v besedilo. Poleg tega obstajajo posebne funkcije za nadzor vrste vrnitve (okrajšanje decimalk, vsiljenje celih števil itd.).

Pogojne vrednosti in obravnavanje napak v stolpcih in merah

Tako kot v Excelu tudi DAX vključuje funkcije za vrni rezultate na podlagi pogojev in elegantno obravnavati napake. Na primer, prodajalce lahko označite kot »Prednostne« ali »Vrednostne« na podlagi njihovega letnega obsega prodaje z uporabo ugnezdenih stavkov IF.

V izračunanem stolpcu pa Ne morete si privoščiti, da bi nekatere vrstice imele napake, druge pa ne.Če ena vrstica povzroči napako, je celoten stolpec označen kot napačen. To zahteva strožji nadzor napak kot v običajni preglednici.

Da preprečite, da bi preprosto deljenje z ničlo ali prazna vrednost povzročilo sesutje celotnega stolpca, je priporočljivo zavijanje občutljivih operacij v predhodne preglede z uporabo funkcij IF in informacijskih funkcij, ki vedno vrnejo veljavno vrednost, tudi če je kombinacija podatkov nenavadna.

Pri gradnji modela je lahko koristno Naj se napake pojavijo že na začetku, da jih lahko odkrijete in popravite.Ko pa ga objavite za druge uporabnike, je pomembno zagotoviti, da so formule varne in da se v vrtilnih tabelah ali vizualizacijah nikoli ne prikaže sporočilo o napaki.

Časovna inteligenca: kumulativne vsote, primerjave in obdobja po meri

Funkcije časovne inteligence so ena od velikih prednosti DAX-a. Omogočajo vam delo z datumskimi obdobji, izračun kumulativnih vsot, primerjavo obdobij in ustvarjanje časovnih oken po meri. z relativno lahkoto, če imate dobro konfigurirano koledarsko tabelo.

Ukrepe je mogoče ustvariti kumulativna prodaja na dan, mesec, četrtletje ali letoIzračunajte začetno in končno stanje za vsako obdobje ali primerjajte prodajo iz enega leta v prejšnje leto, iz četrtletja v četrtletje itd. z uporabo določenih časovnih funkcij.

Poleg tega si lahko opomorete nabori datumov po merina primer »prvih 15 dni po začetku promocije« ali »isto obdobje lani«, nato pa ta niz posredujte funkciji, ki združuje podatke v tem določenem datumskem oknu.

Funkcije, kot je PARALLELPERIOD in druge, povezane z vzporednimi obdobji Omogočajo primerjavo med časovno zamaknjenimi intervali.Na primer, za analizo, ali je kampanja izboljšala rezultate v primerjavi z istim časovnim obdobjem v drugem letu.

Razvrščanje in primerjava vrednosti: top N in dinamična razvrščanja

Ko morate pokazati samo najpomembnejše elemente (na primer 10 najbolje prodajanih izdelkov), imate dve glavni poti: uporabite Excelove funkcije filtriranja v vrtilni tabeli ali pa z DAX ustvarite dinamično razvrstitev.

Excel ponuja filtre tipa »Top 10« v vrtilnih tabelah, zelo enostavna postavitev Če želite prikazati samo elemente nad ali pod danim številskim poljem. Filtrirate lahko po številu elementov, kumulativnem odstotku ali vsoti vrednosti.

Težava tega pristopa je v tem, da Filter je zgolj za predstavitev.Če se osnovni podatki spremenijo, morate ročno posodobiti vrtilno tabelo, da se filter pravilno odraža. Poleg tega te razvrstitve ni mogoče ponovno uporabiti kot del drugih formul DAX.

Alternativa je ustvariti enega izračunani stolpec ali mera, ki dodeli razvrstitev vsakemu elementu z uporabo DAX-a. Ta možnost je računsko dražja, vendar ima prednosti: uvrstitev se dinamično preračuna in jo je mogoče uporabiti v razčlenjevalnikih podatkov, kar uporabniku omogoča, da izbere, ali želi videti prvih 5, prvih 10, prvih 50 itd.

Vendar pa v modelih z milijoni vrstic, Dinamične uvrstitve so lahko nerodne. in oceniti je treba, ali stroške delovanja izravnajo funkcionalne koristi, ki jih zagotavljajo.

Najboljše prakse pri oblikovanju modelov DAX v Power Pivotu

Da bi bil model DAX v Power Pivotu vzdrževalen in dobro deloval, ni dovolj, da formule »delujejo«. Priporočljivo je upoštevati vrsto dobrih praks ki v resničnih projektih naredijo veliko razliko.

Eno ponavljajoče se priporočilo je dajanje prednosti meram pred izračunanimi stolpci, kadar je izračun dejansko dinamična agregacija in ne fiksni atribut. Izračunani stolpci zavzamejo pomnilnik in se preračunajo vsi naenkrat, medtem ko se mere ovrednotijo ​​le, kadar je to potrebno.

Je tudi zelo uporaben uporaba spremenljivk v DAX (VAR) Za poenostavitev zapletenih formul se izognite večkratnemu ponavljanju istega izračuna in izboljšajte berljivost. To pomaga tako pri delovanju kot pri razumevanju modela, ko ga pregleda nekdo drug.

Končno, jasna imena in minimalna interna dokumentacija naredijo vso razliko. Meritve in stolpce poimenujte opisnoIzogibajte se nejasnim okrajšavam in dokumentirajte najpomembnejše formule. To skrajša krivuljo učenja za nove uporabnike in vam prihrani glavobole, ko se boste mesece kasneje vrnili k modelu.

Obvladovanje jezika DAX v orodju Power Pivot ne pomeni pomnjenja vseh funkcij, temveč razumevanja, kako formule delujejo z relacijskim modelom, kontekstom filtra in posodobitvami podatkov. Z dobrimi osnovami izračunanih stolpcev, mer, časovnih funkcij, obravnavanja napak in dobrih oblikovalskih praksVaši tabelarni modeli postanejo bolj prilagodljivi, veliko lažji za analizo in predvsem sposobni odgovoriti na kompleksna poslovna vprašanja preprosto tako, da v vrtilno tabelo povlečete nekaj polj.