DAX (Data Analysis Expressions) i Power Pivot

Forstå DAX-formler

DAX-formler ligner i stor grad på Excel-formler. Du kan opprette en DAX-formel ved å skrive inn et likhetstegn, etterfulgt av et funksjonsnavn eller uttrykk og eventuelle nødvendige verdier eller argumenter. I likhet med Excel inneholder DAX en rekke funksjoner som du kan bruke til å arbeide med strenger, utføre beregninger ved hjelp av dato- og klokkeslett eller opprette betingede verdier.

DAX-formler er imidlertid forskjellige på følgende viktige måter:

Hvis du vil tilpasse beregninger rad for rad, inneholder DAX funksjoner som lar deg bruke gjeldende radverdi eller en relatert verdi til å utføre beregninger som varierer etter kontekst.
DAX inneholder en funksjonstype som returnerer en tabell som resultat, i stedet for en enkeltverdi. Disse funksjonene kan brukes til å angi inndata til andre funksjoner.
Funksjoner for tidsintelligens i DAX tillater beregninger ved hjelp av datoområder og sammenligner resultatene på tvers av parallelle perioder.

Bruk av DAX-formler

Du kan opprette formler i Power Pivot enten i beregnede kolonner eller i beregnede felt.

Beregnede kolonner

En beregnet kolonne er en kolonne du legger til i en eksisterende Power Pivot-tabell. I stedet for å lime inn eller importere verdier i kolonnen kan du opprette en DAX-formel som definerer kolonneverdiene. Hvis du inkluderer Power Pivot-tabellen i en pivottabell (eller et pivotdiagram), kan du bruke den beregnede kolonnen som en hvilken som helst annen kolonne.

Formlene i beregnede kolonner ligner på formlene du oppretter i Excel. I motsetning Excel, kan du imidlertid ikke opprette en annen formel for forskjellige rader i en tabell. I stedet brukes DAX-formelen automatisk på hele kolonnen.

Når en kolonne inneholder en formel, beregnes verdien for hver rad. Resultatet beregnes for kolonnen så snart du lager formelen. Kolonneverdier beregnes bare på nytt hvis de underliggende dataene oppdateres, eller hvis manuell ny beregning brukes.

Du kan opprette beregnede kolonner som er basert på mål og andre beregnede kolonner. Unngå imidlertid å bruke samme navn for en beregnet kolonne og et mål, da dette kan føre til forvirrende resultater. Når du refererer til en kolonne, er det best å bruke en fullstendig kolonnereferanse for å unngå å aktivere et mål ved et uhell.

Hvis du vil ha mer detaljert informasjon, kan du se Beregnede kolonner i Power Pivot.

Mål

Et mål er en formel som er opprettet spesielt for bruk i en pivottabell (PivotChart) som bruker Power Pivot data. Mål kan baseres på standardaggregasjonsfunksjoner, for eksempel ANTALL eller SUMMER, eller du kan definere din egen formel ved hjelp av DAX. Et mål brukes i Verdier-området i en pivottabell. Hvis du vil plassere beregnede resultater i et annet område i en pivottabell, bruker du en beregnet kolonne i stedet.

Når du definerer en formel for et eksplisitt mål, skjer det ingenting før du legger til målet i en pivottabell. Når du legger til målet, evalueres formelen for hver celle i Verdier-området i pivottabellen. Fordi det opprettes et resultat for hver kombinasjon av rad- og kolonneoverskrifter, kan resultatet for målet være forskjellig i hver celle.

Definisjonen av målet du oppretter, lagres med kildedatatabellen. Den vises i listen over pivottabellfelt og er tilgjengelig for alle brukere av arbeidsboken.

Hvis du vil ha mer detaljert informasjon, kan du se Mål i Power Pivot.

Opprette formler ved hjelp av formellinjen

Power Pivot inneholder, i likhet med Excel , en formellinje som gjør det enklere å opprette og redigere formler, og en Autofullfør-funksjon som reduserer skrive- og syntaksfeil.

Angi navnet på en tabell Begynn ved å skrive inn navnet på tabellen. Autofullfør formel viser en rullegardinliste med gyldige navn som begynner med disse bokstavene.

Angi navnet på en kolonne Skriv inn en hakeparentes, og velg deretter kolonnen fra listen over kolonner i den gjeldende tabellen. For kolonner fra en annen tabell begynner du å skrive inn de første bokstavene i tabellnavnet, og deretter velger du kolonnen fra rullegardinlisten for Autofullfør.

Hvis du vil ha mer informasjon og en gjennomgang om hvordan du bygger formler, kan du se Opprette formler for beregninger i Power Pivot.

Tips for bruk av Autofullfør

Du kan bruke Autofullfør formel midt i en eksisterende formel med nestede funksjoner. Teksten rett foran innsettingspunktet brukes til å vise verdier i rullegardinlisten, mens all tekst etter innsettingspunktet forblir uendret.

Definerte navn som du oppretter for konstanter, vises ikke i rullegardinlisten for Autofullfør, men du kan likevel skrive dem inn.

