Data Analysis Expressions (DAX) i Power Pivot

DAX (Data Analysis Expressions) lyder umiddelbart lidt skræmmende, men du skal ikke narre af navnet. Det grundlæggende om DAX er faktisk ret nemt at forstå. Først og fremmest – DAX er IKKE et programmeringssprog. DAX er et formelsprog. Du kan bruge DAX til at definere brugerdefinerede beregninger for beregnede kolonner og for målinger (også kaldet beregnede felter). DAX indeholder nogle af de funktioner, der bruges i Excel-formler, samt yderligere funktioner, der er udviklet til at arbejde med relationelle data og udføre dynamisk aggregering.

Forstå DAX-formler

DAX-formler minder meget om Excel-formler. Hvis du vil oprette et, skal du skrive et lighedstegn efterfulgt af et funktionsnavn eller udtryk samt eventuelle påkrævede værdier eller argumenter. Ligesom Excel indeholder DAX en række funktioner, du kan bruge til at arbejde med strenge, udføre beregninger ved hjælp af datoer og klokkeslæt eller oprette betingede værdier.

DAX-formler er dog forskellige på følgende vigtige måder:

• Hvis du vil tilpasse beregninger på række-for-række-basis, indeholder DAX funktioner, der gør det muligt at bruge den aktuelle rækkeværdi eller en relateret værdi til at udføre beregninger, der varierer i konteksten.
• DAX indeholder en type funktion, der returnerer en tabel som resultat i stedet for en enkelt værdi. Disse funktioner kan bruges til at give input til andre funktioner.
• Tidsintelligente funktioneri DAX tillader beregninger ved hjælp af datointervaller og sammenligner resultaterne på tværs af parallelle perioder.

Hvor bruges DAX-formler?

Du kan oprette formler i Power Pivot enten i beregnede kolonner eller i beregnede felter.

Beregnede kolonner

En beregnet kolonne er en kolonne, du føjer til en eksisterende Power Pivot-tabel. I stedet for at indsætte eller importere værdier i kolonnen kan du oprette en DAX-formel, der definerer kolonneværdierne. Hvis du medtager PowerPivot-tabellen i en pivottabel (eller et pivotdiagram), kan den beregnede kolonne bruges som en hvilken som helst anden datakolonne.

Formlerne i beregnede kolonner minder meget om de formler, du opretter i Excel. I modsætning til i Excel kan du dog ikke oprette en anden formel for forskellige rækker i en tabel. I stedet anvendes DAX-formlen automatisk på hele kolonnen.

Når en kolonne indeholder en formel, beregnes værdien for hver række. Resultatet beregnes for kolonnen, så snart du opretter formlen. Kolonneværdier genberegnes kun, hvis de underliggende data opdateres, eller hvis der bruges manuel genberegning.

Du kan oprette beregnede kolonner, der er baseret på målinger og andre beregnede kolonner. Undgå dog at bruge det samme navn til en beregnet kolonne og en måling, da dette kan føre til forvirrende resultater. Når du refererer til en kolonne, er det bedst at bruge en fuldt kvalificeret kolonnereference for at undgå utilsigtet aktivering af en måling.

Du finder flere oplysninger under Beregnede kolonner i Power Pivot.

Foranstaltninger

En måling er en formel, der oprettes specielt til brug i en pivottabel (eller et pivotdiagram), der anvender Power Pivot-data. Målinger kan baseres på standardsammenlægningsfunktioner, f.eks. TÆL eller SUM, eller du kan definere dine egne formler ved hjælp af DAX. En måling bruges i området Værdier i en pivottabel. Hvis du vil placere beregnede resultater i et andet område af en pivottabel, skal du bruge en beregnet kolonne i stedet.

Når du definerer en formel for en eksplicit måling, sker der intet, før du tilføjer målingen i en pivottabel. Når du tilføjer målingen, evalueres formlen for hver celle i området Værdier i pivottabellen. Da der oprettes et resultat for hver kombination af række- og kolonneoverskrifter, kan resultatet for målingen være forskellig i hver celle.

