Utökade tabeller i LuckyTemplates

I den här handledningen ska vi ta en titt på ett mycket specifikt scenario som jag är säker på att ni alla redan har stött på när ni arbetade med DAX – den utökade tabellen . Utökade tabeller kan förstöra din beräkning om du inte vet hur de faktiskt fungerar. Du kan se hela videon av denna handledning längst ner på den här bloggen.

För det här exemplet använder vi Contoso-databasen, som innehåller försäljningstabellen , kundtabellen , datumtabellen , produkttabellen , produktkategoritabellen och produktunderkategoritabellen .

På vänster sida har vi en-till-många-relation mellan produkter, kategori, underkategori och försäljningstabeller. Vi har även en-till-många-relation mellan kund och försäljning, samt datum och försäljning.

Innehållsförteckning

• Exempel #1
• Exempel #2
• Röd försäljning #1
• Röd försäljning #2
• Röd försäljning #3
• Verifiera utökade tabeller
• Definiera utökade tabeller
• Skiva kategorikolumnen och använda utökade tabeller
• Förklara Red Sales 2
• Förklara Red Sales 3
• Använda DAX Studio för att verifiera utökade tabeller
• Slutsats

Exempel #1

Låt oss säga att vi vill skapa en rapport från denna kategorikolumn i kategoritabellen och använda ett mått som beräknar hur många kunder det finns i kundtabellen. Vi vill dela upp numret för kundens bord efter kategori.

Om jag sätter ett filter på kategoritabellen, kommer det filtret att gå till underkategoritabellen, sedan kommer det att nå produkttabellen och slutligen kommer det att nå och filtrera försäljningstabellen. Men det filtret kommer inte att kunna filtrera kundens tabell om vi inte slår på den dubbelriktade filtreringen.

Låt oss gå tillbaka till rapporten för att fixa denna beräkning. Vi kan lägga in den här beräkningen i BERÄKNA-funktionen och sedan skriva Försäljning. Vi kan se att vi den här gången inte upprepar samma nummer för varje cell i det visuella.

För att verifiera om denna beräkning är korrekt kan vi ta in kundnyckeln från försäljningstabellen för att aggregera, och sedan göra en Count (Distinct) över det.

Du kan se att vi returnerar samma värde för varje rad. Genom att använda försäljningstabellen inuti BERÄKNA-funktionen kunde vi fixa beräkningen.

Exempel #2

Låt oss gå vidare till nästa exempel. För den här vill vi identifiera försäljningsbeloppet för de röda produkterna 2007, 2008 eller 2009. Beroende på valet av skärmaskin vill jag också gå tillbaka ett år.

Till exempel, om jag väljer 2009 vill jag rapportera den röda försäljningen för 2008. Om jag väljer 2008 vill jag också rapportera den röda försäljningen för 2007.

Röd försäljning #1

Beräkningen av vår totala försäljning är i princip en summa av försäljningsdata. I radsammanhang ska vi multiplicera kvantiteten med nettopriset.

Låt oss skapa ett nytt mått och namnge det som Red Sales. Vi ska skriva BERÄKNA, sedan Total försäljning. Vi går till försäljningstabellen för att visa att produkternas färg är lika med röd.

Sedan skriver vi över datumen i datumkolumnen.

När vi tar med den beräkningen i en kortvisual får vi ett blanksteg.

Röd försäljning #2

Vad är det som händer här? Låt oss försöka skriva om den beräkningen och se om vi kan få ett resultat. Vi kommer att skapa ett nytt mått och namnge det som Red Sales 2 och använda den första delen av beräkningen för Red Sales 1.

Vi kommer att initiera ytterligare en BERÄKNA under den första BERÄKNA.

Låt oss ta med detta mått i matrisen och se resultatet. Om vi ​​väljer 2008 får vi 51 947. Om vi ​​väljer 2009 får vi 24 343. Slutligen, om vi väljer 2010 får vi 39 724.

Röd försäljning #3

Det finns ett annat sätt att skriva denna beräkning. Vi skriver ett nytt mått och kallar det Red Sales 3 och använder sedan funktionen BERÄKNA.

Vi kommer att beräkna den totala försäljningen och skriva att för produkterna är färgen lika med röd. Använd sedan SAMEPERIODLASTYEAR-funktionen på datumen.

Om vi ​​drar mått #3 in i kortvisualen kan du se att dessa två kortvärden returnerar samma värde, vilket är korrekt.

