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

Hvis du arbejder med data i Excel og PowerPivotFør eller siden vil du støde på DAX. Selvom navnet "Data Analysis Expressions" lyder teknisk og lidt skræmmendeRealiteten er, at med en god forståelse af de grundlæggende elementer bliver det et meget praktisk og effektivt værktøj til modellering af information.

I Power Pivot er DAX hjertet i tabelmodeller: Det bruges til at oprette beregnede kolonner og målinger, der fodrer pivottabeller og diagrammerDet giver dig mulighed for at lege med filtres kontekst, arbejde med datoer, håndtere fejl og foretage sammenligninger i El tiempo Og meget mere. Lad os se nærmere på, hvordan alt dette passer ind i DAX-modellerne i Power Pivot, og hvordan man kan udnytte det uden at blive vanvittig.

Hvad er DAX, og hvorfor er det vigtigt i Power Pivot-modeller?

DAX (Data Analysis Expressions) er frem for alt, et formelsprog designet til datamodellerikke et sprog af programmering Klassisk. Dens funktion er at definere brugerdefinerede beregninger, der anvendes på tabeller og kolonner i din Power Pivot-model.

Selvom det visuelt kan ligne excel formler, DAX er designet til at arbejde med relationelle data og dynamiske aggregeringer.Det betyder, at den integreres problemfrit med tabeller, der er forbundet via relationer, pivottabeller og komplekse modeller, hvor filterkonteksten ændres afhængigt af de felter, du bruger i rækker, kolonner eller udsnit.

I en Power Pivot-model kan du bruge DAX til at To hovedtyper af objekter: beregnede kolonner og målingerBegge sameksisterer inden for den samme model, men hver har et forskelligt formål og evalueres forskelligt, noget der bør være meget klart for ikke at blande koncepter.

Derudover inkluderer DAX tekst, dato og klokkeslæt, logiske, matematiske, filter- og "tidsintelligens"-funktionersamt funktioner, der returnerer komplette tabeller som følge heraf. Disse sidstnævnte funktioner er en af ​​de største forskelle i forhold til Excel, hvor formler ikke returnerer tabeller som sådan, men højst arrays inden for celleområder.

Oversigt over DAX-formler og formellinjen

DAX-formler følger en struktur, der minder meget om Excels: De starter med lighedstegnet (=), og derefter skrives udtrykket eller funktionen med dets argumenterDu kan kombinere operatorer, funktioner, kolonne- og tabelreferencer osv., ligesom du ville gøre, når du opbygger en kompleks formel i et regneark.

Der er dog vigtige nuancer. DAX fungerer aldrig med referencer af typen A1:C10I stedet henvises der til hele kolonner eller tabeller, for eksempel 'Salg' o 'Kalender'Dette giver meget mening i en tabelmodel, men det tvinger dig til at ændre din tankegang, hvis du kommer fra Excels rendyrkede "celle for celle"-verden.

Power Pivot har en formellinje svarende til Excels hvilket i høj grad forenkler oprettelsen af ​​udtryk. Den har autofuldførelse til funktioner, tabeller og kolonner: Når du begynder at skrive navnet på en tabel eller kolonne, vises en rulleliste med gyldige muligheder, hvilket reducerer syntaksfejl og sparer tid.

For at skrive tabelnavne skal du blot begynde at skrive og lade det køre. Autofuldførelse foreslår matchende navneFor kolonner kan du enten åbne en parentes og vælge kolonnen i den aktuelle tabel eller skrive tabelnavnet efterfulgt af parenteser og vælge fra listen.

Men i modsætning til Excel, Power Pivot lukker ikke automatisk parenteser Den matcher dem ikke for dig. Det er dit ansvar at sikre, at funktionerne er korrekt formaterede med det korrekte antal argumenter og komplette parenteser, ellers kan formlen ikke gemmes eller bruges.

Hvor DAX-formler bruges: beregnede kolonner og målinger