Definitionen af den måling, du opretter, gemmes sammen med dens kildetabel. Den vises på pivottabelfeltlisten og er tilgængelig for alle brugere af projektmappen.

Du finder flere oplysninger under Målinger i Power Pivot.

Oprette formler ved hjælp af formellinjen

Power Pivot indeholder ligesom Excel en formellinje, der gør det nemmere at oprette og redigere formler, og autofuldførelsesfunktionalitet, der minimerer skrivearbejdet og syntaksfejl.

Sådan angiver du navnet på en tabel Begynd at skrive navnet på tabellen. Autofuldførelse af formel indeholder en rulleliste med gyldige navne, der begynder med disse bogstaver.

Sådan angives navnet på en kolonne Indtast en parentes, og vælg derefter kolonnen på listen over kolonner i den aktuelle tabel. For en kolonne fra en anden tabel skal du begynde at skrive de første bogstaver i tabelnavnet og derefter vælge kolonnen på rullelisten Autofuldførelse.

Du kan finde flere oplysninger og en gennemgang af, hvordan du opbygger formler, under Oprette formler til beregninger i Power Pivot.

Tip til brug af Autofuldførelse

Du kan bruge Autofuldførelse af formel midt i en eksisterende formel med indlejrede funktioner. Teksten umiddelbart før indsætningspunktet bruges til at vise værdier på rullelisten, og al tekst efter indsætningspunktet forbliver uændret.

Definerede navne, du opretter for konstanter, vises ikke på rullelisten Autofuldførelse, men du kan stadig skrive dem.

Power Pivot tilføjer ikke den afsluttende parentes for funktioner og matcher ikke automatisk parenteser. Du skal sikre dig, at hver funktion er syntaktisk korrekt, ellers kan du ikke gemme eller bruge formlen. 

Brug af flere funktioner i en formel

Du kan indlejre funktioner, hvilket vil sige, at du bruger resultaterne fra én funktion som argument i en anden funktion. Du kan indlejre op til 64 funktionsniveauer i beregnede kolonner. Men indlejring kan gøre det vanskeligt at oprette eller udføre fejlfinding af formler.

Mange DAX-funktioner er udviklet til udelukkende at blive brugt som indlejrede funktioner. Disse funktioner returnerer en tabel, som ikke kan gemmes direkte som et resultat; Den skal leveres som input til en tabelfunktion. Funktionerne SUMX, AVERAGEX og MINX kræver f.eks. alle en tabel som det første argument.

Sammenligning af DAX-funktioner og Excel-funktioner

DAX-funktionsbiblioteket er baseret på Excel-funktionsbiblioteket, men bibliotekerne er meget forskellige. Dette afsnit opsummerer forskellene og lighederne mellem Excel-funktioner og DAX-funktioner.

• Mange DAX-funktioner har samme navn og samme generelle funktionsmåde som Excel-funktionerne, men er blevet ændret, så de kan tage forskellige typer input og i nogle tilfælde kan de returnere en anden datatype. Generelt kan du ikke bruge DAX-funktioner i en Excel-formel eller bruge Excel-formler i Power Pivot uden ændringer.
• DAX-funktioner bruger aldrig en cellereference eller et område som reference, men i stedet bruger DAX-funktioner en kolonne eller tabel som reference.
• DAX-dato- og klokkeslætsfunktioner returnerer en datetime-datatype. I modsætning hertil returnerer dato- og klokkeslætsfunktioner i Excel et heltal, der repræsenterer en dato som et serienummer.
• Mange af de nye DAX-funktioner returnerer enten en tabel med værdier eller foretager beregninger baseret på en tabel med værdier som input. Excel har derimod ingen funktioner, der returnerer en tabel, men nogle funktioner kan arbejde med matricer. Muligheden for nemt at henvise til komplette tabeller og kolonner er en ny funktion i Power Pivot.
• DAX indeholder nye opslagsfunktioner, der ligner matrix- og vektoropslagsfunktioner i Excel. DAX-funktionerne kræver dog, at der etableres en relation mellem tabellerne.
• Dataene i en kolonne forventes altid at være af samme datatype. Hvis dataene ikke er af samme type, ændrer DAX hele kolonnen til den datatype, der bedst passer til alle værdier.

