DAX-modeller i Power Pivot: formler, kontekst og beste praksis

Hvis du jobber med data i Excel og PowerPivotFør eller siden kommer du til å støte på DAX. Selv om navnet «Data Analysis Expressions» høres teknisk og litt skremmende utRealiteten er at med en god forståelse av det grunnleggende blir det et veldig praktisk og kraftig verktøy for modellering av informasjon.

I Power Pivot er DAX kjernen i tabellmodeller: Den brukes til å opprette beregnede kolonner og målinger som mater pivottabeller og diagrammerDen lar deg leke med konteksten til filtre, jobbe med datoer, håndtere feil og gjøre sammenligninger i tiden Og mye mer. La oss se nærmere på hvordan alt dette passer inn i DAX-modellene i Power Pivot og hvordan man kan dra nytte av det uten å bli gal.

Hva er DAX, og hvorfor er det viktig i Power Pivot-modeller?

DAX (Data Analysis Expressions) er fremfor alt et formelspråk designet for datamodellerikke et språk av programmering Klassisk. Funksjonen er å definere egendefinerte beregninger som brukes på tabeller og kolonner i Power Pivot-modellen.

Selv om det visuelt sett kan ligne på excel-formler, DAX er utviklet for å fungere med relasjonsdata og dynamiske aggregeringer.Dette betyr at den integreres sømløst med tabeller som er koblet sammen av relasjoner, pivottabeller og komplekse modeller der filterkonteksten endres avhengig av feltene du bruker i rader, kolonner eller slicere.

Innenfor en Power Pivot-modell kan du bruke DAX til å To hovedtyper av objekter: beregnede kolonner og målingerBegge sameksisterer innenfor samme modell, men hver har et annet formål og evalueres forskjellig, noe som bør være veldig tydelig for ikke å blande konsepter.

I tillegg inkluderer DAX tekst, dato og klokkeslett, logiske, matematiske, filter- og "tidsintelligens"-funksjonersamt funksjoner som returnerer komplette tabeller som et resultat. Disse sistnevnte funksjonene er en av de største forskjellene sammenlignet med Excel, hvor formler ikke returnerer tabeller som sådan, men høyst matriser innenfor celleområder.

Oversikt over DAX-formler og formellinjen

DAX-formler følger en struktur som er veldig lik den i Excel: De begynner med likhetstegnet (=), og deretter skrives uttrykket eller funksjonen med argumenteneDu kan kombinere operatorer, funksjoner, kolonne- og tabellreferanser osv., akkurat som du ville gjort når du bygger en kompleks formel i et regneark.

Det finnes imidlertid viktige nyanser. DAX fungerer aldri med referanser av typen A1:C10I stedet refereres det til hele kolonner eller tabeller, for eksempel «Salg» o 'Kalender'Dette gir mye mening i en tabellmodell, men det tvinger deg til å endre tankesett hvis du kommer fra den rent "celle for celle"-verdenen i Excel.

Power Pivot har en formellinje som ligner på Excels noe som forenkler opprettelsen av uttrykk betraktelig. Den har autofullføring for funksjoner, tabeller og kolonner: når du begynner å skrive navnet på en tabell eller kolonne, vises en rullegardinliste med gyldige alternativer, noe som reduserer syntaksfeil og sparer tid.

For å skrive tabellnavn, bare begynn å skrive og la det kjøre. Autofullfør foreslår samsvarende navnFor kolonner kan du enten åpne en parentes og velge kolonnen i gjeldende tabell, eller skrive inn tabellnavnet etterfulgt av parenteser og velge fra listen.

Men i motsetning til Excel, Power Pivot lukker ikke parenteser automatisk Den samsvarer ikke med dem for deg. Det er ditt ansvar å sørge for at funksjonene er riktig utformet, med riktig antall argumenter og fullstendige parenteser, ellers kan ikke formelen lagres eller brukes.

