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

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

I Power Pivot er DAX kjernen i tabellmodeller: det brukes til å opprette beregnede kolonner og målinger som mater pivottabeller og diagrammer , lar deg manipulere filterkontekst, jobbe med datoer, håndtere feil, gjøre sammenligninger over tid og mye mer. La oss se nærmere på hvordan alt dette passer inn i DAX-modeller i Power Pivot og hvordan du kan utnytte det effektivt uten å bli overveldet.

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

DAX (Data Analysis Expressions) er først og fremst et formelspråk som er utviklet for datamodeller , ikke et tradisjonelt programmeringsspråk . Funksjonen er å definere tilpassede beregninger som brukes på tabellene og kolonnene i Power Pivot-modellen din.

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

I en Power Pivot-modell kan du bruke DAX for to hovedtyper objekter: beregnede kolonner og målinger . Begge eksisterer samtidig i samme modell, men hver har et distinkt formål og evalueres forskjellig, noe som bør forstås tydelig for å unngå forvirrende konsepter.

I tillegg inkluderer DAX tekst-, dato- og klokkeslett-, logiske, matematiske, filter- og "tidsintelligens"-funksjoner , samt funksjoner som returnerer hele 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 i de fleste tilfeller matriser innenfor celleområder.

Oversikt over DAX-formler og formellinjen

DAX-formler følger en struktur som er veldig lik Excel: de begynner med likhetstegnet (=) etterfulgt av uttrykket eller funksjonen med argumentene . Du 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 som A1:C10 ; i stedet refererer den til hele kolonner eller tabeller, for eksempel «Salg» eller «Kalender» . Dette gir perfekt mening i en tabellmodell, men det krever et skifte i 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 oppretting av uttrykk betraktelig. Den inkluderer også Autofullfør for funksjoner, tabeller og kolonner: når du begynner å skrive et tabell- eller kolonnenavn, vises en rullegardinliste med gyldige alternativer, noe som reduserer syntaksfeil og sparer tid.

For å skrive inn tabellnavn, begynn å skrive og la Autofullfør foreslå samsvarende navn . For kolonner kan du åpne en hakeparentes og velge kolonnen i gjeldende tabell, eller skrive inn tabellnavnet etterfulgt av en hakeparentes og velge fra listen.

I motsetning til Excel lukker eller matcher ikke Power Pivot parenteser automatisk for deg. Det er ditt ansvar å sørge for at funksjonene dine er riktig utformet, med riktig antall argumenter og fullstendige parenteser; ellers blir ikke formelen lagret eller brukt.

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

I en Power Pivot-modell kan du skrive DAX-formler i beregnede kolonner og målinger (også kalt beregnede felt i konteksten av pivottabeller). Selv om de deler samme språk, oppfører de seg svært forskjellig.

Beregnede kolonner i Power Pivot

En beregnet kolonne er et nytt felt som du legger til i en eksisterende tabell i modellen . I 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.

Beregnede kolonner brukes jevnt på tvers av alle rader: du kan ikke ha en annen formel for hver rad , slik du kan i Excel ved å dra og manuelt endre deler. I Power Pivot evalueres uttrykket du definerer automatisk for hele kolonnen og beregnes på nytt når dataene oppdateres eller en modellberegning tvinges frem.

Denne typen kolonne kan være basert på andre beregnede kolonner eller målinger , men det anbefales å ikke bruke samme navn 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 flere attributter å bruke i rader, kolonner, filtre eller slicere i pivottabeller, eller som nøkler for relasjoner. Tenk for eksempel på en «Margin»-kolonne beregnet som – som du deretter kan bruke til gruppering eller filtrering.

Målinger eller beregnede felt

Målinger er derimot beregninger som evalueres i konteksten av en pivottabell eller visualisering . De 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 kan være noe så enkelt som:

Totalt salg = SUM(Salg)

Denne metrikken, som er plassert i Verdier- området i pivottabellen, evalueres for hver celle basert på konteksten (for eksempel etter år, etter produkt, etter region osv.). Den samme beregningen gir forskjellige resultater avhengig av filtrene som brukes og pivottabellens design.

Målinger er ineffektive før de brukes i en rapport. De lagres sammen med datamodellen og vises i feltlisten i pivottabellen, slik at alle brukere av arbeidsboken kan få tilgang til dem. De er viktige for fleksible aggregerte beregninger, for eksempel forholdstall, bidragsprosenter, kumulative totaler, periodesammenligninger og mer.

Viktige forskjeller mellom DAX-funksjoner og Excel-funksjoner

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

For det første fungerer ikke DAX med individuelle celler eller områder ; det bruker alltid hele kolonner eller tabeller som referanser. Dette tvinger deg til å tenke mer i form av datasett enn individuelle elementer, noe som stemmer bedre overens med tilnærmingen til en relasjonell datamodell.