Power Pivot legger ikke til avsluttende parentes i funksjoner og sammenligner heller ikke parenteser automatisk. Du må forsikre deg om at hver funksjon er syntaktisk riktig, ellers vil du ikke kunne lagre eller bruke formelen.

Bruke flere funksjoner i en formel

Funksjoner kan nestes, det vil si at du kan bruke resultater fra én funksjon som argument i en annen funksjon. Du kan neste opptil 64 nivåer med funksjoner i beregnede kolonner. Nesting kan imidlertid gjøre det vanskelig å opprette eller feilsøke formler.

Mange DAX-funksjoner er utformet for å brukes utelukkende som nestede funksjoner. Disse funksjonene returnerer en tabell som ikke kan lagres direkte som et resultat. den skal angis som inndata til en tabellfunksjon. Funksjonene SUMMERX, GJENNOMSNITTX og MINX krever for eksempel en tabell som det første argumentet.

Obs!: Det finnes noen begrensninger for nesting av funksjoner i mål for å sikre at ytelsen ikke påvirkes av de mange beregningene som kreves av avhengigheter mellom kolonner.

Sammenligne DAX-funksjoner og Excel-funksjoner

DAX-funksjonsbiblioteket er basert på Excel-funksjonsbiblioteket, men det er mange forskjeller mellom bibliotekene. Denne delen oppsummerer forskjellene og likhetene mellom Excel-funksjoner og DAX-funksjoner.

Mange DAX-funksjoner har samme (engelske) navn og samme generelle virkemåte som Excel-funksjoner, men de er endret slik at de kan bruke forskjellige typer inndata, og de kan i enkelte tilfeller returnere en annen datatype. Vanligvis kan du ikke bruke DAX-funksjoner i en Excel-formel eller Excel-formler i Power Pivot uten å gjøre enkelte endringer.
DAX-funksjoner bruker aldri en cellereferanse eller et område som referanse, men bruker i stedet en hel kolonne eller tabell som referanse.
Dato- og klokkeslettfunksjonene i DAX returnerer en datetime-datatype. Dato- og klokkeslettfunksjonene i Excel returnerer i stedet et heltall som representerer en dato som et serienummer.
Mange av de nye DAX-funksjonene returnerer en tabell med verdier, eller utfører beregninger basert på en tabell med verdier som inndata. Excel, derimot, har ingen funksjoner som returnerer en tabell, selv om enkelte funksjoner kan fungere med matriser. Muligheten til å referere enkelt til hele tabeller og kolonner er en ny funksjon i Power Pivot.
DAX inneholder nye oppslagsfunksjoner som ligner på matrise- og vektoroppslagsfunksjonene i Excel. DAX-funksjonene krever imidlertid at det opprettes en relasjon mellom tabellene.
Dataene i en kolonne forventes alltid å ha samme datatype. Hvis dataene ikke er av samme type, endrer DAX hele kolonnen til datatypen som passer best til alle verdiene.

DAX-datatyper

Du kan importere data i en Power Pivot-datamodell fra mange forskjellige datakilder, som igjen kan støtte forskjellige datatyper. Når du importerer eller laster inn dataene og deretter bruker dem i beregninger eller pivottabeller , konverteres dataene til en Power Pivot-datatype. For en liste over datatypene kan du se Datatyper i datamodeller.

Tabelldatatyper er en ny datatype i DAX som brukes som inndata eller utdata i mange nye funksjoner. FILTER-funksjonen bruker for eksempel en tabell som inndata og returnerer en annen tabell som bare inneholder radene som oppfyller filterbetingelsene. Ved å kombinere tabellfunksjoner med aggregeringsfunksjoner kan du utføre komplekse beregninger med dynamisk definerte datasett. Hvis du vil ha mer informasjon, kan du se Aggregeringer i Power Pivot.

Formler og relasjonsmodellen

Power Pivot-vinduet er et område der du kan arbeide med flere datatabeller og koble sammen tabellene i en relasjonsmodell. I denne datamodellen er tabeller vanligvis koblet til hverandre gjennom relasjoner, som brukes til å opprette korrelasjoner med kolonner i andre tabeller og utføre mer interessante beregninger. Du kan for eksempel opprette formler som summerer verdier for en relatert tabell og deretter lagrer denne verdien i én enkelt celle. Hvis du vil kontrollere radene fra den relaterte tabellen, kan du bruke filtre på tabeller og kolonner. Hvis du vil ha mer informasjon, kan du se Relasjoner mellom tabeller i en datamodell.

Siden du kan koble tabeller ved å bruke relasjoner, kan pivottabeller også inneholde data fra flere kolonner fra ulike tabeller.

Siden formler kan virke med hele tabeller og kolonner, må du imidlertid utforme beregninger på en annen måte enn du gjør i Excel.

