Изрази за анализ на данни (DAX) в Power Pivot

Изразите за анализ на данни (DAX) отначало звучат малко смущаващо, но не позволявайте на името да ви заблуди. Основите на DAX са наистина лесни за разбиране. Първо най-важно - DAX НЕ е език за програмиране. DAX е език за формули. Можете да използвате DAX, за да дефинирате потребителски изчисления за изчисляеми колони и за мерки (известни също като изчисляеми полета). DAX включва някои от функциите, използвани във формулите на Excel, и допълнителни функции, предназначени за работа с релационни данни и за динамично агрегиране.

Разбиране на DAX формулите

DAX формулите много приличат на формулите на Excel. За да създадете такъв, трябва да въведете знак за равенство, последван от име на функция или израз и всички задължителни стойности или аргументи. Подобно на Excel, DAX предоставя редица функции, които можете да използвате за работа със низове, извършване на изчисления, използвайки дати и часове, или създаване на условни стойности.

DAX формулите обаче се различават по следните важни начини:

• Ако искате да персонализирате изчисленията ред по ред, DAX включва функции, които ви позволяват да използвате текущата стойност на ред или свързана стойност, за да извършвате изчисления, които се различават в зависимост от контекста.
• DAX включва тип функция, която връща като резултат таблица, а не една стойност. Тези функции могат да се използват за предоставяне на входни данни към други функции.
• Интелигентно време Функциитев DAX позволяват изчисления, използващи диапазони от дати, и сравняване на резултатите между паралелни периоди.

Къде да използвате DAX формули

Можете да създавате формули в Power Pivot или в изчисляеми колони, или в изчисляеми полета.

Изчисляеми колони

Изчисляемата колона е колона, която добавяте към съществуваща таблица на Power Pivot. Вместо да поставяте или импортирате стойности в колоната, вие създавате DAX формула, която дефинира стойностите на колоните. Ако включите таблицата на Power Pivot в обобщена таблица (или обобщена диаграма), изчисляемата колона може да се използва като всяка друга колона с данни.

Формулите в изчисляемите колони в голяма степен приличат на формулите, които създавате в Excel. За разлика от Excel обаче, не можете да създадете различна формула за различните редове в таблицата; Вместо това DAX формулата се прилага автоматично към цялата колона.

Когато една колона съдържа формула, стойността се изчислява за всеки ред. Резултатите се изчисляват за колоната веднага щом създадете формулата. Стойностите на колоните се преизчисляват само ако базовите данни се обновят или ако се използва ръчно преизчисляване.

Можете да създавате изчисляеми колони, които се базират на мерки и други изчисляеми колони. Избягвайте обаче да използвате едно и също име за изчисляема колона и мярка, тъй като това може да доведе до объркване на резултатите. Когато правите препратка към колона, най-добре е да използвате напълно квалифицирана препратка към колона, за да избегнете случайно извикване на мярка.

За по-подробна информация вж. "Изчисляеми колони в Power Pivot".

Мерки

Мярката е формула, която е създадена специално за използване в обобщена таблица (или обобщена диаграма), която използва данни на Power Pivot. Мерките могат да се базират на стандартни агрегатни функции, като например COUNT или SUM, или да дефинирате собствена формула с помощта на DAX. Мярката се използва в областта на стойностите на обобщената таблица. Ако искате да поставите изчислените резултати в друга област на обобщената таблица, използвайте изчисляема колона вместо това.

Когато дефинирате формула за явна мярка, нищо не се случва, докато не добавите мярката в обобщена таблица. Когато добавите мярката, формулата се изчислява за всяка клетка в областта на стойностите на обобщената таблица. Тъй като за всяка комбинация от заглавки на редове и колони се създава резултат, резултатът за мярката може да бъде различен във всяка клетка.

Дефиницията на мярката, която създавате, се записва в нейната таблица с данни източник. Тя се появява в списъка с полета на обобщената таблица и е достъпна за всички потребители на работната книга.

За по-подробна информация вж. "Мерки" в Power Pivot.

Създаване на формули с помощта на лентата за формули

Power Pivot, подобно на Excel, предоставя лента за формули, която улеснява създаването и редактирането на формули, и функция "Автодовършване", която позволява да се минимизират грешките при въвеждане и синтактичните грешки.

За да въведете името на таблица Започнете да въвеждате името на таблицата. Автодовършването на формули предоставя падащ списък, съдържащ валидни имена, които започват с тези букви.

За да въведете името на колона Въведете квадратна скоба и след това изберете колоната от списъка с колони в текущата таблица. За колона от друга таблица започнете да въвеждате първите букви от името на таблицата и след това изберете колоната от падащия списък "Автодовършване".

