Една от най-мощните функции в Power Pivot е възможността за създаване на релации между таблиците и след това използване на свързаните таблици за търсене или филтриране на свързани данни. Можете да извличате свързани стойности от таблици с помощта на езика на формулата, предоставен сPower Pivot, изрази за анализ на данни (DAX). DAX използва релационния модел и следователно може лесно и точно да извлече свързани или съответстващи стойности в друга таблица или колона. Ако сте запознати с VLOOKUP в Excel, тази функционалност в Power Pivot е подобна, но много по-лесна за внедряване.
Можете да създавате формули, които правят справки като част от изчисляема колона или като част от мярка за използване в обобщена таблица или обобщена диаграма. За повече информация вж. следните теми:
Изчисляеми полета в Power Pivot
Изчисляеми колони в Power Pivot
Този раздел описва функциите DAX, които са предоставени за справка, заедно с някои примери как да използвате функциите.
Забележка: В зависимост от типа на операцията за справка или формулата за справка, която искате да използвате, може да се наложи първо да създадете релация между таблиците.
Разбиране на функциите за справки
Възможността за търсене на съвпадащи или свързани данни от друга таблица е особено полезна в ситуации, когато текущата таблица има само някакъв идентификатор, но данните, които ви трябват (например цена на продукта, име или други подробни стойности), се съхраняват в свързана таблица. Също така е полезно, когато има множество редове в друга таблица, свързани с текущия ред или текущата стойност. Можете например лесно да извлечете всички продажби, свързани с конкретен регион, магазин или продавач.
За разлика от функциите за търсене на Excel, като например VLOOKUP, които се базират на масиви или LOOKUP, което получава първата от множеството съвпадащи стойности, DAX следва съществуващите релации между таблиците, съединени с клавиши, за да получи единичната свързана стойност, която съответства точно. DAX може също да извлече таблица със записи, които са свързани с текущия запис.
Забележка: Ако сте запознати с релационните бази данни, можете да си представите справките в Power Pivot подобно на вложената команда за избор в Transact-SQL.
Извличане на една свързана стойност
Функцията RELATED връща една стойност от друга таблица, свързана с текущата стойност в текущата таблица. Задавате колоната, съдържаща желаните данни, и функцията следва съществуващите релации между таблиците, за да извлече стойността от указаната колона в свързаната таблица. В някои случаи функцията трябва да следва верига от релации, за да извлече данните.
Да предположим например, че имате списък с днешните пратки в Excel. Списъкът обаче съдържа само ИД номер на служител, ИД на поръчка и ИД номер на доставчик, което прави справката трудна за четене. За да получите допълнителната информация, която искате, можете да конвертирате този списък в Power Pivot свързана таблица и след това да създадете релации към таблиците Служител и Риселър, като съпоставяте EmployeeID с полето EmployeeKey и ResellerID в полето ResellerKey.
За да покажете информацията за справка във вашата свързана таблица, добавяте две нови изчисляеми колони със следните формули:
= RELATED('Employees'[EmployeeName]) = RELATED('Риселър'[ИмеНаФирма])
Днешните пратки преди търсене
|
ИД_поръчка |
ИД на служител |
ResellerID |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Таблица "Служители"
|
ИД на служител |
Служител |
Дистрибутора |
|---|---|---|
|
230 |
Kuppa Vamsi |
Модулни системи за цикъл |
|
15 |
Пилар Аккеман |
Модулни системи за цикъл |
|
76 |
Ким Ралс |
Свързани велосипеди |
Днешните пратки с справки
|
ИД_поръчка |
ИД на служител |
ResellerID |
Служител |
Дистрибутора |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Модулни системи за цикъл |
|
100315 |
15 |
445 |
Пилар Аккеман |
Модулни системи за цикъл |
|
100316 |
76 |
108 |
Ким Ралс |
Свързани велосипеди |
Функцията използва релациите между свързаната таблица и таблицата "Служители" и "Дистрибутори", за да получи правилното име за всеки ред в отчета. Можете също да използвате свързани стойности за изчисления. За повече информация и примери вж. ФУНКЦИЯ RELATED.
Извличане на списък със свързани стойности
Функцията RELATEDTABLE следва съществуваща релация и връща таблица, която съдържа всички съответстващи редове от указаната таблица. Да предположим например, че искате да разберете колко поръчки е направил всеки риселър тази година. Можете да създадете нова изчисляема колона в таблицата Риселъри, която включва следната формула, която търси записи за всеки риселър в таблицата ResellerSales_USD и преброява отделните поръчки, направени от всеки риселър.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
В тази формула функцията RELATEDTABLE първо получава стойността на ResellerKey за всеки риселър в текущата таблица. (Не е нужно да задавате колоната "ИД" някъде във формулата, защото Power Pivot използва съществуващата релация между таблиците.) След това функцията RELATEDTABLE получава всички редове от таблицата ResellerSales_USD, които са свързани с всеки риселър, и преброява редовете. Ако няма релация (директна или непряка) между двете таблици, ще получите всички редове от таблицата ResellerSales_USD.
За системите за модулен цикъл на риселъра в нашата примерна база данни има четири поръчки в таблицата за продажби, така че функцията връща 4. За свързаните велосипеди риселърът няма продажби, така че функцията връща празна стойност.
|
Дистрибутора |
Записи в таблицата за продажби за този риселър |
|
|---|---|---|
|
Модулни системи за цикъл |
ИД на риселър |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
ИД на риселър |
SalesOrderNumber |
|
|
Свързани велосипеди |
Забележка: Тъй като функцията RELATEDTABLE връща таблица, а не една стойност, тя трябва да се използва като аргумент на функция, която извършва операции с таблици. За повече информация вж. RELATEDTABLE функция.