Men det är något fel med vår första beräkning, eftersom vi returnerar ett tomt värde och inte det korrekta värdet.

Låt oss se vad som faktiskt händer här. Nu när vi har sett ett par beräkningar på utökade tabeller, låt oss förstå teorin bakom dem.

Innan du förstår vad en utökad tabell är måste du förstå att alla tabeller som vi har här kallas bastabeller .

Så när blir dessa tabeller utökade tabeller? När du väl har skapat en många-till-en-relation mellan en tabell och en annan tabell, blir bastabellen den utökade tabellen.

Verifiera utökade tabeller

Men hur kan vi verifiera att en tabellexpansion faktiskt sker? Tja, du kan använda det relaterade nyckelordet på vilken tabell som helst. Om du kan komma åt kolumnen från ena sidan, vet du att en tabellexpansion kan hända.

Låt oss gå till försäljningstabellen och skapa en ny beräknad kolumn.

Låt oss säga att vi vill hämta produktfärgen från produkttabellen för just denna produktnyckel. Vi kommer att använda RELATED, som bara ger listan över kolumner i IntelliSense som faktiskt kan utökas från försäljningstabellen.

Vi kan se att kundtabellen och försäljningstabellen har en mång-till-en relation. Vi kan också se listan över kundtabellkolumner, datumtabellkolumner och produktkolumner.

När vi väljer Produkter[Färg] kan vi skapa en ny kolumn i försäljningstabellen med hjälp av RELATED nyckelord. RELATED ger oss bara tillgång till kolumnerna i tabellen som bastabellen faktiskt kan expandera till.

Om vi ​​ändrar karaktären på det förhållandet från många-till-en till många-till-många, kommer den beräkningen att sluta fungera.

Låt oss ändra karaktären på detta förhållande till många-till-många. Vi kan se längst ner att vi får en varningssymbol.

När vi går tillbaka till försäljningstabellen ser vi felmeddelandet som säger att kolumnen Produkter[Färg] antingen inte existerar eller inte har en relation till någon tabell.

I det aktuella sammanhanget är varningen som vi får inte särskilt användarvänlig. I grund och botten betyder det att försäljningstabellen inte kan expandera till produkttabellen eftersom vi inte kan komma åt endast ett enda värde för just den radartikeln.

Och eftersom vi använder många-till-många-relationen, sker inte tabellexpansionen och RELATED fungerar inte.

Låt oss gå tillbaka till diagramvyn och fixa denna beräkning. Vi kommer att ändra karaktären på relationen till många-till-en och aktivera den relationen för att få beräkningen att fungera.

Definiera utökade tabeller

Innan vi börjar titta på beräkningarna som vi redan har gjort i rapporten, låt oss upprepa definitionen av den utökade tabellen.

Om du har en datamodell med ett stjärnschema expanderas faktatabellen till alla tabeller i datamodellen om det finns en mång-till-en-relation mellan dimensionerna och faktatabellen.

Om du har ett snöflingaschema expanderas produktunderkategoritabellen och kategoritabellen till bastabellen, som i det här fallet är produkttabellen. Försäljningstabellen är basbordet, som utökas till alla andra bord.

Bakom scenen kommer försäljningstabellen att ha alla kolumner i en enda tabell. Observera att tabellexpansion bara är ett logiskt koncept, så det kommer inte att expandera och öka storleken på din datamodell.

Den utökade tabellen kommer bara in i bilden när du hänvisar till en tabell (som är bastabellen), och när du har en mång-till-en relation till andra tabeller.

Skiva kategorikolumnen och använda utökade tabeller

Låt oss försöka fixa de beräkningar som vi redan har gjort i rapportvyn. I det här exemplet delar vi efter kategorikolumnen från produktkategoritabellen och vi försöker räkna hur många kunder det finns.

Så vad gör vi här egentligen? Vi använder den utökade försäljningstabellen som referens. När filterkontexten innehåller ett värde från kategoritabellen kommer det filtret att nå försäljningstabellen från underkategorin produkter och direkt till försäljningen.

Eftersom försäljning är en utökad tabell och vi använder den referensen i BERÄKNA-funktionen, kommer försäljningstabellen också att innehålla kolumnen i kundtabellen. När vi applicerar ett filter över försäljningstabellen, indirekt, filtrerar vi även kundtabellen.

