Сценарии DAX
В этом разделе содержатся ссылки на примеры того, как использовать формулы DAX в следующих ситуациях.
Выполнение сложных вычислений
Работа с текстом и датами
Условные значения и проверка на наличие ошибок
Использование логики операций со временем
Ранжирование и сравнение значений
Приступая к работе
Если вы еще не работали с формулами DAX, лучше начать с просмотра примеров в образце книги DAX. Дополнительные сведения о получении образца книги см. в разделе Получение образца данных для PowerPivot.
Дополнительные ресурсы
По следующим ссылкам можно найти видеоматериалы, дополнительные образцы и руководства, которые помогут освоить работу с DAX.
Сценарии: Выполнение сложных вычислений
Формулы DAX могут выполнять сложные вычисления, включая пользовательские агрегаты, фильтрацию и использование условных значений. В этом разделе приводятся примеры того, как начать работать с пользовательскими вычислениями.
Создание пользовательских вычислений для сводной таблицы
CALCULATE и CALCULATETABLE — это мощные и гибкие функции, полезные для определения мер. Эти функции позволяют изменять контекст, в котором будет выполняться вычисление. Можно также настроить тип агрегирования или выполняемой математической операции. Примеры см. в следующих разделах.
Применение фильтра к формуле
В большинстве случаев, когда формула DAX принимает таблицу в качестве аргумента, вместо этой таблицы можно передать отфильтрованную таблицу, указав функцию FILTER вместо имени таблицы или критерий фильтра в качестве одного из аргументов функции. В следующих разделах приводятся примеры создания фильтров и показано влияние фильтров на результаты вычисления формул. Дополнительные сведения см. в разделе Фильтрация данных в формулах.
Функция FILTER позволяет задать условия фильтра с помощью выражения, а другие функции специально предназначены для исключения пустых значений.
Выборочное удаление фильтров для создания динамической пропорции
Создание динамических фильтров в формулах позволяет легко ответить на следующие вопросы:
Какую долю в общих годовых продажах составляют продажи данного продукта?
Какова доля данного отдела в общей прибыли за все операционные годы по сравнению с другими отделами?
На формулы, используемые в сводной таблице, влияет контекст сводной таблицы, но контекст можно выборочно менять, добавляя или удаляя фильтры. Это показано в примере в разделе ALL. Чтобы определить отношение продаж конкретного торгового посредника к объемам продаж всех посредников, создайте меру, которая вычисляет частное от деления значения для текущего контекста на значение для контекста ALL.
В разделе ALLEXCEPT приводится пример выборочной очистки фильтров в формуле. В обоих примерах показано, как меняются результаты в зависимости от структуры сводной таблицы.
Другие примеры, показывающие, как вычислить отношения и доли, см. в следующих разделах:
Использование значения из внешнего цикла
Помимо использования в вычислениях значений из текущего контекста, DAX позволяет использовать значение из предыдущего цикла при создании набора связанных вычислений. В следующем разделе показано по шагам, как создать формулу, ссылающуюся на значение из внешнего цикла. Функция EARLIER поддерживает до двух уровней вложенных циклов.
Дополнительные сведения о контексте строк и связанных таблицах и об использовании этих понятий в формулах см. в разделе Контекст в формулах DAX.
В начало
Сценарии: Работа с текстом и датами
В этом разделе содержатся ссылки на разделы справки DAX, где содержатся примеры типичных сценариев работы с текстом, извлечения и формирования значений даты и времени, а также создания условных значений.
Создание ключевого столбца путем объединения
В сводных таблицах составные ключи не поддерживаются, и если в источнике данных есть составные ключи, может потребоваться объединение этих ключей в один столбец ключа. Следующий раздел содержит пример создания вычисляемого столбца, основанного на составном ключе.
Формирование даты из компонентов даты, извлеченных из текстовой даты
В сводных таблицах для работы с датами используется тип даты/времени SQL Server и, если внешняя дата содержит даты в другом формате, например в региональном формате данных, не распознаваемом подсистемой обработки данных сводных таблиц, или если в данных используются целочисленные суррогатные ключи, может потребоваться формула DAX, чтобы извлечь части даты и объединить их в допустимое представление даты/времени.
Например, если в данных есть столбец дат, которые были представлены в виде целого числа, но импортированы в форме текстовой строки, можно преобразовать эту строку в значение даты/времени с помощью следующей формулы.
=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))
Value1 |
Результат |
01032009 |
1/3/2009 |
12132008 |
12/13/2008 |
06252007 |
6/25/2007 |
Следующие разделы содержат дополнительные сведения о функциях, используемых для извлечения и составления дат.
Определение пользовательского формата даты или числа
Если данные содержат даты или числа, формат которых не соответствует ни одному из стандартных текстовых форматов Windows, для правильной обработки значений можно задать пользовательский формат. Эти форматы используются при преобразовании значений в строки и из строк. В следующих разделах содержится также подробный список стандартных форматов, которые можно использовать при работе с датами и числами.
Изменение типов данных с помощью формулы
В PowerPivot тип выводимых данных зависит от исходных столбцов и невозможно явно задать тип данных результата, так как оптимальный тип данных определяется программой PowerPivot. Однако можно повлиять на тип выходных данных, используя неявные преобразования типов данных в программе PowerPivot. Дополнительные сведения о преобразованиях типов см. в разделе Источники данных, поддерживаемые в книгах PowerPivot.
Чтобы преобразовать строку даты или числовую строку в число, умножьте их на 1,0. Например, следующая формула рассчитывает значение текущей даты минус 3 дня и выводит его в виде целого числа.
=(TODAY()-3)*1.0Чтобы преобразовать значение даты, числа или валюты в строку, объедините это значение с пустой строкой. Например, следующая формула возвращает текущую дату в виде строки.
=""& TODAY()
Следующие функции также могут использоваться для получения определенных типов данных.
Преобразование вещественных чисел в целые
Преобразование вещественных чисел, целых чисел и дат в строки
Преобразование строк в вещественные числа или даты
В начало
Сценарий: Условные значения и проверка на наличие ошибок
Как и в Excel, в DAX есть функции, позволяющие проверять значения данных и возвращать разные значения в зависимости от условия. Например, можно создать вычисляемый столбец, в котором торговые посредники отмечены как Приоритетные или Бюджетные в зависимости от объемов продаж за год. Функции, которые проверяют значения, также полезны при проверке диапазона или типа значений, чтобы непредвиденные ошибки в данных не нарушили ход вычислений.
Создание значений по условию
Для проверки значений и создания новых условных значений можно использовать вложенные условия IF. Следующие разделы содержат несколько простых примеров условной обработки и условных значений.
Проверка формулы
В отличие от Excel, нельзя иметь допустимые значения в одной строке вычисляемого столбца и недопустимые в другой. Если в любой части столбца PowerPivot есть ошибка, весь столбец помечается как содержащий ошибку, поэтому нужно всегда исправлять ошибки формул, которые приводят к недопустимым значениям.
Например, если создать формулу с делением на ноль, можно получить результат, равный бесконечности, или ошибку. Некоторые формулы не срабатывают, если формула ожидает число, а получает числовое значение. Во время разработки модели данных лучше всего включить вывод ошибок, чтобы в случае ошибки можно было щелкнуть на сообщении и устранить проблему. Однако в публикуемые книги следует встраивать логику обработки ошибок, чтобы непредвиденное значение не привело к ошибкам в вычислениях.
Чтобы предотвратить возвращение ошибок в вычисляемом столбце, можно использовать сочетание логических и информационных функций, которые будут проверять формулу на наличие ошибок и всегда возвращать допустимые значения. В следующих разделах приведено несколько простых примеров таких проверок в DAX:
В начало
Сценарии: Использование логики операций со временем
Функции логики операций со временем в языке DAX включают функции, предназначенные для извлечения из данных даты или диапазонов дат. Эти даты или диапазоны дат можно использовать для вычисления значений по одним и тем же периодам. К функциям логики операций со временем относятся также функции, которые работают со стандартными интервалами дат и позволяют сравнивать значения по месяцам, годам или кварталам. Можно также создать формулу, сравнивающую значения на первую и последнюю даты указанного периода.
Список всех функций логики операций со временем см. в разделе Функции логики операций со временем (DAX). Советы по эффективному использованию дат и времени при анализе данных в PowerPivot см. в разделе Даты в PowerPivot.
Вычисление совокупных продаж
В следующих разделах приведены примеры расчета начального и итогового сальдо. В этих примерах вычисляются текущие остатки по различным интервалам (дням, месяцам, кварталам или годам).
Сравнение значений за периоды времени
Следующие разделы содержат примеры сравнения сумм за разные периоды времени: По умолчанию DAX поддерживает следующие периоды: месяцы, кварталы и годы.
Вычисление значения в пользовательском диапазоне дат
Просмотрите приведенные в следующих разделах примеры извлечения пользовательских диапазонов дат, например «первые 15 дней после начала рекламной акции».
Применяя функции логики операций со временем для извлечения пользовательского набора дат, можно использовать такой набор дат в качестве входных данных для функции, производящей вычисления, чтобы создать пользовательские статистические данные за временные периоды. См. пример в следующем разделе:
.gif "Примечание") Примечание |
|---|
Если нет необходимости задавать пользовательский диапазон дат и используются стандартные единицы учета, например месяцы, кварталы или годы, рекомендуется выполнять вычисления с помощью таких функций логики операций со временем, как TOTALQTD, TOTALMTD, TOTALQTD и т. д. |
В начало
Сценарии: Ранжирование и сравнение значений
Существует несколько способов показать только первые несколько значений в столбце или сводной таблице:
Можно использовать функции Excel 2010 для создания фильтра максимальных значений. Определенное количество максимальных или минимальных значений можно также выбрать в сводной таблице. В первой части этого раздела показано, как отфильтровать сводную таблицу, чтобы показать только 10 элементов с максимальными значениями. Дополнительные сведения см. в документации по Excel.
Можно создать формулу, которая динамически ранжирует значения, а затем отфильтровать таблицу по ранжирующим значениям или использовать ранжирующее значение как фильтр среза при разбивке большой таблицы на части. Во второй части раздела показано, как создать такую формулу и использовать это ранжирование в фильтре для среза.
Каждый из способов имеет свои преимущества и недостатки.
Фильтром максимальных значений Excel удобно пользоваться, но он предназначен только для вывода. Если данные в базовой сводной таблице изменятся, необходимо вручную обновить сводную таблицу, чтобы увидеть изменения. Для динамической работы с рейтингами можно при помощи DAX создать формулу, сравнивающую значения с другими значениями в столбце.
Формула DAX является более мощным средством. Кроме того, добавив значение ранжирования в фильтр для среза, можно изменить количество отображаемых максимальных значений одним щелчком на фильтре для среза. Однако этот вариант требует большего количества вычислений и может не подойти для таблиц с большим количеством строк.
Отображение в сводной таблице только десяти элементов с максимальными значениями
Отображение верхних или нижних значений в сводной таблице |
ИмяОписание
ЭлементыВыберите этот параметр, чтобы после фильтрования сводной таблицы отобразился только перечень наивысших или наименьших по значению элементов.
ПроцентВыберите этот параметр, чтобы отфильтровать сводную таблицу для отображения только тех элементов, которые выросли до указанного процентного значения.
СуммаВыберите этот параметр, чтобы после фильтрования сводной таблицы отобразилась сумма значений наибольших или наименьших элементов.
|
Динамическое упорядочение элементов по формуле
Следующий раздел содержит пример использования DAX для создания рейтинга, хранимого в вычисляемом столбце. Поскольку формулы DAX вычисляются динамически, ранжирование всегда остается верным, даже если сами данные изменились. Кроме того, поскольку формула используется в вычисляемом столбце, можно использовать ранжирование в фильтре для среза и затем выбирать первые 5, 10 или даже 100 значений.
В начало
См. также