Перейти к основному контенту
Office

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

В этой статье

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

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

Additional Resources

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

Создание настраиваемых вычислений для сводной таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Additional Resources

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

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

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

Создание настраиваемых вычислений для сводной таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= Дата (ПРАВСИМВ ([value1]; 4); LEFT ([value1]; 2); MID ([value1]; 2))

Взнос

Результат

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 анализе можно найти в разделе даты в Power Pivot.

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

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

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

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

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

В следующих разделах приведены примеры того, как извлекать пользовательские диапазоны дат, например первые 15 дней после начала рекламного заказа.

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

  • Функция PARALLELPERIOD

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

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

Чтобы отобразить только первые n количество элементов в столбце или сводной таблице, есть несколько вариантов:

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

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

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

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

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

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

Отображение наибольших или наименьших значений в сводной таблице

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

  2. Выберите фильтрыпо значению _гт_ Top 10.

  3. В диалоговом окне первые 10 _Лт_колумн фильтров выберите столбец для ранжирования и количество значений, как описано ниже.

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

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

    3. Укажите, как должны отображаться значения:

Имя

Описание

Элементы

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

Процент

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

Sum

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

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

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

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

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

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

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

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

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

×