I en Power Pivot-model kan du skrive DAX-formler i beregnede kolonner og medidas (også kaldet beregnede felter i forbindelse med pivottabeller). Selvom de deler det samme sprog, opfører de sig på meget forskellige måder.

Beregnede kolonner i Power Pivot

En beregnet kolonne er et nyt felt, som du tilføjer til en eksisterende tabel i modellenI stedet for at importere den værdi fra datakilden definerer du en DAX-formel, der evalueres række for række. Resultatet gemmes i kolonnen for hver række i tabellen.

De beregnede kolonner anvendes ensartet på alle rækker: Du kan ikke have en forskellig formel for hver række.I modsætning til i Excel, hvor du kan trække og slippe dele manuelt, evalueres det udtryk, du definerer, automatisk for hele kolonnen og genberegnes i Power Pivot, når dataene opdateres, eller en modelgenberegning fremtvinges.

Denne type kolonne kan baseres på andre beregnede kolonner eller i målingerDet anbefales dog ikke at genbruge det samme navn for både målet og kolonnen for at undgå forvirring ved referencer. Det er en meget god idé altid at bruge den fulde kolonnereference (tabel) for at undgå utilsigtet reference til et mål med samme navn.

Beregnede kolonner er ideelle, når du har brug for det yderligere attributter, du vil bruge i rækker, kolonner, filtre eller udsnit af pivottabeller eller som nøgler til relationer. Tænk for eksempel på en "Margin"-kolonne beregnet som –, som du derefter kan bruge til at gruppere eller filtrere.

Målinger eller beregnede felter

Foranstaltningerne er for deres vedkommende beregninger, der evalueres i konteksten af ​​en pivottabel eller visualiseringDe gemmes ikke rækkevis, men genberegnes løbende for hver kombination af filtre, rækker og kolonner, der er aktiv i rapporten.

en typisk måling Det kunne være noget så simpelt som:

Samlet salg = SUM(Salg)

Denne foranstaltning, der er placeret i området Værdier I pivottabellen evalueres hver celle i henhold til konteksten (f.eks. efter år, efter produkt, efter region...). Den samme beregning returnerer forskellige resultater afhængigt af de anvendte filtre. og designet af pivottabellen.

Målingerne gør ingenting, før de bruges i en rapport. De gemmes sammen med datamodellen og vises i feltlisten i pivottabeller. så alle brugere af bogen kan bruge dem. De er grundlæggende for fleksible aggregerede beregninger, såsom forhold, bidragsprocenter, kumulative totaler, sammenligninger mellem perioder osv.

Vigtige forskelle mellem DAX-funktioner og Excel-funktioner

Selvom mange DAX-funktioner ligner Excel-funktioner i navn og generel funktionalitet, De er ikke blot udskiftelige.Der er vigtige forskelle, der påvirker, hvordan formler konstrueres i en Power Pivot-model.

Først DAX fungerer ikke med individuelle celler eller områderHele kolonner eller tabeller bruges altid som reference. Dette tvinger dig til at tænke mere i datasæt end individuelle elementer, hvilket passer bedre til tilgangen i en relationel datamodel.

I datointervallet returnerer DAX reelle dato- og klokkeslætstypeværdierSelvom Excel typisk repræsenterer datoer som serienumre, er denne forskel gennemsigtig i de fleste tilfælde, men det er vigtigt at huske på det, når man kombinerer modeller eller importerer data fra andre systemer.

Et andet nøglepunkt er det Mange af de nye DAX-funktioner returnerer komplette tabeller (for eksempel FILTER, ALL, VALUES osv.) eller de accepterer tabeller som argumenter. Excel har derimod ikke et koncept for en funktion, der "returnerer en tabel" i samme forstand, selvom der findes matrixformler.