Låt oss gå tillbaka till våra röda försäljningskalkyler och försöka förstå vad som faktiskt händer. Vi börjar med den tomma beräkningen. Om vi ​​väljer det måttet kan du se att vi skriver en kapslad kod. Vi har BERÄKNA, sedan den totala försäljningen, FILTRERA över försäljningen och SAMEPERIODLASTÅR.

Låt oss bryta den beräkningen steg för steg. Först måste vi identifiera det externa filterkontext som finns utanför kalkylen.

Vi har valt 2008 i sektionen för nummer för kalenderår.

Från detta filtersammanhang kommer försäljningstabellen att utvärderas. Försäljningstabellen kommer endast att innehålla raden för kalenderårsnumret 2008 och produktfärgen kommer att vara röd. Vi har två filter här: ett som skapas av filterkontexten och ett som skapas av filtret .

SAMEPERIODLASTYEAR utvärderas i filtersammanhang där året är 2008. Det kommer att få en lista med datum 2008 och det kommer att flytta dessa datum 2008 till 2007. Tabellen som kommer att returneras av detta kommer bara att innehålla datum för 2007.

När dessa två operationer är klara förbereder funktionen CALCULATE filterkontexten och tillämpar dessa två filter i filterkontexten. När vi tillämpar det har vi en filterkontext där produktfärgen är lika med röd, och i kolumnen år har vi ett filter över 2007 och 2008.

Så för denna datamodell finns det ingen enskild transaktion som existerar under två olika år. När BERÄKNA försöker slå samman dessa två filter i ett och tillstånd, kommer det att stå att årtalet ska vara 2008 och 2007, och att produktfärgen ska vara röd.

När vi filtrerar försäljningstabellen med filterkontexten för år 2008, filtrerar vi också indirekt datumtabellen. Försäljningsstallet har en relation av många-till-en till datumstallet.

Låt oss skapa en ny beräkning för att identifiera hur många rader det finns i datumtabellen för de relaterade datumen. Vi kommer att skriva BERÄKNA, sedan av datumtabellen. Sedan kommer vi att FILTRERA HELA försäljningstabellen, och vi kommer att säga att de RELATERADE datumen i kalenderårsnumret bör vara lika med 2008.

Inuti CALCULATE har vi inte refererat till något filter över datumtabellen. Vi kontrollerar helt enkelt att datumen i kalenderårsnumret ska vara från 2008. Helst ska detta filter inte kunna filtrera datumtabellen. Vi använder också funktionen, som ignorerar filterkontexten som kommer från slicern.

Låt oss skapa ett nytt kort för denna beräkning. du kan se att vi returnerar 348 rader.

Så hur är det möjligt att vi endast returnerar 348 rader från en datumtabell med 2500 rader? Om vi ​​ska använda en utökad tabell, så kommer vi att indirekt filtrera den andra tabellen också, som är ansluten genom en många-till-en-relation.

Även om vi inte har något filter under innevarande år, begränsar vi fortfarande antalet rader som är synliga för dimensionstabellen som vi har på ena sidan.

När vi returnerar försäljningstabellen returnerar vi också en filtrerad version av datumtabellen, kundtabellen, produkttabellen, produktkategoritabellen och produktunderkategoritabellen.

Förklara Red Sales 2

Låt oss gå vidare till nästa beräkning, som är Red Sales 2. Vi börjar med den yttre CALCULATE eftersom om vi kapslar den här funktionen i något scenario, bör den yttre CALCULATE förbereda filterkontexten för den inre CALCULATE.

På slicern väljer vi kalenderåret 2008. När SAMEPERIODLASTYEAR-funktionen får datumet för 2008, kommer den att flytta dessa datum till 2007 och det kommer att vara FILTER-kontexten för den inre beräkningen. Denna inre BERÄKNA kommer att utvärdera år 2007.

FILTER-funktionen kommer att filtrera försäljningstabellen, och försäljningstabellen kommer endast att vara begränsad för raden där året är 2007. När vi har raderna för 2007 ska vi kontrollera att de relaterade produkterna är lika med röda.

Till skillnad från den första beräkningen returnerar vi inte två olika nivåer. Dessa två olika nivåer tillämpas inte på FILTER-kontexten så tabellen som returneras av FILTER-funktionen kommer att innehålla alla raden i försäljningstabellen.

När detta filter har tillämpats kommer vi att få försäljningsbeloppet för år 2007, såväl som de produkter som är lika med rött, medan vi i den första beräkningen returnerade år 2008 och 2007.

