Uitgebreide tabellen in LuckyTemplates

In deze zelfstudie gaan we een heel specifiek scenario bekijken waarvan ik zeker weet dat jullie allemaal al zijn tegengekomen tijdens het werken met DAX: de uitgebreide tabel . Uitgebreide tabellen kunnen uw berekening in de war sturen als u niet weet hoe ze echt werken. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Voor dit voorbeeld gebruiken we de Contoso-database, die de verkooptabel , klanttabel , datumtabel , productentabel , productencategorietabel en productensubcategorietabel bevat .

Aan de linkerkant hebben we een een-op-veel-relatie tussen de tabellen producten, categorie, subcategorie en verkoop. We hebben ook een één-op-veel-relatie tussen klant en verkoop, evenals data en verkoop.

Inhoudsopgave

• Voorbeeld 1
• Voorbeeld #2
• Rode Verkoop #1
• Rode Verkoop #2
• Rode Verkoop #3
• Uitgebreide tabellen verifiëren
• Uitgebreide tabellen definiëren
• De categoriekolom doorsnijden en uitgebreide tabellen gebruiken
• Red Sales uitleggen 2
• Red Sales uitleggen 3
• DAX Studio gebruiken om uitgebreide tabellen te verifiëren
• Conclusie

Voorbeeld 1

Laten we zeggen dat we een rapport willen maken van deze categoriekolom in de categorietabel en een meting gebruiken die berekent hoeveel klanten er in de klantentabel zijn. We willen het nummer voor de tafel van de klant op categorie splitsen.

Als ik een filter op de categorietabel plaats, gaat dat filter naar de subcategorietabel, vervolgens naar de productentabel en uiteindelijk naar de verkooptabel en deze filteren. Maar dat filter kan de tafel van de klant niet filteren, tenzij we de bidirectionele filtering inschakelen.

Laten we teruggaan naar het rapport om deze berekening op te lossen. We kunnen deze berekening in de functie CALCULATE plaatsen en vervolgens Sales schrijven. We kunnen zien dat we deze keer niet hetzelfde nummer herhalen voor elke cel van de visual.

Om te controleren of deze berekening correct is, kunnen we de klantsleutel uit de verkooptabel invoeren om te aggregeren, en daar vervolgens een Count (Distinct) over doen.

U kunt zien dat we voor elke rij dezelfde waarde retourneren. Door de verkooptabel in de CALCULATE-functie te gebruiken, konden we de berekening corrigeren.

Voorbeeld #2

Laten we verder gaan met het volgende voorbeeld. Voor deze willen we het verkoopbedrag van de rode producten in 2007, 2008 of 2009 identificeren. Afhankelijk van de selectie van de snijmachine wil ik ook een jaar teruggaan.

Als ik bijvoorbeeld 2009 selecteer, wil ik de rode verkopen voor 2008 rapporteren. Als ik 2008 selecteer, wil ik ook de rode verkopen voor 2007 rapporteren.

Rode Verkoop #1

De berekening van onze Total Sales is in feite een som van de verkoopgegevens. In de rijcontext gaan we de hoeveelheid vermenigvuldigen met de nettoprijs.

Laten we een nieuwe meting maken en deze de naam Red Sales geven. We schrijven BEREKENEN en vervolgens Totale verkoop. We gaan de verkooptabel producten gelijk is aan rood.

Dan schrijven we JAAR over de datums in de datumkolom.

Wanneer we die berekening in een visuele kaart brengen, krijgen we een spatie.

Rode Verkoop #2

Wat gebeurt hier? Laten we proberen die berekening te herschrijven en kijken of we een resultaat kunnen krijgen. We gaan een nieuwe meting maken en deze de naam Red Sales 2 geven en het eerste deel van de berekening gebruiken voor Red Sales 1.

We gaan een nieuwe BEREKENING starten over de eerste BEREKENING.

