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

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

  • Выполнение сложных вычислений

  • Работа с текстом и датами

  • Условные значения и проверка на наличие ошибок

  • Использование логики операций со временем

  • Ранжирование и сравнение значений

В этой статье

Приступая к работе

Образец данных

Дополнительные ресурсы

Сценарии: Выполнение сложных вычислений

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

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

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

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

Сценарии: Работа с текстом и датами

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

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

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

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

Сценарий: Условные значения и проверка на наличие ошибок

Создание значений по условию

Проверка формулы

Сценарии: Использование логики операций со временем

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

Сравнение значений за периоды времени

Вычисление значения в пользовательском диапазоне дат

Сценарии: Ранжирование и сравнение значений

Отображение в сводной таблице только десяти элементов

Динамическое упорядочение элементов по формуле

Приступая к работе

Образцы данных

Если вы новичок в формулах DAX, может потребоваться запустить, изучив примеры образцы данных для Power Pivot. Дополнительные сведения читайте в статье Получение образцов данных для DAX и модели данных.

Дополнительные ресурсы

Можно также посетите Центр ресурс Wiki DAX , где вы сможете найти все виды сведения о DAX, включая блоги, примеры, технические документы и видео, предоставляемых ИТ-специалистов начальных и Microsoft.

Сценарии. Выполнение сложных вычислений

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

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

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

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

В большинстве места, где принимает таблицу в качестве аргумента функции DAX можно обычно передать Отфильтрованные таблицы вместо этого с помощью функции ФИЛЬТРА вместо имени таблицы или указав выражения фильтра в качестве одного из аргументов функции. В следующих разделах примеры того, как создавать фильтры и как фильтры влияет на результаты формул. Фильтрация данных в формулах DAXДополнительные сведения, см.

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

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

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

  • Какую долю в общих годовых продажах составляют продажи данного продукта?

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

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

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

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

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

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

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

Сценарии. Работа с текстом и датами

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

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

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

Формирование даты из компонентов даты, извлеченных из текстовой даты

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

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

=Date(RIGHT([value1],4),Left([value1],2),MID([value1],2))

Значение1

Result (Результат)

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

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

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

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

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

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

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

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

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

    = "" & Сегодня()

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

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

Сценарии. Условные значения и проверка на наличие ошибок

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

Создание значений по условию

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

Проверка формулы

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

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

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

Сценарии. Использование логики операций со временем

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

Список всех логики операций со временем читайте в статье Функции логики операций со временем (DAX). Советы по эффективному использованию значения даты и времени в анализ Power Pivot просмотреть даты в PowerPivot.

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

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

Сравнение значений за периоды времени

Следующие разделы содержат примеры сравнения сумм за разные периоды времени: По умолчанию DAX поддерживает следующие периоды: месяцы, кварталы и годы.

Вычисление значения в пользовательском диапазоне дат

Просмотрите приведенные в следующих разделах примеры извлечения пользовательских диапазонов дат, например «первые 15 дней после начала рекламной акции».

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

  • Функция PARALLELPERIOD

    Примечание : Если нет необходимости задавать пользовательский диапазон дат, а используются стандартные единицы учета, например месяцы, кварталы или годы, то рекомендуется выполнять вычисления с помощью таких функций логики операций со временем, как TOTALQTD, TOTALMTD, TOTALQTD и т. д.

Сценарии. Ранжирование и сравнение значений

Существует несколько способов показать только первые несколько значений в столбце или сводной таблице:

  • Можно использовать функции Excel 2010 для создания фильтра максимальных значений. Определенное количество максимальных или минимальных значений можно также выбрать в сводной таблице. В первой части этого раздела показано, как отфильтровать сводную таблицу, чтобы показать только 10 элементов с максимальными значениями. Дополнительные сведения см. в документации по Excel.

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

Каждый из способов имеет свои преимущества и недостатки.

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

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

Отображение в сводной таблице только десяти элементов с максимальными значениями

Чтобы отобразить верхних или нижних значений в сводной таблице

  1. Внутри сводной таблицы щелкните стрелку вниз в заголовке Названия строк.

  2. Выберите Фильтры по значению > Первые 10.

  3. В диалоговом окне Фильтр 10 верхней < имя столбца > выберите столбец и число значений следующим образом:

    1. Выберите Наибольших, чтобы увидеть ячейки с максимальными значениями, или Наименьших, чтобы увидеть ячейки с минимальными значениями.

    2. Введите число верхних или нижних значений, которые нужно отобразить. Значение по умолчанию — 10.

    3. Выберите порядок отображения значений:

Имя

Описание

Элементов списка

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

Процент

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

Сумма

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

  1. Выберите столбец, содержащий значения, которые нужно ранжировать.

  2. Нажмите кнопку ОК.

Динамическое упорядочение элементов по формуле

Следующий раздел содержит пример использования DAX для создания рейтинга, хранимого в вычисляемом столбце. Поскольку формулы DAX вычисляются динамически, ранжирование всегда остается верным, даже если сами данные изменились. Кроме того, поскольку формула используется в вычисляемом столбце, можно использовать ранжирование в фильтре для среза и затем выбирать первые 5, 10 или даже 100 значений.

Примечания : 

  • Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

  • Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала. Вместе с другими участниками сообщества Майкрософт вы можете улучшить эту статью с помощью Community Translation Framework (CTF). Просто наведите указатель мыши на предложение в статье и выберите команду "УЛУЧШИТЬ ПЕРЕВОД" в мини-приложении CTF. Для получения дополнительных сведений о CTF щелкните здесь. Используя CTF, вы соглашаетесь с нашими условиями предоставления услуг.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×