Utvidede tabeller i LuckyTemplates

I denne opplæringen skal vi ta en titt på et veldig spesifikt scenario som jeg er sikker på at alle dere allerede har møtt mens du jobbet med DAX – den utvidede tabellen . Utvidede tabeller kan ødelegge beregningen din hvis du ikke vet hvordan de faktisk fungerer. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

For dette eksempelet bruker vi Contoso-databasen, som inneholder salgstabellen , kundetabellen , datotabellen , produkttabellen , produktkategoritabellen og produktunderkategoritabellen .

På venstre side har vi ett-til-mange forhold mellom produktene, kategorien, underkategorien og salgstabellene. Vi har også en-til-mange-forhold mellom kunde og salg, samt datoer og salg.

Innholdsfortegnelse

• Eksempel #1
• Eksempel #2
• Rødt salg #1
• Rødt salg #2
• Rødt salg #3
• Verifisering av utvidede tabeller
• Definere utvidede tabeller
• Skjæring av kategorikolonnen og bruk utvidede tabeller
• Forklaring av rødt salg 2
• Forklaring av rødt salg 3
• Bruke DAX Studio til å bekrefte utvidede tabeller
• Konklusjon

Eksempel #1

La oss si at vi ønsker å lage en rapport fra denne kategorikolonnen i kategoritabellen, og bruke et mål som beregner hvor mange kunder det er i kundetabellen. Vi ønsker å dele tallet for kundens bord etter kategori.

Hvis jeg legger et filter på kategoritabellen, vil det filteret gå til underkategoritabellen, så vil det nå produkttabellen, og til slutt vil det nå og filtrere salgstabellen. Men det filteret vil ikke kunne filtrere kundens tabell med mindre vi slår på toveisfiltreringen.

La oss gå tilbake til rapporten for å fikse denne beregningen. Vi kan legge denne beregningen inn i CALCUATE-funksjonen, og deretter skrive Sales. Vi kan se at denne gangen gjentar vi ikke det samme tallet for hver celle i det visuelle.

For å verifisere om denne beregningen er riktig, kan vi hente inn kundenøkkelen fra salgstabellen for å aggregere, og deretter gjøre en Count (Distinct) over det.

Du kan se at vi returnerer samme verdi for hver rad. Ved å bruke salgstabellen inne i CALCUATE-funksjonen, klarte vi å fikse beregningen.

Eksempel #2

La oss gå videre til neste eksempel. For denne ønsker vi å identifisere salgsbeløpet for de røde produktene i 2007, 2008 eller 2009. Avhengig av utvalget av skjæremaskinen ønsker jeg også å gå ett år tilbake.

For eksempel, hvis jeg velger 2009, vil jeg rapportere det røde salget for 2008. Hvis jeg velger 2008, vil jeg også rapportere det røde salget for 2007.

Rødt salg #1

Beregningen av vårt totale salg er i utgangspunktet en sum av salgsdata. I radsammenheng skal vi multiplisere mengden med nettoprisen.

La oss lage et nytt mål og gi det navnet Red Sales. Vi skal skrive BEREGN, deretter Totalt salg. Vi går til salgstabellen for å vise at produktenes farge er lik rød.

Deretter vil vi skrive over datoene i datokolonnen.

Når vi tar med det regnestykket i en kortvisual, får vi en blank.

Rødt salg #2

Hva skjer her? La oss prøve å omskrive den beregningen og se om vi klarer å få et resultat. Vi skal lage et nytt mål og navngi det som Red Sales 2 og bruke den første delen av beregningen for Red Sales 1.

Vi skal sette i gang en ny CALCUATE over den første CALCUATE.

La oss ta med dette målet i matrisen og se resultatene. Velger vi 2008, får vi 51.947. Velger vi 2009, får vi 24.343. Til slutt, hvis vi velger 2010, får vi 39 724.

Rødt salg #3

Det er en annen måte å skrive denne beregningen på. Vi skriver et nytt mål og kaller det Red Sales 3, og bruker deretter CALCUATE-funksjonen.

Vi skal beregne det totale salget og skrive at for produktene er fargen lik rød. Bruk deretter SAMEPERIODLASTYEAR-funksjonen på datoene.

Hvis vi drar mål #3 inn i kortvisualen, kan du se at disse to kortverdiene returnerer samme verdi, noe som er riktig.

Men det er noe galt med vår første beregning, fordi vi returnerer en blank og ikke riktig verdi.