За повече подробности и преглед как да създавате формули, вижте "Създаване на формули за изчисления в Power Pivot".

Съвети за използване на автодовършване

Можете да използвате "Автодовършване на формули" в средата на съществуваща формула с вложени функции. Текстът непосредствено преди точката на вмъкване се използва за показване на стойностите в падащия списък, а целият текст след точката на вмъкване остава непроменен.

Дефинираните имена, които създавате за константи, не се показват в падащия списък за автодовършване, но все пак можете да ги въведете.

Power Pivot не добавя затварящите кръгли скоби на функциите и не съпоставя автоматично скобите. Трябва да се уверите, че всяка функция е синтактично правилна, в противен случай няма да можете да запишете или използвате формулата. 

Използване на няколко функции в една формула

Можете да влагате функции, което означава, че използвате резултатите от една функция като аргумент на друга функция. В изчисляеми колони може да влагате до 64 нива на функции. Влагането обаче може да затрудни създаването или отстраняването на неизправности в формули.

Много функции на DAX са предназначени да се използват единствено като вложени функции. Тези функции връщат таблица, която не може да бъде директно записана в резултат; Тя трябва да се предоставя като входни данни за функция на таблица. Например функциите SUMX, AVERAGEX и MINX изискват таблица като първи аргумент.

Сравняване на функциите DAX и функциите на Excel

Библиотеката с функции на DAX се базира на библиотеката с функции на Excel, но библиотеките имат много разлики. Този раздел обобщава разликите и сходствата между функциите на Excel и функциите DAX.

• Много от DAX функциите имат едно и също име и същото общо поведение като функциите на Excel, но са модифицирани да вземат различни типове входни данни и в някои случаи е възможно да връщат различен тип данни. По принцип не можете да използвате функции DAX във формули на Excel или формули на Excel в Power Pivot без някои модификации.
• Функциите DAX никога не приемат за препратка към клетка или диапазон, а вместо това функциите DAX приемат колона или таблица като препратка.
• Функциите за дата и час на DAX връщат тип данни за дата и час. За разлика от това функциите за дата и час на Excel връщат цяло число, което представя дата като пореден номер.
• Много от новите функции DAX или връщат таблица със стойности, или извършват изчисления въз основа на таблица със стойности като входни данни. За разлика от това, в Excel няма функции, които връщат таблица, но някои функции могат да работят с масиви. Възможността за лесни препратки към цели таблици и колони е нова функция в Power Pivot.
• DAX предоставя нови функции за търсене, които са подобни на функциите за масиви и вектори в Excel. DAX функциите обаче изискват да се създаде релация между таблиците.
• Данните в дадена колона се очаква винаги да са от един и същ тип. Ако данните не са от един и същ тип, DAX променя цялата колона на типа данни, който побира най-добре всички стойности.

Типове данни на DAX

Можете да импортирате данни в модел на данни на Power Pivot от много различни източници на данни, които може да поддържат различни типове данни. Когато импортирате или заредите данните и след това използвате данните в изчисления или обобщени таблици, данните се преобразуват в един от типовете данни на Power Pivot. За списък на типовете данни вж. "Типове данни в моделите на данни".

Табличният тип данни е нов тип данни в DAX, който се използва като вход или изход за много нови функции. Например функцията FILTER взема таблица като вход и извежда друга таблица, която съдържа само редовете, отговарящи на условията за филтриране. Чрез комбиниране на функциите на таблица с агрегатните функции можете да извършвате сложни изчисления върху динамично дефинирани набори от данни. За повече информация вж. "Агрегирания" в Power Pivot.

Формулите и релационният модел

Прозорецът на Power Pivot е област, в която можете да работите с множество таблици с данни и да свързвате таблиците в релационен модел. В този модел на данни таблиците са свързани помежду си чрез релации, което ви позволява да създавате корелации с колони в други таблици и да създавате по-интересни изчисления. Можете например да създадете формули, които сумират стойности за свързана таблица, а след това да запишете тази стойност в една клетка. Или, за да управлявате редовете от свързаната таблица, можете да приложите филтри към таблици и колони. За повече информация вж. "Релации между таблици в модел на данни".

Тъй като можете да свързвате таблици с помощта на релации, вашите обобщени таблици могат също да включват данни от няколко колони, които са от различни таблици.

Тъй като обаче формулите могат да работят с цели таблици и колони, трябва да проектирате изчисленията по различен начин, отколкото в Excel.