Der DAX-formler brukes: beregnede kolonner og målinger

I en Power Pivot-modell kan du skrive DAX-formler i beregnede kolonner og i tiltak (også kalt beregnede felt i sammenheng med pivottabeller). Selv om de deler samme språk, oppfører de seg på svært forskjellige måter.

Beregnede kolonner i Power Pivot

En beregnet kolonne er et nytt felt som du legger til i en eksisterende tabell i modellenI stedet for å importere den verdien fra datakilden, definerer du en DAX-formel som evalueres rad for rad. Resultatet lagres i kolonnen for hver rad i tabellen.

De beregnede kolonnene brukes jevnt på alle rader: Du kan ikke ha en annen formel for hver rad.I motsetning til i Excel, hvor du kan dra og slippe deler manuelt, evalueres uttrykket du definerer automatisk for hele kolonnen og beregnes på nytt i Power Pivot når dataene oppdateres eller en modellberegning tvinges frem.

Denne typen kolonne kan være basert på andre beregnede kolonner eller i målingerDet anbefales imidlertid å ikke bruke samme navn på nytt for både målet og kolonnen for å unngå forvirring ved referanser. Det er lurt å alltid bruke hele kolonnereferansen (tabell) for å unngå å utilsiktet referere til et mål med samme navn.

Beregnede kolonner er ideelle når du trenger tilleggsattributter du vil bruke i rader, kolonner, filtre eller slicere av pivottabeller, eller som nøkler for relasjoner. Tenk for eksempel på en «Margin»-kolonne beregnet som – som du deretter kan bruke til å gruppere eller filtrere.

Målinger eller beregnede felt

Tiltakene er på sin side beregninger som evalueres i konteksten av en pivottabell eller visualiseringDe lagres ikke rad for rad, men beregnes på nytt for hver kombinasjon av filtre, rader og kolonner som er aktive i rapporten.

en typisk måling Det kan være noe så enkelt som:

Totalt salg = SUM(Salg)

Dette tiltaket, plassert i området verdier I pivottabellen evalueres hver celle i henhold til konteksten (for eksempel etter år, etter produkt, etter region...). Den samme beregningen gir forskjellige resultater avhengig av hvilke filtre som brukes. og designet til pivottabellen.

Tiltakene gjør ingenting før de brukes i en rapport. De lagres sammen med datamodellen og vises i feltlisten i pivottabeller. slik at alle brukere av boken kan bruke dem. De er grunnleggende for fleksible samlede beregninger, som forholdstall, bidragsprosenter, kumulative totaler, sammenligninger mellom perioder osv.

Viktige forskjeller mellom DAX-funksjoner og Excel-funksjoner

Selv om mange DAX-funksjoner ligner på Excel-funksjoner i navn og generell oppførsel, De er ikke bare utskiftbare.Det er viktige forskjeller som påvirker hvordan formler konstrueres i en Power Pivot-modell.

Først, DAX fungerer ikke med individuelle celler eller områderHele kolonner eller tabeller brukes alltid som referanse. Dette tvinger deg til å tenke mer i datasett enn individuelle elementer, noe som passer bedre med tilnærmingen til en relasjonell datamodell.

I datointervallet returnerer DAX virkelige dato- og klokkeslett-typeverdierSelv om Excel vanligvis representerer datoer som serienumre, er denne forskjellen gjennomsiktig i de fleste tilfeller, men det er viktig å huske på det når du kombinerer modeller eller importerer data fra andre systemer.

Et annet nøkkelpoeng er det Mange av de nye DAX-funksjonene returnerer komplette tabeller (for eksempel FILTER, ALL, VALUES osv.) eller de godtar tabeller som argumenter. Excel, derimot, har ikke et konsept for en funksjon "som returnerer en tabell" i samme forstand, selv om det finnes matriseformler.

