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

Создание пользовательских вычислений для сводной таблицы

CALCULATE и CALCULATETABLE — это мощные гибкие функции, которые полезны для определения вычисляемых полей. Эти функции позволяют изменить контекст, в котором будет выполняться вычисление. Вы также можете настроить тип статистической или математической операции для выполнения. Примеры см. в следующих разделах.

• ВЫЧИСЛЯТЬ

• CALCULATETABLE

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

В большинстве мест, где функция DAX принимает таблицу в качестве аргумента, вместо нее можно передать отфильтрованную таблицу либо с помощью функции FILTER вместо имени таблицы, либо путем указания выражения фильтра в качестве одного из аргументов функции. В следующих разделах приведены примеры создания фильтров и их влияния на результаты формул. Дополнительные сведения см. в разделе Фильтрация данных в формулах DAX.

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

• ФИЛЬТР

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Выборочное удаление фильтров для создания динамического соотношения

Создавая динамические фильтры в формулах, вы можете легко ответить на следующие вопросы:

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

• Сколько это разделение способствовало общей прибыли за все операционные годы, по сравнению с другими подразделениями?

На формулы, используемые в сводной таблице, может влиять контекст сводной таблицы, но вы можете выборочно изменить контекст, добавив или удалив фильтры. В примере в разделе ALL показано, как это сделать. Чтобы найти отношение продаж для конкретного торгового посредника к продажам для всех торговых посредников, создайте меру, которая вычисляет значение для текущего контекста, делив его на значение для контекста ALL.

В разделе ALLEXCEPT приведен пример выборочного очистки фильтров по формуле. Оба примера показано, как меняются результаты в зависимости от структуры сводной таблицы.

• ВСЕ

• ALLEXCEPT

Другие примеры вычисления коэффициентов и процентных значений см. в следующих разделах:

• ISBLANK

• ISERROR

Использование значения из внешнего цикла

Помимо использования значений из текущего контекста в вычислениях, DAX может использовать значение из предыдущего цикла при создании набора связанных вычислений. В следующем разделе представлено пошаговое руководство по созданию формулы, ссылающейся на значение из внешнего цикла. Функция EARLIER поддерживает до двух уровней вложенных циклов.

• Функция РАНЕЕ

Дополнительные сведения о контексте строк и связанных таблицах, а также о том, как использовать эту концепцию в формулах, см. в статье Контекст в формулах DAX.

Создание ключевого столбца путем объединения

Power Pivot не допускает составные ключи; Поэтому при наличии составных ключей в источнике данных может потребоваться объединить их в один ключевой столбец. В следующем разделе приведен один из примеров создания вычисляемого столбца на основе составного ключа.

• Функция сцепления

Создание даты на основе частей даты, извлеченных из текстовой даты

Power Pivot использует тип данных даты и времени SQL Server для работы с датами; Таким образом, если внешние данные содержат даты, отформатированные по-разному( например, если даты написаны в формате региональных дат, который не распознается подсистемой данных Power Pivot, или если в данных используются целые суррогатные ключи, может потребоваться использовать формулу DAX, чтобы извлечь части даты, а затем создать их в допустимое представление даты и времени.

Например, если у вас есть столбец дат, который был представлен в виде целого числа, а затем импортирован в виде текстовой строки, можно преобразовать строку в значение даты и времени с помощью следующей формулы:

=DATE(RIGHT([Value1];4),LEFT([Value1];2),MID([Value1];2))

В следующих разделах содержатся дополнительные сведения о функциях, используемых для извлечения и составления дат.

• Функция DATEVALUE

• Функция TIMEVALUE

• Функция DATE

Определение пользовательского формата даты или числа

Если данные содержат даты или числа, которые не представлены в одном из стандартных текстовых форматов Windows, можно определить настраиваемый формат, чтобы обеспечить правильную обработку значений. Эти форматы используются при преобразовании значений в строки или из строк. В следующих разделах также представлен подробный список стандартных форматов, доступных для работы с датами и числами.

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

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

• Предопределенные форматы даты и времени для функции FORMAT

• Настраиваемые форматы даты и времени для функции FORMAT

Изменение типов данных с помощью формулы

В Power Pivot тип данных выходных данных определяется исходными столбцами, и вы не можете явно указать тип данных результата, так как оптимальный тип данных определяется Power Pivot. Однако для управления типом выходных данных можно использовать неявные преобразования типов данных, выполняемые Power Pivot. 

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

  =(СЕГОДНЯ()-3)*1.0

• Чтобы преобразовать значение даты, числа или валюты в строку, сцепить значение с пустой строкой. Например, следующая формула возвращает текущую дату в виде строки.

  =""& СЕГОДНЯ()

Для возврата определенного типа данных также можно использовать следующие функции:

Преобразование реальных чисел в целые числа

• Функция ROUND

• Функция CEILING

• Функция FLOOR

  Преобразование реальных чисел, целых чисел или дат в строки

• Функция FIXED

• Функция FORMAT

  Преобразование строк в реальные числа или даты

• Функция VALUE

• Функция DATEVALUE

• Функция TIMEVALUE

Создание значения на основе условия

Вложенные условия IF можно использовать для тестирования значений и создания новых значений условно. В следующих разделах приведены простые примеры условной обработки и условных значений.

• ЕСЛИ

• Функция OR

• Функция сцепления

Проверка на наличие ошибок в формуле

В отличие от Excel, нельзя иметь допустимые значения в одной строке вычисляемого столбца и недопустимые значения в другой строке. То есть при возникновении ошибки в любой части столбца Power Pivot весь столбец помечается ошибкой, поэтому необходимо всегда исправлять ошибки формулы, которые приводят к недопустимым значениям.

Например, если создать формулу, которая делится на ноль, можно получить результат бесконечности или ошибку. Некоторые формулы также завершатся ошибкой, если функция обнаруживает пустое значение при ожидании числового значения. Во время разработки модели данных лучше разрешить появление ошибок, чтобы можно было щелкнуть сообщение и устранить проблему. Однако при публикации книг следует включить обработку ошибок, чтобы предотвратить сбой вычислений с непредвиденными значениями.

Чтобы избежать возврата ошибок в вычисляемом столбце, используйте сочетание логических и информационных функций для проверки на наличие ошибок и всегда возвращать допустимые значения. В следующих разделах приведены простые примеры того, как это сделать в DAX:

• Функция ISBLANK

• Функция IFERROR

Вычисление совокупных продаж

В следующих разделах приведены примеры вычисления сальдо закрытия и открытия. Примеры позволяют создавать балансы между различными интервалами, такими как дни, месяцы, кварталы или годы.

• CLOSINGBALANCEMONTH Function, CLOSINGBALANCEQUARTER Function, CLOSINGBALANCEYEAR Function

• ФУНКЦИЯ 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 значений.

• Функция РАНЕЕ.