Generelt brukes en DAX-formel i en kolonne alltid på hele settet av verdier i kolonnen (aldri bare på noen få rader eller celler).
Tabeller i Power Pivot må alltid ha samme antall kolonner i hver rad, og alle rader i en kolonne må inneholde samme datatype.
Når tabeller er koblet sammen med en relasjon, forventes det at du kontrollerer at de to kolonnene som brukes som nøkler, for det meste har samsvarende verdier. Siden Power Pivot ikke fremtvinger referanseintegritet, kan du ha verdier uten samsvar i en nøkkelkolonne og likevel opprette en relasjon. Tomme verdier eller verdier uten samsvar kan imidlertid virke inn på formelresultater og hvordan pivottabeller vises. Hvis du vil ha mer informasjon, kan du se Oppslag i Power Pivot-formler.
Når du kobler tabeller ved å bruke relasjoner, øker du omfanget, eller konteksten , som formlene evalueres i. Formler i en pivottabell kan for eksempel bli påvirket av eventuelle filtre eller kolonne- og radoverskrifter i pivottabellen. Du kan skrive formler som endrer konteksten, men konteksten kan også føre til at resultatene endres på måter du kanskje ikke har forventet. Hvis du vil ha mer informasjon, kan du se Kontekst i DAX-formler.

Oppdatere formelresultatene

Data oppdatering og ny beregning er to separate, men relaterte operasjoner du bør forstå når du utformer en datamodell som inneholder kompliserte formler, store datamengder eller data som er hentet fra eksterne datakilder.

Oppdatere data innebærer oppdatering av dataene i arbeidsboken med nye data fra en ekstern datakilde. Du kan oppdatere data manuelt etter intervaller du angir selv. Hvis du har publisert arbeidsboken til et SharePoint-nettsted, kan du i stedet planlegge automatisk oppdatering fra eksterne kilder.

Ny beregning innebærer oppdatering av resultatene av formler og beregnede kolonner i arbeidsboken for å gjenspeile eventuelle endringer i formlene og eventuelle endringer i de underliggende dataene. Ny beregning kan virke inn på ytelsen på følgende måter:

Når det gjelder en beregnet kolonne, må resultatet av formelen alltid beregnes på nytt. Når det gjelder hele kolonnen, må resultatet beregnes på nytt hver gang du endrer formelen.
For et mål beregnes ikke resultatene av en formel før målet er plassert i konteksten til pivottabellen eller PivotChart. Formelen blir også beregnet på nytt når du endrer en rad- eller kolonneoverskrift som påvirker filtre på dataene, eller når du oppdaterer pivottabellen manuelt.

Feilsøke formler

Feil under skriving av formler

Hvis du får en feil når du definerer en formel, kan det hende at formelen enten har en syntaksfeil, en semantisk feil eller en beregningsfeil.

Syntaksfeilene er enklest å løse. De handler vanligvis om manglende parenteser eller semikolon. Hvis du trenger hjelp med syntaksen for enkeltfunksjoner, kan du se DAX-funksjonsreferansen.

Den andre typen feil oppstår når syntaksen er korrekt, men verdien eller kolonnen det refereres til, ikke er logisk i formelkonteksten. Slike semantiske feil og beregningsfeil kan skyldes noen av følgende problemer:

Formelen refererer til en kolonne, tabell eller funksjon som ikke finnes.
Formelen ser ut til å være riktig, men når datamotoren henter dataene, finner den en typekonflikt og returnerer en feil.
Formelen sender feil antall eller feil type parametere til en funksjon.
Formelen refererer til en annen kolonne som inneholder en feil, og kolonneverdiene er derfor ugyldige.
Formelen refererer til en kolonne som ikke har blitt prosessert, noe som betyr at den har metadata men ingen faktiske data som kan brukes i beregninger.

I de fire første tilfellene flagger DAX hele kolonnen som inneholder den ugyldige formelen. I det siste tilfellet tones kolonnen ned av DAX for å angi at kolonnen er i en ubehandlet tilstand.

Feil eller uvanlige resultater under rangering eller sortering av kolonneverdier

Når man rangerer eller sorterer en kolonne som inneholder verdien NaN (Not a Number - ikke et tall), kan du få feil eller uventede resultater. Når for eksempel 0 divideres med 0 i en beregning, returneres et NaN-resultat.

Dette skyldes at formelmotoren utfører sortering og rangering ved å sammenligne de numeriske verdiene. NaN kan imidlertid ikke sammenlignes med andre tall i kolonnen.

Du kan sikre korrekte resultater ved å bruke betingelsessetninger der en HVIS-funksjon tester for NaN-verdier og returnerer en numerisk 0-verdi.

Kompatibilitet med Analysis Services-tabellmodeller og DirectQuery-modus

Generelt er DAX-formler som bygges i Power Pivot helt kompatible med Analysis Services-tabellmodeller. Hvis du imidlertid overfører Power Pivot-modellen til en Analysis Services-forekomst og deretter distribuerer modellen i DirectQuery-modus, finnes det noen begrensninger.

Noen DAX-formler kan returnere forskjellige resultater hvis du distribuerer modellen i DirectQuery-modus.
Noen formler kan forårsake valideringsfeil når du distribuerer modellen til DirectQuery-modus fordi formelen inneholder en DAX-funksjon som ikke støttes mot en relasjonsdatakilde.

Hvis du vil ha mer informasjon, kan du se dokumentasjonen for tabellmodellering i Analysis Services i SQL Server 2012 Books Online.