Kontekst gjør det mulig å utføre dynamisk analyse, der resultatene av en formel kan endres for å gjenspeile gjeldende rad eller celleutvalg og eventuelle relaterte data. Det er svært viktig å forstå kontekst og bruke kontekst effektivt for å bygge formler med høy ytelse, dynamiske analyser og for å feilsøke problemer i formler.
Denne delen definerer de ulike konteksttypene: radkontekst, spørringskontekst og filterkontekst. Den forklarer hvordan konteksten evalueres for formler i beregnede kolonner og i pivottabeller.
Den siste delen av denne artikkelen inneholder koblinger til detaljerte eksempler som illustrerer hvordan resultatene av formler endres i henhold til konteksten.
Forstå kontekst
Formler i Power Pivot påvirkes av filtrene som brukes i en pivottabell, etter relasjoner mellom tabeller og filtre som brukes i formler. Kontekst er det som gjør det mulig å utføre dynamisk analyse. Det er viktig å forstå konteksten for å bygge og feilsøke formler.
Det finnes ulike typer kontekst: radkontekst, spørringskontekst og filterkontekst.
Radkontekst kan ses på som «gjeldende rad». Hvis du har opprettet en beregnet kolonne, består radkonteksten av verdiene i hver enkelt rad og verdier i kolonner som er relatert til gjeldende rad. Det finnes også noen funksjoner (TIDLIGERE og TIDLIGSTE)som får en verdi fra gjeldende rad og deretter bruker denne verdien mens du utfører en operasjon over en hel tabell.
Spørringskontekst refererer til delsettet med data som er implisitt opprettet for hver celle i en pivottabell, avhengig av rad- og kolonneoverskriftene.
Filterkontekst er settet med verdier som er tillatt i hver kolonne, basert på filterbegrensninger som ble brukt på raden eller som er definert av filteruttrykk i formelen.
Radkontekst
Hvis du oppretter en formel i en beregnet kolonne, inneholder radkonteksten for denne formelen verdiene fra alle kolonnene i gjeldende rad. Hvis tabellen er relatert til en annen tabell, inneholder innholdet også alle verdiene fra den andre tabellen som er relatert til gjeldende rad.
Anta for eksempel at du oppretter en beregnet kolonne, =[Frakt] + [Avgift],
, som legger sammen to kolonner fra samme tabell. Denne formelen oppfører seg som formler i en Excel tabell, som automatisk refererer til verdier fra samme rad. Vær oppmerksom på at tabeller er forskjellige fra områder: Du kan ikke referere til en verdi fra raden før den gjeldende raden ved hjelp av område notasjon, og du kan ikke referere til en vilkårlig enkeltverdi i en tabell eller celle. Du må alltid arbeide med tabeller og kolonner.
Radkonteksten følger automatisk relasjonene mellom tabeller for å bestemme hvilke rader i relaterte tabeller som er knyttet til gjeldende rad.
Formelen nedenfor bruker for eksempel RELATERT-funksjonen til å hente en avgiftsverdi fra en relatert tabell, basert på området som ordren ble sendt til. Avgiftsverdien bestemmes ved å bruke verdien for området i den gjeldende tabellen, søke etter området i den relaterte tabellen og deretter hente avgiftssatsen for dette området fra den relaterte tabellen.
= [Frakt] + RELATED('Region'[TaxRate])
Denne formelen henter ganske enkelt avgiftssatsen for gjeldende område, fra Område-tabellen. Du trenger ikke å vite eller angi nøkkelen som kobler sammen tabellene.
Kontekst med flere rader
I tillegg inneholder DAX funksjoner som iter beregninger over en tabell. Disse funksjonene kan ha flere gjeldende rader og gjeldende radkontekster. I programmeringsformler kan du opprette formler som gjentas over en indre og ytre sløyfe.
Anta for eksempel at arbeidsboken inneholder en Produkter-tabell og en salgstabell. Du vil kanskje gå gjennom hele salgstabellen, som er full av transaksjoner som involverer flere produkter, og finne det største antallet som er bestilt for hvert produkt i én transaksjon.
I Excel krever denne beregningen en serie mellomliggende sammendrag, som må bygges om hvis dataene endres. Hvis du er en power user of Excel, kan du kanskje bygge matriseformler som ville gjort jobben. Alternativt kan du skrive nestede undervalg i en relasjonsdatabase.
Med DAX kan du imidlertid bygge en enkelt formel som returnerer den riktige verdien, og resultatene oppdateres automatisk hver gang du legger til data i tabellene.
=MAKSX(FILTER(Salg,[ProdKey]=EARLIER([ProdKey])),Salg[Ordreantall])
Hvis du vil ha en detaljert gjennomgang av denne formelen, kan du se TIDLIGERE.
Kort sagt lagrer TIDLIGERE-funksjonen radkonteksten fra operasjonen som kom foran den gjeldende operasjonen. Funksjonen lagrer hele tiden i minnet to sett med kontekst: ett sett med kontekst representerer gjeldende rad for den indre løkken i formelen, og et annet sett med kontekst representerer gjeldende rad for den ytre løkken i formelen. DAX mater automatisk verdier mellom de to løkkene, slik at du kan opprette komplekse mengdemengder.
Spørringskontekst
Spørringskontekst refererer til delsettet med data som er implisitt hentet for en formel. Når du slipper et mål eller et annet verdifelt i en celle i en pivottabell, undersøker Power Pivot motoren rad- og kolonneoverskriftene, slicerne og rapportfiltrene for å bestemme konteksten. Deretter Power Pivot de nødvendige beregningene for å fylle ut hver celle i pivottabellen. Settet med data som hentes, er spørringskonteksten for hver celle.
Fordi konteksten kan endres avhengig av hvor du plasserer formelen, endres også resultatene av formelen avhengig av om du bruker formelen i en pivottabell med mange grupperinger og filtre, eller i en beregnet kolonne uten filtre og minimal kontekst.
Anta for eksempel at du oppretter denne enkle formelen som summerer verdiene i Fortjeneste-kolonnen i salgstabellen:=SUMMER('Salg'[Fortjeneste]).
Hvis du bruker denne formelen i en beregnet kolonne i Salg-tabellen, vil resultatene for formelen være de samme for hele tabellen, fordi spørringskonteksten for formelen alltid er hele datasettet i Salgs-tabellen. Resultatene vil ha fortjeneste for alle områder, alle produkter, alle år og så videre.
Vanligvis vil du imidlertid ikke se det samme resultatet hundrevis av ganger, men i stedet vil du få fortjenesten for et bestemt år, et bestemt land eller område, et bestemt produkt eller en kombinasjon av disse, og deretter få en totalsum.
I en pivottabell er det enkelt å endre kontekst ved å legge til eller fjerne kolonne- og radoverskrifter og ved å legge til eller fjerne slicere. Du kan opprette en formel som den ovenfor, i et mål, og deretter slippe den i en pivottabell. Når du legger til kolonne- eller radoverskrifter i pivottabellen, endrer du spørringskonteksten der målet evalueres. Lisensierings- og filtreringsoperasjoner påvirker også konteksten. Den samme formelen, som brukes i en pivottabell, evalueres derfor i en annen spørringskontekst for hver celle.
Filterkontekst
Filterkontekst legges til når du angir filterbegrensninger for verdisettet som er tillatt i en kolonne eller tabell, ved hjelp av argumenter i en formel. Filterkontekst gjelder oppå andre kontekster, for eksempel radkontekst eller spørringskontekst.
En pivottabell beregner for eksempel verdiene for hver celle basert på rad- og kolonneoverskriftene, som beskrevet i forrige inndeling i spørringskonteksten. I mål eller beregnede kolonner som du legger til i pivottabellen, kan du imidlertid angi filteruttrykk for å kontrollere verdiene som brukes av formelen. Du kan også selektivt fjerne filtrene på bestemte kolonner.
Hvis du vil ha mer informasjon om hvordan du oppretter filtre i formler, kan du se Filterfunksjoner.
Hvis du vil ha et eksempel på hvordan filtre kan tømmes for å opprette totalsummer, kan du se ALLE.
Hvis du vil ha eksempler på hvordan du velger å fjerne og bruke filtre i formler, kan du se ALLEXCEPT-funksjonen.
Derfor må du se gjennom definisjonen av mål eller formler som brukes i en pivottabell, slik at du er klar over filterkonteksten når du tolker resultatene av formler.
Fastslå kontekst i formler
Når du oppretter en formel, Power Pivot for Excel først etter generell syntaks, og deretter kontrollerer den navnene på kolonner og tabeller som du angir, mot mulige kolonner og tabeller i gjeldende kontekst. Hvis Power Pivot ikke finner kolonnene og tabellene som er angitt av formelen, får du en feilmelding.
Konteksten bestemmes som beskrevet i de foregående inndelingene, ved å bruke de tilgjengelige tabellene i arbeidsboken, eventuelle relasjoner mellom tabellene og eventuelle filtre som er brukt.
Hvis du for eksempel nettopp har importert noen data til en ny tabell og ikke har brukt filtre, er hele settet med kolonner i tabellen en del av gjeldende kontekst. Hvis du har flere tabeller som er koblet av relasjoner, og du arbeider i en pivottabell som er filtrert ved å legge til kolonneoverskrifter og bruke slicere, inkluderer konteksten relaterte tabeller og eventuelle filtre på dataene.
Kontekst er et kraftig konsept som også kan gjøre det vanskelig å feilsøke formler. Vi anbefaler at du begynner med enkle formler og relasjoner for å se hvordan konteksten fungerer, og deretter begynne å eksperimentere med enkle formler i pivottabeller. Den følgende delen inneholder også noen eksempler på hvordan formler bruker ulike typer kontekst til å returnere resultater dynamisk.
Eksempler på kontekst i formler
-
RELATERT-funksjonen utvider konteksten for gjeldende rad for å inkludere verdier i en relatert kolonne. Dette lar deg utføre oppslag. Eksemplet i dette emnet illustrerer samhandlingen mellom filtrering og radkontekst.
-
Med FILTER-funksjonen kan du angi radene som skal inkluderes i gjeldende kontekst. Eksemplene i dette emnet illustrerer også hvordan du bygger inn filtre i andre funksjoner som utfører aggregerte data.
-
ALL-funksjonen angir kontekst i en formel. Du kan bruke den til å overstyre filtre som brukes som et resultat av spørringskonteksten.
-
Med ALLEXCEPT-funksjonen kan du fjerne alle filtre bortsett fra ett du angir. Begge emnene inneholder eksempler som viser deg hvordan du bygger formler og forstår komplekse sammenhenger.
-
Funksjonene TIDLIGERE og TIDLIGST lar deg bla gjennom tabeller ved å utføre beregninger, mens du refererer til en verdi fra en indre sløyfe. Hvis du er kjent med konseptet med rekursjon og med indre og ytre løkker, vil du sette pris på kraften som funksjonene TIDLIGERE og TIDLIGSTE gir. Hvis du ikke har brukt disse begrepene før, bør du følge trinnene i eksemplet nøye for å se hvordan de indre og ytre kontekstene brukes i beregninger.
Referanseintegritet
Denne delen omhandler noen avanserte begreper relatert til manglende verdier i Power Pivot tabeller som er koblet sammen med relasjoner. Denne delen kan være nyttig for deg hvis du har arbeidsbøker med flere tabeller og komplekse formler og vil ha hjelp til å forstå resultatene.
Hvis relasjonsdatakonsepter er nytt for deg, anbefaler vi at du først leser det innledende emnet, Oversikt over relasjoner.
Referanseintegritet og Power Pivot relasjoner
Power Pivot krever ikke at referanseintegritet fremtvinges mellom to tabeller for å definere en gyldig relasjon. I stedet opprettes en tom rad på «én»-enden av hver én-til-mange-relasjon og brukes til å håndtere alle rader som ikke samsvarer fra den relaterte tabellen. Den fungerer effektivt som en SQL ytre sammenføyning.
Hvis du grupperer data etter én side av relasjonen i pivottabeller, grupperes alle unike data på mange-siden av relasjonen sammen og inkluderes i totaler med en tom radoverskrift. Den tomme overskriften tilsvarer omtrent det «ukjente medlemmet».
Forstå det ukjente medlemmet
Konseptet med det ukjente medlemmet er sannsynligvis kjent for deg hvis du har arbeidet med flerdimensjonale databasesystemer, for eksempel SQL Server Analysis Services. Hvis begrepet er nytt for deg, forklarer eksemplet nedenfor hva det ukjente medlemmet er og hvordan det påvirker beregninger.
La oss si at du oppretter en beregning som summerer månedlig salg for hver butikk, men en kolonne i Salg-tabellen mangler en verdi for butikknavnet. Gitt at tabellene for Store og Salg er koblet sammen med butikknavnet, hva forventer du at skal skje i formelen? Hvordan skal pivottabellgruppen eller vise salgstallene som ikke er relatert til en eksisterende butikk?
Dette problemet er vanlig i datalagre, der store tabeller med faktadata må være logisk relatert til dimensjonstabeller som inneholder informasjon om butikker, områder og andre attributter som brukes til å kategorisere og beregne fakta. For å løse problemet blir eventuelle nye fakta som ikke er relatert til en eksisterende enhet, midlertidig tilordnet til det ukjente medlemmet. Derfor vises ikke-relaterte fakta gruppert i en pivottabell under en tom overskrift.
Behandling av tomme verdier kontra den tomme raden
Tomme verdier er forskjellige fra de tomme radene som legges til for å gi plass til det ukjente medlemmet. Den tomme verdien er en spesiell verdi som brukes til å representere nullverdier, tomme strenger og andre manglende verdier. Hvis du vil ha mer informasjon om den tomme verdien og andre DAX-datatyper, kan du se Datatyper i datamodeller.
