DAX-modeller i Power Pivot: formler, kontext och bästa praxis

Om du arbetar med data i Excel och PowerPivotFörr eller senare kommer du att stöta på DAX. Även om namnet ”Data Analysis Expressions” låter tekniskt och lite skrämmandeVerkligheten är att med en god förståelse av grunderna blir det ett mycket bekvämt och kraftfullt verktyg för att modellera information.

I Power Pivot är DAX hjärtat i tabellmodeller: Den används för att skapa beräknade kolumner och mått som matar pivottabeller och diagramDet låter dig experimentera med filterkontexten, arbeta med datum, hantera fel och göra jämförelser i el tiempo Och mycket mer. Låt oss titta närmare på hur allt detta passar in i DAX-modellerna i Power Pivot och hur man kan dra nytta av det utan att bli galen.

Vad är DAX och varför är det viktigt i Power Pivot-modeller?

DAX (Data Analysis Expressions) är framför allt ett formelspråk utformat för datamodellerinte ett språk av programmering Klassisk. Dess funktion är att definiera anpassade beräkningar som tillämpas på tabeller och kolumner i din Power Pivot-modell.

Även om det visuellt kan likna excel-formler, DAX är utformat för att fungera med relationsdata och dynamiska aggregeringar.Det betyder att den integreras sömlöst med tabeller som är kopplade via relationer, pivottabeller och komplexa modeller där filterkontexten ändras beroende på vilka fält du använder i rader, kolumner eller utsnitt.

Inom en Power Pivot-modell kan du använda DAX för att Två huvudtyper av objekt: beräknade kolumner och måttBåda samexisterar inom samma modell, men var och en har ett annat syfte och utvärderas på olika sätt, något som bör vara mycket tydligt för att inte blanda ihop begrepp.

Dessutom inkluderar DAX text, datum och tid, logiska, matematiska, filter- och "tidsintelligens"-funktionersåväl som funktioner som returnerar kompletta tabeller som ett resultat. Dessa senare funktioner är en av de största skillnaderna jämfört med Excel, där formler inte returnerar tabeller som sådana, utan som mest arrayer inom cellområden.

Översikt över DAX-formler och formelfältet

DAX-formler följer en struktur som är väldigt lik den i Excel: De börjar med likhetstecknet (=) och sedan skrivs uttrycket eller funktionen med dess argumentDu kan kombinera operatorer, funktioner, kolumn- och tabellreferenser etc., precis som du skulle göra när du skapar en komplex formel i ett kalkylblad.

Det finns dock viktiga nyanser. DAX fungerar aldrig med referenser av typen A1:C10Istället hänvisas till hela kolumner eller tabeller, till exempel Försäljning o 'Kalender'Detta är mycket logiskt i en tabellmodell, men det tvingar dig att ändra ditt tankesätt om du kommer från Excels renodlade "cell för cell"-värld.

Power Pivot har en formelfält liknande Excels vilket avsevärt förenklar skapandet av uttryck. Den har autokomplettering för funktioner, tabeller och kolumner: när du börjar skriva namnet på en tabell eller kolumn visas en rullgardinslista med giltiga alternativ, vilket minskar syntaxfel och sparar tid.

För att skriva tabellnamn, börja helt enkelt skriva och låt det köras. Autoslutförande föreslår matchande namnFör kolumner kan du antingen öppna en hakparentes och markera kolumnen i den aktuella tabellen, eller skriva tabellnamnet följt av hakparenteser och välja från listan.

Men till skillnad från Excel, Power Pivot stänger inte automatiskt parenteser Den matchar inte dem åt dig. Det är ditt ansvar att se till att funktionerna är korrekt utformade, med rätt antal argument och kompletta parenteser, annars kan formeln inte sparas eller användas.

Där DAX-formler används: beräknade kolumner och mått

I en Power Pivot-modell kan du skriva DAX-formler i beräknade kolumner och åtgärder (även kallade beräknade fält i samband med pivottabeller). Även om de använder samma språk, beter de sig på väldigt olika sätt.

Beräknade kolumner i Power Pivot

En beräknad kolumn är ett nytt fält som du lägger till i en befintlig tabell i modellenIstället för att importera det värdet från datakällan definierar du en DAX-formel som utvärderas rad för rad. Resultatet lagras i kolumnen för varje rad i tabellen.