Laten we deze maat in de matrix brengen en de resultaten bekijken. Als we 2008 selecteren, krijgen we 51.947. Als we 2009 selecteren, krijgen we 24.343. Als we ten slotte 2010 selecteren, krijgen we 39.724.

Rode Verkoop #3

Er is een andere manier om deze berekening te schrijven. We schrijven een nieuwe meting en noemen deze Red Sales 3 en gebruiken vervolgens de CALCULATE-functie.

We gaan de Totale Verkoop berekenen en schrijven dat voor de producten de kleur gelijk is aan rood. Gebruik dan de functie ZELFDEPERIODE LAATSTE JAAR op de datums.

Als we maat #3 naar de kaartafbeelding slepen, kunt u zien dat deze twee kaartwaarden dezelfde waarde retourneren, wat correct is.

Maar er is iets mis met onze eerste berekening, omdat we een lege waarde retourneren en niet de juiste waarde.

Laten we eens kijken wat hier werkelijk gebeurt. Nu we een aantal berekeningen op uitgebreide tabellen hebben gezien, laten we de theorie erachter begrijpen.

Voordat u begrijpt wat een uitgevouwen tabel is, moet u begrijpen dat alle tabellen die we hier hebben basistabellen worden genoemd .

Dus wanneer worden deze tabellen uitgebreide tabellen? Zodra u een veel-op-een-relatie tussen een tabel en een andere tabel hebt gemaakt, wordt de basistabel de uitgevouwen tabel.

Uitgebreide tabellen verifiëren

Maar hoe kunnen we controleren of er daadwerkelijk een tabeluitbreiding plaatsvindt? Welnu, u kunt het gerelateerde trefwoord op elke tafel gebruiken. Als je vanaf de ene kant toegang hebt tot de kolom, weet je dat er een tafeluitbreiding kan plaatsvinden.

Laten we naar de verkooptabel gaan en een nieuwe berekende kolom maken.

Laten we zeggen dat we de productkleur willen ophalen uit de producttabel voor deze specifieke productcode. We gebruiken RELATED, dat alleen de lijst met kolommen in de IntelliSense geeft die daadwerkelijk kunnen worden uitgevouwen vanuit de verkooptabel.

We kunnen zien dat de klantentabel en de verkooptabel een veel-op-een-relatie hebben. We kunnen ook de lijst met klantentabelkolommen, datumtabelkolommen en productkolommen zien.

Wanneer we Products[Color] selecteren, kunnen we met behulp van het RELATED-trefwoord een nieuwe kolom in de verkooptabel maken. RELATED geeft ons alleen toegang tot de kolommen van de tabel waarnaar de basistabel daadwerkelijk kan worden uitgebreid.

Als we de aard van die relatie veranderen van veel-op-één in veel-op-veel, zal die berekening niet meer werken.

Laten we de aard van deze relatie veranderen in veel-op-veel. We zien onderaan dat we een waarschuwingssymbool krijgen.

Wanneer we teruggaan naar de verkooptabel, zien we de foutmelding dat de kolom Products[Color] niet bestaat of geen relatie heeft met een tabel.

In de huidige context is de waarschuwing die we krijgen niet erg gebruiksvriendelijk. In feite betekent dit dat de verkooptabel niet kan worden uitgebreid naar de producttabel omdat we geen toegang hebben tot slechts één enkele waarde voor dat specifieke rij-item.

En aangezien we de veel-op-veel-relatie gebruiken, gebeurt de tabeluitbreiding niet en werkt RELATED niet.

Laten we teruggaan naar de diagramweergave en deze berekening corrigeren. We veranderen de aard van de relatie in veel-op-een en activeren die relatie om de berekening te laten werken.

Uitgebreide tabellen definiëren

Voordat we gaan kijken naar de berekeningen die we al in het rapport hebben gedaan, herhalen we de definitie van de uitgevouwen tabel.

Als u een gegevensmodel met een sterschema heeft, wordt de feitentabel uitgebreid naar alle tabellen in het gegevensmodel als er een veel-op-één-relatie is tussen de dimensies en de feitentabel.

