Jedną z najbardziej zaawansowanych funkcji w Power Pivot jest możliwość tworzenia relacji między tabelami, a następnie używania powiązanych tabel do wyszukiwania lub filtrowania powiązanych danych. Powiązane wartości można pobierać z tabel przy użyciu języka formuł udostępnionego w językuPower Pivot, Data Analysis Expressions (DAX). W języku DAX jest używany model relacyjny, dlatego można łatwo i dokładnie pobrać powiązane lub odpowiadające im wartości w innej tabeli lub kolumnie. Jeśli znasz funkcję WYSZUKAJ.PIONOWO w programie Excel, ta funkcja w Power Pivot jest podobna, ale znacznie łatwiejsza do zaimplementowania.
Można tworzyć formuły do wyszukiwania jako część kolumny obliczeniowej lub jako część miary do użycia w tabeli przestawnej lub na wykresie przestawnym. Aby uzyskać więcej informacji, zobacz następujące tematy:
Pola obliczeniowe w dodatku Power Pivot
Kolumny obliczeniowe w dodatku Power Pivot
W tej sekcji opisano funkcje języka DAX, które są dostępne do wyszukiwania, oraz kilka przykładów użycia tych funkcji.
Uwaga: W zależności od typu operacji odnośnika lub formuły odnośnika, której chcesz użyć, może być konieczne uprzednie utworzenie relacji między tabelami.
Opis funkcji odnośników
Możliwość wyszukiwania zgodnych lub powiązanych danych z innej tabeli jest szczególnie przydatna w sytuacjach, gdy bieżąca tabela ma tylko określony identyfikator, ale potrzebne dane (takie jak cena produktu, nazwa lub inne szczegółowe wartości) są przechowywane w powiązanej tabeli. Jest to również przydatne, gdy w innej tabeli znajduje się wiele wierszy powiązanych z bieżącym wierszem lub bieżącą wartością. Możesz na przykład łatwo pobrać całą sprzedaż związaną z określonym regionem, sklepem lub sprzedawcą.
W przeciwieństwie do funkcji wyszukiwania programu Excel, takich jak WYSZUKAJ.PIONOWO, które są oparte na tablicach, lub funkcji WYSZUKAJ, która pobiera pierwszą z wielu zgodnych wartości, język DAX podąża za istniejącymi relacjami między tabelami połączonymi kluczami, aby uzyskać pojedynczą powiązaną wartość, która dokładnie odpowiada. Język DAX może również pobrać tabelę rekordów powiązanych z bieżącym rekordem.
Uwaga: Jeśli znasz relacyjne bazy danych, możesz myśleć o odnośnikach w programie Power Pivot, podobnie jak w przypadku instrukcji zagnieżdżonych podznaczeń w języku Transact-SQL.
Pobieranie pojedynczej wartości pokrewnej
Funkcja RELATED zwraca pojedynczą wartość z innej tabeli powiązaną z bieżącą wartością w bieżącej tabeli. Określasz kolumnę zawierającą odpowiednie dane, a funkcja jest zgodna z istniejącymi relacjami między tabelami, aby pobrać wartość z określonej kolumny w powiązanej tabeli. W niektórych przypadkach funkcja musi podążać za łańcuchem relacji, aby pobrać dane.
Załóżmy na przykład, że masz listę dzisiejszych przesyłek w programie Excel. Lista zawiera jednak tylko numer identyfikacyjny pracownika, numer identyfikacyjny zamówienia i numer identyfikacyjny spedytora, co utrudnia odczytanie raportu. Aby uzyskać żądane dodatkowe informacje, możesz przekonwertować tę listę na tabelę połączoną Power Pivot, a następnie utworzyć relacje z tabelami Pracownik i Odsprzedawca, dopasować pole EmployeeID do pola EmployeeKey i ResellerID do pola ResellerKey.
Aby wyświetlić informacje odnośnika w tabeli połączonej, dodaj dwie nowe kolumny obliczeniowe z następującymi formułami:
= RELATED('Employees'[EmployeeName]) = RELATED('Resellers'[CompanyName])
Dzisiejsze wysyłki przed wyszukiwaniem
|
ID_zamówienia |
Idpracownika |
Identyfikator odsprzedawcy |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Tabela Employees
|
Idpracownika |
Pracownika |
Sprzedawcy |
|---|---|---|
|
230 |
Kuppa Vamsi |
Modular Cycle Systems |
|
15 |
Pilar Ackeman |
Modular Cycle Systems |
|
76 |
Kim Ralls |
Skojarzone rowery |
Dzisiejsze wysyłki z odnośnikami
|
ID_zamówienia |
Idpracownika |
Identyfikator odsprzedawcy |
Pracownika |
Sprzedawcy |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Modular Cycle Systems |
|
100315 |
15 |
445 |
Pilar Ackeman |
Modular Cycle Systems |
|
100316 |
76 |
108 |
Kim Ralls |
Skojarzone rowery |
Funkcja używa relacji między tabelą połączoną a tabelami Pracownicy i Odsprzedawcy, aby uzyskać poprawną nazwę dla każdego wiersza w raporcie. Do obliczeń można także używać powiązanych wartości. Aby uzyskać więcej informacji i przykłady, zobacz RELATED, funkcja.
Pobieranie listy powiązanych wartości
Funkcja RELATEDTABLE jest zgodna z istniejącą relacją i zwraca tabelę zawierającą wszystkie pasujące wiersze z określonej tabeli. Załóżmy na przykład, że chcesz sprawdzić, ile zamówień złożył każdy odsprzedawcy w tym roku. W tabeli Resellers (Odsprzedawcy) można utworzyć nową kolumnę obliczeniową zawierającą następującą formułę, która umożliwia wyszukanie rekordów dla każdego odsprzedawcy w ResellerSales_USD tabeli i zliczania pojedynczych zamówień składanych przez każdego odsprzedawcy.
=ILE.LICZB(RELATEDTABLE(ResellerSales_USD))
W tej formule funkcja RELATEDTABLE najpierw pobiera wartość ResellerKey dla każdego odsprzedawcy w bieżącej tabeli. (Nie trzeba określać kolumny identyfikatora w formule, ponieważ Power Pivot używa istniejącej relacji między tabelami). Następnie funkcja TABELA.POKR pobiera wszystkie wiersze z ResellerSales_USD tabeli powiązanej z każdym odsprzedawcą i zlicza wiersze. Jeśli między obiema tabelami nie ma relacji (bezpośredniej ani pośredniej), wszystkie wiersze zostaną pobrane z ResellerSales_USD tabeli.
W przypadku odsprzedawcy Modular Cycle Systems w naszej przykładowej bazie danych istnieją cztery zamówienia w tabeli sprzedaży, więc funkcja zwraca wartość 4. W przypadku skojarzonych rowerów odsprzedawca nie ma sprzedaży, więc funkcja zwraca wartość pustą.
|
Sprzedawcy |
Rekordy tego odsprzedawcy w tabeli sprzedaży |
|
|---|---|---|
|
Modular Cycle Systems |
Identyfikator odsprzedawcy |
Salesordernumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
Identyfikator odsprzedawcy |
Salesordernumber |
|
|
Skojarzone rowery |
Uwaga: Funkcja RELATEDTABLE zwraca tabelę, a nie jedną wartość, dlatego musi być użyta jako argument funkcji wykonującej operacje na tabelach. Aby uzyskać więcej informacji, zobacz RELATEDTABLE, funkcja.
