Met context kunt u dynamische analyses uitvoeren, waarbij de resultaten van een formule kunnen worden gewijzigd om de huidige rij of celselectie en eventuele gerelateerde gegevens weer te geven. Inzicht in context en effectief gebruik van context zijn erg belangrijk voor het maken van formules met hoge prestaties, dynamische analyses en voor het oplossen van problemen in formules.
In deze sectie worden de verschillende typen context gedefinieerd: rijcontext, querycontext en filtercontext. Hierin wordt uitgelegd hoe context wordt geëvalueerd voor formules in berekende kolommen en in draaitabellen.
Het laatste deel van dit artikel bevat koppelingen naar gedetailleerde voorbeelden die laten zien hoe de resultaten van formules veranderen afhankelijk van de context.
Context begrijpen
Formules in Power Pivot kunnen worden beïnvloed door de filters die zijn toegepast in een draaitabel, door relaties tussen tabellen en door filters die worden gebruikt in formules. Context maakt het mogelijk om dynamische analyses uit te voeren. Inzicht in context is belangrijk voor het bouwen en voor het oplossen van problemen met formules.
Er zijn verschillende soorten context: rijcontext, querycontext en filtercontext.
Rijcontext kan worden beschouwd als 'de huidige rij'. Als u een berekende kolom hebt gemaakt, bestaat de rijcontext uit de waarden in elke afzonderlijke rij en waarden in kolommen die zijn gerelateerd aan de huidige rij. Er zijn ook enkele functies (EERDER en EARLIEST) die een waarde ophalen uit de huidige rij en die waarde vervolgens gebruiken tijdens het uitvoeren van een bewerking voor een hele tabel.
Querycontext verwijst naar de subset gegevens die impliciet wordt gemaakt voor elke cel in een draaitabel, afhankelijk van de rij- en kolomkoppen.
Filtercontext is de set waarden die in elke kolom zijn toegestaan, op basis van filterbeperkingen die zijn toegepast op de rij of die zijn gedefinieerd door filterexpressies in de formule.
Rijcontext
Als u een formule maakt in een berekende kolom, bevat de rijcontext voor die formule de waarden uit alle kolommen in de huidige rij. Als de tabel is gerelateerd aan een andere tabel, bevat de inhoud ook alle waarden uit die andere tabel die zijn gerelateerd aan de huidige rij.
Stel dat u een berekende kolom maakt, =[Vracht] + [Belasting], waarmee twee kolommen uit dezelfde tabel worden samengevoegd. Deze formule gedraagt zich als formules in een Excel-tabel, die automatisch verwijzen naar waarden uit dezelfde rij. Tabellen verschillen van bereiken: u kunt niet verwijzen naar een waarde uit de rij vóór de huidige rij met behulp van de bereiknotatie en u kunt niet verwijzen naar een willekeurige enkele waarde in een tabel of cel. U moet altijd met tabellen en kolommen werken.
Rijcontext volgt automatisch de relaties tussen tabellen om te bepalen welke rijen in gerelateerde tabellen zijn gekoppeld aan de huidige rij.
In de volgende formule wordt bijvoorbeeld de functie RELATED gebruikt om een belastingwaarde op te halen uit een gerelateerde tabel, op basis van de regio waarnaar de order is verzonden. De belastingwaarde wordt bepaald door de waarde voor regio in de huidige tabel te gebruiken, de regio in de gerelateerde tabel op te zoeken en vervolgens het belastingtarief voor die regio op te halen uit de gerelateerde tabel.
= [Vracht] + RELATED('Region'[TaxRate])
Met deze formule wordt het belastingtarief voor de huidige regio opgehaald uit de tabel Regio. U hoeft de sleutel waarmee de tabellen worden verbonden niet te weten of op te geven.
Context met meerdere rijen
Daarnaast bevat DAX functies waarmee berekeningen voor een tabel worden herhaald. Deze functies kunnen meerdere huidige rijen en huidige rijcontexten hebben. In programmeertermen kunt u formules maken die via een binnenste en buitenste lus worden herhaald.
Stel dat uw werkmap een tabel Producten en een tabel Verkoop bevat. Misschien wilt u de volledige verkooptabel doorlopen, die vol staat met transacties met meerdere producten, en de grootste bestelde hoeveelheid voor elk product in één transactie vinden.
In Excel is voor deze berekening een reeks tussenliggende samenvattingen vereist, die opnieuw moeten worden opgebouwd als de gegevens worden gewijzigd. Als u een hoofdgebruiker van Excel bent, kunt u mogelijk matrixformules maken die de taak zouden kunnen uitvoeren. U kunt ook geneste subselecties schrijven in een relationele database.
Met DAX kunt u echter één formule maken die de juiste waarde retourneert. De resultaten worden automatisch bijgewerkt wanneer u gegevens aan de tabellen toevoegt.
=MAXX(FILTER(Sales;[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Zie de functie EARLIER voor een gedetailleerd overzicht van deze formule.
Kortom, de functie EARLIER slaat de rijcontext op van de bewerking die voorafging aan de huidige bewerking. De functie slaat te allen tijde twee contextsets op in het geheugen: één contextset vertegenwoordigt de huidige rij voor de binnenste lus van de formule en een andere set context vertegenwoordigt de huidige rij voor de buitenste lus van de formule. DAX voert automatisch waarden tussen de twee lussen door, zodat u complexe aggregaties kunt maken.
Querycontext
Querycontext verwijst naar de subset gegevens die impliciet wordt opgehaald voor een formule. Wanneer u een meting of een ander waardeveld in een cel in een draaitabel neerzet, onderzoekt de Power Pivot engine de rij- en kolomkoppen, slicers en rapportfilters om de context te bepalen. Vervolgens maakt Power Pivot de benodigde berekeningen om elke cel in de draaitabel te vullen. De gegevensset die wordt opgehaald, is de querycontext voor elke cel.
Omdat de context kan veranderen afhankelijk van waar u de formule plaatst, veranderen de resultaten van de formule ook, afhankelijk van of u de formule gebruikt in een draaitabel met veel groeperingen en filters, of in een berekende kolom zonder filters en minimale context.
Stel dat u deze eenvoudige formule maakt waarmee de waarden in de kolom Winst van de tabel Verkoop worden opgeteld:
=SOM('Verkoop'[Winst])
Als u deze formule gebruikt in een berekende kolom in de tabel Verkoop , zijn de resultaten voor de formule hetzelfde voor de hele tabel, omdat de querycontext voor de formule altijd de volledige gegevensset van de tabel Verkoop is. Uw resultaten hebben winst voor alle regio's, alle producten, alle jaren, enzovoort.
Meestal wilt u echter niet honderden keren hetzelfde resultaat zien, maar in plaats daarvan wilt u de winst voor een bepaald jaar, een bepaald land of een bepaalde regio, een bepaald product of een combinatie hiervan, en vervolgens een eindtotaal krijgen.
In een draaitabel kunt u eenvoudig de context wijzigen door kolom- en rijkoppen toe te voegen of te verwijderen en door slicers toe te voegen of te verwijderen. U kunt een formule zoals hierboven maken in een meting en deze vervolgens in een draaitabel neerzetten. Wanneer u kolom- of rijkoppen toevoegt aan de draaitabel, wijzigt u de querycontext waarin de meting wordt geëvalueerd. Segmenterings- en filterbewerkingen zijn ook van invloed op de context. Daarom wordt dezelfde formule, die wordt gebruikt in een draaitabel, geëvalueerd in een andere querycontext voor elke cel.
Filtercontext
Filtercontext wordt toegevoegd wanneer u filterbeperkingen opgeeft voor de set waarden die zijn toegestaan in een kolom of tabel, met behulp van argumenten voor een formule. Filtercontext wordt toegepast op andere contexten, zoals rijcontext of querycontext.
Een draaitabel berekent bijvoorbeeld de waarden voor elke cel op basis van de rij- en kolomkoppen, zoals beschreven in de voorgaande sectie over querycontext. Binnen de metingen of berekende kolommen die u toevoegt aan de draaitabel, kunt u echter filterexpressies opgeven om de waarden te bepalen die door de formule worden gebruikt. U kunt ook selectief de filters op bepaalde kolommen wissen.
Zie de filterfuncties voor meer informatie over het maken van filters in formules.
Zie de functie ALL voor een voorbeeld van hoe filters kunnen worden gewist om eindtotalen te maken.
Zie de functie ALLEXCEPT voor voorbeelden van het selectief wissen en toepassen van filters in formules.
Daarom moet u de definitie controleren van metingen of formules die in een draaitabel worden gebruikt, zodat u op de hoogte bent van de filtercontext bij het interpreteren van de resultaten van formules.
Context in formules bepalen
Wanneer u een formule maakt, controleert Power Pivot voor Excel eerst op algemene syntaxis en controleert vervolgens de namen van kolommen en tabellen die u opgeeft op basis van mogelijke kolommen en tabellen in de huidige context. Als Power Pivot de kolommen en tabellen die door de formule zijn opgegeven, niet kunt vinden, krijgt u een foutmelding.
Context wordt bepaald zoals beschreven in de voorgaande secties, met behulp van de beschikbare tabellen in de werkmap, eventuele relaties tussen de tabellen en filters die zijn toegepast.
Als u bijvoorbeeld zojuist gegevens in een nieuwe tabel hebt geïmporteerd en geen filters hebt toegepast, maakt de hele set kolommen in de tabel deel uit van de huidige context. Als u meerdere tabellen hebt die zijn gekoppeld op basis van relaties en u werkt in een draaitabel die is gefilterd door kolomkoppen toe te voegen en slicers te gebruiken, bevat de context de gerelateerde tabellen en eventuele filters op de gegevens.
Context is een krachtig concept dat het ook moeilijk kan maken om problemen met formules op te lossen. We raden u aan om te beginnen met eenvoudige formules en relaties om te zien hoe context werkt en vervolgens te experimenteren met eenvoudige formules in draaitabellen. De volgende sectie bevat ook enkele voorbeelden van hoe formules verschillende typen context gebruiken om dynamisch resultaten te retourneren.
Voorbeelden van context in formules
-
Met de functie RELATED wordt de context van de huidige rij uitgebreid met waarden in een gerelateerde kolom. Hiermee kunt u zoekopdrachten uitvoeren. Het voorbeeld in dit onderwerp illustreert de interactie tussen filteren en rijcontext.
-
Met de functie FILTER kunt u de rijen opgeven die in de huidige context moeten worden opgenomen. De voorbeelden in dit onderwerp laten ook zien hoe u filters insluit in andere functies die aggregaties uitvoeren.
-
Met de functie ALL stelt u de context in een formule in. U kunt deze gebruiken om filters te overschrijven die worden toegepast als resultaat van de querycontext.
-
Met de functie ALLEXCEPT kunt u alle filters verwijderen, behalve de filters die u opgeeft. Beide onderwerpen bevatten voorbeelden die u helpen bij het bouwen van formules en het begrijpen van complexe contexten.
-
Met de functies EARLIER en EARLIEST kunt u tabellen doorlopen door berekeningen uit te voeren, terwijl u verwijst naar een waarde uit een binnenste lus. Als u bekend bent met het concept van recursie en met binnenste en buitenste lussen, zult u de kracht waarderen die de functies EARLIER en EARLIEST bieden. Als u geen gebruik hebt van deze concepten, moet u de stappen in het voorbeeld zorgvuldig volgen om te zien hoe de binnenste en buitencontexten worden gebruikt in berekeningen.
Referentiële integriteit
In deze sectie worden enkele geavanceerde concepten besproken met betrekking tot ontbrekende waarden in Power Pivot tabellen die zijn verbonden door relaties. Deze sectie kan handig voor u zijn als u werkmappen met meerdere tabellen en complexe formules hebt en hulp nodig hebt bij het begrijpen van de resultaten.
Als u nog niet bekend bent met concepten van relationele gegevens, raden we u aan eerst het inleidende onderwerp Relatiesoverzicht te lezen.
Referentiële integriteit en Power Pivot relaties
Power Pivot vereist niet dat referentiële integriteit tussen twee tabellen wordt afgedwongen om een geldige relatie te definiëren. In plaats daarvan wordt een lege rij gemaakt aan het 'een'-einde van elke een-op-veel-relatie en wordt deze gebruikt om alle niet-overeenkomende rijen uit de gerelateerde tabel te verwerken. Het gedraagt zich effectief als een SQL Outer Join.
Als u in draaitabellen gegevens groepeert aan de ene kant van de relatie, worden niet-overeenkomende gegevens aan de veel-zijde van de relatie gegroepeerd en opgenomen in totalen met een lege rijkop. De lege kop is ongeveer gelijk aan het 'onbekend lid'.
Informatie over het onbekende lid
Het concept van het onbekende lid is u waarschijnlijk bekend als u hebt gewerkt met multidimensionale databasesystemen, zoals SQL Server Analysis Services. Als de term nieuw voor u is, wordt in het volgende voorbeeld uitgelegd wat het onbekende lid is en hoe dit van invloed is op berekeningen.
Stel dat u een berekening maakt waarmee de maandelijkse verkoop voor elke winkel wordt opgeteld, maar in een kolom in de tabel Verkoop ontbreekt een waarde voor de winkelnaam. Gezien het feit dat de tabellen voor Winkel en Verkoop zijn verbonden door de winkelnaam, wat zou u dan verwachten te gebeuren in de formule? Hoe moet de draaitabel de verkoopcijfers groepeeren of weergeven die niet gerelateerd zijn aan een bestaande winkel?
Dit probleem komt vaak voor in datawarehouses, waarbij grote tabellen met feitengegevens logisch moeten worden gerelateerd aan dimensietabellen die informatie bevatten over winkels, regio's en andere kenmerken die worden gebruikt voor het categoriseren en berekenen van feiten. Om het probleem op te lossen, worden nieuwe feiten die geen verband houden met een bestaande entiteit tijdelijk toegewezen aan het onbekende lid. Daarom worden niet-gerelateerde feiten gegroepeerd weergegeven in een draaitabel onder een lege kop.
Behandeling van lege waarden versus de lege rij
Lege waarden verschillen van de lege rijen die worden toegevoegd aan het onbekende lid. De lege waarde is een speciale waarde die wordt gebruikt om nullen, lege tekenreeksen en andere ontbrekende waarden weer te geven. Zie Gegevenstypen in gegevensmodellen voor meer informatie over de lege waarde en andere DAX-gegevenstypen.
