Met context kunt u dynamische analyses uitvoeren, waarbij de resultaten van een formule kunnen worden aangepast aan de huidige rij- of celselectie en eventuele gerelateerde gegevens. Inzicht in context en effectief gebruik van context zijn zeer belangrijk voor het bouwen van goed presterende formules, dynamische analyses en voor het oplossen van problemen in formules.
In dit gedeelte worden de verschillende typen context gedefinieerd: rijcontext, querycontext en filtercontext. In dit artikel wordt uitgelegd hoe de 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 worden toegepast in een draaitabel, door relaties tussen tabellen en door filters die in formules worden gebruikt. Context maakt het mogelijk om dynamische analyses uit te voeren. Kennis van de context is belangrijk bij het opstellen van formules en voor het oplossen van problemen met formules.
Er zijn verschillende soorten context: rijcontext, querycontext en filtercontext.
U kunt de rijcontext zien 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 aan de huidige rij zijn gerelateerd. Er zijn ook functies (EERDER en VROEGST) die een waarde ophalen uit de huidige rij en die waarde vervolgens gebruiken tijdens het uitvoeren van een bewerking op een volledige tabel.
Querycontext verwijst naar de subset gegevens die impliciet wordt gemaakt voor elke cel in een draaitabel, afhankelijk van de rij- en kolomkoppen.
De filtercontext is de set waarden die in elke kolom is toegestaan, op basis van filterbeperkingen die zijn toegepast op de rij of die zijn gedefinieerd door filterexpressies in de formule.
Naar boven
Rijcontext
Als u een formule in een berekende kolom maakt, omvat de rijcontext voor die formule de waarden uit alle kolommen in de huidige rij. Als de tabel aan een andere tabel is gerelateerd, bevat de inhoud ook alle waarden uit die andere tabel die aan de huidige rij zijn gerelateerd.
U maakt bijvoorbeeld een berekende kolom =[Freight] + [Tax] waarmee twee kolommen uit dezelfde tabel bij elkaar worden opgeteld. Deze formule werkt als formules in een Excel-tabel, die automatisch naar waarden uit dezelfde rij verwijzen. Tabellen verschillen van bereiken: u kunt niet met behulp van bereiknotatie verwijzen naar een waarde uit de rij vóór de huidige rij en u kunt niet verwijzen naar een willekeurige enkele waarde in een tabel of cel. U moet altijd met tabellen en kolommen werken.
De rijcontext volgt automatisch de relaties tussen tabellen om te bepalen welke rijen in gerelateerde tabellen bij de huidige rij horen.
In de volgende formule wordt bijvoorbeeld de functie RELATED gebruikt om een btw-waarde 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 op te zoeken in de gerelateerde tabel en vervolgens het belastingtarief voor deze regio uit de gerelateerde tabel te halen.
= [Vrachtkosten] + RELATED('Regio'[Belastingtarief])
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 kennen of op te geven.
Context voor meerdere rijen
Daarnaast bevat DAX functies waarmee berekeningen over een tabel worden herhaald. Deze functies kunnen meerdere huidige rijen en huidige rijcontexten hebben. In programmeertermen kun je formules maken die over een binnenste en buitenste lus recureren.
Stel dat uw werkmap een tabel Producten en een tabel Verkoop bevat. U kunt bijvoorbeeld door de hele verkooptabel bladeren, die vol staat met transacties die betrekking hebben op meerdere producten, en de grootste bestelde hoeveelheid voor elk product in één transactie vinden.
In Excel vereist deze berekening een reeks tussenliggende samenvattingen, die opnieuw moeten worden opgebouwd als de gegevens worden gewijzigd. Als u een veelvuldig gebruiker van Excel bent, kunt u mogelijk matrixformules maken die voldoen. In een relationele database kunt u ook geneste subselecties schrijven.
Met DAX kunt u echter één formule maken die de juiste waarde retourneert, en de resultaten worden automatisch bijgewerkt wanneer u gegevens aan de tabellen toevoegt.
=MAXX(FILTER(Verkoop,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Zie de functie EERDER voor een gedetailleerde beschrijving van deze formule.
Kortom, met de functie EERDER wordt de rijcontext opgeslagen van de bewerking die voorafging aan de huidige bewerking. De functie slaat altijd twee contextsets in het geheugen op: de ene set context vertegenwoordigt de huidige rij voor de binnenste lus van de formule, en de andere set context vertegenwoordigt de huidige rij voor de buitenste lus van de formule. In DAX worden automatisch waarden tussen de twee lussen ingevoerd, zodat u complexe aggregaties kunt maken.
Naar boven
Query Context
Querycontext verwijst naar de subset van gegevens die impliciet wordt opgehaald voor een formule. Wanneer u een meting of een ander waardeveld in een cel in een draaitabel neerzet, worden de rij- en kolomkoppen, slicers en rapportfilters onderzocht door de Power Pivot-engine om de context vast te stellen. Vervolgens voert Power Pivot de benodigde berekeningen uit om elke cel in de draaitabel te vullen. De set gegevens 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 met 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 Sales , zijn de resultaten voor de formule hetzelfde voor de hele tabel omdat de querycontext voor de formule altijd de gehele gegevensset van de tabel Sales 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 wilt u in plaats daarvan de winst krijgen 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 de bovenstaande in een meting maken 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. Snij- en filterbewerkingen hebben ook invloed op de context. Daarom wordt dezelfde formule die in een draaitabel wordt gebruikt, geëvalueerd in een verschillende querycontext voor elke cel.
Naar boven
Filtercontext
De filtercontext wordt toegevoegd wanneer u met behulp van argumenten in een formule filterbeperkingen opgeeft voor de set waarden die is toegestaan in een kolom of tabel. De filtercontext wordt toegepast op andere contexten, zoals rijcontext of querycontext.
Zo berekent een draaitabel de waarden voor elke cel op basis van de rij- en kolomkoppen, zoals beschreven in de vorige sectie over de querycontext. In de metingen of berekende kolommen die u aan de draaitabel toevoegt, kunt u echter filterexpressies opgeven om de waarden te bepalen die door de formule worden gebruikt. U kunt ook selectief de filters voor bepaalde kolommen wissen.
Zie Filterfuncties voor meer informatie over het maken van filters in formules.
Zie de functie ALL voor een voorbeeld van de manier waarop filters kunnen worden gewist om eindtotalen te maken.
Zie de functie ALLEXCEPT voor voorbeelden van hoe u selectief filters in formules kunt wissen en toepassen.
Daarom moet u de definitie van maten of formules die in een draaitabel worden gebruikt, controleren, zodat u weet wat de filtercontext is wanneer u de resultaten van formules interpreteert.
Naar boven
Context in formules bepalen
Wanneer u een formule maakt, controleert Power Pivot voor Excel eerst de algemene syntaxis en vervolgens worden de namen van kolommen en tabellen die u opgeeft gecontroleerd met mogelijke kolommen en tabellen in de huidige context. Als Power Pivot de kolommen en tabellen niet kan vinden die in de formule zijn opgegeven, wordt er een fout weergegeven.
De context wordt bepaald zoals beschreven in de vorige secties, met behulp van de beschikbare tabellen in de werkmap, eventuele relaties tussen de tabellen en eventuele filters die zijn toegepast.
Als u bijvoorbeeld net enkele 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 aan elkaar zijn gekoppeld door relaties en u werkt in een draaitabel die is gefilterd door het toevoegen van kolomkoppen en het gebruik van slicers, omvat de context de gerelateerde tabellen en eventuele filters op de gegevens.
Context is een krachtig concept dat het ook moeilijk kan maken om fouten in formules op te lossen. U wordt aangeraden te beginnen met eenvoudige formules en relaties om te zien hoe de context werkt, en vervolgens te experimenteren met eenvoudige formules in draaitabellen. De volgende sectie bevat ook enkele voorbeelden van hoe in formules verschillende typen context worden gebruikt 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 kunt insluiten in andere functies die aggregaties uitvoeren.
- Met de functie ALL stelt u context in een formule in. U kunt deze functie gebruiken om filters te overschrijven die zijn toegepast op basis van de querycontext.
- Met de functie ALLEXCEPT kunt u alle filters verwijderen, behalve het filter dat u opgeeft. Beide onderwerpen bevatten voorbeelden die u begeleiden bij het bouwen van formules en het begrijpen van complexe contexten.
- Met de functies EERDER en VROEGST kunt u tabellen doorlopen door berekeningen uit te voeren en tegelijkertijd te verwijzen 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 EERDER en VROEGST bieden. Als deze concepten nieuw voor u zijn, moet u de stappen in het voorbeeld zorgvuldig volgen om te zien hoe de binnenste en uiterlijke context in berekeningen worden gebruikt.
Naar boven
Referentiële integriteit
In dit gedeelte worden enkele geavanceerde concepten besproken met betrekking tot ontbrekende waarden in Power Pivot-tabellen die via relaties met elkaar zijn verbonden. Deze sectie kan nuttig zijn als u werkmappen met meerdere tabellen en complexe formules hebt en hulp nodig hebt bij het begrijpen van de resultaten.
Als u geen ervaring hebt met concepten van relationele gegevens, raden we u aan eerst het inleidende onderwerp Overzicht van relaties te lezen.
Referentiële integriteit en Power Pivot-relaties
In Power Pivot hoeft geen referentiële integriteit tussen twee tabellen af te dwingen om een geldige relatie te definiëren. In plaats daarvan wordt er een lege rij gemaakt aan de 'een'-kant van elke een-op-veel-relatie en deze wordt gebruikt voor het verwerken van alle niet-overeenkomende rijen uit de gerelateerde tabel. Het gedraagt zich in feite als een SQL outer join.
Als u in draaitabellen gegevens groepeert op de één-kant van de relatie, worden alle niet-gerelateerde gegevens aan de veel-kant van de relatie gegroepeerd en worden ze opgenomen in totalen met een lege rijkop. De lege kop komt ruwweg overeen met het 'onbekende lid'.
Het onbekende lid begrijpen
Het concept van het onbekende lid komt u waarschijnlijk bekend voor 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 verkopen voor elke winkel worden berekend, maar dat er in een kolom in de tabel Verkoop een waarde voor de winkelnaam ontbreekt. Aangezien de tabellen voor Winkel en Verkoop zijn verbonden door de naam van de winkel, wat verwacht u dan dat er in de formule gebeurt? Hoe moet de draaitabel de verkoopcijfers groeperen of weergeven die niet gerelateerd zijn aan een bestaande winkel?
Dit probleem komt vaak voor in datawarehouses, waar grote tabellen met feitengegevens logisch moeten zijn gerelateerd aan dimensietabellen die informatie bevatten over opslagplaatsen, regio's en andere kenmerken die worden gebruikt voor het categoriseren en berekenen van feiten. Om het probleem op te lossen, worden alle nieuwe feiten die niet gerelateerd zijn aan 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 om het onbekende lid tegemoet te komen. De lege waarde is een speciale waarde die wordt gebruikt voor het vertegenwoordigen van null-waarden, lege tekenreeksen en andere ontbrekende waarden. Zie Gegevenstypen in gegevensmodellen voor meer informatie over de lege waarde en andere DAX-gegevenstypen.
Naar boven