DAX-datatyper

Du kan importere data til en Power Pivot-datamodel fra mange forskellige datakilder, der kan understøtte forskellige datatyper. Når du importerer eller indlæser dataene og derefter bruger dataene i beregninger eller pivottabeller, konverteres dataene til en af Power Pivot-datatyperne. Du kan finde en liste over datatyperne under Datakilder i datamodeller.

Tabeldatatypen er en ny datatype i DAX, der bruges som input eller output til mange nye funktioner. Funktionen FILTRER tager f.eks. en tabel som input og lagrer en anden tabel, der kun indeholder de rækker, der opfylder filterbetingelserne. Ved at kombinere tabelfunktioner med sammenlægningsfunktioner kan du udføre komplekse beregninger over dynamisk definerede datasæt. Du kan finde flere oplysninger under Sammenlægninger i Power Pivot.

Formler og relationsmodellen

Power Pivot-vinduet er et område, hvor du kan arbejde med flere tabeller med data og forbinde tabellerne i en relationel model. I denne datamodel er tabeller forbundet med hinanden ved hjælp af relationer, hvilket gør det muligt at oprette korrelationer med kolonner i andre tabeller og oprette mere interessante beregninger. Du kan f.eks. oprette formler, der lægger værdier sammen i en relateret tabel og derefter gemmer værdien i en enkelt celle. Hvis du vil styre rækkerne fra den relaterede tabel, kan du også anvende filtre på tabeller og kolonner. Du kan finde flere oplysninger under Relationer mellem tabeller i en datamodel.

Da du kan sammenkæde tabeller ved hjælp af relationer, kan dine pivottabeller også indeholde data fra flere kolonner, der kommer fra forskellige tabeller.

Men da formler kan arbejde med hele tabeller og kolonner, skal du designe beregninger anderledes, end du gør i Excel.

• Generelt anvendes en DAX-formel i en kolonne altid på hele værdisættet i kolonnen (aldrig kun på nogle få rækker eller celler).
• Tabeller i Power Pivot skal altid have det samme antal kolonner i hver række, og alle rækker i en kolonne skal indeholde den samme datatype.
• Når tabeller er forbundet via en relation, forventes det, at du sørger for, at de to kolonner, der bruges som nøgler, for det meste har værdier, der matcher. Da Power Pivot ikke gennemtvinger referentiel integritet, er det muligt at have ikke-matchende værdier i en nøglekolonne og stadig oprette en relation. Forekomsten af tomme eller ikke-matchende værdier kan dog påvirke resultaterne af formler og udseendet af pivottabeller. Du finder flere oplysninger under Opslag i Power Pivot-formler.
• Når du sammenkæder tabeller ved hjælp af relationer, forstørrer du omfanget eller konteksten, hvori dine formler evalueres. Formler i en pivottabel kan f.eks. påvirkes af filtre eller kolonne- og rækkeoverskrifter i pivottabellen. Du kan skrive formler, der manipulerer konteksten, men konteksten kan også medføre, at dine resultater ændres på måder, som du ikke ville forvente. Du kan finde flere oplysninger under Kontekst i DAX-formler.

Opdatere resultaterne af formler

Dataopdatering og -genberegning er to adskilte, men relaterede handlinger, som du skal forstå, når du designer en datamodel, der indeholder komplekse formler, store mængder data eller data, der er hentet fra eksterne datakilder.