Når det gjelder datoer, returnerer DAX ekte dato- og klokkeslettverdier , mens Excel vanligvis representerer datoer som serienumre. Denne forskjellen er tydelig i de fleste tilfeller, men det er viktig å huske på den når man kombinerer modeller eller importerer data fra andre systemer.

Et annet viktig poeng er at mange av DAXs nyere funksjoner returnerer hele tabeller (f.eks. FILTER, ALL, VALUES osv.) eller 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 antar DAX at alle verdier i en kolonne deler samme datatype . Hvis datatyper blandes, vil databasemotoren tvinge frem en konvertering av hele kolonnen til den typen som passer best til alle poster, noe som noen ganger kan føre til uventede problemer hvis datakildene ikke kontrolleres grundig.

Datatyper i DAX og tabelltypen

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

En viktig ny funksjon sammenlignet med klassisk Excel er tabelldatatypen . Mange 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.

Ved å kombinere funksjoner som returnerer tabeller med aggregeringsfunksjoner som SUMX, AVERAGEX eller MINX , kan du bygge svært sofistikerte beregninger som fungerer på dynamisk definerte delsett av data. Dette resulterer i tilpassede aggregeringer som tilpasser seg filtrene som er i bruk.

Relasjoner, kontekst og relasjonsmodellen i Power Pivot

I Power Pivot-vinduet bygger du den relasjonelle datamodellen. 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, kan du skrive formler som aggregerer verdier fra en relatert tabell og bruker dem i tabellen du skriver uttrykket fra. Du kan også kontrollere hvilke rader som inkluderes i en beregning ved å bruke filtre på bestemte kolonner.

Det er viktig å merke seg at alle rader i en Power Pivot-tabell må ha samme antall kolonner , og hver 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 kontekst . I DAX snakker vi hovedsakelig om radkontekst og filterkontekst. Radkontekst er den "gjeldende" raden som en beregnet kolonne eller iterator evalueres på; filterkontekst er settet med aktive filtre (fra pivottabellen, slicere, relasjoner, funksjoner som CALCULATE osv.).

Ved å bruke funksjoner som CALCULATE, ALL, ALLEXCEPT eller FILTER kan du endre filterkonteksten for å endre hvordan et mål evalueres . Dette lar deg for eksempel beregne prosentandelen av et produkts salg i forhold til totalen, eller sammenligne en divisjons ytelse med selskapets samlede ytelse uten filtre.

Dataoppdatering og omberegning av DAX-formel

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

Oppdatering av data innebærer å hente nye poster inn i arbeidsboken fra eksterne kilder ( databaser , filer, nettjenester osv.). Du kan kjøre denne oppdateringen manuelt etter behov, eller planlegge den hvis arbeidsboken er publisert til SharePoint eller et annet kompatibelt miljø. Ofte utføres denne prosessen ved hjelp av Power Query i Excel for å forberede og transformere dataene før de lastes inn i modellen.

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

Disse omberegningene kan påvirke ytelsen, spesielt når man bruker mange komplekse beregnede kolonner eller intensive iterative funksjoner på store tabeller . Derfor 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: syntaksfeil, semantiske feil og beregningsfeil . Hver av dem har sine egne spesifikke omstendigheter og metoder for korrigering.

Syntaksfeil er de enkleste: manglende parenteser, feilplasserte komma, feilstavede funksjonsnavn osv. Autofullføring og DAX-funksjonsreferansen vil spare deg for mange av disse fallgruvene.

Semantiske feil og beregningsfeil oppstår når formelen, selv om syntaksen er riktig, 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 den beregnede kolonnen som feilaktig , ikke bare en bestemt rad, fordi kolonnen regnes som en enkelt 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 NaN -verdier (ikke et tall) , som for eksempel kan oppstå når man deler 0 med 0. Hvis en kolonne inneholder NaN-verdier, kan sortering eller klassifisering av disse verdiene gi uvanlige 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 vanligvis kompatible med tabellmodeller i SQL Server Analysis Services . Dette betyr at du kan migrere modellen til en tabellserver og fortsette å utnytte logikken du allerede har bygget.

Når man implementerer en tabellmodell i DirectQuery- modus , kan det imidlertid oppstå begrensninger: noen DAX-funksjoner støttes ikke direkte på visse relasjonsdatabaser , eller de kan returnere litt forskjellige resultater på grunn av hvordan spørringer delegeres.

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

Praktiske scenarier: komplekse beregninger med CALCULATE og filtre

