В этом разделе приведены ссылки на примеры использования формул DAX в описанных ниже сценариях.
- Выполнение сложных вычислений
- Работа с текстом и датами
- Условные значения и проверка на наличие ошибок
- Использование аналитики по времени
- Ранжирование и сравнение значений
В этом разделе...
Начало работы
Посетите вики-страницу Центра ресурсов DAX , где можно найти разнообразные сведения о DAX, включая блоги, примеры, технические документы и видео, предоставленные ведущими специалистами отрасли и корпорацией Майкрософт.
Сценарии: выполнение сложных вычислений
Формулы DAX позволяют выполнять сложные вычисления, включающие настраиваемые агрегаты, фильтрацию и использование условных значений. В этом разделе приводятся примеры начала работы с пользовательскими вычислениями.
Создание настраиваемых вычислений для сводной таблицы
CALCULATE и CALCULATETABLE — это мощные и гибкие функции, которые используются для определения вычисляемых полей. Эти функции позволяют изменить контекст, в котором будет выполняться вычисление. Вы также можете настроить тип агрегирования или математических операций, которые необходимо выполнить. Примеры см. в следующих разделах.
Применение фильтра к формуле
В большинстве мест, где функция DAX принимает таблицу в качестве аргумента, вместо имени таблицы обычно можно передать отфильтрованную таблицу. Вместо имени таблицы или укажите выражение фильтра в качестве одного из аргументов функции. В следующих разделах приведены примеры создания фильтров и влияния фильтров на результаты формул. Дополнительные сведения см. в статье Фильтрация данных в формулах DAX.
Функция ФИЛЬТР позволяет задавать условия фильтра с помощью выражения, а другие функции предназначены специально для фильтрации пустых значений.
Выборочно удаляйте фильтры для создания динамического соотношения
Создавая динамические фильтры в формулах, вы можете легко ответить на следующие вопросы:
- Каков был вклад продаж текущего продукта в общий объем продаж за год?
- Насколько это подразделение внесло свой вклад в общую прибыль за все годы работы по сравнению с другими подразделениями?
На формулы, используемые в сводной таблице, может влиять контекст сводной таблицы, но вы можете выборочно изменять контекст, добавляя или удаляя фильтры. Пример из раздела ВСЕ показывает, как это сделать. Чтобы найти отношение продаж конкретного торгового посредника к продажам всех торговых посредников, создайте меру, которая вычисляет значение для текущего контекста, деленное на значение контекста ALL.
В разделе ALLEXCEPT приведен пример выборочной очистки фильтров для формулы. В обоих примерах объясняется, как изменяются результаты в зависимости от структуры сводной таблицы.
Другие примеры вычисления отношений и процентов см. в следующих разделах:
Использование значения из внешнего цикла
Помимо использования в вычислениях значений из текущего контекста, DAX может использовать значение из предыдущего цикла при создании набора связанных вычислений. В следующем разделе приводится пошаговое руководство по созданию формулы, ссылающейся на значение из внешнего цикла. Функция РАНЕЕ поддерживает до двух уровней вложенных циклов.
Дополнительные сведения о контексте строки и связанных таблицах, а также об использовании этого понятия в формулах см. в статье Контекст в формулах DAX.
Сценарии: работа с текстом и датами
В этом разделе приведены ссылки на справочные разделы по DAX, в которых приведены примеры распространенных сценариев работы с текстом, извлечения и составления значений даты и времени или создания значений на основе условий.
Создание ключевого столбца путем объединения
В Power Pivot нельзя использовать составные ключи. Поэтому, если в источнике данных есть составные ключи, может потребоваться объединить их в один ключевой столбец. В следующем разделе приведен пример создания вычисляемого столбца на основе составного ключа.
Compose даты на основе частей даты, извлеченных из текстовой даты
Для работы с датами в Power Pivot используется тип данных даты и времени SQL Server. Поэтому, если внешние данные содержат даты в другом формате (например, если даты записаны в региональном формате, который не распознается модулем данных Power Pivot), или если в данных используются целочисленные суррогатные ключи, вам может потребоваться использовать формулу DAX для извлечения частей дат и их последующего объединения в допустимую дату. представление времени.
Например, если у вас есть столбец с датами, которые были представлены как целое число, а затем импортированы как текстовая строка, вы можете преобразовать строку в значение даты и времени с помощью следующей формулы:
=ДАТА(ПРАВСИМВ([Значение1];4);ЛЕВСИМВ([Значение1];2);ПСТР([Значение1];2))
| Значение1 | Result (Результат) |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
В следующих разделах представлены дополнительные сведения о функциях, используемых для извлечения и создания дат.
Определение пользовательского формата даты или числового формата
Если данные содержат даты или числа, которые не представлены в стандартном текстовом формате Windows, для правильной обработки значений можно задать специальный формат. Эти форматы используются при преобразовании значений в строки или из строк. В следующих разделах также представлен подробный список готовых форматов, которые доступны для работы с датами и числами.
- Предопределенные числовые форматы для функции FORMAT
- Пользовательские числовые форматы для функции FORMAT
- Предопределенные форматы даты и времени для функции FORMAT
- Пользовательские форматы даты и времени для функции FORMAT
Изменение типов данных с помощью формулы
В Power Pivot тип данных выходных данных определяется столбцами-источниками, и невозможно явно указать тип данных результата, так как оптимальный тип данных определяет Power Pivot. Однако для управления типом выходных данных можно использовать неявные преобразования типов данных, выполняемые Power Pivot.
- Чтобы преобразовать дату или строку числа в число, умножьте его на 1,0. Например, в приведенной ниже формуле вычисляется текущая дата минус 3 дня и выводится соответствующее целое число.
=(СЕГОДНЯ()-3)*1.0 - Чтобы преобразовать дату, число или денежное значение в строку, соедините значение с помощью пустой строки. Например, следующая формула возвращает текущую дату в виде строки.
=""& СЕГОДНЯ()
Кроме того, для возврата определенного типа данных можно использовать перечисленные ниже функции.
Преобразование действительных чисел в целые
- Функция ОКРУГЛ
- ОКРВВЕРХ
-
FLOOR (функция FLOOR)
Преобразование действительных чисел, целых чисел или дат в строки - ФИКСИРОВАННЫЙ
-
FORMAT Function
Преобразование строк в действительные числа или даты - ЗНАЧЕН
- Функция ДАТАЗНАЧ
- Функция ВРЕМЗНАЧ
Сценарий: условные значения и проверка на наличие ошибок
Как и в Excel, в DAX есть функции, которые позволяют проверять значения данных и возвращать различные значения на основе условий. Например, можно создать вычисляемый столбец, который помечает торговых посредников как предпочтительных или стоимостных , в зависимости от объема продаж за год. Функции, проверяющие значения, также полезны для проверки диапазона или типа значений, чтобы предотвратить неожиданные ошибки данных, мешающие вычислениям.
Создание значения на основе условия
Можно использовать вложенные условия ЕСЛИ для проверки значений и условного создания новых значений. В следующих разделах приведены несколько простых примеров условной обработки и условных значений.
Проверка на наличие ошибок в формуле
В отличие от Excel, в одной строке вычисляемого столбца допустимые значения недопустимы, а в другой строке нельзя. То есть, если есть ошибка в какой-либо части столбца Power Pivot, весь столбец помечается как ошибка, поэтому необходимо всегда исправлять ошибки в формуле, которые приводят к недопустимым значениям.
Например, если вы создадите формулу с делением на ноль, вы можете получить результат бесконечности или ошибку. Некоторые формулы также не будут работать, если функция встречает пустое значение, когда ожидается числовое значение. Во время разработки модели данных лучше разрешить появление ошибок, чтобы можно было щелкнуть сообщение и устранить проблему. Однако при публикации книг следует учитывать обработку ошибок, чтобы неожиданные значения не приводили к сбою вычислений.
Чтобы избежать возврата ошибок в вычисляемом столбце, используйте сочетание логических и информационных функций для проверки ошибок и всегда возвращайте допустимые значения. В следующих разделах представлено несколько простых примеров того, как это сделать в DAX.
Сценарии: использование операции по времени
Функции DAX включают функции, помогающие получать даты и диапазоны дат из ваших данных. Затем по этим датам или диапазонам дат можно вычислить значения за аналогичные периоды. Функции анализа времени также включают функции, работающие со стандартными интервалами дат, что позволяет сравнивать значения по месяцам, годам или кварталам. Можно также создать формулу, которая сравнивает значения для первой и последней даты указанного периода.
Список всех функций аналитики времени см. в разделе Функции аналитики времени (DAX). Советы по эффективному использованию дат и времени в анализе Power Pivot см. в разделе Даты в Power Pivot.
Расчет совокупных продаж
В следующих разделах приведены примеры расчета итогового и начального балансов. В этих примерах можно создавать текущие балансы с разными интервалами, такими как дни, месяцы, кварталы или годы.
- Функция CLOSINGBALANCEMONTH, функция CLOSINGBALANCEQUARTER, функция CLOSINGBALANCEYEAR
- Функция OPENINGBALANCEMONTH, функция OPENINGBALANCEQUARTER, функция OPENINGBALANCEYEAR
Сравнение значений с течением времени
В следующих разделах приведены примеры сравнения сумм за разные периоды времени. По умолчанию DAX поддерживает периоды времени, равные месяцы, кварталы и годы.
- PREVIOUSMONTH, PREVIOUSQUARTER,PREVIOUSYEAR
- Функция TOTALMTD,функция TOTALQTD, функция TOTALYTD
- Функция PARALLELPERIOD
Вычисление значения на основе настраиваемого диапазона дат
В следующих разделах приведены примеры получения настраиваемых диапазонов дат, таких как первые 15 дней после начала рекламной акции.
Если функции логики во времени используются для получения настраиваемого набора дат, этот набор дат можно использовать в качестве входных данных для функции, выполняющей вычисления для создания настраиваемых агрегатных значений по периодам времени. Пример того, как это сделать, см. в следующем разделе:
-
Примечание
Если не нужно указывать настраиваемый диапазон дат, но вы работаете со стандартными единицами учета, такими как месяцы, кварталы или годы, рекомендуется выполнять вычисления с помощью разработанных для этой цели функций логики операций, таких как TOTALQTD, TOTALMTD, TOTALQTD и т. д.
Сценарии: ранжирование и сравнение значений
Чтобы отобразить только первое n элементов в столбце или сводной таблице, есть несколько вариантов:
- С помощью функций Excel можно создать фильтр "Топ". Вы также можете выбрать несколько максимальных или наименьших значений в сводной таблице. В первой части этого раздела описывается, как фильтровать 10 самых важных элементов сводной таблицы. Дополнительные сведения см. в документации Excel.
- Вы можете создать формулу с динамическим ранжированием значений, а затем фильтровать по значениям ранжирования или использовать значение ранжирования в качестве среза. Во второй части этого раздела описывается, как создать эту формулу и затем использовать ее ранжирование в срезе.
У каждого метода есть свои преимущества и недостатки.
- Фильтр "Верхние" в Excel прост в использовании, но он предназначен исключительно для отображения. Если данные, лежащие в основе сводной таблицы, изменяются, необходимо вручную обновить сводную таблицу, чтобы увидеть изменения. Если вам нужна динамическая работа с ранжированием, вы можете использовать DAX для создания формулы, которая сравнивает значения с другими значениями в столбце.
- Формула DAX обладает более широкими возможностями; более того, добавив значение ранжирования в срез, вы можете просто щелкнуть его, чтобы изменить количество отображаемых верхних значений. Однако вычисления требуют больших вычислительных ресурсов, и этот метод может не подходить для таблиц с большим количеством строк.
Отображение только первых десяти элементов сводной таблицы
Отображение максимального или минимального значения в сводной таблице
|
|---|
Динамическое упорядочивание элементов с помощью формулы
В следующем разделе приведен пример использования DAX для создания ранжирования, хранящегося в вычисляемом столбце. Поскольку формулы DAX вычисляются динамически, вы всегда можете быть уверены в правильности ранжирования, даже если исходные данные изменились. Кроме того, поскольку формула используется в вычисляемом столбце, вы можете использовать ранжирование в срезе, а затем выбрать значения "Первые 5", "Первые 10" или "Первые 100".