Opdatering af data er den proces, hvor data opdateres i projektmappen med nye data fra en ekstern datakilde. Du kan opdatere data manuelt med intervaller, du angiver. Eller hvis du har publiceret projektmappen på et SharePoint-websted, kan du planlægge en automatisk opdatering fra eksterne kilder.

Genberegning er en proces, hvor resultaterne af formler opdateres, så de afspejler eventuelle ændringer af selve formlerne og disse ændringer i de underliggende data. Genberegning kan påvirke ydeevnen på følgende måder:

• For en beregnet kolonne skal resultatet af formlen altid genberegnes for hele kolonnen, når du ændrer formlen.
• For en måling beregnes resultaterne af en formel ikke, før målingen placeres i konteksten af pivottabellen eller pivotdiagrammet. Formlen genberegnes også, når du ændrer en række- eller kolonneoverskrift, der påvirker filtre på dataene, eller når du opdaterer pivottabellen manuelt.

Fejlfinding af formler

Fejl ved skrivning af formler

Hvis du får en fejl, når du definerer en formel, kan formlen indeholde enten en syntaktisk fejl, en semantisk fejl eller en beregningsfejl.

Syntaktiske fejl er nemmest at løse. De involverer normalt en manglende parentes eller komma. Du kan finde hjælp med syntaksen for individuelle funktioner i funktionsreferencen til DAX.

Den anden type fejl opstår, når syntaksen er korrekt, men værdien eller kolonnen, der henvises til, ikke giver mening i forbindelse med formlen. Sådanne semantiske fejl og beregningsfejl kan skyldes et af følgende problemer:

• Formlen refererer til en kolonne, tabel eller funktion, der ikke findes.
• Formlen ser ud til at være korrekt, men når dataprogrammet henter dataene, finder det en typeuoverensstemmelse, og der opstår en fejl.
• Formlen videregiver et forkert antal eller en forkert type af parametre til en funktion.
• Formlen refererer til en anden kolonne, der indeholder en fejl, og dens værdier er derfor ugyldige.
• Formlen refererer til en kolonne, der ikke er blevet behandlet, hvilket betyder, at den har metadata, men ingen faktiske data til at bruge til beregninger.

I de første fire tilfælde markerer DAX hele kolonnen, der indeholder den ugyldige formel. I det sidste tilfælde nedtoner DAX kolonnen for at angive, at kolonnen er i en ubehandlet tilstand.

Forkerte eller usædvanlige resultater ved rangering eller sortering af kolonneværdier

Når du rangerer eller sorterer en kolonne, der indeholder værdien Ikke-aktuelt (ikke et tal), kan du få forkerte eller uventede resultater. Når en beregning f.eks. dividerer 0 med 0, returneres et NaN-resultat.

Dette skyldes, at formelprogrammet udfører sortering og rangering ved at sammenligne de numeriske værdier. NaN kan dog ikke sammenlignes med andre tal i kolonnen.

For at sikre korrekte resultater kan du bruge betingede udtryk med funktionen HVIS til at teste for NaN-værdier og returnere en numerisk 0-værdi.

Kompatibilitet med Analysis Services tabelmodeller og DirectQuery-tilstand

Generelt er DAX-formler, som du bygger i Power Pivot, fuldt kompatible med Analysis Services-tabelmodeller. Men hvis du overfører din Power Pivot-model til en Analysis Services-forekomst og derefter installerer modellen i DirectQuery-tilstand, er der nogle begrænsninger.

• Nogle DAX-formler kan returnere andre resultater, hvis du installerer modellen i DirectQuery-tilstand.
• Nogle formler kan medføre valideringsfejl, når du installerer modellen i DirectQuery-tilstand, fordi formlen indeholder en DAX-funktion, der ikke understøttes af relationelle datakilder.

Du kan finde flere oplysninger i dokumentationen til Analysis Services-tabelmodellering i SQL Server 2012 BooksOnline.