Endelig antages det i DAX, at Alle værdier i en kolonne deler den samme datatypeHvis datatyper blandes, vil datamotoren gennemtvinge en konvertering af hele kolonnen til den type, der bedst passer til alle poster, hvilket nogle gange kan føre til overraskelser, hvis datakilderne ikke kontrolleres grundigt.

Datatyper i DAX og tabeltypen

Når du importerer oplysninger til en Power Pivot-model, Dataene konverteres til en af ​​de datatyper, der understøttes af motoren. (tal, tekst, booleske værdier, datoer og klokkeslæt, valuta osv.). Denne type data bestemmer, hvilke operationer der er gyldige, og hvordan formler evalueres.

En vigtig ny funktion i forhold til klassisk Excel er tabeldatatypeMange DAX-funktioner accepterer en hel tabel som argument og returnerer en anden tabel som følge heraf. For eksempel tager FILTER en tabel og en betingelse og returnerer en tabel med kun de rækker, der opfylder betingelsen.

Kombinering af funktioner, der returnerer tabeller med aggregeringsfunktioner såsom SUMX, AVERAGEX eller MINXDer kan bygges meget sofistikerede beregninger, der fungerer på dynamisk definerede delmængder af data. Dette resulterer i brugerdefinerede aggregeringer, der tilpasser sig de filtre, der er aktive på et givet tidspunkt.

Relationer, kontekst og den relationelle model i Power Pivot

Power Pivot-vinduet er der, hvor den relationelle datamodel bygges. Der kan du importere flere tabeller og oprette relationer mellem dem. (for eksempel Salg med produkter, Salg med kalender, Salg med kunder osv.). Disse relationer er grundlaget for, at DAX-formler kan hoppe fra én tabel til en anden.

Når tabeller er relaterede, Du kan skrive formler, der lægger værdier sammen fra en relateret tabel og brug dem i den tabel, hvorfra du skriver udtrykket. Du kan også styre, hvilke rækker der deltager i en beregning, ved at anvende filtre på bestemte kolonner.

Det er vigtigt at lægge vægt på Alle rækker i en Power Pivot-tabel skal have det samme antal kolonnerHver kolonne skal opretholde en ensartet datatype på tværs af alle dens rækker. Hvis relationsnøglerne har uoverensstemmende værdier (blanke felter, forældreløse værdier osv.), kan opslagsformler og pivottabeller returnere uventede resultater.

Et andet grundlæggende koncept er sammenhængI DAX bruges udtrykkene rækkekontekst og filterkontekst primært. Rækkekonteksten er den "aktuelle" række, hvor en beregnet kolonne eller iterator evalueres; filterkonteksten er sættet af aktive filtre (fra pivottabellen, udsnit, relationer, funktioner som CALCULATE osv.).

Ved at lege med funktioner som CALCULATE, ALL, ALLEXCEPT eller FILTER kan du Rediger filterkonteksten for at ændre, hvordan en måling evalueres.Dette gør det f.eks. muligt at beregne procentdelen af ​​salget af et produkt i forhold til det samlede salg eller at sammenligne en divisions præstation med virksomheden som helhed uden filtre.

Dataopdatering og genberegning af DAX-formel

I en model, der bruger komplekse formler eller store datamængder, er det vigtigt at forstå, hvordan opdateringen fungerer. Det er vigtigt at skelne mellem at opdatere dataene og at genberegne formlerne.som er relaterede, men uafhængige processer.

