Сценарии на DAX в Power Pivot

Създаване на потребителски изчисления за обобщена таблица

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

• ИЗЧИСЛЯВАМ

• ТАБЛИЦА ЗА ИЗЧИСЛЯВАНЕ

Прилагане на филтър към формула

На повечето места, където дадена DAX функция взема таблица като аргумент, обикновено можете да подадете във филтрирана таблица вместо това с помощта на функцията FILTER вместо името на таблицата, или като зададете израз за филтър като един от аргументите на функцията. Темите по-долу предоставят примери как да създавате филтри и как филтрите влияят върху резултатите от формулите. За повече информация вижте Филтриране на данни в DAX формули.

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

• ФИЛТЪР

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Избирателно премахване на филтри за създаване на динамично съотношение

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

• Какъв беше приносът на текущите продажби на продукта към общите продажби за годината?

• Колко е допринесъл това подразделение за общата печалба за всички оперативни години в сравнение с другите подразделения?

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

Темата ALLEXCEPT предоставя пример за това как избирателно да се изчистват филтрите във формула. И двата примера ви превеждат през промяната на резултатите в зависимост от проекта на обобщената таблица.

• ЦЯЛ

• ALLEXCEPT

За други примери как да изчислявате съотношения и проценти, вижте следните теми:

• ISBLANK

• ISERROR

Използване на стойност от външен цикъл

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

• Функция EARLIER

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

Създаване на ключова колона чрез конкатенация

Power Pivot не позволява комбинирани ключове; следователно, ако имате съставни ключове в източника на данни, може да се наложи да ги комбинирате в една колона с ключ. Следващата тема предоставя един пример как да създадете изчисляема колона на базата на съставен ключ.

• CONCATENATE функция

Съставяне на дата на базата на части за дата, извлечени от текстова дата

Power Pivot използва данни от тип дата/час на SQL Server, за да работи с дати; следователно, ако вашите външни данни съдържат дати, които са форматирани по различен начин – например ако датите ви са написани в регионален формат за дата, който не е разпознат от Power Pivot ядро за данни, или ако вашите данни използват целочислени заместващи ключове – може да се наложи да използвате DAX формула, за да извлечете частите от датата и след това да съставите частите във валидно представяне на дата/час.

Ако например имате колона с дати, които са представени като цяло число и след това са импортирани като текстов низ, можете да преобразувате низа в стойност за дата/час с помощта на следната формула:

=DATE(RIGHT([Стойност1];4);LEFT([Стойност1];2);MID([Стойност1];2))

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

• DATEVALUE функция

• Функция TIMEVALUE

• DATE функция

Дефиниране на формат на дата или число по избор

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

• Предварително дефинирани числови формати за функцията FORMAT

• Числови формати по избор за функцията FORMAT

• Предварително дефинирани формати за дата и час за функцията FORMAT

• Формати за дата и час по избор за функцията FORMAT

Промяна на типовете данни с помощта на формула

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

• За да преобразувате дата или числов низ в число, умножете по 1,0. Например следващата формула изчислява текущата дата минус 3 дни и след това извежда съответната целочислена стойност.

  =(TODAY()-3)*1,0

• За да преобразувате стойност на дата, число или валута в низ, конкатенирайте стойността с празен низ. Например следната формула връща днешната дата като низ.

  =""& TODAY()

Следните функции също могат да се използват, за да се гарантира връщането на определен тип данни:

Преобразуване на реални числа в цели числа

• Функция ROUND

• CEILING функция

• FLOOR функция

  Преобразуване на реални числа, цели числа или дати в низове

• ФУНКЦИЯ FIXED

• Функция FORMAT

  Преобразуване на низове в реални числа или дати

• Функция VALUE

• DATEVALUE функция

• Функция TIMEVALUE

Създаване на стойност на базата на условие

Можете да използвате вложени условия IF, за да тествате стойности и да генерирате нови стойности условно. Следните теми съдържат някои прости примери за условно обработване и условни стойности:

• АКО

• ФУНКЦИЯ OR

• CONCATENATE функция

Проверка за грешки във формула

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

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

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

• ISBLANK функция

• IFERROR функция

Изчисляване на кумулативни продажби

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

• CLOSINGBALANCEMONTH функция, CLOSINGBALANCEQUARTER функция, CLOSINGBALANCEYEAR функция

• OPENINGBALANCEMONTH функция, OPENINGBALANCEQUARTER функция, OPENINGBALANCEYEAR функция

Сравняване на стойности с течение на времето

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

• PREVIOUSMONTH функция, PREVIOUSQUARTER, PREVIOUSYEAR функция

• TOTALMTD функция, TOTALQTD функция, TOTALYTD функция

• Функция PARALLELPERIOD

Изчисляване на стойност в диапазон от дати по избор

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

• DATESINPERIOD функция

• DATESBETWEEN функция

• DATEADD функция

• Функция FIRSTDATE

• Функция LASTDATE

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

• Функция PARALLELPERIOD

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

Показване само на десетте най-важни елемента в обобщена таблица

Подреждане на елементи динамично с помощта на формула

Следващата тема съдържа пример как да използвате DAX за създаване на класиране, което се съхранява в изчисляема колона. Тъй като формулите на DAX се изчисляват динамично, винаги можете да сте сигурни, че класирането е правилно дори ако базовите данни са променени. Освен това, тъй като формулата се използва в изчисляема колона, можете да използвате класирането в сегментатор и след това да изберете първите 5, първите 10 или дори първите 100 стойности.

• Функция EARLIER.