La oss se hva som faktisk skjer her. Nå som vi har sett et par beregninger på utvidede tabeller, la oss forstå teorien bak dem.

Før du forstår hva en utvidet tabell er, må du forstå at alle tabellene vi har her kalles basistabeller .

Så når blir disse tabellene utvidede tabeller? Når du oppretter et mange-til-en-forhold mellom en tabell og en annen tabell, blir basistabellen den utvidede tabellen.

Verifisering av utvidede tabeller

Men hvordan kan vi bekrefte at en tabellutvidelse faktisk skjer? Vel, du kan bruke det relaterte søkeordet på hvilken som helst tabell. Hvis du har tilgang til kolonnen fra den ene siden, vil du vite at en tabellutvidelse kan skje.

La oss gå til salgstabellen og lage en ny beregnet kolonne.

La oss si at vi ønsker å hente produktfargen fra produkttabellen for denne spesielle produktnøkkelen. Vi bruker RELATED, som bare vil gi listen over kolonner i IntelliSense som faktisk kan utvides fra salgstabellen.

Vi kan se at kundetabellen og salgstabellen har et mange-til-en forhold. Vi kan også se listen over kundetabellkolonner, datotabellkolonner og produktkolonner.

Når vi velger Produkter[Farge], kan vi opprette en ny kolonne i salgstabellen ved hjelp av RELATED søkeord. RELATED gir oss bare tilgang til kolonnene i tabellen som basistabellen faktisk kan utvides til.

Hvis vi endrer karakteren av det forholdet fra mange-til-en til mange-til-mange, vil den beregningen slutte å fungere.

La oss endre naturen til dette forholdet til mange-til-mange. Vi kan se nederst at vi får et advarselssymbol.

Når vi går tilbake til salgstabellen, ser vi feilmeldingen som sier at kolonnen Produkter[Farge] enten ikke eksisterer, eller ikke har en relasjon til noen tabell.

I dagens sammenheng er advarselen vi får lite brukervennlig. I bunn og grunn betyr det at salgstabellen ikke kan utvides til produkttabellen fordi vi ikke har tilgang til kun én enkelt verdi for den bestemte radelementet.

Og siden vi bruker mange-til-mange-relasjonen, skjer ikke tabellutvidelsen og RELATED fungerer ikke.

La oss gå tilbake til diagramvisningen og fikse denne beregningen. Vi endrer relasjonens natur til mange-til-en og aktiverer relasjonen for å få beregningen til å fungere.

Definere utvidede tabeller

Før vi begynner å se på beregningene som vi allerede har gjort i rapporten, la oss gjenta definisjonen av den utvidede tabellen.

Hvis du har en datamodell med et stjerneskjema , vil faktatabellen utvides til alle tabellene i datamodellen hvis det er en mange-til-en-relasjon mellom dimensjonene og faktatabellen.

Hvis du har et snøfnuggskjema , vil produktunderkategoritabellen og kategoritabellen utvides til basistabellen, som i dette tilfellet er produkttabellen. Salgstabellen er basistabellen, som utvides til alle de andre bordene.

Bak scenen vil salgstabellen ha alle kolonnene i én enkelt tabell. Merk at tabellutvidelse bare er et logisk konsept, så det kommer ikke til å utvide og øke størrelsen på datamodellen din.

Den utvidede tabellen kommer bare inn i bildet når du refererer til en tabell (som er basistabellen), og når du har et mange-til-en forhold til andre tabeller.

Skjæring av kategorikolonnen og bruk utvidede tabeller

La oss prøve å fikse beregningene som vi allerede har gjort i rapportvisningen. I dette eksemplet deler vi etter kategorikolonnen fra produktkategoritabellen, og vi prøver å telle hvor mange kunder det er.

Så hva gjør vi egentlig her? Vi bruker den utvidede salgstabellen som referanse. Når filterkonteksten inneholder en verdi fra kategoritabellen, vil filteret nå salgstabellen fra underkategorien produkter og direkte til salget.

Siden salg er en utvidet tabell og vi bruker den referansen i CALCUATE-funksjonen, vil salgstabellen også inneholde kolonnen i kundetabellen. Når vi bruker et filter over salgstabellen, indirekte, filtrerer vi også kundetabellen.

La oss gå tilbake til våre røde salgsberegninger og prøve å forstå hva som faktisk skjer. Vi starter med den tomme beregningen. Hvis vi velger det målet, kan du se at vi skriver en nestet kode. Vi har BEREGN, deretter Totalt salg, FILTER over salget og SAMEPERIODLASTÅR.