Als u een sneeuwvlokschema hebt , worden de productsubcategorietabel en de categorietabel uitgebreid naar de basistabel, in dit geval de productentabel. De verkooptabel is de basistabel, die uitbreidt naar alle andere tabellen.

Achter de schermen zal de verkooptafel alle kolommen in één enkele tabel hebben. Merk op dat tabeluitbreiding slechts een logisch concept is, dus het zal uw gegevensmodel niet uitbreiden en vergroten.

De uitgevouwen tabel komt alleen in beeld als u verwijst naar een tabel (de basistabel) en als u een veel-op-een-relatie heeft met andere tabellen.

De categoriekolom doorsnijden en uitgebreide tabellen gebruiken

Laten we proberen de berekeningen op te lossen die we al in de rapportweergave hebben gedaan. In dit voorbeeld snijden we de categoriekolom uit de tabel met productcategorieën en proberen we te tellen hoeveel klanten er zijn.

Dus wat doen we hier eigenlijk? We gebruiken de uitgebreide verkooptabel als referentie. Zodra de filtercontext een waarde uit de categorietabel bevat, bereikt dat filter de verkooptabel vanuit de subcategorieproducten en rechtstreeks naar de verkoop.

Aangezien verkoop een uitgevouwen tabel is en we die referentie gebruiken in de functie BEREKENEN, bevat de verkooptabel ook de kolom van de klantentabel. Wanneer we een filter toepassen op de verkooptabel, filteren we indirect ook de klantentabel.

Laten we teruggaan naar onze rode verkoopberekeningen en proberen te begrijpen wat er werkelijk gebeurt. We beginnen met de blanco berekening. Als we die maat selecteren, kun je zien dat we een geneste code schrijven. We hebben BEREKENEN, vervolgens de totale verkoop, FILTER over de verkoop en ZELFDE PERIODE LAATSTE JAAR.

Laten we die berekening stap voor stap onderbreken. Eerst moeten we de externe filtercontext identificeren die uit de berekening bestaat.

We hebben 2008 geselecteerd in de snijmachine voor kalenderjaarnummers.

Vanuit deze filtercontext wordt de verkooptabel geëvalueerd. De verkooptabel bevat alleen de regel voor het kalenderjaar 2008 en de productkleur is rood. We hebben hier twee filters: een die wordt gemaakt door de filtercontext en een die wordt gemaakt door de filter .

De SAMEPERIODLASTYEAR wordt geëvalueerd in de filtercontext waarbij het jaar 2008 is. Het ontvangt een lijst met datums in 2008 en verschuift die datums in 2008 naar 2007. De tabel die hierdoor wordt geretourneerd, bevat alleen de data voor 2007.

Zodra deze twee bewerkingen zijn voltooid, bereidt de functie CALCULATE de filtercontext voor en past deze twee filters toe op de filtercontext. Wanneer we dat toepassen, hebben we een filtercontext waarbij de productkleur gelijk is aan rood, en in de jaarkolom hebben we een filter over 2007 en 2008.

Dus voor dit datamodel is er geen enkele transactie die in twee verschillende jaren bestaat. Wanneer CALCULATE deze twee filters probeert samen te voegen in een en- conditie, zal het zeggen dat het jaar 2008 en 2007 moet zijn en dat de productkleur rood moet zijn.

Wanneer we de verkooptabel filteren met de filtercontext van het jaar 2008, filteren we indirect ook de datumtabel. De verkoopstal heeft een veel-op-een relatie met de datumstal.

Laten we een nieuwe berekening maken om te identificeren hoeveel rijen er zijn in de datumtabel voor de gerelateerde datums. We schrijven CALCULATE en vervolgens van de datumtabel. Vervolgens FILTEREN we de ALLES van de verkooptabel en gaan we zeggen dat de GERELATEERDE datums in het kalenderjaarnummer gelijk moeten zijn aan 2008.