De beräknade kolumnerna tillämpas enhetligt på alla rader: Du kan inte ha en annan formel för varje rad.Till skillnad från i Excel, där du kan dra och släppa delar manuellt, utvärderas uttrycket du definierar automatiskt för hela kolumnen och beräknas om i Power Pivot när data uppdateras eller en modellomberäkning tvingas fram.

Den här typen av kolumn kan baseras på andra beräknade kolumner eller i måttDet rekommenderas dock att inte återanvända samma namn för både måttet och kolumnen för att undvika förvirring vid referenser. Det är en mycket bra idé att alltid använda hela kolumnreferensen (tabell) för att undvika att av misstag referera till ett mått med samma namn.

Beräknade kolumner är idealiska när du behöver ytterligare attribut som du vill använda i rader, kolumner, filter eller utsnitt av pivottabeller, eller som nycklar för relationer. Tänk till exempel på en "Marginal"-kolumn beräknad som – som du sedan kan använda för att gruppera eller filtrera.

Mått eller beräknade fält

Åtgärderna är för sin del beräkningar som utvärderas i samband med en pivottabell eller visualiseringDe lagras inte radvis, utan beräknas om direkt för varje kombination av filter, rader och kolumner som är aktiv i rapporten.

en typisk mätning Det kan vara något så enkelt som:

Total försäljning = SUMMA(Försäljning)

Denna åtgärd, som placeras inom området värden I pivottabellen utvärderas varje cell enligt sammanhanget (till exempel per år, per produkt, per region...). Samma beräkning ger olika resultat beroende på vilka filter som används. och pivottabellens design.

Åtgärderna gör ingenting förrän de används i en rapport. De lagras med datamodellen och visas i fältlistan i pivottabeller. så att alla användare av boken kan använda dem. De är grundläggande för flexibla aggregerade beräkningar, såsom förhållanden, bidragsprocent, kumulativa totaler, jämförelser mellan perioder etc.

Viktiga skillnader mellan DAX-funktioner och Excel-funktioner

Även om många DAX-funktioner liknar Excel-funktioner i namn och allmänt beteende, De är inte bara utbytbara.Det finns viktiga skillnader som påverkar hur formler konstrueras i en Power Pivot-modell.

Först av allt DAX fungerar inte med enskilda celler eller områdenHela kolumner eller tabeller används alltid som referens. Detta tvingar dig att tänka mer i termer av datamängder än enskilda objekt, vilket passar bättre in i en relationell datamodell.

I datumintervallet returnerar DAX verkliga datum- och tidstypvärdenÄven om Excel vanligtvis representerar datum som serienummer är denna skillnad tydlig i de flesta fall, men det är viktigt att ha den i åtanke när man kombinerar modeller eller importerar data från andra system.

En annan viktig punkt är det Många av de nya DAX-funktionerna returnerar kompletta tabeller (till exempel FILTER, ALL, VALUES, etc.) eller så accepterar de tabeller som argument. Excel, å andra sidan, har inte ett koncept för en funktion "som returnerar en tabell" i samma bemärkelse, även om det finns matrisformler.

Slutligen antas det i DAX att Alla värden i en kolumn delar samma datatypOm datatyper blandas kommer datamotorn att tvinga fram en konvertering av hela kolumnen till den typ som bäst passar alla poster, vilket ibland kan leda till överraskningar om datakällorna inte kontrolleras noggrant.

Datatyper i DAX och tabelltypen

När du importerar information till en Power Pivot-modell, Data konverteras till en av de datatyper som stöds av motorn. (tal, text, booleska värden, datum och tider, valuta, etc.). Denna typ av data avgör vilka operationer som är giltiga och hur formler kommer att utvärderas.

En viktig ny funktion jämfört med klassisk Excel är tabelldatatypMånga DAX-funktioner accepterar en hel tabell som argument och returnerar en annan tabell som ett resultat. Till exempel tar FILTER en tabell och ett villkor och returnerar en tabell med endast de rader som uppfyller villkoret.

Kombinera funktioner som returnerar tabeller med aggregeringsfunktioner som SUMX, AVERAGEX eller MINXMycket sofistikerade beräkningar kan byggas som arbetar med dynamiskt definierade delmängder av data. Detta resulterar i anpassade aggregeringar som anpassar sig till de filter som är aktiva vid varje given tidpunkt.

Relationer, kontext och relationsmodellen i Power Pivot

Power Pivot-fönstret är där relationsdatamodellen byggs. Där kan du importera flera tabeller och skapa relationer mellan dem. (till exempel Försäljning med Produkter, Försäljning med Kalender, Försäljning med Kunder, etc.). Dessa relationer är grunden för att DAX-formler ska kunna hoppa från en tabell till en annan.