Dataopdateringen består af bringe nye optegnelser ind i bogen fra eksterne kilder (databaser(filer, onlinetjenester osv.). Du kan starte denne opdatering manuelt efter behov, eller planlægge den, hvis bogen er udgivet i SharePoint eller et andet kompatibelt miljø. Ofte udføres denne proces ved hjælp af Power Query i Excel at forberede og transformere dataene, før de indlæses i modellen.

Genberegning er derimod den proces, hvorved DAX-formlerne revurderes for at afspejle ændringer i dataene eller i selve udtrykkene.For beregnede kolonner skal hele kolonnen genberegnes på én gang, hvis du ændrer formlen. For målinger sker genberegningen, når konteksten ændres (filtre, række-/kolonnefelter i pivottabeller), eller når pivottabeller opdateres manuelt.

Disse genberegninger kan påvirke ydeevnen, især hvis mange komplekse beregnede kolonner eller intensive iterative funktioner bruges i store tabellerDerfor er det en god praksis at flytte det meste af logikken til målinger i stedet for kolonner, når det er muligt.

Registrering og korrektion af fejl i DAX-formler

Når man skriver DAX-formler, er det almindeligt at støde på tre typer fejl: syntaktiske fejl, semantiske fejl og beregningsfejlEnhver har sine egne omstændigheder og sin egen måde at rette sig selv på.

Syntaksfejl er de enkleste: manglende parenteser, forkert placerede kommaer, forkert stavede funktionsnavneosv. Hjælpen til automatisk fuldførelse og DAX-funktionsreferencen sparer dig for mange af disse faldgruber.

Semantiske fejl og beregningsfejl opstår, når syntaksen er korrekt, selvom Formlen gør noget, der ikke giver mening i modellens kontekst.For eksempel at referere til en ikke-eksisterende tabel eller kolonne, sende et forkert antal argumenter til en funktion, blande inkompatible typer eller være afhængig af en kolonne med tidligere fejl.

I disse tilfælde markerer DAX normalt Hele kolonnen blev beregnet som forkert.ikke blot en specifik række, fordi kolonnen betragtes som en enhed. Hvis en kolonne kun indeholder metadata, men endnu ikke er blevet behandlet (ikke har indlæst data), vises den nedtonet, og formler, der er afhængige af den, vil ikke kunne evalueres korrekt.

Et særligt tilfælde er værdier NaN (Ikke et tal)Disse værdier kan for eksempel opstå, når man dividerer 0 med 0. Hvis en kolonne indeholder NaN-værdier, kan sortering eller klassificering af disse værdier give mærkelige resultater, fordi NaN-værdier ikke kan sammenlignes med andre tal på den sædvanlige måde. I sådanne tilfælde er det tilrådeligt at bruge HVIS-sætninger eller andre logiske funktioner til at erstatte NaN-værdierne med 0 eller en anden håndterbar numerisk værdi.

Kompatibilitet med tabelmodeller og DirectQuery-tilstand

De DAX-formler, du opretter i Power Pivot, er generelt kompatibel med SQL Server Analysis Services tabelmodellerDet betyder, at du kan migrere din model til en tabelserver og fortsætte med at udnytte den logik, du allerede har opbygget.

Men når en tabelmodel implementeres i tilstanden DirectQueryDer kan forekomme begrænsninger: Nogle DAX-funktioner understøttes ikke direkte på visse relationelle databaser. eller de kan returnere lidt forskellige resultater på grund af hvordan forespørgslerne er delegeret.

I disse scenarier er det vigtigt at gennemgå den specifikke dokumentation for den tabelformede motor og validér de kritiske foranstaltninger for at bekræfte, at de fortsat fungerer som forventet efter aktivering af DirectQuery.

Praktiske scenarier: komplekse beregninger med CALCULATE og filtre

En af DAX's styrker er dens evne til at udføre komplekse beregninger, der er afhængige af brugerdefinerede aggregeringer og dynamiske filtreFunktionerne CALCULATE og CALCULATETABLE er centrale i denne type scenarie.

BEREGN tillader omdefiner filterkonteksten, som et udtryk evalueres overFor eksempel kan du anmode om "summen af ​​salg filtreret til et bestemt år, selvom pivottabellen viser andre år" eller "totalen uden at anvende bestemte produktfiltre".

Overalt hvor en DAX-funktion accepterer en tabel som et argument, Du kan sende en filtreret version af den tabelDette kan gøres enten ved at bruge FILTER eller ved at angive betingelser i CALCULATE. Dette gør det muligt at bygge målinger, der tilpasser sig tusindvis af kombinationer af betingelser uden at skulle oprette mellemliggende kolonner.

Det er også muligt fjern selektivt eksisterende filtre ved hjælp af funktioner som ALL eller ALLEXCEPT. For eksempel, for at beregne bidraget fra en specifik forhandler i forhold til det samlede antal forhandlere, kan du have en måling, der dividerer værdien i den aktuelle kontekst med værdien i "ALL"-konteksten (uden filtre efter forhandler).

I andre tilfælde skal du bruge værdier af en "ydre løkke"Det vil sige, at referere tilbage til den forrige række eller iterationskontekst. Det er her, funktioner som EARLIER kommer ind i billedet, som tillader op til to niveauer af indbyggede løkker og er meget nyttige til at oprette ranglister, gruppetotaler eller beregninger, der afhænger af en tidligere rækkekontekst.

Jeg arbejder med tekst, datoer og nøgler i DAX

DAX tilbyder også mange værktøjer til manipulere tekst og datoerDette er kritisk, når datakilder indeholder datoer i usædvanlige formater, sammensatte nøgler eller tekstfelter, der skal konverteres til tidsværdier.

Power Pivot understøtter dem ikke direkte sammensatte nøgler i relationerHvis din kilde bruger flere kolonner som nøgler, skal du i mange tilfælde oprette en beregnet kolonne, der sammenkæder disse dele til en enkelt nøgle og bruge det som et relationsfelt.

Når datoer findes i formater, der ikke genkendes af systemet (f.eks. en dato i et usædvanligt regionalt format eller et heltal som 01032009 importeret som tekst), kan du oprette formler som denne:

=INFORMATION(HØJRE(,4), VENSTRE(,2), MIDTE(,3,2))

Med denne type udtryk, Du rekonstruerer en gyldig SQL Server-dato fra fragmenter udtrukket fra strengen, som derefter giver dig mulighed for at bruge tidsintelligensfunktioner uden problemer.

Du kan også ændre datatyper ved hjælp af formlerGang med 1,0 for at konvertere datoer eller numeriske strenge til tal, eller sammenkæd med en tom streng for at transformere et tal eller en dato til tekst. Derudover er der specifikke funktioner til at kontrollere returtypen (afkorte decimaler, tvinge heltal osv.).

Betingede værdier og fejlhåndtering i kolonner og målinger

Ligesom i Excel indeholder DAX funktioner til returnere resultater baseret på betingelser og til at håndtere fejl elegant. For eksempel kan du mærke forhandlere som "Foretrukken" eller "Værdi" baseret på deres årlige salgsvolumen ved hjælp af indlejrede HVIS-sætninger.

I en beregnet kolonne dog Du har ikke råd til at have nogle rækker med fejl og andre uden.Hvis én række giver en fejl, markeres hele kolonnen som forkert. Dette kræver strengere fejlkontrol end i et konventionelt regneark.

For at forhindre, at en simpel division med nul eller en blank værdi får hele kolonnen til at gå ned, anbefales det indpak følsomme operationer i forudgående kontroller Ved hjælp af HVIS- og informationsfunktioner returneres altid en gyldig værdi, selv når datakombinationen er mærkelig.

Når du bygger modellen, kan den være nyttig Lad fejlene fremgå i begyndelsen, så du kan finde og rette dem.Men når du først har udgivet den til andre brugere, er det vigtigt at sikre, at formlerne er sikre, og at der aldrig vises nogen fejlmeddelelse i pivottabellerne eller visualiseringerne.

Tidsintelligens: kumulative totaler, sammenligninger og brugerdefinerede perioder

Tidsintelligensfunktioner er en af ​​DAX' store attraktioner. De giver dig mulighed for at arbejde med datointervaller, beregne akkumulerede totaler, sammenligne perioder og generere brugerdefinerede tidsvinduer. med relativ lethed, forudsat at du har en velkonfigureret kalendertabel.

Målinger kan oprettes kumulativt salg pr. dag, måned, kvartal eller årBeregn åbnings- og slutsaldi for hver periode, eller sammenlign salg fra et år til det foregående år, kvartal til kvartal osv. ved hjælp af specifikke tidsfunktioner.

Derudover kan du genvinde brugerdefinerede sæt datoersåsom "de første 15 dage efter starten af ​​en kampagne" eller "samme periode sidste år", og derefter sende dette sæt til en funktion, der aggregerer dataene over det specifikke datovindue.

Funktioner som PARALLELPERIODE og andre relateret til parallelle perioder De letter sammenligning mellem tidsforskudte intervaller.For eksempel at analysere, om en kampagne har forbedret resultaterne sammenlignet med samme periode i et andet år.

Rangering og sammenligning af værdier: top N og dynamiske rangeringer

Når du har brug for at vise kun de mest relevante elementer (for eksempel de 10 bedst sælgende produkter), har du to hovedveje: brug Excels filtreringsfunktioner på pivottabellen eller opbyg en dynamisk rangering med DAX.

Excel tilbyder filtre af typen "Top 10" i pivottabeller, meget nem at sætte op For kun at vise elementerne over eller under et givet numerisk felt. Du kan filtrere efter antal elementer, kumulativ procentdel eller sum af værdier.

Problemet med denne tilgang er, at Filteret er udelukkende til præsentation.Hvis de underliggende data ændrer sig, skal du manuelt opdatere pivottabellen for at filteret afspejles korrekt. Derudover kan du ikke genbruge denne rangering som en del af andre DAX-formler.

Alternativet er at oprette en beregnet kolonne eller måling, der tildeler en rangering til hvert element ved hjælp af DAX. Denne mulighed er mere beregningsmæssigt dyr, men den har fordele: rangeringen genberegnes dynamisk og kan bruges i dataslicere, så brugeren kan vælge, om de vil se top 5, top 10, top 50 osv.

Men i modeller med millioner af rækker, Dynamiske ranglister kan være besværlige. og det er nødvendigt at vurdere, om omkostningerne i ydeevne opvejes af den funktionelle fordel, de giver.

Bedste fremgangsmåder ved design af DAX-modeller i Power Pivot

For at en DAX-model i Power Pivot kan vedligeholdes og fungere godt, er det ikke nok, at formlerne "virker". Det er tilrådeligt at følge en række gode fremgangsmåder som gør en stor forskel i virkelige projekter.

En tilbagevendende anbefaling er prioritér målinger frem for beregnede kolonner, når beregningen faktisk er en dynamisk aggregering og ikke en fast attribut. Beregnede kolonner optager hukommelse og genberegnes på én gang, mens målinger kun evalueres, når det er nødvendigt.

Det er også meget nyttigt brug af variabler i DAX (VAR) For at forenkle komplekse formler skal du undgå at gentage den samme beregning flere gange og forbedre læsbarheden. Dette hjælper både ydeevnen og forståelsen af ​​modellen, når en anden gennemgår den.

Endelig gør klare navne og minimal intern dokumentation hele forskellen. Giv beskrivende navne til målinger og kolonnerUndgå obskure forkortelser, og dokumenter de vigtigste formler. Dette reducerer indlæringskurven for nye brugere og sparer dig hovedpine, når du vender tilbage til modellen måneder senere.

At mestre DAX i Power Pivot handler ikke om at huske alle funktionerne, men om at forstå, hvordan formlerne interagerer med relationsmodellen, filterkonteksten og dataopdateringer. Med et solidt fundament i beregnede kolonner, målinger, tidsfunktioner, fejlhåndtering og god designpraksisDine tabelmodeller bliver mere fleksible, meget nemmere at analysere og frem for alt i stand til at besvare komplekse forretningsspørgsmål blot ved at trække et par felter ind i en pivottabel.