En av DAXs styrker er evnen til å utføre komplekse beregninger som er avhengige av tilpassede aggregeringer og dynamiske filtre . Funksjonene CALCULATE og CALCULATETABLE er sentrale i denne typen scenarioer.

Med CALCULATE kan du omdefinere filterkonteksten som et uttrykk evalueres på . Du kan for eksempel be om «summen av salg filtrert til et bestemt år, selv om andre år vises i pivottabellen», eller «totalen uten å bruke bestemte produktfiltre».

Overalt hvor en DAX-funksjon godtar en tabell som et argument, kan du sende en filtrert versjon av tabellen , enten ved å bruke FILTER eller ved å angi betingelser i CALCULATE. Dette gjør det mulig å bygge målinger som passer til tusenvis av kombinasjoner av betingelser uten å opprette mellomliggende kolonner.

Det er også mulig å selektivt fjerne eksisterende filtre ved hjelp av funksjoner som ALL eller ALLEXCEPT. For eksempel, for å beregne en spesifikk forhandlers bidrag til det totale antallet forhandlere, kan du få et mål til å dele verdien i gjeldende kontekst med verdien i «ALL»-konteksten (uten filtre for forhandlere).

I andre tilfeller må du bruke verdier fra en «ytre løkke », det vil si referere 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 datoer . Dette er kritisk når datakilder inneholder datoer i uvanlige formater, sammensatte nøkler eller tekstfelt som må konverteres til tidsverdier.

Power Pivot støtter ikke direkte sammensatte nøkler i relasjoner . Hvis datakilden 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 bruker den som relasjonsfeltet.

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:

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

Med denne typen uttrykk rekonstruerer du en gyldig SQL Server-dato fra fragmenter hentet fra strengen , slik at du deretter kan bruke tidsintelligensfunksjoner sømløst.

Du kan også endre datatyper ved hjelp av formler : multipliser 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 den returnerte typen (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 elegant håndtering av feil. Du kan for eksempel merke forhandlere som «Foretrukket» eller «Verdi» basert på deres årlige salgsvolum ved hjelp av nestede HVIS-uttrykk.

I en beregnet kolonne har du imidlertid ikke råd til at noen rader har feil og andre ikke : hvis én rad produserer en feil, merkes hele kolonnen som feil. Dette krever strengere feilhåndtering enn i et konvensjonelt regneark.

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

Når du bygger modellen, kan det være nyttig å la feil vises i starten, slik at du kan finne og korrigere dem . Men når du publiserer den til andre brukere, bør du 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 DAXs største fordeler. De lar deg jobbe med datointervaller, beregne kumulative totaler, sammenligne perioder og generere tilpassede tidsvinduer relativt enkelt, forutsatt at du har en riktig konfigurert kalendertabell.

Du kan opprette kumulative salgsmål per dag, måned, kvartal eller år , beregne åpnings- og sluttsaldo for hver periode, eller sammenligne salg fra ett år til foregående år, kvartal til kvartal osv., ved hjelp av tidsspesifikke funksjoner.

I tillegg kan du hente egendefinerte sett med datoer , for eksempel «de første 15 dagene etter starten av en kampanje» eller «samme periode i fjor», og deretter sende dette settet til en funksjon som samler dataene over det spesifikke datovinduet.

Funksjoner som PARALLELPERIODE og andre relatert til parallelle perioder forenkler sammenligningen mellom tidsforskjøvede intervaller , for eksempel for å analysere om en kampanje har forbedrede resultater sammenlignet med samme tidsperiode i et annet år.

Rangering og sammenligning av verdier: topp N og dynamiske rangeringer

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

Excel tilbyr «Topp 10»-filtre i pivottabeller, som er svært enkle å konfigurere til å bare vise de øverste eller nederste elementene basert på et numerisk felt. Du kan filtrere etter antall elementer, kumulativ prosentandel eller sum av verdier.

Problemet med denne tilnærmingen er at filteret utelukkende er for presentasjonsformål : hvis de underliggende dataene endres, må du manuelt oppdatere pivottabellen 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 et mål 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 slicere, slik at brukeren kan velge om de vil se topp 5, topp 10, topp 50 osv.

I modeller med millioner av rader kan imidlertid dynamiske rangeringer være tungvinte , og det er nødvendig å vurdere om ytelseskostnaden er verdt 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 bare «fungerer». Det er viktig å følge en rekke beste praksiser som utgjør en betydelig forskjell i prosjekter i den virkelige verden.

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

Det er også veldig nyttig å bruke 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 kolonner , unngå 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 formler samhandler med relasjonsmodellen, konteksten til filtre og dataoppdateringer. Med et solid grunnlag i beregnede kolonner, målinger, tidsfunksjoner, feilhåndtering og god designpraksis blir tabellmodellene dine 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.