När tabeller är relaterade, Du kan skriva formler som adderar värden från en relaterad tabell och använd dem i tabellen från vilken du skriver uttrycket. Du kan också styra vilka rader som ingår i en beräkning genom att använda filter på specifika kolumner.

Det är viktigt att uppmärksamma Alla rader i en Power Pivot-tabell måste ha samma antal kolumnerVarje kolumn måste ha en konsekvent datatyp över alla sina rader. Om relationsnycklarna har värden som inte matchar (blanka, överblivna värden etc.) kan uppslagsformler och pivottabeller ge oväntade resultat.

Ett annat grundläggande koncept är sammanhangI DAX används huvudsakligen termerna radkontext och filterkontext. Radkontexten är den "aktuella" raden på vilken en beräknad kolumn eller iterator utvärderas; filterkontexten är uppsättningen aktiva filter (från pivottabellen, utsnitt, relationer, funktioner som CALCULATE, etc.).

Genom att experimentera med funktioner som CALCULATE, ALL, ALLEXCEPT eller FILTER kan du Ändra filterkontexten för att ändra hur ett mått utvärderas.Detta möjliggör till exempel att beräkna andelen försäljning av en produkt i förhållande till totalen, eller att jämföra en divisions resultat med företaget som helhet utan filter.

Datauppdatering och omberäkning av DAX-formeln

I en modell som använder komplexa formler eller stora datamängder är det viktigt att förstå hur uppdateringen fungerar. Det är viktigt att skilja mellan att uppdatera data och att beräkna om formlerna.som är relaterade men oberoende processer.