Til slutt, i DAX antas det at Alle verdier i en kolonne deler samme datatypeHvis datatyper blandes, vil datamotoren tvinge frem en konvertering av hele kolonnen til den typen som passer best til alle poster, noe som noen ganger kan føre til overraskelser hvis datakildene ikke kontrolleres grundig.

Datatyper i DAX og tabelltypen

Når du importerer informasjon til en Power Pivot-modell, Dataene konverteres til en av datatypene som støttes av motoren. (tall, tekst, boolske verdier, datoer og klokkeslett, valuta osv.). Denne typen data bestemmer hvilke operasjoner som er gyldige og hvordan formler skal evalueres.

En viktig ny funksjon sammenlignet med klassisk Excel er tabelldatatypeMange DAX-funksjoner godtar en hel tabell som et argument og returnerer en annen tabell som et resultat. For eksempel tar FILTER en tabell og en betingelse, og returnerer en tabell med bare radene som oppfyller betingelsen.

Kombinere funksjoner som returnerer tabeller med aggregeringsfunksjoner som SUMX, AVERAGEX eller MINXSvært sofistikerte beregninger kan bygges som fungerer på dynamisk definerte delsett av data. Dette resulterer i tilpassede aggregeringer som tilpasser seg filtrene som er aktive til enhver tid.

Relasjoner, kontekst og relasjonsmodellen i Power Pivot

Power Pivot-vinduet er der den relasjonelle datamodellen bygges. Der kan du importere flere tabeller og opprette relasjoner mellom dem. (for eksempel Salg med produkter, Salg med kalender, Salg med kunder osv.). Disse relasjonene er grunnlaget for at DAX-formler skal kunne hoppe fra én tabell til en annen.

Når tabeller er relaterte, Du kan skrive formler som legger sammen verdier fra en relatert tabell og bruk dem i tabellen du skriver uttrykket fra. Du kan også kontrollere hvilke rader som deltar i en beregning ved å bruke filtre på bestemte kolonner.

Det er viktig å sette oppmerksomhet på Alle rader i en Power Pivot-tabell må ha samme antall kolonnerHver kolonne må opprettholde en konsistent datatype på tvers av alle radene. Hvis relasjonsnøklene har verdier som ikke samsvarer (blanke felt, foreldreløse verdier osv.), kan oppslagsformler og pivottabeller returnere uventede resultater.

Et annet grunnleggende konsept er kontekstI DAX brukes primært begrepene radkontekst og filterkontekst. Radkonteksten er den "gjeldende" raden som en beregnet kolonne eller iterator evalueres på; filterkonteksten er settet med aktive filtre (fra pivottabellen, slicere, relasjoner, funksjoner som CALCULATE osv.).

Ved å leke med funksjoner som CALCULATE, ALL, ALLEXCEPT eller FILTER, kan du Endre filterkonteksten for å endre hvordan et mål evalueres.Dette tillater for eksempel å beregne prosentandelen av salget av et produkt i forhold til totalen, eller sammenligne resultatene til en divisjon med selskapet som helhet uten filtre.

Dataoppdatering og omberegning av DAX-formel

I en modell som bruker komplekse formler eller store datamengder, er det viktig å forstå hvordan oppdateringen fungerer. Det er viktig å skille mellom å oppdatere dataene og å beregne formlene på nytt.som er relaterte, men uavhengige prosesser.