• По принцип DAX формулата в колона винаги се прилага към целия набор от стойности в колоната (никога не се отнася само за няколко реда или клетки).
• Таблиците в Power Pivot трябва винаги да имат един и същ брой колони във всеки ред и всички редове в една колона трябва да съдържат данни от един и същ тип.
• Когато таблиците са свързани с релация, от вас се очаква да се уверите, че двете колони, използвани като ключове, в повечето случаи съвпадат. Тъй като Power Pivot не поддържа целостта на връзките, е възможно да имате несъвпадащи стойности в ключова колона и все пак да създадете релация. Наличието на празни или несъвпадащи стойности обаче може да повлияе на резултатите от формулите и на облика на обобщените таблици. За повече информация вж. "Справки във формули на Power Pivot".
• Когато свързвате таблици с помощта на релации, разширявате обхвата или контекста, в който се изчисляват формулите. Например формулите в обобщена таблица могат да бъдат засегнати от всякакви филтри или заглавия на колони и редове в обобщената таблица. Можете да пишете формули, които манипулират контекста, но контекстът може да доведе до промяна на резултатите по начини, които може да не предвидите. За повече информация вж. "Контекст във формули на DAX".

Актуализиране на резултатите от формули

Обновяването и преизчисляването на данните са две отделни, но свързани операции, които трябва да разберете, когато проектирате модел на данни, който съдържа сложни формули, големи количества данни или данни, получени от външни източници на данни.

Обновяването на данните е процесът на актуализиране на данните във вашата работна книга с нови данни от външен източник на данни. Можете да обновявате данните ръчно през интервали, които вие задавате. Или, ако сте публикували работната книга в сайт на SharePoint, можете да планирате автоматично обновяване от външни източници.

Преизчисляването е процесът на актуализиране на резултатите от формулите, за да се отразят всички промени в самите формули и да се отразят тези промени в базовите данни. Преизчисляването може да повлияе на производителността по следните начини:

• За изчисляема колона резултатът от формулата винаги трябва да се преизчислява за цялата колона, когато променяте формулата.
• За мярка резултатите от формула не се изчисляват, докато мярката не се постави в контекста на обобщената таблица или обобщената диаграма. Формулата също така ще се преизчислява, когато промените заглавие на ред или колона, което засяга филтрите на данните, или когато обновите обобщената таблица ръчно.

Отстраняване на проблеми с формули

Грешки при писане на формули

Ако получите грешка, когато дефинирате формула, формулата може да съдържа синтактична грешка, семантична грешка или грешка при изчисление.

Синтактичните грешки се отстраняват най-лесно. Те обикновено включват липсваща скоба или запетая. За помощ относно синтаксиса на отделни функции вж. справката за функциите на DAX.

Другият тип грешка възниква, когато синтаксисът е правилен, но стойността или колоната, към които препращате, нямат смисъл в контекста на формулата. Такива семантични и изчислителни грешки могат да бъдат причинени от някой от следните проблеми:

• Формулата препраща към несъществуваща колона, таблица или функция.
• Формулата изглежда правилна, но когато ядрото за данни извлича данните, открива несъответствие на типове и издава съобщение за грешка.
• Формулата подава неправилен брой или тип параметри на дадена функция.
• Формулата препраща към друга колона, в която има грешка, следователно нейните стойности са невалидни.
• Формулата препраща към колона, която не е била обработена, което означава, че тя има метаданни, но не и действителни данни, които да се използва за изчисления.

В първите четири случая DAX маркира с флаг цялата колона, която съдържа невалидната формула. В последния случай DAX оцветява колоната в сиво, за да покаже, че колоната е в състояние на необработена обработка.

Неправилни или необичайни резултати при класиране или подреждане на стойностите на колоните

При класиране или подреждане на колона, съдържаща стойност NaN (Not a Number), може да получите грешни или неочаквани резултати. Например, когато изчисление раздели 0 на 0, връща се NaN резултат.

Това е така, защото формулата извършва подреждане и подреждане чрез сравняване на числовите стойности; обаче NaN не може да се сравнява с други числа в колоната.

За да гарантирате правилни резултати, можете да използвате условни команди, използващи функция IF, за да проверите за NaN стойности и да върнете числова стойност 0.

Съвместимост с табличните модели на услугите за анализ и режима на DirectQuery

По принцип DAX формулите, които създавате в Power Pivot, са напълно съвместими с табличните модели на услугите за анализ. Ако обаче мигрирате модела на Power Pivot към екземпляр на услугите за анализ и след това разположите модела в режим на DirectQuery, има някои ограничения.

• Някои DAX формули може да връщат различни резултати, ако разположите модела в режим на DirectQuery.
• Възможно е някои формули да предизвикат грешки при проверката, когато разполагате модела в режим на DirectQuery, тъй като формулата съдържа DAX функция, която не се поддържа за релационен източник на данни.

За повече информация вж. документацията за таблично моделиране на услугите за анализ в SQL Server 2012 BooksOnline.