Datauppdateringen består av ta in nya register i boken från externa källor (databaser(filer, onlinetjänster etc.). Du kan starta den här uppdateringen manuellt vid behov, eller schemalägga den om boken är publicerad i Sharepoint eller en annan kompatibel miljö. Ofta utförs denna process med hjälp av Power Query i Excel att förbereda och transformera data innan de laddas in i modellen.

Omräkning, å andra sidan, är den process genom vilken DAX-formlerna utvärderas på nytt för att återspegla ändringar i data eller i själva uttrycken.För beräknade kolumner, om du ändrar formeln, måste hela kolumnen beräknas om på en gång. För mått sker omberäkningen när kontexten ändras (filter, rad-/kolumnfält i pivottabeller) eller när pivottabeller uppdateras manuellt.

Dessa omberäkningar kan påverka prestandan, särskilt om många komplexa beräknade kolumner eller intensiva iterativa funktioner används i stora tabellerDärför är det en bra idé att flytta det mesta av logiken till mått istället för kolumner, när det är möjligt.

Identifiering och korrigering av fel i DAX-formler

När man skriver DAX-formler är det vanligt att man stöter på tre typer av fel: syntaktiska fel, semantiska fel och beräkningsfelVar och en har sina egna omständigheter och sitt eget sätt att korrigera sig själv.

Syntaxfel är de enklaste: saknade parenteser, felplacerade kommatecken, felstavade funktionsnamnetc. Hjälpen för automatisk komplettering och DAX-funktionsreferensen räddar dig från många av dessa fallgropar.

Semantiska fel och beräkningsfel uppstår när, trots att syntaxen är korrekt, Formeln gör något som inte är logiskt i modellens sammanhang.Till exempel att referera till en icke-existerande tabell eller kolumn, skicka ett felaktigt antal argument till en funktion, blanda inkompatibla typer eller vara beroende av en kolumn med tidigare fel.

I dessa fall markerar DAX vanligtvis Hela kolumnen beräknades som felaktig.inte bara en specifik rad, eftersom kolumnen betraktas som en enhet. Om en kolumn bara innehåller metadata men ännu inte har bearbetats (inte har några data inlästa) kommer den att visas nedtonad, och formler som är beroende av den kommer inte att kunna utvärderas korrekt.

Ett specialfall är värden NaN (Inte ett nummer)Dessa värden kan uppstå till exempel när man dividerar 0 med 0. Om en kolumn innehåller NaN-värden kan sortering eller klassificering av dessa värden ge konstiga resultat eftersom NaN-värden inte kan jämföras på vanligt sätt med andra tal. I sådana fall är det lämpligt att använda OM-satser eller andra logiska funktioner för att ersätta NaN-värdena med 0 eller ett annat hanterbart numeriskt värde.

Kompatibilitet med tabellmodeller och DirectQuery-läge

DAX-formlerna du skapar i Power Pivot är i allmänhet, kompatibel med tabellmodeller i SQL Server Analysis ServicesDet här innebär att du kan migrera din modell till en tabellserver och fortsätta att utnyttja den logik du redan har byggt.

Men när en tabellmodell implementeras i läge DirectQueryBegränsningar kan förekomma: Vissa DAX-funktioner stöds inte direkt på vissa relationsdatabaser. eller så kan de returnera något olika resultat på grund av hur frågorna delegeras.

I dessa scenarier är det viktigt att granska den specifika dokumentationen för tabellmotorn och validera de kritiska åtgärderna för att bekräfta att de fortsätter att fungera som förväntat efter att DirectQuery har aktiverats.

Praktiska scenarier: komplexa beräkningar med CALCULATE och filter

En av DAX:s styrkor är dess förmåga att genomföra komplexa beräkningar som förlitar sig på anpassade aggregeringar och dynamiska filterFunktionerna CALCULATE och CALCULATETABLE är centrala för den här typen av scenario.

BERÄKNA tillåter omdefiniera filterkontexten över vilken ett uttryck utvärderasDu kan till exempel begära "summan av försäljningen filtrerad till ett specifikt år, även om pivottabellen visar andra år" eller "totalen utan att tillämpa vissa produktfilter".

Överallt där en DAX-funktion accepterar en tabell som argument, Du kan skicka en filtrerad version av den tabellenDetta kan göras antingen genom att använda FILTER eller genom att ange villkor i CALCULATE. Detta gör det möjligt att bygga mått som anpassar sig till tusentals kombinationer av villkor utan att behöva skapa mellanliggande kolumner.

Det är också möjligt ta bort befintliga filter selektivt med hjälp av funktioner som ALL eller ALLEXCEPT. För att till exempel beräkna bidraget från en specifik återförsäljare i förhållande till det totala antalet återförsäljare kan du låta ett mått dividera värdet i det aktuella sammanhanget med värdet i "ALL"-sammanhanget (utan filter efter återförsäljare).

I andra fall behöver du använda värden för en "yttre slinga"Det vill säga, att referera tillbaka till föregående rad eller iterationskontext. Det är här funktioner som EARLIER kommer in i bilden, vilka tillåter upp till två nivåer av kapslade loopar och är mycket användbara för att skapa rankningar, grupptotaler eller beräkningar som är beroende av en föregående radkontext.

Jag arbetar med text, datum och nycklar i DAX

DAX erbjuder också många verktyg för manipulera text och datumDetta är avgörande när datakällor innehåller datum i ovanliga format, sammansatta nycklar eller textfält som behöver konverteras till tidsvärden.

Power Pivot har inte direkt stöd för dem sammansatta nycklar i relationerOm din källa använder flera kolumner som nycklar, behöver du i många fall skapa en beräknad kolumn som sammanfogar dessa delar till en enda nyckel och använda det som ett relationsfält.

När datum visas i format som inte känns igen av sökmotorn (till exempel ett datum i ett ovanligt regionalt format eller ett heltal som 01032009 importerat som text) kan du skapa formler så här:

=DATUM(HÖGER(,4), VÄNSTER(,2), MITTEN(,3,2))

Med den här typen av uttryck, Du rekonstruerar ett giltigt SQL Server-datum från fragment som extraherats från strängen., vilket sedan låter dig använda tidsintelligensfunktioner utan problem.

Du kan också ändra datatyper med hjälp av formlerMultiplicera med 1,0 för att konvertera datum eller numeriska strängar till tal, eller sammanfoga med en tom sträng för att omvandla ett tal eller datum till text. Dessutom finns det specifika funktioner för att styra returtypen (avkorta decimaler, tvinga fram heltal, etc.).

Villkorliga värden och felhantering i kolumner och mått

Precis som i Excel innehåller DAX funktioner för returnera resultat baserat på villkor och för att hantera fel elegant. Du kan till exempel märka återförsäljare som "Föredragna" eller "Värde" baserat på deras årliga försäljningsvolym med hjälp av kapslade OM-satser.

I en beräknad kolumn däremot, Du har inte råd att ha vissa rader med fel och andra utan.Om en rad ger ett fel markeras hela kolumnen som felaktig. Detta kräver striktare felkontroll än i ett konventionellt kalkylblad.

För att förhindra att en enkel division med noll eller ett blankvärde kraschar hela kolumnen rekommenderas slå in känsliga operationer i förhandskontroller med hjälp av OM- och informationsfunktioner, returnerar alltid ett giltigt värde även när datakombinationen är konstig.

När du bygger modellen kan den vara användbar Låt felen synas i början så att du kan hitta och korrigera dem.Men när du väl publicerar den för andra användare är det viktigt att se till att formlerna är säkra och att inga felmeddelanden någonsin visas i pivottabellerna eller visualiseringarna.

Tidsintelligens: kumulativa totaler, jämförelser och anpassade perioder

Tidsintelligensfunktioner är en av DAX stora attraktionerna. De låter dig arbeta med datumintervall, beräkna ackumulerade totaler, jämföra perioder och generera anpassade tidsfönster. med relativ lätthet, förutsatt att du har en välkonfigurerad kalendertabell.

Åtgärder kan skapas kumulativ försäljning per dag, månad, kvartal eller årBeräkna ingående och utgående saldon för varje period, eller jämför försäljning från ett år till föregående år, kvartal till kvartal, etc., med hjälp av specifika tidsfunktioner.

Dessutom kan du återhämta dig anpassade uppsättningar datumtill exempel "de första 15 dagarna efter starten av en kampanj" eller "samma period förra året", och sedan skicka den uppsättningen till en funktion som aggregerar data över det specifika datumfönstret.

Funktioner som PARALLELLPERIOD och andra relaterade till parallella perioder De underlättar jämförelse mellan tidsförskjutna intervall.Till exempel för att analysera om en kampanj har förbättrat resultaten jämfört med samma tidsperiod ett annat år.

Rankning och jämförelse av värden: topp N och dynamiska rankningar

När du behöver visa endast de mest relevanta elementen (till exempel de 10 bäst säljande produkterna) har du två huvudsakliga vägar: använd Excels filtreringsfunktioner i pivottabellen eller bygg en dynamisk ranking med DAX.

Excel erbjuder filter av typen "Topp 10" i pivottabeller, väldigt lätt att installera För att endast visa objekten ovanför eller under ett givet numeriskt fält. Du kan filtrera efter antal objekt, kumulativ procentandel eller summa av värden.

Problemet med detta tillvägagångssätt är att Filtret är enbart för presentation.Om underliggande data ändras måste du manuellt uppdatera pivottabellen för att filtret ska återspeglas korrekt. Dessutom kan du inte återanvända den rankningen som en del av andra DAX-formler.

Alternativet är att skapa en beräknad kolumn eller mått som tilldelar en rangordning till varje element med hjälp av DAX. Det här alternativet är mer beräkningsmässigt dyrt, men det har fördelar: rankningen beräknas om dynamiskt och kan användas i datautsnitt, vilket gör att användaren kan välja om de vill se topp 5, topp 10, topp 50, etc.

Men i modeller med miljontals rader, Dynamiska rankningar kan vara besvärliga. och det är nödvändigt att utvärdera om prestandakostnaden uppvägs av den funktionella nytta de ger.

Bästa praxis vid utformning av DAX-modeller i Power Pivot

För att en DAX-modell i Power Pivot ska vara underhållbar och fungera bra räcker det inte att formlerna "fungerar". Det är lämpligt att följa en rad goda rutiner som gör stor skillnad i verkliga projekt.

En återkommande rekommendation är prioritera mått framför beräknade kolumner när beräkningen faktiskt är en dynamisk aggregering och inte ett fast attribut. Beräknade kolumner tar upp minne och beräknas om på en gång, medan mått endast utvärderas vid behov.

Det är också mycket användbart använda variabler i DAX (VAR) För att förenkla komplexa formler, undvik att upprepa samma beräkning flera gånger och förbättra läsbarheten. Detta hjälper både prestandan och förståelsen av modellen när någon annan granskar den.

Slutligen gör tydliga namn och minimal intern dokumentation hela skillnaden. Ge beskrivande namn till mått och kolumnerUndvik obskyra förkortningar och dokumentera de viktigaste formlerna. Detta minskar inlärningskurvan för nya användare och sparar dig huvudbry när du återvänder till modellen månader senare.

Att behärska DAX i Power Pivot handlar inte om att memorera alla funktioner, utan om att förstå hur formlerna interagerar med relationsmodellen, filterkontexten och datauppdateringar. Med en solid grund i beräknade kolumner, mått, tidsfunktioner, felhantering och god designpraxisEra tabellmodeller blir mer flexibla, mycket enklare att analysera och framför allt kapabla att besvara komplexa affärsfrågor genom att helt enkelt dra några fält till en pivottabell.