Dataoppdateringen består av bringe nye poster inn i boken fra eksterne kilder (databaser(filer, nettjenester osv.). Du kan starte denne oppdateringen manuelt når det er nødvendig, eller planlegge den hvis boken er utgitt i Sharepoint eller et annet kompatibelt miljø. Ofte utføres denne prosessen ved hjelp av Power Query i Excel å forberede og transformere dataene før de lastes inn i modellen.

Omberegning er derimot prosessen der DAX-formlene evalueres på nytt for å gjenspeile endringer i dataene eller i selve uttrykkene.For beregnede kolonner, hvis du endrer formelen, må hele kolonnen beregnes på nytt samtidig. For målinger skjer omberegningen når konteksten endres (filtre, felt for rad/kolonne i pivottabell) eller når pivottabeller oppdateres manuelt.

Disse omberegningene kan påvirke ytelsen, spesielt hvis mange komplekse beregnede kolonner eller intensive iterative funksjoner brukes i store tabellerDerfor er det god praksis å flytte mesteparten av logikken til målinger i stedet for kolonner, når det er mulig.

Deteksjon og korrigering av feil i DAX-formler

Når man skriver DAX-formler, er det vanlig å støte på tre typer feil: syntaktiske feil, semantiske feil og beregningsfeilHver og en har sine egne omstendigheter og sin egen måte å korrigere seg selv på.

Syntaksfeil er de enkleste: manglende parenteser, feilplasserte kommaer, feilstavede funksjonsnavnosv. Hjelpen for autofullføring og referansen for DAX-funksjonen sparer deg for mange av disse fallgruvene.

Semantiske feil og beregningsfeil oppstår når, selv om syntaksen er korrekt, Formelen gjør noe som ikke gir mening i modellens kontekst.For eksempel å referere til en ikke-eksisterende tabell eller kolonne, sende feil antall argumenter til en funksjon, blande inkompatible typer eller være avhengig av en kolonne med tidligere feil.

I disse tilfellene markerer DAX vanligvis Hele kolonnen ble beregnet som feil.ikke bare en bestemt rad, fordi kolonnen regnes som en enhet. Hvis en kolonne bare inneholder metadata, men ennå ikke er behandlet (ikke har lastet inn data), vil den vises nedtonet, og formler som er avhengige av den, vil ikke kunne evalueres riktig.

Et spesialtilfelle er verdier NaN (ikke et tall)Disse verdiene kan for eksempel dukke opp når man deler 0 med 0. Hvis en kolonne inneholder NaN-verdier, kan sortering eller klassifisering av disse verdiene gi merkelige resultater fordi NaN-verdier ikke kan sammenlignes på vanlig måte med andre tall. I slike tilfeller er det lurt å bruke HVIS-setninger eller andre logiske funksjoner for å erstatte NaN-verdiene med 0 eller en annen håndterbar numerisk verdi.

Kompatibilitet med tabellmodeller og DirectQuery-modus

DAX-formlene du oppretter i Power Pivot er generelt sett, kompatibel med tabellmodeller for SQL Server Analysis ServicesDette betyr at du kan migrere modellen din til en tabellarisk server og fortsette å utnytte logikken du allerede har bygget.

Men når en tabellmodell implementeres i modus DirectQueryBegrensninger kan forekomme: Enkelte DAX-funksjoner støttes ikke direkte på visse relasjonsdatabaser. eller de kan returnere litt forskjellige resultater på grunn av hvordan spørringene delegeres.

I disse scenariene er det viktig å gjennomgå den spesifikke dokumentasjonen for tabellmotoren og validere de kritiske tiltakene for å bekrefte at de fortsetter å fungere som forventet etter at DirectQuery er aktivert.

Praktiske scenarier: komplekse beregninger med CALCULATE og filtre

En av styrkene til DAX er dens evne til å utføre komplekse beregninger som er avhengige av tilpassede aggregeringer og dynamiske filtreFunksjonene CALCULATE og CALCULATETABLE er sentrale i denne typen scenario.

BEREGN tillater omdefiner filterkonteksten som et uttrykk evalueres overDu kan for eksempel be om «summen av salg filtrert til et bestemt år, selv om pivottabellen viser andre år», eller «totalen uten å bruke bestemte produktfiltre».

Overalt hvor en DAX-funksjon godtar en tabell som et argument, Du kan sende en filtrert versjon av den tabellenDette kan gjøres enten ved å bruke FILTER eller ved å spesifisere betingelser i CALCULATE. Dette gjør det mulig å bygge målinger som tilpasser seg tusenvis av kombinasjoner av betingelser uten å måtte opprette mellomliggende kolonner.

Det er også mulig fjern eksisterende filtre selektivt ved hjelp av funksjoner som ALL eller ALLEXCEPT. Hvis du for eksempel vil beregne bidraget fra en bestemt forhandler i forhold til det totale antallet forhandlere, kan du få et mål til å dele verdien i gjeldende kontekst med verdien i «ALL»-konteksten (uten filtre etter forhandler).

I andre tilfeller må du bruke verdier av en «ytre sløyfe»Det vil si å referere tilbake til forrige rad eller iterasjonskontekst. Det er her funksjoner som EARLIER kommer inn i bildet, som tillater opptil to nivåer av nestede løkker og er svært nyttige for å lage rangeringer, gruppetotaler eller beregninger som er avhengige av en forrige radkontekst.

Jeg jobber med tekst, datoer og nøkler i DAX

DAX tilbyr også mange verktøy for manipulere tekst og datoerDette er kritisk når datakilder inneholder datoer i uvanlige formater, sammensatte nøkler eller tekstfelt som må konverteres til tidsverdier.

Power Pivot støtter dem ikke direkte sammensatte nøkler i relasjonerHvis kildekoden din bruker flere kolonner som nøkler, må du i mange tilfeller opprette en beregnet kolonne som sammenkobler disse delene til én enkelt nøkkel og bruk det som et relasjonsfelt.

Når datoer kommer i formater som ikke gjenkjennes av søkemotoren (for eksempel en dato i et uvanlig regionalt format eller et heltall som 01032009 importert som tekst), kan du lage formler som dette:

=INFORMASJON(HØYRE(,4), VENSTRE(,2), MIDTEN(,3,2))

Med denne typen uttrykk, Du rekonstruerer en gyldig SQL Server-dato fra fragmenter hentet fra strengen, som deretter lar deg bruke tidsintelligensfunksjoner uten problemer.

Du kan også endre datatyper ved hjelp av formlerMultipliser med 1,0 for å konvertere datoer eller numeriske strenger til tall, eller sammenkoble med en tom streng for å transformere et tall eller en dato til tekst. I tillegg finnes det spesifikke funksjoner for å kontrollere returtypen (avkorte desimaler, tvinge frem heltall osv.).

Betingede verdier og feilhåndtering i kolonner og målinger

Akkurat som i Excel inkluderer DAX funksjoner for returnere resultater basert på betingelser og for å håndtere feil elegant. Du kan for eksempel merke forhandlere som «Foretrukket» eller «Verdi» basert på deres årlige salgsvolum ved hjelp av nestede HVIS-setninger.

I en beregnet kolonne, derimot, Du har ikke råd til at noen rader har feil og andre ikke.Hvis én rad gir en feil, merkes hele kolonnen som feil. Dette krever strengere feilkontroll enn i et vanlig regneark.

For å forhindre at en enkel divisjon med null eller en blank verdi krasjer hele kolonnen, anbefales det pakk inn sensitive operasjoner i forhåndskontroller bruker HVIS- og informasjonsfunksjoner, og returnerer alltid en gyldig verdi selv når datakombinasjonen er merkelig.

Når du bygger modellen, kan den være nyttig La feilene dukke opp i begynnelsen, slik at du kan finne og rette dem.Men når du publiserer den for andre brukere, er det viktig å sørge for at formlene er sikre og at det aldri vises noen feilmelding i pivottabellene eller visualiseringene.

Tidsintelligens: kumulative totaler, sammenligninger og tilpassede perioder

Tidsintelligensfunksjoner er en av de store attraksjonene ved DAX. De lar deg jobbe med datointervaller, beregne kumulative totaler, sammenligne perioder og generere tilpassede tidsvinduer. relativt enkelt, forutsatt at du har en godt konfigurert kalendertabell.

Tiltak kan opprettes kumulativt salg per dag, måned, kvartal eller årBeregn åpnings- og sluttbalanse for hver periode, eller sammenlign salg fra ett år til året før, kvartal til kvartal osv., ved hjelp av spesifikke tidsfunksjoner.

I tillegg kan du gjenopprette tilpassede sett med datoerfor eksempel «de første 15 dagene etter starten av en kampanje» eller «samme periode i fjor», og deretter sende det settet til en funksjon som aggregerer dataene over det spesifikke datovinduet.

Funksjoner som PARALLELPERIODE og andre relatert til parallelle perioder De forenkler sammenligning mellom tidsforskjøvede intervaller.For eksempel for å analysere om en kampanje har forbedret resultatene sammenlignet med samme tidsperiode i et annet år.

Rangering og sammenligning av verdier: topp N og dynamiske rangeringer

Når du trenger å vise bare de mest relevante elementene (for eksempel de 10 mest solgte produktene), har du to hovedveier: bruk Excels filtreringsfunksjoner på pivottabellen eller bygg en dynamisk rangering med DAX.

Excel tilbyr filtre av typen «Topp 10» i pivottabeller, veldig enkelt å sette opp For å vise bare elementene over eller under et gitt numerisk felt. Du kan filtrere etter antall elementer, kumulativ prosentandel eller sum av verdier.

Problemet med denne tilnærmingen er at Filteret er kun for presentasjon.Hvis de underliggende dataene endres, må du oppdatere pivottabellen manuelt for at filteret skal gjenspeiles riktig. Videre kan du ikke bruke den rangeringen på nytt som en del av andre DAX-formler.

Alternativet er å opprette en beregnet kolonne eller måling som tilordner en rangering til hvert element ved hjelp av DAX. Dette alternativet er mer beregningsmessig dyrt, men det har fordeler: rangeringen beregnes dynamisk på nytt og kan brukes i dataslicere, slik at brukeren kan velge om de vil se topp 5, topp 10, topp 50 osv.

Men i modeller med millioner av rader, Dynamiske rangeringer kan være tungvinte. og det er nødvendig å vurdere om ytelseskostnadene oppveies av den funksjonelle fordelen de gir.

Beste fremgangsmåter for utforming av DAX-modeller i Power Pivot

For at en DAX-modell i Power Pivot skal være vedlikeholdbar og fungere bra, er det ikke nok at formlene «fungerer». Det er lurt å følge en rekke gode fremgangsmåter som utgjør en stor forskjell i virkelige prosjekter.

En tilbakevendende anbefaling er prioritere målinger fremfor beregnede kolonner når beregningen faktisk er en dynamisk aggregering og ikke et fast attributt. Beregnede kolonner tar opp minne og beregnes på nytt samtidig, mens målinger bare evalueres når det er nødvendig.

Det er også veldig nyttig bruk av variabler i DAX (VAR) For å forenkle komplekse formler, unngå å gjenta den samme beregningen flere ganger, og forbedre lesbarheten. Dette hjelper både ytelsen og forståelsen av modellen når noen andre gjennomgår den.

Til slutt utgjør tydelige navn og minimal intern dokumentasjon hele forskjellen. Gi beskrivende navn til målinger og kolonnerUnngå obskure forkortelser og dokumenter de viktigste formlene. Dette reduserer læringskurven for nye brukere og sparer deg for hodebry når du kommer tilbake til modellen måneder senere.

Å mestre DAX i Power Pivot handler ikke om å memorere alle funksjonene, men om å forstå hvordan formlene samhandler med relasjonsmodellen, filterkonteksten og dataoppdateringer. Med et solid grunnlag i beregnede kolonner, målinger, tidsfunksjoner, feilhåndtering og god designpraksisTabellmodellene dine blir mer fleksible, mye enklere å analysere, og fremfor alt i stand til å svare på komplekse forretningsspørsmål ved ganske enkelt å dra noen få felt inn i en pivottabell.