Een van de krachtigste functies in Power Pivot is de mogelijkheid om relaties te maken tussen tabellen en vervolgens de gerelateerde tabellen te gebruiken om gerelateerde gegevens op te zoeken of te filteren. U haalt gerelateerde waarden op uit tabellen met de formuletaal DAX (Data Analysis Expressions), die bij Power Pivot wordt geleverd. 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, zult u merken dat deze functionaliteit in Power Pivot vergelijkbaar is, maar veel eenvoudiger kan worden geïmplementeerd.
U kunt formules maken die opzoekingen doen 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.
Opmerking: Afhankelijk van het type opzoekbewerking of opzoekformule dat u gebruikt, moet u misschien eerst een relatie tussen de tabellen maken.
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.
Opmerking: Als u bekend bent met relationele databases, kunt u zoekacties in Power Pivot vergelijken met een geneste subselectie-instructie in Transact-SQL.
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 benodigde extra informatie wilt ophalen, kunt u de lijst omzetten in een gekoppelde Power Pivot-tabel en vervolgens relaties maken tussen de tabellen Employee en Reseller, waarbij u EmployeeID koppelt 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 RELATED, functie 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 nergens in de formule de id-kolom op te geven omdat Power Pivot de bestaande relatie tussen de tabellen gebruikt.) De functie RELATEDTABLE haalt vervolgens alle rijen op van de tabelResellerSales_USD die verwant zijn aan de wederverkopers en telt de rijen. 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 |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
Wederverkoper-id |
SalesOrderNumber |
|
|
Associated Fietsen |
Opmerking: Aangezien de functie RELATEDTABLE een tabel retourneert in plaats van één waarde, moet deze worden gebruikt als een argument in een functie waarmee bewerkingen op tabellen worden uitgevoerd. Zie RELATEDTABLE,functie voor meer informatie.