Binnen CALCULATE hebben we niet verwezen naar een filter over de datumtabel. We controleren gewoon of de datums in het kalenderjaarnummer vanaf 2008 moeten zijn. Idealiter zou dit filter niet in staat moeten zijn om de datumtabel te filteren. We gebruiken ook de functie, die de filtercontext negeert die uit de slicer komt.

Laten we een nieuwe kaart maken voor deze berekening. je kunt zien dat we 348 rijen retourneren.

Dus hoe is het mogelijk dat we uit een datumtabel van 2500 rijen slechts 348 rijen retourneren? Als we een uitgevouwen tabel gaan gebruiken, gaan we indirect ook de andere tabel filteren, die is verbonden via een veel-op-een-relatie.

Hoewel we geen filter hebben voor het huidige jaar, beperken we nog steeds het aantal rijen dat zichtbaar is voor de dimensietabel die we aan de ene kant hebben.

Wanneer we de verkooptabel retourneren, retourneren we ook een gefilterde versie van de datumtabel, klantentabel, producttabel, productcategorietabel en productsubcategorietabel.

Red Sales uitleggen 2

Laten we verder gaan met de volgende berekening, Red Sales 2. We beginnen met de buitenste CALCULATE, want als we deze functie in een willekeurig scenario nesten, moet de buitenste CALCULATE de filtercontext voorbereiden voor de innerlijke CALCULATE.

Op de slicer selecteren we het kalenderjaar 2008. Wanneer de functie SAMEPERIODLASTYEAR de datum voor 2008 krijgt, worden die datums in 2007 verschoven en wordt het de FILTER-context voor de interne berekening. Deze innerlijke CALCULATE zal het jaar 2007 evalueren.

De functie FILTER filtert de verkooptabel en de verkooptabel is alleen beperkt voor de rij waarin het jaar 2007 is. Zodra we de rijen voor 2007 hebben, gaan we controleren of de gerelateerde producten gelijk zijn aan rood.

In tegenstelling tot de eerste berekening, retourneren we niet twee verschillende niveaus. Die twee verschillende niveaus worden niet toegepast op de FILTER-context, dus de tabel die door de FILTER-functie wordt geretourneerd, bevat de hele rij van de verkooptabel.

Zodra dit filter is toegepast, krijgen we het verkoopbedrag voor het jaar 2007, evenals de producten die gelijk zijn aan rood, terwijl we in de eerste berekening het jaar 2008 en 2007 retourneerden.

Deze keer hebben we de filtercontext voor de verkooptabel voorbereid door de functie SAMEPERIODLASTYEAR te gebruiken en door de functie CALCULATE in een andere CALCULATE te nesten.

Red Sales uitleggen 3

Voor het derde voorbeeld hebben we een zeer eenvoudige CALCULATE-functie met twee uitspraken: de ene is productkleur is gelijk aan rood, en vervolgens de ZELFDEPERIODE LAATSTE JAAR op datums.

De functie SAMEPERIODLASTYEAR wordt geëvalueerd in een filtercontext, die alleen de datums van 2007 zal bevatten. De kleur van de producten past een filtercontext van rood toe op de producttabel, die op zijn beurt de verkooptabel filtert en alleen de rijen voor de rode producten.

Wanneer deze twee waarden worden toegepast in de filtercontext, filteren ze de verkooptabel, maar niet de uitgebreide verkooptabel. je kunt zien dat we nergens in de code verwezen naar de uitgebreide verkooptabel.

Dit is het ideale scenario dat u zou moeten gebruiken als u probeert te verwijzen naar de uitgevouwen tabel. Wanneer u de uitgevouwen tabel gebruikt, kunnen de berekeningen erg ingewikkeld worden en soms kunt u niet vaststellen waarom uw berekeningen een onjuist resultaat opleveren.