La oss bryte den beregningen trinn for trinn. Først må vi identifisere den eksterne filterkonteksten som eksisterer ut av kalkulasjonen.

Vi har valgt 2008 i skjæreren for nummer for kalenderår.

Fra denne filterkonteksten vil salgstabellen bli evaluert. Salgstabellen vil kun inneholde raden for kalenderårsnummeret 2008 og produktfargen vil være rød. Vi har to filtre her: ett som er opprettet av filterkonteksten og ett som blir opprettet av filteret .

SAMEPERIODLASTYEAR blir evaluert i filterkonteksten der året er 2008. Det kommer til å motta en liste over datoer i 2008, og det vil flytte disse datoene i 2008 til 2007. Tabellen som skal returneres av dette vil kun inneholde datoene for 2007.

Når disse to operasjonene er fullført, forbereder CALCUATE-funksjonen filterkonteksten og bruker disse to filtrene i filterkonteksten. Når vi bruker det, har vi en filterkontekst med at produktfargen er lik rød, og på årskolonnen har vi et filter over 2007 og 2008.

Så for denne datamodellen er det ingen enkelt transaksjon som eksisterer i to forskjellige år. Når CALCULATE prøver å slå sammen disse to filtrene i en og- tilstand, vil det stå at året skal være 2008 og 2007, og at produktfargen skal være rød.

Når vi filtrerer salgstabellen med filterkonteksten for år 2008, filtrerer vi også indirekte datotabellen. Salgsstallen har et forhold på mange-til-en til datostallen.

La oss lage en ny beregning for å identifisere hvor mange rader det er i datotabellen for de relaterte datoene. Vi skal skrive CALCULE, deretter av datotabellen. Da vil vi FILTRERE HELE salgstabellen, og vi skal si at de RELATERTE datoene i kalenderårsnummeret skal være lik 2008.

Inne i CALCUATE har vi ikke referert til noe filter over datotabellen. Vi sjekker rett og slett at datoene i kalenderårsnummeret skal være fra 2008. Ideelt sett skal ikke dette filteret kunne filtrere datotabellen. Vi bruker også funksjonen, som vil ignorere filterkonteksten som kommer fra sliceren.

La oss lage et nytt kort for denne beregningen. du kan se at vi returnerer 348 rader.

Så hvordan er det mulig at fra en datotabell med 2500 rader, returnerer vi bare 348 rader? Hvis vi skal bruke en utvidet tabell, så skal vi indirekte filtrere den andre tabellen også, som er koblet sammen gjennom en mange-til-en-relasjon.

Selv om vi ikke har noe filter for inneværende år, begrenser vi fortsatt antall rader som er synlige for dimensjonstabellen vi har på den ene siden.

Når vi returnerer salgstabellen, returnerer vi også en filtrert versjon av datotabellen, kundetabellen, produkttabellen, produktkategoritabellen og produktunderkategoritabellen.

Forklaring av rødt salg 2

La oss gå videre til neste beregning, som er Red Sales 2. Vi starter med den ytre CALCULATE fordi hvis vi nester denne funksjonen i ethvert scenario, bør den ytre CALCULATE forberede filterkonteksten for den indre CALCULATE.

På sliceren velger vi kalenderåret 2008. Når SAMEPERIODLASTYEAR-funksjonen får datoen for 2008, kommer den til å flytte disse datoene i 2007, og det vil være FILTER-konteksten for den indre kalkulasjonen. Denne indre CALCUATE vil evaluere året 2007.

FILTER-funksjonen vil filtrere salgstabellen, og salgstabellen vil kun være begrenset for raden der året er 2007. Når vi har radene for 2007, skal vi sjekke at de relaterte produktene er lik rødt.

I motsetning til den første beregningen, returnerer vi ikke to forskjellige nivåer. Disse to forskjellige nivåene blir ikke brukt på FILTER-konteksten, så tabellen returnert av FILTER-funksjonen vil inneholde hele raden i salgstabellen.

Når dette filteret er brukt, kommer vi til å få salgsbeløpet for år 2007, samt produktene som er lik rødt, mens vi i den første beregningen returnerte år 2008 og 2007.

Denne gangen har vi forberedt filterkonteksten for salgstabellen ved å bruke SAMEPERIODLASTYEAR-funksjonen og ved å neste CALCUATE-funksjonen inne i en annen CALCUATE.

