Zoekacties in Power Pivot-formules

Een van de krachtigste functies in Power Pivot is de mogelijkheid om relaties tussen tabellen te maken en vervolgens de gerelateerde tabellen te gebruiken om gerelateerde gegevens op te zoeken of te filteren. U haalt gerelateerde waarden op uit tabellen met behulp van de formuletaal van Power Pivot, Data Analysis Expressions (DAX). DAX gebruikt een relationeel model en kan daarom gemakkelijk en nauwkeurig gerelateerde of bijbehorende waarden in een andere tabel of kolom ophalen. Als u bekend bent met VERT.ZOEKEN in Excel, is deze functionaliteit in Power Pivot vergelijkbaar, maar veel eenvoudiger te implementeren.

U kunt formules maken die zoekacties uitvoeren als onderdeel van een berekende kolom of als onderdeel van een meting voor gebruik in een draaitabel of draaigrafiek. Zie de volgende onderwerpen voor meer informatie:

Berekende velden in Power Pivot

Berekende kolommen in Power Pivot

In dit gedeelte worden de lookup-functies van DAX beschreven. Verder worden enkele voorbeelden gegeven van het gebruik van deze functies.

Lookup-functies

De mogelijkheid om overeenkomende of gerelateerde gegevens uit een andere tabel op te zoeken, is vooral nuttig in situaties waarin de huidige tabel alleen een of andere identificatie bevat, maar de gegevens die u nodig hebt (zoals productprijs, naam of andere gedetailleerde waarden), in een gerelateerde tabel zijn opgeslagen. Het is ook nuttig wanneer er meerdere rijen in een andere tabel zijn die gerelateerd zijn aan de huidige rij of huidige waarde. U kunt bijvoorbeeld gemakkelijk de verkopen opvragen die aan een specifieke regio, winkel of verkoper zijn gebonden.

In tegenstelling tot de zoekfuncties in Excel zoals VLOOKUP, die zijn gebaseerd op matrices, of LOOKUP, die de eerste van meerdere overeenkomende waarden ophaalt, volgt DAX bestaande relaties onder tabellen die met sleutels zijn samengevoegd om die ene gerelateerde waarde te vinden die exact overeenkomt. DAX kan ook een tabel met records ophalen die verwant zijn aan de huidige record.

Eén gerelateerde waarde ophalen

Met de functie RELATED wordt een enkele waarde geretourneerd van een andere tabel die is gerelateerd aan de huidige waarde in de huidige tabel. U geeft de kolom op die de gewenste gegevens bevat en de functie volgt bestaande relaties tussen tabellen om de waarde uit de opgegeven kolom in de gerelateerde tabel op te halen. In sommige gevallen moet de functie een keten van relaties volgens om de gegevens op te halen.

Stel dat u in Excel een lijst hebt met de verzendingen van vandaag. Het overzicht bevat echter alleen een medewerker-id-nummer, een order-id-nummer en een vervoerder-id-nummer, waardoor het rapport moeilijk leesbaar is. Als u de gewenste extra informatie wilt ophalen, kunt u die lijst converteren naar een gekoppelde Power Pivot-tabel en vervolgens relaties maken met de tabellen Werknemer en Reseller, waarbij EmployeeID wordt gekoppeld aan het veld EmployeeKey en ResellerID aan het veld ResellerKey.

Als u de lookup-gegevens wilt weergeven in uw gekoppelde tabel, voegt u met de volgende formules twee nieuwe berekende kolommen toe:

= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])

De verzendingen van vandaag vóór lookup

Order-id	Medewerker-id	Wederverkoper-id
100314	230	445
100315	15	445
100316	76	108

De tabel Medewerkers

Medewerker-id	Medewerker	Wederverkoper
230	Kuppa Vamsi	Modular Cycle Systems
15	Pilar Ackeman	Modular Cycle Systems
76	Kim Ralls	Associated Fietsen

De verzendingen van vandaag met lookups

Order-id	Medewerker-id	Wederverkoper-id	Medewerker	Wederverkoper
100314	230	445	Kuppa Vamsi	Modular Cycle Systems
100315	15	445	Pilar Ackeman	Modular Cycle Systems
100316	76	108	Kim Ralls	Associated Fietsen

De functie gebruikt de relaties tussen de gekoppelde tabel en de tabellen Employees (medewerkers) en Resellers (wederverkopers) om voor elke rij in het rapport de juiste naam op te halen. U kunt de gerelateerde waarden ook voor berekeningen gebruiken. Zie De functie RELATED voor meer informatie en voorbeelden.

Een lijst gerelateerde waarden ophalen

De functie RELATEDTABLE volgt een bestaande relatie en retourneert een tabel met alle overeenkomende rijen uit de opgegeven tabel. Stelt dat u wilt weten hoeveel orders elke wederverkoper dit jaar heeft geplaatst. U kunt dan een nieuwe berekende kolom maken in de tabel Reseller met de volgende formule, waarmee records worden opgezocht voor elke wederverkoper in de tabel ResellerSales_USD en het aantal afzonderlijke orders van elke wederverkoper wordt geteld. 

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

In deze formule haalt u met de functie RELATEDTABLE eerst de waarde van ResellerKey voor elke wederverkoper in de huidige tabel op. (U hoeft de kolom ID nergens in de formule op te geven, omdat Power Pivot gebruikmaakt van de bestaande relatie tussen de tabellen.) Met de functie RELATEDTABLE worden vervolgens alle rijen uit de ResellerSales_USD tabel opgehaald die zijn gerelateerd aan elke reseller en worden de rijen geteld. Als er echter geen relatie (direct of indirect) tussen de tabellen is, worden alle rijen uit de tabel ResellerSales_USD opgehaald.

Voor de wederverkoper Modular Cycle Systems in onze voorbeelddatabase zijn er vier orders in de omzettabel, dus wordt 4 door de functie geretourneerd. De wederverkoper Associated Fietsen heeft geen omzet behaald, dus wordt een lege waarde geretourneerd.

Wederverkoper	Records in omzettabel voor deze wederverkoper
Modular Cycle Systems	Wederverkoper-id
	445
	445
	445
	445
	Wederverkoper-id
Associated Fietsen

Naar boven