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

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

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

• Рассчитать

• CALCULATETABLE

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

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

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

• Фильтр

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

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

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

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

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

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

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

• Все

• ALLEXCEPT

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

• ISBLANK

• ISERROR

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

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

• Функция EARLIER

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

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

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

• Функция С CONCATENATE

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

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

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

=ДАТА(ПРАВБ([Значение1];4);ЛЕВЫЙ([Значение1];2);MID([Значение1];2))

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

• Функция ДАТАVALUE

• Функция TIMEVALUE

• Функция ДАТА

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

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

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

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

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

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

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

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

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

  =(СЕГОДНЯ()-3)*1,0

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

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

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

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

• Функция ОКРУГЛ

• Функция ОКЧЕК

• Функция ОКЛ

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

• Функция ФИКСИРОВАННЫЙ

• Функция FORMAT

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

• Функция VALUE

• Функция ДАТАVALUE

• Функция TIMEVALUE

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

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

• Если

• Функция ИЛИ

• Функция CONCATENATE

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

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

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

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

• Функция ЕBLANK

• Функция ЕСЛИERROR

Расчет совокупных продаж

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

• Функция CLOSINGBALANCEMONTH, ФУНКЦИЯ CLOSINGBALANCEQUARTER, ФУНКЦИЯ CLOSINGBALANCEYEAR

• Функция OPENINGBALANCEMONTH, функция OPENINGBALANCEQUARTER, ФУНКЦИЯ OPENINGBALANCEYEAR

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

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

• Функция PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR

• Функция TOTALMTD, функция TOTALQTD, функция TOTALYTD

• Функция PARALLELPERIOD

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

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

• ФУНКЦИЯ DATESINPERIOD

• ФУНКЦИЯ DATESBETWEEN

• Функция DATEADD

• Функция FIRSTDATE

• Функция LASTDATE

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

• Функция PARALLELPERIOD

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

Показать только десять лучших элементов в pivotTable

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

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

• Функция EARLIER.