Den här gången har vi förberett filterkontexten för försäljningstabellen genom att använda SAMEPERIODLASTYEAR-funktionen och genom att kapsla CALCULATE-funktionen i en annan CALCULATE.

Förklara Red Sales 3

För det tredje exemplet har vi en mycket enkel BERÄKNA-funktion med två påståenden: en är att produktens färg är lika med röd, och sedan SAMEPERIODLASTÅR på datum.

SAMEPERIODLASTYEAR-funktionen kommer att utvärderas i en filterkontext, som endast kommer att innehålla datumen 2007. Produkternas färg kommer att tillämpa ett filterkontext av rött över produkttabellen, vilket i sin tur kommer att filtrera försäljningstabellen och endast innehålla raderna för de röda produkterna.

När dessa två värden tillämpas i filtersammanhanget kommer de att filtrera försäljningstabellen men inte den utökade försäljningstabellen. du kan se att ingenstans i koden refererade vi till den utökade försäljningstabellen.

Detta är det idealiska scenariot som du bör använda om du försöker referera till den utökade tabellen. När du använder den utökade tabellen kan beräkningarna bli riktigt invecklade och ibland kan du inte identifiera varför dina beräkningar ger ett felaktigt resultat.

Om du inte förstår konceptet med utökade tabeller kan du utveckla just den här koden som ett modellmått och sedan distribuera den i produktionen. Andra användare kan börja skapa andra åtgärder över denna åtgärd, och de kanske inte kan ta reda på varför beräkningarna inte fungerar eftersom de inte bara har den nivån av förståelse för DAX-språket.

Om du arbetar med DAX, försök alltid att placera filtret över en enstaka kolumn, för när du använder en enda kolumn tillämpas inte konceptet med utökade tabeller. Till exempel, när du använder PRODUKTER [Färg] = "Röd" når filtret försäljningstabellen, men det filtret kan inte nå kundens bord eftersom vi inte använder den utökade försäljningstabellen.

När vi använder en enda kolumn hänvisar vi inte till begreppet utökade tabeller. Vi använder helt enkelt ett grundläggande filter över försäljningstabellen, som inte sprider sig till de andra tabellerna.

Använda DAX Studio för att verifiera utökade tabeller

Innan vi avslutar kommer vi att använda DAX studio för att verifiera vår kod för Red Sales 3. Låt oss gå till fliken Visa, klicka på Performance Analyzer och starta sedan inspelningen. Vi uppdaterar bilden och väljer ett tomt kort.

Från de externa verktygen kommer vi att lansera DAX studio

Vi kan se att i resultatskärmen längst ner får vi ett tomt tecken.

Vi kommer att aktivera Server Timings för att förstå de frågor som genereras bakom scenen. Vi går till fliken Server Timings och utökar den för att se allt.

Vi utför en JOIN på försäljningsbordet på vänster sida. Vi utför även en JOIN på produktnyckeln från produkttabellen.

I WHERE-satsen står det att datumen i kalenderårsnumret ska vara lika med 2008 och att produktfärgen ska vara röd.

Sedan har vi ett annat villkor som säger Datum[Datum], sedan följt av ett datumintervall. Så vad är dessa datum? Helst måste dessa datum tillhöra 2008, men de är från 2007 när vi ändrar formateringen av dessa siffror i Excel.

Det är därför vi returnerar ett tomt värde. Vi säger att kalenderåret ska vara 2008 och tillämpar ett AND-villkor om att datumen ska vara 2007. Detta 2007 är på grund av funktionen SAMEPERIODLASTYEAR som vi använde i beräkningen.

Låt oss ändra måttet och referensen till Red Sales 2. När vi kört koden kan du se att vi får datumen från datumtabellen, sedan filtrerar vi datumkolumnen för kalenderårsnumret 2008.

Den här gången kan vi returnera ett resultat eftersom vi inte tillämpar ett filter på WHERE-satsen och vi säger att datumen i kalenderårsnumret ska vara lika med 2008.

Slutsats

Förhoppningsvis var den här handledningen till hjälp för att förstå vad en utökad tabell är och hur den kan störa dina beräkningar. Utökade tabeller är inte riktigt intuitiva och inte heller lätta att förstå.

För mer information om utökade tabeller, glöm inte att prenumerera på LuckyTemplates TV-kanal. Vi har en enorm mängd innehåll som kommer ut hela tiden från mig själv och en rad innehållsskapare, alla dedikerade till att förbättra ditt sätt att använda LuckyTemplates och Power Platform.