Forklaring av rødt salg 3

For det tredje eksemplet har vi en veldig enkel CALCUATE-funksjon med to utsagn: en er produktfarge er lik rød, og deretter SAMEPERIODLASTYEAR på datoer.

SAMEPERIODLASTYEAR-funksjonen vil bli evaluert i en filterkontekst, som kun vil inneholde datoene fra 2007. Produktenes farge vil bruke en filterkontekst med rødt over produkttabellen, som igjen vil filtrere salgstabellen og kun inneholde radene for de røde produktene.

Når disse to verdiene brukes i filterkonteksten, kommer de til å filtrere salgstabellen, men ikke den utvidede salgstabellen. du kan se at ingen steder i koden refererte vi til den utvidede salgstabellen.

Dette er det ideelle scenariet du bør bruke hvis du prøver å referere til den utvidede tabellen. Når du bruker den utvidede tabellen, kan beregningene bli veldig kronglete, og noen ganger kan du ikke identifisere hvorfor beregningene dine gir et feil resultat.

Hvis du ikke forstår konseptet med utvidede tabeller, kan du utvikle denne spesielle koden som et modellmål, og deretter distribuere den i produksjon. Andre brukere kan begynne å lage andre mål over dette tiltaket, og de kan kanskje ikke finne ut hvorfor beregningene ikke fungerer fordi de ikke bare har det nivået av forståelse for DAX-språket.

Hvis du jobber med DAX, prøv alltid å sette filteret over en enkelt kolonne fordi når du bruker en enkelt kolonne, brukes ikke konseptet med utvidede tabeller. For eksempel, når du bruker PRODUKTER [Farge] = "Rød" , når filteret salgstabellen, men det filteret kan ikke nå kundens bord fordi vi ikke bruker den utvidede salgstabellen.

Når du bruker en enkelt kolonne, refererer vi ikke til konseptet utvidede tabeller. Vi bruker ganske enkelt et grunnleggende filter over salgstabellen, som ikke forplanter seg til de andre tabellene.

Bruke DAX Studio til å bekrefte utvidede tabeller

Før vi avslutter, skal vi bruke DAX studio for å verifisere koden vår for Red Sales 3. La oss gå til fanen Vis, klikk på Performance Analyzer, og start deretter innspillingen. Vi oppdaterer det visuelle bildet og velger et tomt kort.

Fra de eksterne verktøyene skal vi lansere DAX studio

Vi kan se at i resultatskjermbildet nederst får vi en blank.

Vi slår på servertimingene for å forstå spørringene som genereres bak scenen. Vi går til fanen Server Timings og utvider den for å se alt.

Vi utfører en JOIN på salgsbordet på venstre side. Vi utfører også en JOIN på produktnøkkelen fra produkttabellen.

I WHERE-klausulen står det at datoene i kalenderårsnummeret skal være lik 2008 og at produktfargen skal være rød.

Så har vi en annen betingelse som sier Datoer[Dato], deretter etterfulgt av et datointervall. Så hva er disse datoene? Ideelt sett må disse datoene tilhøre 2008, men de er fra 2007 når vi endrer formateringen av disse tallene i Excel.

Dette er grunnen til at vi returnerer en blank verdi. Vi sier at kalenderåret skal være 2008 og bruker en OG-betingelse om at datoene skal være i 2007. Dette 2007 er på grunn av SAMEPERIODLASTYEAR-funksjonen vi brukte i beregningen.

La oss endre mål og referanse til Red Sales 2. Når vi kjører koden, kan du se at vi får datoene fra datotabellen, så filtrerer vi datokolonnen for kalenderårsnummeret 2008.

Denne gangen kan vi returnere et resultat fordi vi ikke bruker et filter på WHERE-klausulen og vi sier at datoene i kalenderårsnummeret skal være lik 2008.

Konklusjon

Forhåpentligvis var denne opplæringen nyttig for å forstå hva en utvidet tabell er og hvordan den kan ødelegge beregningene dine. Utvidede tabeller er egentlig ikke intuitive, og de er heller ikke enkle å forstå.

For mer informasjon om utvidede tabeller, ikke glem å abonnere på LuckyTemplates TV-kanal. Vi har en enorm mengde innhold som kommer ut hele tiden fra meg selv og en rekke innholdsskapere, alle dedikert til å forbedre måten du bruker LuckyTemplates og Power Platform på.