Als u het concept van uitgebreide tabellen niet begrijpt, kunt u deze specifieke code ontwikkelen als een modelmaatregel en deze vervolgens in productie implementeren. Andere gebruikers kunnen andere meetwaarden gaan maken boven deze meetwaarde, en ze kunnen misschien niet achterhalen waarom de berekeningen niet werken, omdat ze niet simpelweg dat niveau van kennis van de DAX-taal bezitten.

Als u met DAX werkt, probeer dan altijd het filter op een enkele kolom te plaatsen, want wanneer u een enkele kolom gebruikt, wordt het concept van uitgebreide tabellen niet toegepast. Als u bijvoorbeeld PRODUCTEN [Kleur] = "Rood" gebruikt , bereikt het filter de verkooptabel, maar dat filter kan de tafel van de klant niet bereiken omdat we de uitgebreide verkooptabel niet gebruiken.

Bij het gebruik van een enkele kolom verwijzen we niet naar het concept van uitgebreide tabellen. We passen gewoon een basisfilter toe op de verkooptabel, die niet wordt doorgegeven aan de andere tabellen.

DAX Studio gebruiken om uitgebreide tabellen te verifiëren

Voordat we afronden, gaan we DAX Studio gebruiken om onze code voor Red Sales 3 te verifiëren. Laten we naar het tabblad Weergave gaan, op Performance Analyzer klikken en de opname starten. We vernieuwen die visual en selecteren een lege kaart.

Vanuit de externe tools gaan we DAX studio lanceren

We kunnen zien dat we in het resultatenscherm onderaan een blanco krijgen.

We zullen de Server Timings inschakelen om inzicht te krijgen in de vragen die achter de schermen worden gegenereerd. We gaan naar het tabblad Server Timings en vouwen het uit om alles te zien.

We voeren een JOIN uit op de verkooptafel aan de linkerkant. We voeren ook een JOIN uit op de productsleutel uit de producttabel.

In de WHERE-clausule staat dat de datums in het kalenderjaarnummer gelijk moeten zijn aan 2008 en dat de productkleur rood moet zijn.

Dan hebben we nog een voorwaarde die zegt Datums[Datum], gevolgd door een datumbereik. Dus wat zijn deze data? Idealiter moeten deze datums behoren tot 2008, maar ze zijn vanaf 2007 wanneer we de opmaak van deze getallen in Excel wijzigen.

Daarom retourneren we een blanco waarde. We zeggen dat het kalenderjaar 2008 moet zijn en passen een EN-voorwaarde toe dat de datums in 2007 moeten vallen. Dit 2007 komt door de functie SAMEPERIODLASTYEAR die we in de berekening hebben gebruikt.

Laten we de meting en verwijzing naar Red Sales 2 wijzigen. Nadat we de code hebben uitgevoerd, kunt u zien dat we de datums uit de datumtabel halen, vervolgens filteren we de datumkolom voor het kalenderjaarnummer van 2008.

Deze keer kunnen we een resultaat retourneren omdat we geen filter toepassen op de WHERE-component en we zeggen dat de datums in het kalenderjaarnummer gelijk moeten zijn aan 2008.

Waar vindt u de verborgen DAX-trap
Klanten tellen in de loop van de tijd met behulp van DISTINCTCOUNT in LuckyTemplates
Analyse van nieuwe klanten met behulp van geavanceerde DAX

Conclusie

Hopelijk was deze zelfstudie nuttig om te begrijpen wat een uitgebreide tabel is en hoe deze uw berekeningen in de war kan sturen. Uitgebreide tabellen zijn niet echt intuïtief en evenmin gemakkelijk te begrijpen.

Voor meer informatie over uitgebreide tabellen, vergeet u niet te abonneren op het LuckyTemplates TV-kanaal. Er komt voortdurend een enorme hoeveelheid content uit van mijzelf en een reeks contentmakers, allemaal toegewijd aan het verbeteren van de manier waarop u LuckyTemplates en het Power Platform gebruikt.