Excel DAX — Обзор

DAX обозначает D ata A- nalysis E x . DAX — это язык формул, представляющий собой набор функций, операторов и констант, которые можно использовать в формуле или выражении для вычисления и возврата одного или нескольких значений. DAX — это язык формул, связанный с моделью данных Excel Power Pivot.

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

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

Важность DAX

Основой DAX является модель данных, которая является базой данных Power Pivot в Excel. Модель данных состоит из таблиц, между которыми можно определить отношения, чтобы объединить данные из разных источников. Соединения данных с моделью данных могут обновляться по мере изменения исходных данных. Модель данных использует механизм аналитики в памяти Power Pivot xVelocity (VertiPaq), который делает операции с данными максимально быстрыми в дополнение к размещению нескольких тысяч строк данных. Для получения дополнительной информации о модели данных, обратитесь к учебнику — Power Pivot.

DAX в сочетании с моделью данных обеспечивает несколько мощных функций в Excel — Power Pivot, Power PivotTables, Power PivotCharts и Power View. Вы можете использовать DAX для решения ряда основных задач расчета и анализа данных.

DAX также полезен в Power BI для создания нового файла Power BI Desktop и импорта в него некоторых данных. Кроме того, формулы DAX предоставляют такие возможности, как анализ процента роста по категориям продуктов и для разных диапазонов дат, расчет годового роста по сравнению с рыночными тенденциями и многими другими.

Изучение того, как создавать эффективные формулы DAX, поможет вам получить максимальную отдачу от ваших данных. Получив необходимую информацию, вы можете начать решать реальные бизнес-проблемы, которые влияют на вашу прибыль. Это сила Power BI, и DAX поможет вам в этом.

Предварительные условия для этого урока

Этот учебник является расширением учебника Excel Power Pivot, в котором вы узнали о функции Power Pivot, модели данных, отношениях, Power PivotTable, Power Pivot Charts и т. Д. Было бы неплохо освежить в памяти этот учебник, прежде чем углубиться в изучение. в DAX, поскольку это руководство больше относится к языку DAX, в котором вы пишете формулы для анализа данных в модели данных и сообщаете об этих результатах.

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

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

Расчетные столбцы

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

Расчетные поля / меры

Вы не можете изменить значения в таблицах в модели данных путем редактирования. Однако в таблицу можно добавить вычисляемые поля, которые можно использовать в сводных таблицах Power. Вычисляемые поля определяются путем присвоения имени и определения формулы DAX. Подробнее см. Главу « Расчетные поля» .

Вычисленные поля были названы показателями в версиях Excel до Excel 2013. Они переименованы обратно в показатели в Excel 2016. В этом руководстве мы будем называть их вычисляемыми полями. Но обратите внимание, что термины — вычисляемые поля и меры — являются синонимами и относятся к одному и тому же во всех аспектах.

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

DAX Формулы

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

Формулы DAX содержат таблицы, столбцы, вычисляемые столбцы, вычисляемые поля, операторы DAX и функции DAX. Обратитесь к главе — DAX Formulas, чтобы узнать подробности.

Синтаксис DAX

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

Вы узнаете следующее в главе — Синтаксис DAX —

• Требования к именованию DAX для таблиц, столбцов
• Операторы DAX
• DAX специальные значения
• Типы данных DAX
• DAX неявные преобразования типов данных

Операторы DAX

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

• Арифметические операторы DAX
• Операторы сравнения DAX
• Оператор объединения текстов DAX
• DAX логические операторы

Порядок приоритета оператора DAX также определен и отличается от порядка приоритета оператора Excel. Обратитесь к главе « Операторы DAX» .

Стандартные параметры DAX

Синтаксис функции DAX имеет определенные требования к параметрам. Это связано с тем, что аргументами функции DAX могут быть таблицы или столбцы, вычисляемые поля или другие функции DAX. См. Главу « Стандартные параметры DAX» .

Функции DAX

В Excel 2013 имеется 246 функций DAX, которые можно использовать в формулах DAX. Вы узнаете об этих функциях на уровне категорий в главе « Функции DAX» . Тем не менее, для получения подробной информации о синтаксисе каждой функции DAX, параметрах, использовании и возвращаемых значениях, вы должны обратиться к нашему руководству по — Функции DAX . Названия разделов, используемые для описания каждой функции DAX, приведены в главе « Общие сведения о функциях DAX» .

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

Специальные функции DAX

DAX имеет некоторые функции, которые делают DAX мощным. Эти функции DAX подразделяются на категории — функции анализа времени DAX и функции фильтра DAX и требуют особого упоминания. Вы узнаете о функциях DAX Time Intelligence в главе « Общие сведения о DAX Time Intelligence» . Вы узнаете об использовании функций фильтра DAX в главе « Функции фильтра DAX» .

DAX Оценочный контекст

Результаты формулы DAX могут варьироваться в зависимости от контекста, который используется для оценки. DAX имеет два типа оценочного контекста — контекст строки и контекст фильтра. См. Главу « Контекст оценки DAX» .

DAX Формулы

DAX — это язык формул, и вы должны максимально использовать его при написании формул DAX. Обратитесь к главе «Формулы DAX», чтобы узнать о синтаксисе формул и о том, как их легко и правильно создавать.

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

Данные в модели данных ожидаются и время от времени могут изменяться. Это связано с тем, что данные используются для операций анализа данных, которые требуют актуальных данных в любой момент времени. Чтобы понять различные способы обновления данных, обратитесь к главе « Обновление данных в модели данных» .

Вы поймете различные типы пересчета формул DAX в главе « Пересчет формул DAX» .

Пересчеты формул DAX должны учитывать зависимости данных и следовать определенному порядку. В противном случае вы можете получить ошибки или ошибочные результаты. Подробнее см. Главу « Устранение неполадок при пересчете формулы DAX» .

Вы получите представление о некоторых типичных ошибках формул DAX и узнаете, как их исправить, в главе « Ошибки формул DAX» .

DAX Сценарии

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

Обратитесь к следующим главам, чтобы получить подробную информацию об этом.

• DAX Сценарии
• Сценарии — Выполнение сложных расчетов
• Сценарии — Работа с текстом и датами
• Сценарии — условные значения и тестирование на ошибки
• Сценарии — Использование Time Intelligence
• Сценарии — ранжирование и сравнение ценностей

Excel DAX — вычисляемые столбцы

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

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

Понимание вычисляемых столбцов

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

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

= YEAR ([Date]) 

YEAR — это функция DAX, а Date — существующий столбец в таблице. Как видно, имя таблицы заключено в квадратные скобки. Подробнее об этом вы узнаете в главе « Синтаксис DAX» .

Когда вы добавляете столбец в таблицу с этой формулой DAX, значения столбцов вычисляются сразу после создания формулы. Будет создан новый столбец с заголовком CalculatedColumn1, заполненным значениями Year.

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

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

Рассмотрим модель данных с результатами Олимпиады, как показано на следующем снимке экрана.

• Нажмите Просмотр данных.
• Нажмите вкладку Результаты.

Вы будете просматривать таблицу результатов.

Как видно на скриншоте выше, самый правый столбец имеет заголовок — Добавить столбец.

• Перейдите на вкладку «Дизайн» на ленте.
• Нажмите Добавить в группе Столбцы.

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

• Введите = ГОД ([Дата]) в строке формул.

Как видно на скриншоте выше, выделен самый правый столбец с заголовком — Добавить столбец.

• Нажмите Ввод.

Для выполнения расчетов потребуется некоторое время (несколько секунд). Пожалуйста, подождите.

Новый вычисляемый столбец будет вставлен слева от крайнего правого столбца Добавить.

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

Переименование вычисляемого столбца

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

• Дважды щелкните заголовок столбца. Имя столбца будет подсвечено.

• Выберите имя столбца.
• Введите год (новое имя).

Как видно на скриншоте выше, имя вычисляемого столбца изменилось.

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

Просто убедитесь, что новое имя не конфликтует с существующим именем в таблице.

Проверка типа данных вычисляемого столбца

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

• Перейдите на вкладку «Главная» на ленте.
• Нажмите Тип данных.

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

Ошибки в вычисляемых столбцах

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

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

• Формула содержит циклическую или самозависимую зависимость.

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

Формула содержит циклическую или самозависимую зависимость.

Проблемы с производительностью

Как видно ранее на примере результатов Олимпиады, таблица «Результаты» содержит около 35000 строк данных. Следовательно, когда вы создали столбец с формулой DAX, он рассчитал все 35000+ значений в столбце одновременно, для чего потребовалось немного времени. Модель данных и таблицы предназначены для обработки миллионов строк данных. Следовательно, это может повлиять на производительность, если в формуле DAX слишком много ссылок. Вы можете избежать проблем с производительностью, сделав следующее:

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

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

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

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

Excel DAX — вычисляемые поля / показатели

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

Понимание вычисляемых полей

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

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

Ниже приводится разница между вычисляемым полем и вычисляемым столбцом.

• Вычисляемое поле может использоваться только в области VALUES сводной таблицы.

• Вычисляемый столбец с рассчитанными результатами можно использовать также в областях ROWS, COLUMNS и FILTERS.

Вычисляемое поле может использоваться только в области VALUES сводной таблицы.

Вычисляемый столбец с рассчитанными результатами можно использовать также в областях ROWS, COLUMNS и FILTERS.

Сохранение рассчитанного поля

Рассчитанное поле будет сохранено вместе с исходной таблицей в модели данных. Он отображается в списке полей Power PivotTable или Power PivotChart как поле в таблице.

Использование вычисляемого поля

Чтобы использовать вычисляемое поле, вы должны выбрать его из списка Поля сводной таблицы. Вычисленное поле будет добавлено в область ЗНАЧЕНИЯ, и будет вычислена формула, используемая для вычисляемого поля. Результат создается для каждой комбинации полей строки и столбца.

Расчетное поле — пример

Рассмотрим следующую модель данных для данных Олимпийских игр —

Как видно на снимке экрана выше, в таблице «Результаты» есть поле «Медаль», в котором содержатся значения — золото, серебро или бронза для каждой строки, содержащей комбинацию «Спорт — Событие — Страна — Дата». Предположим, вы хотите подсчет медалей для каждой страны, затем вы можете создать вычисляемое поле Medal Count по следующей формуле DAX —

Medal Count := COUNTA([Medal])

Создание вычисляемого поля в таблице

Чтобы создать вычисляемое поле Medal Count в таблице Results, сделайте следующее —

• Щелкните по ячейке в области расчета под столбцом «Медаль» в таблице «Результаты». Ячейка будет выделена.

• Введите Medal Count: = COUNTA ([Medal]) в строке формул.

Щелкните по ячейке в области расчета под столбцом «Медаль» в таблице «Результаты». Ячейка будет выделена.

Введите Medal Count: = COUNTA ([Medal]) в строке формул.

Нажмите Ввод.

Как видно на скриншоте выше, вычисленное поле появляется в выбранной ячейке, показывая значение 34,094. Это число — общее количество строк в таблице результатов. Следовательно, это не имеет большого смысла на первый взгляд. Как обсуждалось ранее, реальное использование вычисляемого поля можно увидеть, только добавив его в Power PivotTable или Power PivotChart.

Использование вычисленного поля в сводной таблице Power

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

• Нажмите на сводную таблицу на ленте в окне Power Pivot.
• Нажмите на сводную таблицу в раскрывающемся списке.

Откроется диалоговое окно «Создать сводную таблицу».

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

Будет создана пустая сводная таблица.

• Щелкните таблицу «Результаты» в списке «Поля сводной таблицы».
• Нажмите на поля — Страна и Количество медалей.

Как вы можете заметить, счетчик медалей добавляется в область ЗНАЧЕНИЙ, а страна — в область строк. Сводная таблица создается со значениями поля Страна, появляющимися в строках. И для каждой строки рассчитывается и отображается значение количества медалей. Таким образом, вычисляемое поле оценивает используемую формулу DAX и отображает значения.

• Добавьте поле Спорт из таблицы результатов в область ROWS.

Как вы можете видеть на скриншоте выше, количество медалей рассчитывается для каждой страны — в зависимости от вида спорта и промежуточного итога для самой страны.

Вот как DAX дополняет функции Power.

Типы вычисляемых полей

Существует два типа вычисляемых полей — неявные и явные.

• Неявное вычисляемое поле создается в области списка Power PivotTable Fields.

• Явное вычисляемое поле создается либо в таблице в окне Power Pivot, либо из ленты PowerPivot в окне Excel.

Неявное вычисляемое поле создается в области списка Power PivotTable Fields.

Явное вычисляемое поле создается либо в таблице в окне Power Pivot, либо из ленты PowerPivot в окне Excel.

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

Неявное вычисляемое поле может быть создано двумя способами, оба на панели Power PivotTable Fields.

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

Вы можете создать поле Count of Medal из поля Medal в списке полей сводной таблицы следующим образом:

• Отмените выбор поля «Количество медалей».
• Щелкните правой кнопкой мыши на поле Медаль.
• Нажмите Add to Values ​​в раскрывающемся списке.

Количество медалей появляется в области значений. Столбец Количество медалей будет добавлен в сводную таблицу.

Создание неявного вычисляемого поля в области VALUES

Вы можете создать неявное рассчитанное поле -% родительской строки в области «Значения», чтобы выразить количество медалей по каждому виду спорта, выигранному страной, в процентах от общего числа медалей, выигранных этой страной.

• Нажмите стрелку вниз в поле Count of Medal в области VALUES.
• Нажмите Настройки поля значения в раскрывающемся списке.

Откроется диалоговое окно «Настройки поля значений».

• Введите% Медали в поле Пользовательское имя.
• Перейдите на вкладку «Показать значения как».
• Щелкните поле под Показать значения как.
• Нажмите% от общего количества родительских строк.

• Нажмите кнопку «Числовой формат».

Откроется диалоговое окно «Формат ячеек».

• Нажмите Процент.
• Введите 0 в десятичных разрядах.
• Нажмите ОК.
• Нажмите кнопку ОК в диалоговом окне «Параметры поля значений».
• Выберите Не показывать промежуточные итоги.

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

Недостатки неявного вычисляемого поля

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

• Они изменчивы . Это означает, что если вы отмените выбор поля, которое вы использовали для вычисляемого поля, оно будет удалено. Если вы хотите отобразить его снова, вы должны снова создать его.

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

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

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

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

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

Вы можете создать явное вычисляемое поле двумя способами:

• В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе — Создание вычисляемого поля в таблице.

• Из ленты PowerPivot в таблице Excel. Этот способ создания явного вычисляемого поля вы узнаете в следующем разделе.

В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе — Создание вычисляемого поля в таблице.

Из ленты PowerPivot в таблице Excel. Этот способ создания явного вычисляемого поля вы узнаете в следующем разделе.

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

Чтобы создать явное вычисляемое поле из ленты PowerPivot, выполните следующие действия:

• Откройте вкладку POWERPIVOT на ленте в своей книге.
• Нажмите кнопку «Рассчитанные поля» в области «Расчеты».
• Нажмите Новое вычисляемое поле в раскрывающемся списке.

Откроется диалоговое окно «Вычисленное поле».

• Заполните необходимую информацию, как показано на следующем снимке экрана.

• Нажмите кнопку Проверить формулу.
• Нажмите ОК, только если в формуле нет ошибок.

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

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

Excel DAX — редактирование вычисляемого поля

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

Нахождение вычисляемых полей

Чтобы найти вычисляемые поля в модели данных, выполните следующие действия:

• Перейдите на вкладку «Дополнительно» на ленте в окне Power Pivot.
• Нажмите Показать неявные вычисленные поля.

• Нажмите на представление схемы.

Как вы можете видеть на скриншоте выше, Показать неявные вычисляемые поля выделено на ленте. Если он не выделен, щелкните по нему еще раз.

Вы также можете заметить, что есть 4 флажка — Столбцы, Вычисляемые поля, Иерархии и KPI. По умолчанию выбраны все 4.

• Снимите флажки — Столбцы, Иерархии и KPI.

Это оставит только флажок «Рассчитанные поля» отмеченным.

Как видно на скриншоте выше, поля отображаются только в таблице «Результаты». Две другие таблицы пустые. Это показывает, что только таблица «Результаты» имеет рассчитанные поля. Вы также можете заметить, что неявные вычисляемые поля имеют значок отображается, тогда как явное вычисляемое поле — Medal Count не имеет этого значка.

Просмотр вычисленных полей в таблице

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

• Нажмите на рассчитанное поле.
• Щелкните правой кнопкой мыши и выберите Перейти в раскрывающемся списке.

Таблица появится в представлении данных.

Как видно на скриншоте выше, вычисленные поля появляются в области расчета таблицы.

Изменение вычисляемого поля в таблице

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

• Щелкните вычисляемое поле в таблице в представлении данных модели данных.
• Выберите формулу в строке формул — справа от: =.

Формула будет выделена.

• Введите новую формулу.
• Нажмите Ввод.

Вы узнаете больше о формулах DAX в последующих главах.

Переименование вычисляемого поля в модели данных

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

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

• Щелкните вычисляемое поле в таблице в представлении данных модели данных.
• Выберите вычисляемое имя поля в строке формул — слева от: =.

Вычисленное имя поля будет подсвечено.

• Введите новое имя для вычисляемого поля.
• Нажмите Ввод.

Вы узнаете больше о синтаксисе DAX в следующих главах.

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

• Щелкните правой кнопкой мыши по вычисленному имени поля в таблице в представлении схемы.
• Нажмите Переименовать в раскрывающемся списке.

Имя попадет в режим редактирования. Введите новое имя для вычисляемого поля.

Просмотр вычисляемых полей в окне Excel

Вы можете просмотреть рассчитанные поля в окне Excel следующим образом —

• Нажмите вкладку POWERPIVOT на ленте.
• Нажмите Вычисляемые поля в группе Расчеты.
• Нажмите Управление вычисляемыми полями в раскрывающемся списке.

Откроется диалоговое окно «Управление вычисляемыми полями». Имена явных вычисляемых полей в модели данных появятся в диалоговом окне.

Изменение вычисляемого поля в Управлении вычисляемыми полями

Вы можете изменить вычисляемое поле в диалоговом окне «Управление вычисляемыми полями».

• Нажмите на счет медали.
• Нажмите кнопку Изменить.

Откроется диалоговое окно «Вычисленное поле».

• Выберите формулу справа от = в поле формулы.

• Введите новую формулу.
• Нажмите ОК.
• Нажмите кнопку «Закрыть» в диалоговом окне «Управление вычисляемыми полями».

Переименование вычисляемого поля в Управление вычисляемыми полями

Вы можете переименовать вычисляемое поле в диалоговом окне «Управление вычисляемыми полями».

• Нажмите на счет медали.
• Нажмите кнопку Изменить.

Откроется диалоговое окно «Вычисленное поле».

• Выберите имя в поле имени вычисляемого поля.

• Введите новое имя для вычисляемого поля.
• Нажмите ОК.
• Нажмите кнопку «Закрыть» в диалоговом окне «Управление вычисляемыми полями».

Перемещение вычисляемого поля в модели данных

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

• Щелкните правой кнопкой мыши вычисляемое поле.
• Нажмите Cut.
• Переместите указатель в другое место в области вычисления той же таблицы.
• Нажмите Вставить.

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

Excel DAX — удаление вычисляемого поля

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

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

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

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

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

• Создать неявное вычисляемое поле проще, чем создать явное вычисляемое поле. Следовательно, требуется большая осторожность перед удалением явного вычисляемого поля.

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

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

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

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

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

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

Удаление явного вычисляемого поля в модели данных

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

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

• Найдите вычисляемое поле в области вычислений в представлении данных.
• Щелкните правой кнопкой мыши вычисляемое поле.
• Нажмите Удалить в раскрывающемся списке.

Появится сообщение для подтверждения удаления.

Нажмите Удалить из модели. Явное вычисляемое поле будет удалено.

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

• Найдите вычисляемое поле в таблице данных в представлении схемы.
• Щелкните правой кнопкой мыши по вычисленному имени поля.
• Нажмите Удалить в раскрывающемся списке.

Появится сообщение для подтверждения удаления.

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

Удаление явного вычисляемого поля в окне Excel

Вы можете удалить явное вычисляемое поле из окна Excel следующим образом:

• Нажмите вкладку POWERPIVOT на ленте.
• Нажмите Вычисляемое поле в группе Расчеты.
• Нажмите Управление вычисляемыми полями в раскрывающемся списке.

Откроется диалоговое окно «Управление вычисляемыми полями».

• Нажмите явное имя вычисляемого поля.
• Нажмите кнопку Удалить.

Появится подтверждающее сообщение для удаления.

• Нажмите Да. Информационное сообщение об изменении модели данных появляется вверху.
• Нажмите кнопку Закрыть в диалоговом окне.

Явное вычисляемое поле будет удалено и не будет отображаться в списке полей сводной таблицы / сводной диаграммы в рабочей книге.

Удаление неявного вычисляемого поля

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

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

• Найдите вычисляемое поле в области вычислений в представлении данных.
• Щелкните правой кнопкой мыши вычисляемое поле.
• Нажмите Удалить в раскрывающемся списке.

Появится сообщение для подтверждения удаления.

• Нажмите Удалить из модели. Неявное вычисляемое поле будет удалено.

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

• Найдите вычисляемое поле в таблице данных в представлении схемы.
• Щелкните правой кнопкой мыши по вычисленному имени поля.
• Нажмите Удалить в раскрывающемся списке.

Появится сообщение для подтверждения удаления.

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

Excel DAX — синтаксис

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

Синтаксис DAX можно отнести к категории —

• Синтаксис для формул DAX
• Требования к именованию DAX
• DAX Особые ценности
• Функции DAX
• Операторы DAX
• Типы данных DAX

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

Различия между формулами Excel и формулами DAX

Формулы DAX аналогичны формулам Excel, и их можно ввести в строке формул. Однако между ними есть некоторые существенные различия.

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

Формулы Excel набираются на панели формул в окне Excel.

Формулы DAX вводятся в строке формул в окне Power Pivot.

В формулах Excel вы можете ссылаться на отдельные ячейки или массивы данных.

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

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

Формулы Excel поддерживают определенные типы данных.

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

Excel не поддерживает какие-либо неявные преобразования данных.

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

Excel DAX — Операторы

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

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

Типы операторов DAX

DAX поддерживает следующие типы операторов —

• Арифметические операторы DAX
• Операторы сравнения DAX
• Оператор объединения текстов DAX
• DAX логические операторы

Порядок приоритета оператора DAX

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

Приоритет оператора DAX по умолчанию приведен в следующей таблице.

Порядок приоритета	Оператор (ы)	операция
1	^	Возведение
2	—	Знак
3	* а также /	Умножение и деление
4	!	НЕ
5	+ и —	Сложение и вычитание
6	&	конкатенация
7	=, <,>, <=,> = и <>	Равно, меньше, больше, меньше или равно, больше или равно и не равно

Синтаксис выражения DAX

Сначала необходимо понять синтаксис выражения DAX и то, как выполняется оценка выражения с операндами и операторами.

• Все выражения всегда начинаются со знака равенства (=). Знак равенства означает, что последующие символы составляют выражение.

• Справа от знака равенства у вас будут операнды, связанные операторами DAX. Например, = 5 + 4> 5.

  = 5 * 6 — 3.

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

• Если операторы DAX имеют одинаковое значение приоритета, они оцениваются слева направо. Например, = 5 * 6/10. И * и / имеют одинаковый порядок прецедента. Следовательно, выражение оценивается как 30/10 = 3.

• Если операторы DAX в выражении имеют разные значения приоритета, то они оцениваются в порядке приоритета слева направо.

  • = 5 + 4> 7. Приоритет по умолчанию + первый и> следующий. Следовательно, выражение вычисляется слева направо. — Сначала вычисляется 5 + 4, в результате получается 9, а затем 9> 5, что дает ИСТИНА.

  • = 5 * 6 — 3. Приоритет по умолчанию * первый и — следующий. Следовательно, выражение вычисляется слева направо. — сначала вычисляется 5 * 6, в результате получается 30, а затем вычисляется 30 — 3, что приводит к 27.

  • = 2 * 5 — 6 * 3. Приоритет по умолчанию — * сначала, * затем, а затем -. Следовательно, выражение оценивается как 10 — 18, а затем как -8. Обратите внимание, что это не 10 — 6, в результате 4, а затем 4 * 3, что 12.

Все выражения всегда начинаются со знака равенства (=). Знак равенства означает, что последующие символы составляют выражение.

Справа от знака равенства у вас будут операнды, связанные операторами DAX. Например, = 5 + 4> 5.

= 5 * 6 — 3.

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

Если операторы DAX имеют одинаковое значение приоритета, они оцениваются слева направо. Например, = 5 * 6/10. И * и / имеют одинаковый порядок прецедента. Следовательно, выражение оценивается как 30/10 = 3.

Если операторы DAX в выражении имеют разные значения приоритета, то они оцениваются в порядке приоритета слева направо.

= 5 + 4> 7. Приоритет по умолчанию + первый и> следующий. Следовательно, выражение вычисляется слева направо. — Сначала вычисляется 5 + 4, в результате получается 9, а затем 9> 5, что дает ИСТИНА.

= 5 * 6 — 3. Приоритет по умолчанию * первый и — следующий. Следовательно, выражение вычисляется слева направо. — сначала вычисляется 5 * 6, в результате получается 30, а затем вычисляется 30 — 3, что приводит к 27.

= 2 * 5 — 6 * 3. Приоритет по умолчанию — * сначала, * затем, а затем -. Следовательно, выражение оценивается как 10 — 18, а затем как -8. Обратите внимание, что это не 10 — 6, в результате 4, а затем 4 * 3, что 12.

Использование скобок для управления порядком расчета DAX

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

Например, = 5 * 6 — 3 оценивается как 27 с порядком приоритета оператора DAX по умолчанию. Если вы используете круглые скобки для группировки операндов и операторов как = 5 * (6 — 3), то сначала вычисляется 6 — 3, в результате получается 3, а затем 5 * 3, что приводит к 15.

= 2 * 5 — 6 * 3 — до -8 с порядком приоритета оператора DAX по умолчанию. Если вы используете круглые скобки для группировки операндов и операторов как = 2 * (5-6) * 3, то сначала вычисляется 5-6, что приводит к -1, а затем вычисляется 2 * (-1) * 3, что приводит к -6 ,

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

Различия между Excel и DAX

Хотя DAX имеет сходство с формулами Excel, между ними есть определенные существенные различия.

• DAX является более мощным, чем Excel, из-за его основного механизма вычисления резидентной памяти.

• DAX поддерживает больше типов данных, чем Excel.

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

DAX является более мощным, чем Excel, из-за его основного механизма вычисления резидентной памяти.

DAX поддерживает больше типов данных, чем Excel.

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

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

• Тип данных приведение
• Типы данных

Разница в приведении типов данных

В DAX, когда у вас есть оператор value2 value1, value2, два операнда value1 и value2 должны быть одного типа данных. Если типы данных отличаются, DAX сначала неявно преобразует их в общий тип данных. Подробнее см. Главу «Синтаксис DAX».

Например, вам нужно сравнить два операнда разных типов данных, скажем, число, полученное из формулы, например = [Amount] * 0,08 и целое число. Первое число может быть десятичным числом со многими десятичными разрядами, тогда как второе число является целым числом. Затем DAX обрабатывает это следующим образом:

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

• Далее DAX сравнит два действительных числа.

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

Далее DAX сравнит два действительных числа.

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

Разница в типах данных

Порядок приоритетов операторов в DAX и Excel одинаков. Однако процент оператора (%) и диапазоны данных, которые поддерживает Excel, не поддерживаются DAX. Более того, DAX поддерживает таблицу как тип данных, что не имеет место в Excel.

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

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

Вы узнаете больше обо всем этом в следующих главах.

Excel DAX — стандартные параметры

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

Стандартные имена параметров

Ниже приведены имена стандартных параметров DAX —

Sr.No.	Имя параметра и описание
1	выражение Любое выражение DAX, которое возвращает одно скалярное значение, где выражение должно оцениваться несколько раз (для каждой строки / контекста).
2	значение Любое выражение DAX, которое возвращает единственное скалярное значение, где выражение должно оцениваться ровно один раз перед всеми другими операциями.
3	Таблица Любое выражение DAX, которое возвращает таблицу данных.
4	TABLENAME Имя существующей таблицы с использованием стандартного синтаксиса DAX. Это не может быть выражением.
5	ColumnName Имя существующего столбца с использованием стандартного синтаксиса DAX, как правило, полностью уточнено. Это не может быть выражением.
6	название Строковая константа, которая будет использоваться для предоставления имени нового объекта.
7	порядок Перечисление, используемое для определения порядка сортировки.
8	связи Перечисление, используемое для определения обработки значений связывания.
9	тип Перечисление, используемое для определения типа данных для PathItem и PathItemReverse.

выражение

Любое выражение DAX, которое возвращает одно скалярное значение, где выражение должно оцениваться несколько раз (для каждой строки / контекста).

значение

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

Таблица

Любое выражение DAX, которое возвращает таблицу данных.

TABLENAME

Имя существующей таблицы с использованием стандартного синтаксиса DAX. Это не может быть выражением.

ColumnName

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

название

Строковая константа, которая будет использоваться для предоставления имени нового объекта.

порядок

Перечисление, используемое для определения порядка сортировки.

связи

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

тип

Перечисление, используемое для определения типа данных для PathItem и PathItemReverse.

Префикс имен параметров

Вы можете указать имя параметра с префиксом —

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

• Префикс должен быть таким, чтобы избежать неоднозначного чтения параметра.

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

Префикс должен быть таким, чтобы избежать неоднозначного чтения параметра.

Например,

• Result_ColumnName — указывает на существующий столбец, используемый для получения значений результата в функции DAX LOOKUPVALUE ().

• Search_ColumnName — указывает на существующий столбец, используемый для поиска значения в функции DAX LOOKUPVALUE ().

Result_ColumnName — указывает на существующий столбец, используемый для получения значений результата в функции DAX LOOKUPVALUE ().

Search_ColumnName — указывает на существующий столбец, используемый для поиска значения в функции DAX LOOKUPVALUE ().

Использование только префикса в качестве параметра

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

Например, рассмотрим дату (год_значения, месяц_значения, день_значения). Вы можете опустить параметр имя — значение, которое повторяется трижды, и записать его как DATE (год, месяц, день). Как вы можете заметить, используя только префиксы, функция более читабельна.

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

Например, рассмотрим Year_columnName. Имя параметра — ColumnName, а префикс — Год. И то, и другое требуется, чтобы пользователь понял, что параметр требует ссылки на существующий столбец лет.

Excel DAX — Функции

Большинство функций DAX имеют те же имена и функциональность, что и функции Excel. Однако функции DAX были изменены, чтобы использовать типы данных DAX и работать с таблицами и столбцами.

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

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

Что такое функция DAX?

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

Функции Excel и DAX

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

Сходства между функциями Excel и DAX

• Многие функции DAX имеют то же имя и такое же общее поведение, что и функции Excel.

• DAX имеет функции поиска, которые аналогичны функциям поиска в массиве и векторе в Excel.

Многие функции DAX имеют то же имя и такое же общее поведение, что и функции Excel.

DAX имеет функции поиска, которые аналогичны функциям поиска в массиве и векторе в Excel.

Различия между функциями Excel и DAX

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

• Вы не можете использовать функции DAX в формуле Excel или использовать формулы / функции Excel в DAX без необходимых изменений.

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

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

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

• Функции поиска DAX требуют, чтобы между таблицами была установлена ​​связь.

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

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

Вы не можете использовать функции DAX в формуле Excel или использовать формулы / функции Excel в DAX без необходимых изменений.

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

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

В Excel нет функций, возвращающих таблицу, но некоторые функции могут работать с массивами. Многие из функций DAX могут легко ссылаться на полные таблицы и столбцы, чтобы выполнять вычисления и возвращать таблицу или столбец значений. Эта способность DAX добавляет мощность в Power Pivot, Power View и Power BI, где используется DAX.

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

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

Чтобы узнать о типах данных DAX и приведении типов данных, обратитесь к главе — Справочник по синтаксису DAX.

Типы функций DAX

DAX поддерживает следующие типы функций.

• Табличные функции DAX
  • Функции фильтра DAX
  • Функции агрегации DAX
  • DAX Time Intelligence Функции
• Функции даты и времени DAX
• Информационные функции DAX
• DAX логические функции
• DAX Math и Trig Функции
• DAX Другие функции
• DAX родительские и дочерние функции
• Статистические функции DAX
• Текстовые функции DAX

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

Функции анализа времени DAX и функции фильтра DAX являются мощными и требуют особого упоминания. Обратитесь к главам — Общие сведения о функциях DAX Time Intelligence и DAX Filter.

Табличные функции DAX

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

• Функции агрегации DAX
• Функции фильтра DAX
• Интеллектуальные функции DAX Time

Понимание табличных функций DAX поможет вам эффективно писать формулы DAX.

Функции агрегации DAX

Функции агрегации DAX агрегируют любое выражение по строкам таблицы и полезны в вычислениях.

Ниже приведены некоторые функции агрегации DAX.

Ниже приведен список функций агрегирования DAX.

• ADDCOLUMNS (<таблица>, <имя>, <выражение>, [<имя>, <выражение>]…)

• СРЕДНИЙ (<столбец>)

• AVERAGEA (<колонка>)

• AVERAGEX (<таблица>, <выражение>)

• COUNT (<столбец>)

• COUNTA (<столбец>)

• COUNTAX (<таблица>, <выражение>)

• COUNTBLANK (<столбец>)

• COUNTROWS (<таблица>)

• COUNTX (<таблица>, <выражение>)

• CROSSJOIN (<table1>, <table2>, [<table3>]…)

• DISTINCTCOUNT (<столбец>)

• GENERATE (<table1>, <table2>)

• GENERATEALL (<table1>, <table2>)

• MAX (<столбец>)

• МАКСА (<столбец>)

• MAXX (<таблица>, <выражение>)

• MIN (<столбец>)

• MINA (<столбец>)

• MINX (<таблица>, <выражение>)

• ПРОДУКТ (<колонка>)

• PRODUCTX (<таблица>, <выражение>)

• ROW (<имя>, <выражение>, [<имя>, <выражение>]…)

• SELECTCOLUMNS (<таблица>, <имя>, <скалярное выражение>,

• [<name>, <scalar_expression>]…)

• СУММА (<столбец>)

• SUMMARIZE (<таблица>, <groupBy_columnName>, [<groupBy_columnName>]…, [<имя>, <выражение>]…)

• SUMX (<таблица>, <выражение>)

• TOPN (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

ADDCOLUMNS (<таблица>, <имя>, <выражение>, [<имя>, <выражение>]…)

СРЕДНИЙ (<столбец>)

AVERAGEA (<колонка>)

AVERAGEX (<таблица>, <выражение>)

COUNT (<столбец>)

COUNTA (<столбец>)

COUNTAX (<таблица>, <выражение>)

COUNTBLANK (<столбец>)

COUNTROWS (<таблица>)

COUNTX (<таблица>, <выражение>)

CROSSJOIN (<table1>, <table2>, [<table3>]…)

DISTINCTCOUNT (<столбец>)

GENERATE (<table1>, <table2>)

GENERATEALL (<table1>, <table2>)

MAX (<столбец>)

МАКСА (<столбец>)

MAXX (<таблица>, <выражение>)

MIN (<столбец>)

MINA (<столбец>)

MINX (<таблица>, <выражение>)

ПРОДУКТ (<колонка>)

PRODUCTX (<таблица>, <выражение>)

ROW (<имя>, <выражение>, [<имя>, <выражение>]…)

SELECTCOLUMNS (<таблица>, <имя>, <скалярное выражение>,

[<name>, <scalar_expression>]…)

СУММА (<столбец>)

SUMMARIZE (<таблица>, <groupBy_columnName>, [<groupBy_columnName>]…, [<имя>, <выражение>]…)

SUMX (<таблица>, <выражение>)

TOPN (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

Функции фильтра DAX

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

Ниже приведены некоторые функции фильтра DAX —

Ниже приведен список функций фильтра DAX.

• ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>]…, <таблица>, <groupingColumn>, [<groupingColumn>]… [filterTable]…)

• ВСЕ ({<таблица> | <столбец>, [<столбец>], [<столбец>]…})

• ALLEXCEPT (<таблица>, <столбец>, [<столбец>]…)

• ALLNOBLANKROW (<таблица> | <столбец>)

• ALLSELECTED ([<tableName> | <columnName>])

• CALCULATE (<выражение>, <фильтр1>, <фильтр2>…)

• CALCULATETABLE (<выражение>, <фильтр1>, <фильтр2>…)

• CROSSFILTER (<columnName1>, <columnName2>, <direction>)

• DISTINCT (<столбец>)

• РАНЬШЕ (<колонка>, <число>)

• EARLIEST (<столбец>)

• ФИЛЬТР (<таблица>, <фильтр>)

• ФИЛЬТРЫ (<ColumnName>)

• HASONEFILTER (<ColumnName>)

• HASONEVALUE (<ColumnName>)

• ISCROSSFILTERED (<columnName>)

• ISFILTERED (<columnName>)

• KEEPFILTERS (<выражение>)

• СВЯЗАННЫЕ С (<столбец>)

• RELATEDTABLE (<TABLENAME>)

• SUBSTITUTEWITHINDEX (<таблица>, <indexColumnName>, <indexColumnsTable>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…])

• USERELATIONSHIP (<имя_столбца1>, <имя_столбца2>)

• VALUES (<TableNameOrColumnName>)

ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>]…, <таблица>, <groupingColumn>, [<groupingColumn>]… [filterTable]…)

ВСЕ ({<таблица> | <столбец>, [<столбец>], [<столбец>]…})

ALLEXCEPT (<таблица>, <столбец>, [<столбец>]…)

ALLNOBLANKROW (<таблица> | <столбец>)

ALLSELECTED ([<tableName> | <columnName>])

CALCULATE (<выражение>, <фильтр1>, <фильтр2>…)

CALCULATETABLE (<выражение>, <фильтр1>, <фильтр2>…)

CROSSFILTER (<columnName1>, <columnName2>, <direction>)

DISTINCT (<столбец>)

РАНЬШЕ (<колонка>, <число>)

EARLIEST (<столбец>)

ФИЛЬТР (<таблица>, <фильтр>)

ФИЛЬТРЫ (<ColumnName>)

HASONEFILTER (<ColumnName>)

HASONEVALUE (<ColumnName>)

ISCROSSFILTERED (<columnName>)

ISFILTERED (<columnName>)

KEEPFILTERS (<выражение>)

СВЯЗАННЫЕ С (<столбец>)

RELATEDTABLE (<TABLENAME>)

SUBSTITUTEWITHINDEX (<таблица>, <indexColumnName>, <indexColumnsTable>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…])

USERELATIONSHIP (<имя_столбца1>, <имя_столбца2>)

VALUES (<TableNameOrColumnName>)

DAX Time Intelligence Функции

Функции DAX Time Intelligence возвращают таблицу дат или используют таблицу дат для расчета агрегации. Эти функции DAX помогают создавать расчеты, которые отвечают потребностям анализа бизнес-аналитики, позволяя вам манипулировать данными с использованием периодов времени, включая дни, месяцы, кварталы и годы.

Ниже приведены некоторые функции DAX Time Intelligence —

Ниже приведен список функций DAX Time Intelligence.

• CLOSINGBALANCEMONTH (<выражение>, <даты> [, <фильтр>])

• CLOSINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>])

• CLOSINGBALANCEYEAR (<выражение>, <даты>, [<фильтр>], [<год_дата_]>))

• DATEADD (<даты>, <number_of_intervals>, <интервал>)

• DATESBETWEEN (<date>, <start_date>, <end_date>)

• DATESINPERIOD (<date>, <start_date>, <number_of_intervals>, <interval>)

• DATESMTD (<даты>)

• DATESQTD (<даты>)

• DATESYTD (<date>, [<year_end_date>])

• ENDOFMONTH (<даты>)

• ENDOFQUARTER (<даты>)

• ENDOFYEAR (<даты>, [<year_end_date>])

• FIRSTDATE (<даты>)

• FIRSTNONBLANK (<столбец>, <выражение>)

• ПОСЛЕДНЯЯ (<даты>)

• LASTNONBLANK (<столбец>, <выражение>)

• СЛЕДУЮЩИЙ (<даты>)

• СЛЕДУЮЩИЙ (<даты>)

• СЛЕДУЮЩИЙ (<даты>)

• NEXTYEAR (<date>, [<year_end_date>])

• OPENINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>])

• OPENINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>])

• OPENINGBALANCEYEAR (<выражение>, <даты>, [<фильтр>], [<год_дата_]>))

• PARALLELPERIOD (<даты>, <number_of_intervals>, <интервал>)

• PREVIOUSDAY (<даты>)

• PREVIOUSMONTH (<даты>)

• PREVIOUSQUARTER (<даты>)

• PREVIOUSYEAR (<date>, [<year_end_date>])

• SAMEPERIODLASTYEAR (<даты>)

• STARTOFMONTH (<даты>)

• STARTOFQUARTER (<даты>)

• STARTOFYEAR (<даты>)

• TOTALMTD (<выражение>, <даты>, [<фильтр>])

• TOTALQTD (<выражение>, <даты>, [<фильтр>])

• TOTALYTD (<выражение>, <даты>, [<фильтр>], [<год_энд_дата>])

CLOSINGBALANCEMONTH (<выражение>, <даты> [, <фильтр>])

CLOSINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>])

CLOSINGBALANCEYEAR (<выражение>, <даты>, [<фильтр>], [<год_дата_]>))

DATEADD (<даты>, <number_of_intervals>, <интервал>)

DATESBETWEEN (<date>, <start_date>, <end_date>)

DATESINPERIOD (<date>, <start_date>, <number_of_intervals>, <interval>)

DATESMTD (<даты>)

DATESQTD (<даты>)

DATESYTD (<date>, [<year_end_date>])

ENDOFMONTH (<даты>)

ENDOFQUARTER (<даты>)

ENDOFYEAR (<даты>, [<year_end_date>])

FIRSTDATE (<даты>)

FIRSTNONBLANK (<столбец>, <выражение>)

ПОСЛЕДНЯЯ (<даты>)

LASTNONBLANK (<столбец>, <выражение>)

СЛЕДУЮЩИЙ (<даты>)

СЛЕДУЮЩИЙ (<даты>)

СЛЕДУЮЩИЙ (<даты>)

NEXTYEAR (<date>, [<year_end_date>])

OPENINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>])

OPENINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>])

OPENINGBALANCEYEAR (<выражение>, <даты>, [<фильтр>], [<год_дата_]>))

PARALLELPERIOD (<даты>, <number_of_intervals>, <интервал>)

PREVIOUSDAY (<даты>)

PREVIOUSMONTH (<даты>)

PREVIOUSQUARTER (<даты>)

PREVIOUSYEAR (<date>, [<year_end_date>])

SAMEPERIODLASTYEAR (<даты>)

STARTOFMONTH (<даты>)

STARTOFQUARTER (<даты>)

STARTOFYEAR (<даты>)

TOTALMTD (<выражение>, <даты>, [<фильтр>])

TOTALQTD (<выражение>, <даты>, [<фильтр>])

TOTALYTD (<выражение>, <даты>, [<фильтр>], [<год_энд_дата>])

Функции даты и времени DAX

Функции даты и времени DAX аналогичны функциям даты и времени в Excel. Однако функции DAX Date и Time основаны на типе данных datetime DAX.

Ниже приведены функции даты и времени DAX.

Ниже приведен список функций DAX Date и Time.

• ДАТА (<год>, <месяц>, <день>)
• ДАТАЗНАЧ (дата_как_текст)
• ДЕНЬ (<дата>)
• EDATE (<start_date>, <months>)
• EOMONTH (<start_date>, <months>)
• ЧАС (<DateTime>)
• МИНУТЫ (<DateTime>)
• МЕСЯЦ (<DateTime>)
• СЕЙЧАС()
• ВТОРОЙ (<время>)
• ВРЕМЯ (час, минута, секунда)
• ВРЕМЗНАЧ (TIME_TEXT)
• СЕГОДНЯ()
• WEEKDAY (<date>, <return_type>)
• WEEKNUM (<дата>, <возвращаемый_тип>)
• YEAR (<дата>)
• YEARFRAC (<начальная_дата>, <конечная_дата>, <базовый>)

Информационные функции DAX

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

Ниже приведены некоторые функции DAX Information.

Ниже приведен список информационных функций DAX.

• СОДЕРЖИТ (<table>, <columnName>, <value>, [<columnName>, <value>]…)

• CustomData ()

• ЕПУСТО (<значение>)

• ЕОШИБКА (<значение>)

• ISEVEN (номер)

• ISLOGICAL (<значение>)

• ISNONTEXT (<значение>)

• ЕЧИСЛО (<значение>)

• ISONORAFTER (<scalar_expression>, <scalar_expression>, [sort_order], [<scalar_expression>, <scalar_expression>, [sort_order]]…)

• ISTEXT (<значение>)

• LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…)

• ИМЯ_ПОЛЬЗОВАТЕЛЯ ()

СОДЕРЖИТ (<table>, <columnName>, <value>, [<columnName>, <value>]…)

CustomData ()

ЕПУСТО (<значение>)

ЕОШИБКА (<значение>)

ISEVEN (номер)

ISLOGICAL (<значение>)

ISNONTEXT (<значение>)

ЕЧИСЛО (<значение>)

ISONORAFTER (<scalar_expression>, <scalar_expression>, [sort_order], [<scalar_expression>, <scalar_expression>, [sort_order]]…)

ISTEXT (<значение>)

LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…)

ИМЯ_ПОЛЬЗОВАТЕЛЯ ()

DAX логические функции

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

Ниже приведены DAX логические функции —

Ниже приведен список функций DAX Logical.

• И (<logical1>, <логическое_значение2>)
• ЛОЖНЫЙ()
• IF (логический_тест>, <value_if_true>, value_if_false)
• IFERROR (значение, значение_if_error)
• NOT (<логическое>)
• ИЛИ (<logical1>, <логическое_значение2>)
• SWITCH (<выражение>, <значение>, <результат>, [<значение>, <результат>]…, [<еще>])
• ПРАВДА()

DAX Math и Trig Функции

Математические и тригонометрические функции DAX очень похожи на математические и тригонометрические функции Excel.

Ниже приведены некоторые функции DAX Math и Trig —

Ниже приведен список функций DAX Math и Trig.

• АБС (<число>)
• ACOS (номер)
• ACOSH (номер)
• ASIN (число)
• ASINH (номер)
• ATAN (номер)
• ATANH (номер)
• ПОТОЛОК (<число>, <значимость>)
• КОМБИНАТ (число, число, выбор)
• КОМБИНА (номер, номер_выбран)
• COS (число)
• COSH (номер)
• ВАЛЮТЫ (<значение>)
• ГРАДУСЫ (угол)
• РАЗДЕЛИТЬ (<числитель>, <знаменатель>, [<альтернативный результат>])
• Четное число)
• EXP (<число>)
• ФАКТ (<число>)
• ЭТАЖ (<число>, <значимость>)
• GCD (номер1, [номер2], …)
• INT (<номер>)
• ISO.CEILING (<число>, [<значимость>])
• LCM (номер1, [номер2], …)
• LN (<число>)
• LOG (<число>, <база>)
• Log10 (<число>)
• INT (<номер>)
• MROUND (<число>, <несколько>)
• Нечетное число)
• ЧИСЛО ПИ()
• МОЩНОСТЬ (<число>, <сила>)
• ИЗДЕЛИЯ (<столбец>)
• PRODUCTX (<таблица>, <выражение>)
• QUOTIENT (<числитель>, <знаменатель>)
• РАДИАНЫ (угол)
• RAND ()
• СЛУЧМЕЖДУ (<снизу>, <верх>)
• ROUND (<число>, <num_digits>)
• ROUNDDOWN (<число>, <num_digits>)
• ROUNDUP (<число>, <num_digits>)
• SIN (число)
• SINH (номер)
• ЗНАК (<число>)
• SQRT (<число>)
• СУММА (<столбец>)
• SUMX (<таблица>, <выражение>)
• TAN (число)
• TANH (номер)
• TRUNC (<номер>, <число_разрядов>)

DAX Другие функции

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

Ниже приведены некоторые другие функции DAX —

Ниже приведен список других функций DAX.

• ИСКЛЮЧИТЬ (<table_expression1>, <table_expression2>

• GROUPBY (<таблица>, [<groupBy_columnName1>], [<имя>, <выражение>]…)

• INTERSECT (<table_expression1>, <table_expression2>)

• IsEmpty (<table_expression>)

• NATURALINNERJOIN (<leftJoinTable>, <rightJoinTable>)

• NATURALLEFTOUTERJOIN (<leftJoinTable>, <rightJoinTable>)

• SUMMARIZECOLUMNS (<groupBy_columnName>, [<groupBy_columnName>]…, [<filterTable>]…, [<name>, <expression>]……)

• UNION (<table_expression1>, <table_expression2>, [<table_expression>]…)

• VAR <имя> = <выражение>

ИСКЛЮЧИТЬ (<table_expression1>, <table_expression2>

GROUPBY (<таблица>, [<groupBy_columnName1>], [<имя>, <выражение>]…)

INTERSECT (<table_expression1>, <table_expression2>)

IsEmpty (<table_expression>)

NATURALINNERJOIN (<leftJoinTable>, <rightJoinTable>)

NATURALLEFTOUTERJOIN (<leftJoinTable>, <rightJoinTable>)

SUMMARIZECOLUMNS (<groupBy_columnName>, [<groupBy_columnName>]…, [<filterTable>]…, [<name>, <expression>]……)

UNION (<table_expression1>, <table_expression2>, [<table_expression>]…)

VAR <имя> = <выражение>

DAX родительские и дочерние функции

Родительские и дочерние функции DAX полезны для управления данными, которые представлены в иерархии «родитель / потомок» в модели данных.

Ниже приведены некоторые родительские и дочерние функции DAX.

Ниже приведен список функций DAX Parent и Child.

• PATH (<ID_columnName>, <parent_columnName>)
• PATHCONTAINS (<путь>, <элемент>)
• PATHITEM (<путь>, <позиция>, [<тип>])
• PATHITEMREVERSE (<путь>, <позиция>, [<тип>])
• Длина пути (<путь>)

Статистические функции DAX

Статистические функции DAX очень похожи на статистические функции Excel.

Ниже приведены некоторые статистические функции DAX —

Ниже приведен список статистических функций DAX.

• BETA.DIST (x, альфа, бета, накопительное, [A], [B])

• BETA.INV (вероятность, альфа, бета, [A], [B])

• CHISQ.INV (вероятность, град_свободы)

• CHISQ.INV.RT (вероятность, градус свободы)

• CONFIDENCE.NORM (альфа, стандарт_дев, размер)

• CONFIDENCE.T (альфа, стандарт_дев, размер)

• DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2 …, {{Value1, Value2 …}, {ValueN, ValueN + 1 …} …})

• EXPON.DIST (x, лямбда, накопительный)

• GEOMEAN (<столбец>)

• GEOMEANX (<таблица>, <выражение>)

• Медиана (<столбец>)

• MEDIANX (<таблица>, <выражение>)

• PERCENTILE.EXC (<столбец>, <k>)

• PERCENTILE.INC (<столбец>, <k>)

• PERCENTILEX.EXC (<таблица>, <выражение>, k)

• PERCENTILEX.EXC (<таблица>, <выражение>, k)

• POISSON.DIST (x, среднее, кумулятивное)

• RANK.EQ (<value>, <columnName> [, <order>])

• RANKX (<таблица>, <выражение> [, <значение> [, <порядок> [, <связи>]]])

• SAMPLE (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

• STDEV.P (<ColumnName>)

• STDEV.S (<ColumnName>)

• STDEVX.P (<таблица>, <выражение>)

• STDEVX.S (<таблица>, <выражение>)

• SQRTPI (номер)

• VAR.P (<ColumnName>)

• VAR.S (<ColumnName>)

• VARX.P (<таблица>, <выражение>)

• VARX.S (<таблица>, <выражение>)

• XIRR (<таблица>, <значения>, <даты>, [предположение])

• XNPV (<таблица>, <значения>, <даты>, <ставка>)

BETA.DIST (x, альфа, бета, накопительное, [A], [B])

BETA.INV (вероятность, альфа, бета, [A], [B])

CHISQ.INV (вероятность, град_свободы)

CHISQ.INV.RT (вероятность, градус свободы)

CONFIDENCE.NORM (альфа, стандарт_дев, размер)

CONFIDENCE.T (альфа, стандарт_дев, размер)

DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2 …, {{Value1, Value2 …}, {ValueN, ValueN + 1 …} …})

EXPON.DIST (x, лямбда, накопительный)

GEOMEAN (<столбец>)

GEOMEANX (<таблица>, <выражение>)

Медиана (<столбец>)

MEDIANX (<таблица>, <выражение>)

PERCENTILE.EXC (<столбец>, <k>)

PERCENTILE.INC (<столбец>, <k>)

PERCENTILEX.EXC (<таблица>, <выражение>, k)

PERCENTILEX.EXC (<таблица>, <выражение>, k)

POISSON.DIST (x, среднее, кумулятивное)

RANK.EQ (<value>, <columnName> [, <order>])

RANKX (<таблица>, <выражение> [, <значение> [, <порядок> [, <связи>]]])

SAMPLE (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

STDEV.P (<ColumnName>)

STDEV.S (<ColumnName>)

STDEVX.P (<таблица>, <выражение>)

STDEVX.S (<таблица>, <выражение>)

SQRTPI (номер)

VAR.P (<ColumnName>)

VAR.S (<ColumnName>)

VARX.P (<таблица>, <выражение>)

VARX.S (<таблица>, <выражение>)

XIRR (<таблица>, <значения>, <даты>, [предположение])

XNPV (<таблица>, <значения>, <даты>, <ставка>)

Текстовые функции DAX

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

Ниже приведены некоторые функции DAX Text —

Ниже приведен список функций DAX Text.

• BLANK ()
• CODE (текст)
• CONCATENATE (<text1>, <text2>)
• CONCATENATEX (<таблица>, <выражение>, [разделитель])
• EXACT (<text1>, <text2>)
• НАЙТИ (<find_text>, <inside_text>, [<start_num>], [<NotFoundValue>])
• ИСПРАВЛЕНО (<число>, <десятичные числа>, <no_commas>)
• FORMAT (<value>, <format_string>)
• ВЛЕВО (<text>, <num_chars>)
• LEN (<текст>)
• LOWER (<текст>)
• MID (<text>, <start_num>, <num_chars>)
• REPLACE (<old_text>, <start_num>, <num_chars>, <new_text>)
• REPT (<text>, <num_times>)
• ВПРАВО (<text>, <num_chars>)
• ПОИСК (<find_text>, <inside_text>, [<start_num>], [<NotFoundValue>])
• ЗАМЕНА (<text>, <old_text>, <new_text>, <instance_num>)
• TRIM (<текст>)
• UPPER (<текст>)
• VALUE (<текст>)

Excel DAX — Понимание функций DAX

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

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

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

Функция DAX — объяснение структуры

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

• Описание
• Синтаксис
• параметры
• Возвращаемое значение
• замечания
• пример

Вы узнаете о каждом из них в следующих разделах.

Описание

В разделе «Описание» вы узнаете, что такое функция DAX и где ее можно использовать.

Синтаксис

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

параметры

В разделе «Параметры» вы узнаете о каждом из параметров конкретной функции DAX, является ли параметр входом или выходом и есть ли какие-либо параметры. Как видно из главы «Стандартные параметры DAX», будут использоваться только имена стандартных параметров.

Возвращаемое значение

В разделе «Возвращаемое значение» вы узнаете, какое значение будет возвращать функция DAX, и ее тип данных.

замечания

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

пример

Описание функции DAX закончится примером использования функции.

Excel DAX — оценочный контекст

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

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

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

Типы контекста в DAX

DAX поддерживает следующие контексты оценки —

• Контекст строки
• Контекст фильтра

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

Контекст строки

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

Некоторые функции DAX (например, X-функции, FILTER ()) и все вычисляемые столбцы имеют контекст строки. Например, если вы создаете вычисляемый столбец Year с формулой DAX = YEAR ([Дата]), значения вычисляемого столбца получаются путем применения заданной формулы DAX к указанному столбцу в таблице, строка за строкой.

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

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

Напротив, если у вас есть функция DAX, такая как SUMX, значения, вычисленные построчно, суммируются, и отображается только конечный результат. То есть промежуточные значения отбрасываются.

Если у вас есть связанные таблицы, контекст строки определяет, какие строки в связанной таблице связаны с текущей строкой. Однако контекст строки не распространяется через отношения автоматически. Для этого вы должны использовать функции DAX — RELATED и RELATEDTABLE.

Многострочный контекст

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

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

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

Контекст фильтра

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

Контекст фильтра, созданный с помощью сводной таблицы

Контекст фильтра, созданный с помощью сводной таблицы, — это естественная фильтрация, которая применяется при выборе полей в сводной таблице из следующих элементов:

• Ряды
• Колонны
• фильтры
• Срезы

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

Контекст фильтра, созданный функциями DAX

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

Фильтр контекста как дополнение к контексту строки

Контекст строки не создает автоматически контекст фильтра. Вы можете достичь того же с формулами DAX, содержащими функции фильтра DAX.

Excel DAX — формулы

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

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

Функции DAX против формул DAX

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

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

• Если вы хотите настраивать вычисления построчно, Power Pivot предоставляет функции, которые позволяют использовать текущее значение строки или связанное значение для выполнения расчетов, которые зависят от контекста.

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

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

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

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

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

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

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

Каждая формула DAX имеет следующий синтаксис —

• Каждая формула должна начинаться со знака равенства.

• Справа от знака равенства вы можете либо ввести, либо выбрать имя функции, либо ввести выражение. Выражение может содержать имена таблиц и столбцов, связанные операторами DAX.

Каждая формула должна начинаться со знака равенства.

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

Ниже приведены некоторые действительные формулы DAX —

• [column_Cost] + [column_Tax]
• = Сегодня ()

Понимание функции IntelliSense

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

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

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

• Нажмите на название функции. Имя функции отображается в строке формул и отображается синтаксис, который поможет вам при выборе аргументов.

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

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

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

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

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

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

Нажмите на название функции. Имя функции отображается в строке формул и отображается синтаксис, который поможет вам при выборе аргументов.

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

Нажмите клавишу TAB или щелкните имя, чтобы добавить элемент из списка автозаполнения в формулу.

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

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

Настоятельно рекомендуется использовать эту удобную функцию IntelliSense.

Где использовать формулы DAX?

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

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

• Вы можете использовать формулы DAX в вычисляемых полях. Вы создаете эти формулы —

  • В окне Excel в диалоговом окне «Вычисляемое поле» или

  • В окне Power Pivot в области расчета таблицы.

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

Вы можете использовать формулы DAX в вычисляемых полях. Вы создаете эти формулы —

В окне Excel в диалоговом окне «Вычисляемое поле» или

В окне Power Pivot в области расчета таблицы.

Одна и та же формула может вести себя по-разному в зависимости от того, используется ли формула в вычисляемом столбце или вычисляемом поле.

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

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

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

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

При написании формул DAX важно понимать концепцию контекста в DAX. Это может быть немного сложным в начале вашего пути DAX, но как только вы его освоите, вы можете написать эффективные формулы DAX, которые необходимы для сложного и динамического анализа данных. Подробнее см. Главу «Контекст DAX».

Создание формулы DAX

Вы уже узнали о функции IntelliSense в предыдущем разделе. Не забудьте использовать его при создании любой формулы DAX.

Чтобы создать формулу DAX, выполните следующие действия:

• Введите знак равенства.

• Справа от знака равенства введите следующее —

  • Введите первую букву имени функции или таблицы и выберите полное имя в раскрывающемся списке.

  • Если вы выбрали имя функции, введите круглые скобки ‘(‘.

  • Если вы выбрали имя таблицы, введите скобку ‘[‘. Введите первую букву имени столбца и выберите полное имя в раскрывающемся списке.

  • Закройте имена столбцов с помощью ‘]’ и имена функций с помощью ‘)’.

  • Введите оператор DAX между выражениями или введите ‘,’ для разделения аргументов функции.

  • Повторите шаги 1 — 5 до завершения формулы DAX.

Введите знак равенства.

Справа от знака равенства введите следующее —

Введите первую букву имени функции или таблицы и выберите полное имя в раскрывающемся списке.

Если вы выбрали имя функции, введите круглые скобки ‘(‘.

Если вы выбрали имя таблицы, введите скобку ‘[‘. Введите первую букву имени столбца и выберите полное имя в раскрывающемся списке.

Закройте имена столбцов с помощью ‘]’ и имена функций с помощью ‘)’.

Введите оператор DAX между выражениями или введите ‘,’ для разделения аргументов функции.

Повторите шаги 1 — 5 до завершения формулы DAX.

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

SUM ([East_Sales[Amount]) 

Как уже обсуждалось в главе «Синтаксис DAX», рекомендуется использовать имя таблицы вместе с именем столбца в каждой ссылке на любое имя столбца. Это называется «полное имя».

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

Создание формулы DAX для вычисляемого столбца

Вы можете создать формулу DAX для вычисляемого столбца в окне Power Pivot.

• Нажмите на вкладку таблицы, в которую вы хотите добавить вычисляемый столбец.
• Перейдите на вкладку «Дизайн» на ленте.
• Нажмите Добавить.
• Введите формулу DAX для вычисляемого столбца в строке формул.

= DIVIDE (East_Sales[Amount], East_Sales[Units])

Эта формула DAX делает следующее для каждой строки в таблице East_Sales —

• Делит значение в столбце Сумма строки на значение в столбце Единицы в той же строке.

• Помещает результат в новый добавленный столбец в той же строке.

• Повторите шаги 1 и 2 итеративно, пока он не завершит все строки в таблице.

Делит значение в столбце Сумма строки на значение в столбце Единицы в той же строке.

Помещает результат в новый добавленный столбец в той же строке.

Повторите шаги 1 и 2 итеративно, пока он не завершит все строки в таблице.

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

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

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

Обратитесь к главе — Рассчитанные столбцы за подробностями.

Создание формулы DAX для вычисляемого поля

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

• Чтобы создать формулу DAX для вычисляемого поля в окне Excel, используйте диалоговое окно «Вычисляемое поле».

• Чтобы создать формулу DAX для вычисляемого поля в окне Power Pivot, щелкните ячейку в области вычислений в соответствующей таблице. Запустите формулу DAX с CalculatedFieldName: =.

Чтобы создать формулу DAX для вычисляемого поля в окне Excel, используйте диалоговое окно «Вычисляемое поле».

Чтобы создать формулу DAX для вычисляемого поля в окне Power Pivot, щелкните ячейку в области вычислений в соответствующей таблице. Запустите формулу DAX с CalculatedFieldName: =.

Например, общая восточная сумма продаж: = SUM ([East_Sales [Amount])

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

Подробнее об этих параметрах см. В главе «Вычисляемые поля».

Создание формул DAX с помощью панели формул

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

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

• Чтобы ввести имя столбца, вы можете выбрать его из списка имен столбцов в выбранной таблице. Введите скобку «[» справа от имени таблицы, а затем выберите столбец из списка столбцов в выбранной таблице.

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

Чтобы ввести имя столбца, вы можете выбрать его из списка имен столбцов в выбранной таблице. Введите скобку «[» справа от имени таблицы, а затем выберите столбец из списка столбцов в выбранной таблице.

Советы по использованию автозаполнения

Ниже приведены некоторые советы по использованию автозаполнения —

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

• Определенные имена, которые вы создаете для констант, не отображаются в раскрывающемся списке автозаполнения, но вы все равно можете их ввести.

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

• Вы должны убедиться, что каждая функция синтаксически верна.

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

Определенные имена, которые вы создаете для констант, не отображаются в раскрывающемся списке автозаполнения, но вы все равно можете их ввести.

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

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

Понимание функции вставки

Вы можете найти кнопку Вставить функцию, помеченную как fx , как в окне Power Pivot, так и в окне Excel.

• Кнопка «Вставить функцию» в окне «Power Pivot» находится слева от строки формул.

• Кнопка «Вставить функцию» в окне Excel находится в диалоговом окне «Вычисляемое поле» справа от формулы.

Кнопка «Вставить функцию» в окне «Power Pivot» находится слева от строки формул.

Кнопка «Вставить функцию» в окне Excel находится в диалоговом окне «Вычисляемое поле» справа от формулы.

При нажатии на кнопку « fx» появляется диалоговое окно «Вставить функцию». Диалоговое окно «Вставить функцию» — это самый простой способ найти функцию DAX, которая соответствует вашей формуле DAX.

Диалоговое окно «Вставка функции» помогает выбрать функции по категориям и предоставляет краткое описание каждой функции.

Использование функции вставки в формуле DAX

Предположим, вы хотите создать следующее вычисляемое поле —

Medal Count: = COUNTA (]Medal]) 

Вы можете использовать диалоговое окно «Вставить функцию», выполнив следующие действия:

• Нажмите на область расчета таблицы результатов.
• Введите следующее в строке формул —

Medal Count: = 

• Нажмите кнопку «Вставить функцию» ( fx ).

Откроется диалоговое окно «Вставить функцию».

• Выберите «Статистика» в поле «Выбрать категорию», как показано на следующем снимке экрана.

• Выберите COUNTA в поле Select a function, как показано на следующем снимке экрана.

Выберите «Статистика» в поле «Выбрать категорию», как показано на следующем снимке экрана.

Выберите COUNTA в поле Select a function, как показано на следующем снимке экрана.

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

• Нажмите ОК. Medal Count: = COUNTA (появляется в строке формул, а также появляется всплывающая подсказка, отображающая синтаксис функции.

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

• Тип M. Отображаемые имена в раскрывающемся списке будут ограничены именами, начинающимися с буквы «M».

• Нажмите Медаль.

Нажмите ОК. Medal Count: = COUNTA (появляется в строке формул, а также появляется всплывающая подсказка, отображающая синтаксис функции.

Тип [. Это означает, что вы собираетесь ввести имя столбца. Имена всех столбцов и вычисляемых полей в текущей таблице будут отображены в раскрывающемся списке. Вы можете использовать IntelliSense для завершения формулы.

Тип M. Отображаемые имена в раскрывающемся списке будут ограничены именами, начинающимися с буквы «M».

Нажмите Медаль.

• Дважды щелкните Медаль. Число медалей: = COUNTA ([Медаль] будет отображаться на панели формул. Закройте скобки.

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

• Нажмите кнопку «Вставить функцию» ( fx ) справа от формулы.

Дважды щелкните Медаль. Число медалей: = COUNTA ([Медаль] будет отображаться на панели формул. Закройте скобки.

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

Нажмите кнопку «Вставить функцию» ( fx ) справа от формулы.

Откроется диалоговое окно «Вставить функцию». Остальные шаги такие же, как указано выше.

Использование нескольких функций в формуле DAX

Формулы DAX могут содержать до 64 вложенных функций. Но вряд ли формула DAX содержит так много вложенных функций.

Если формула DAX имеет много вложенных функций, она имеет следующие недостатки:

• Формула будет очень сложно создать.
• Если в формуле есть ошибки, отладку будет очень сложно.
• Оценка формулы не будет очень быстрой.

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

Создание формулы DAX с использованием стандартных агрегатов

Когда вы выполняете анализ данных, вы будете выполнять расчеты на агрегированных данных. Существует несколько функций агрегирования DAX, таких как SUM, COUNT, MIN, MAX, DISTINCTCOUNT и т. Д., Которые можно использовать в формулах DAX.

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

• Перейдите на вкладку «Результаты» в окне «Power Pivot». Таблица результатов будет отображаться.
• Нажмите на столбец медали. Весь столбец — медаль будет выбран.
• Перейдите на вкладку «Главная» на ленте.
• Нажмите стрелку вниз рядом с AutoSum в группе Расчеты.

• Нажмите COUNT в раскрывающемся списке.

Как вы можете заметить, вычисленное поле Count of Medal появляется в области вычисления под колонкой — Medal. Формула DAX также появляется в строке формул —

Count of Medal: = COUNTA([Medal]) 

Функция AutoSum сделала всю работу за вас — создала вычисляемое поле для агрегирования данных. Кроме того, AutoSum выбрал соответствующий вариант функции DAX COUNT, т.е. COUNTA (DAX имеет функции COUNT, COUNTA, COUNTAX).

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

Sum of Medal: = SUM([Medal]) 

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

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

Формулы DAX и реляционная модель

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

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

Ссылочная целостность

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

Если вы обеспечите ссылочную целостность, вы можете предотвратить следующие ловушки —

• Добавление строк в связанную таблицу, когда в основной таблице нет связанной строки (т. Е. С совпадающими значениями в ключевых столбцах).

• Изменение данных в первичной таблице, что приведет к появлению потерянных строк в связанной таблице (т. Е. Строк со значением данных в ключевом столбце, у которого нет совпадающего значения в ключевом столбце первичной таблицы).

• Удаление строк из первичной таблицы при наличии совпадающих значений данных в строках связанной таблицы.

Добавление строк в связанную таблицу, когда в основной таблице нет связанной строки (т. Е. С совпадающими значениями в ключевых столбцах).

Изменение данных в первичной таблице, что приведет к появлению потерянных строк в связанной таблице (т. Е. Строк со значением данных в ключевом столбце, у которого нет совпадающего значения в ключевом столбце первичной таблицы).

Удаление строк из первичной таблицы при наличии совпадающих значений данных в строках связанной таблицы.

Обновление результатов формул DAX

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

Результаты формул DAX необходимо обновлять в двух случаях —

• Обновление данных — когда данные обновляются.

• Пересчет — когда есть изменения в формуле DAX.

Обновление данных — когда данные обновляются.

Пересчет — когда есть изменения в формуле DAX.

Понимание обновления данных и пересчета

Обновление и пересчет данных — это две отдельные, но связанные операции.

• Обновление данных — это процесс обновления данных в модели данных вашей рабочей книги с получением актуальных данных из внешних источников данных.

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

Обновление данных — это процесс обновления данных в модели данных вашей рабочей книги с получением актуальных данных из внешних источников данных.

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

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

Различные способы обновления данных в модели данных

Power Pivot не обнаруживает автоматически изменения во внешних источниках данных.

• Вы можете обновить данные вручную из окна Power Pivot с указанными интервалами.

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

Вы можете обновить данные вручную из окна Power Pivot с указанными интервалами.

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

Подробнее об этом см. Главу « Обновление данных в модели данных» .

Пересчет формул DAX

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

Пересчет может повлиять на производительность следующими способами —

• Для вычисляемого столбца результат формулы DAX должен всегда пересчитываться для всего столбца при каждом изменении формулы DAX.

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

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

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

В DAX пересчет формул может выполняться автоматически или вручную.

Чтобы узнать больше о пересчете, обратитесь к главе — Пересчет формул DAX .

Excel DAX — обновление данных в модели данных

DAX используется для расчетов данных в модели данных в Excel Power Pivot. DAX позволяет эффективно выполнять моделирование данных и отчетность. Однако для этого необходимо время от времени обновлять данные в модели данных, чтобы они отражали текущие данные.

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

Различные способы обновления данных в модели данных

Вы можете обновить данные в модели данных следующими способами:

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

Обновление данных в модели данных

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

Обратите внимание, что добавление данных, изменение данных или редактирование фильтров всегда вызывает пересчет формул DAX, которые зависят от этого источника данных. Подробнее см. Главу «Пересчет формул DAX».

У вас есть два типа обновления данных в модели данных —

Обновить вручную

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

Автоматическое или запланированное обновление

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

Обновление существующего источника данных вручную

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

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

Чтобы обновить данные для одной таблицы или всех таблиц в модели данных, выполните следующие действия:

• Перейдите на вкладку «Главная» на ленте в окне Power Pivot.
• Нажмите Обновить.
• Нажмите Обновить в раскрывающемся списке, чтобы обновить выбранную таблицу.
• Нажмите Обновить все в раскрывающемся списке, чтобы обновить все таблицы.

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

• Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».
• Нажмите Существующие подключения в группе Получить внешние данные.

Откроется диалоговое окно существующих подключений.

• Выберите соединение.
• Нажмите кнопку Обновить.

Появляется диалоговое окно «Обновление данных», и информация о ходе обновления данных отображается, когда механизм PowerPivot перезагружает данные из выбранной таблицы или из всех таблиц из источника данных.

Есть три возможных результата —

• Успех — отчеты о количестве строк, импортированных в каждую таблицу.

• Ошибка — ошибка может возникнуть, если база данных находится в автономном режиме, у вас больше нет разрешений. Таблица или столбец удаляются или переименовываются в источнике.

• Отменено — это означает, что Excel не выдавал запрос на обновление, возможно, потому что обновление отключено в соединении.

Успех — отчеты о количестве строк, импортированных в каждую таблицу.

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

Отменено — это означает, что Excel не выдавал запрос на обновление, возможно, потому что обновление отключено в соединении.

Нажмите кнопку Закрыть.

Изменение источника данных

Чтобы изменить данные в вашей модели данных, вы можете отредактировать информацию о соединении или обновить определение таблиц и столбцов, используемых в вашей модели данных, в окне Power Pivot.

Вы можете внести следующие изменения в существующие источники данных —

связи

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

таблицы

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

Колонны

• Измените имена столбцов.
• Добавить новые столбцы.
• Удалить столбцы из модели данных (не влияет на источник данных).

Вы можете редактировать свойства существующего источника данных следующими способами:

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

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

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

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

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

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

Изменение соединения с существующим источником данных

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

• Перейдите на вкладку «Главная» на ленте в окне PowerPivot.
• Нажмите Существующие подключения в группе Получить внешние данные.

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

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

• Нажмите кнопку Изменить. Откроется диалоговое окно «Редактировать соединение».

• Нажмите кнопку «Обзор», чтобы найти другую базу данных того же типа (в данном примере — книгу Excel), но с другим именем или местоположением.

• Нажмите кнопку Открыть.

Нажмите кнопку Изменить. Откроется диалоговое окно «Редактировать соединение».

Нажмите кнопку «Обзор», чтобы найти другую базу данных того же типа (в данном примере — книгу Excel), но с другим именем или местоположением.

Нажмите кнопку Открыть.

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

• Нажмите кнопку Сохранить. Вы вернетесь в диалоговое окно «Существующие подключения».

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

• Нажмите Закрыть, как только обновление данных будет успешным.

• Нажмите Закрыть в диалоговом окне «Существующие подключения».

Нажмите кнопку Сохранить. Вы вернетесь в диалоговое окно «Существующие подключения».

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

Нажмите Закрыть, как только обновление данных будет успешным.

Нажмите Закрыть в диалоговом окне «Существующие подключения».

Редактирование таблиц и сопоставлений столбцов (привязок)

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

• Выберите вкладку, содержащую таблицу, которую вы хотите изменить, в окне Power Pivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Свойства таблицы.

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

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Свойства таблицы.

Откроется диалоговое окно «Редактировать свойства таблицы».

Вы можете наблюдать следующее —

• Имя выбранной таблицы в модели данных отображается в поле «Имя таблицы».

• Имя соответствующей таблицы во внешнем источнике данных отображается в поле «Имя источника».

• Существует два варианта имен столбцов: «Исходный» и «Модальный».

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

• Предварительный просмотр выбранной таблицы появится в диалоговом окне.

Имя выбранной таблицы в модели данных отображается в поле «Имя таблицы».

Имя соответствующей таблицы во внешнем источнике данных отображается в поле «Имя источника».

Существует два варианта имен столбцов: «Исходный» и «Модальный».

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

Предварительный просмотр выбранной таблицы появится в диалоговом окне.

Вы можете редактировать следующее —

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

• При необходимости измените сопоставления столбцов.

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

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

• Нажмите кнопку Сохранить.

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

При необходимости измените сопоставления столбцов.

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

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

Нажмите кнопку Сохранить.

При сохранении текущего набора свойств таблицы вы получите сообщение — Пожалуйста, подождите. Затем будет отображено количество найденных строк.

В таблице в модели данных все недопустимые столбцы автоматически удаляются и добавляются новые столбцы.

Изменение имени столбца и типа данных

Вы можете изменить имя столбца в таблице в модели данных следующим образом:

• Дважды щелкните заголовок столбца. Имя столбца в заголовке будет выделено.

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

• Выберите столбец, нажав на его заголовок.

• Щелкните правой кнопкой мыши столбец.

• Нажмите Переименовать столбец в раскрывающемся списке.

Дважды щелкните заголовок столбца. Имя столбца в заголовке будет выделено.

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

Выберите столбец, нажав на его заголовок.

Щелкните правой кнопкой мыши столбец.

Нажмите Переименовать столбец в раскрывающемся списке.

Имя столбца в заголовке будет выделено. Введите имя нового столбца, перезаписав старое имя.

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

Чтобы изменить тип данных столбца, выполните следующие действия:

• Выберите столбец, который вы хотите изменить, щелкнув его заголовок.

• Перейдите на вкладку «Главная» на ленте.

• Щелкните элементы управления в группе «Форматирование», чтобы изменить тип и формат данных столбца.

Выберите столбец, который вы хотите изменить, щелкнув его заголовок.

Перейдите на вкладку «Главная» на ленте.

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

Добавление / изменение фильтра к источнику данных

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

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

Чтобы добавить новый фильтр в источник данных во время импорта данных, выполните следующие действия:

• Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».
• Щелкните один из источников данных в группе «Получить внешние данные».

Откроется диалоговое окно мастера импорта таблиц.

• Перейдите к шагу — выберите таблицы и представления.
• Выберите таблицу и нажмите «Просмотр и фильтр».

Появится диалоговое окно «Просмотр выбранной таблицы».

• Нажмите на столбец, к которому вы хотите применить фильтр.
• Нажмите стрелку вниз справа от заголовка столбца.

Чтобы добавить фильтр, выполните одно из следующих действий:

• В списке значений столбцов выберите или очистите одно или несколько значений для фильтрации, а затем нажмите кнопку «ОК».

  Однако, если количество значений очень велико, отдельные элементы могут не отображаться в списке. Вместо этого вы увидите сообщение — «Слишком много предметов для показа».

• Нажмите Числовые фильтры или Текстовые фильтры (в зависимости от типа данных столбца).

  • Затем щелкните одну из команд оператора сравнения (например, «Равно») или нажмите «Пользовательский фильтр». В диалоговом окне «Пользовательский фильтр» создайте фильтр и нажмите «ОК».

В списке значений столбцов выберите или очистите одно или несколько значений для фильтрации, а затем нажмите кнопку «ОК».

Однако, если количество значений очень велико, отдельные элементы могут не отображаться в списке. Вместо этого вы увидите сообщение — «Слишком много предметов для показа».

Нажмите Числовые фильтры или Текстовые фильтры (в зависимости от типа данных столбца).

Затем щелкните одну из команд оператора сравнения (например, «Равно») или нажмите «Пользовательский фильтр». В диалоговом окне «Пользовательский фильтр» создайте фильтр и нажмите «ОК».

Примечание. Если вы допустили ошибку на каком-либо этапе, нажмите кнопку «Очистить фильтры строк» ​​и начните все сначала.

• Нажмите ОК. Вы вернетесь на страницу выбора таблиц и представлений мастера импорта таблиц.

Как вы можете заметить, в столбце «Сведения о фильтре» появляется ссылка «Примененные фильтры» для столбца, для которого вы определили фильтр.

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

• Нажмите Готово, чтобы импортировать данные с примененными фильтрами.
• Закройте Мастер импорта таблиц.

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

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

• Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».

• Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно существующих подключений.

• Нажмите на соединение, которое содержит таблицу, для которой вы должны изменить фильтр.

• Нажмите кнопку Открыть.

Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».

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

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

Нажмите кнопку Открыть.

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

Excel DAX — пересчет формул DAX

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

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

Типы пересчета

У вас есть два варианта пересчета формул DAX —

• Режим автоматического пересчета (по умолчанию)
• Режим ручного пересчета

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

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

Автоматический пересчет формул DAX

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

Следующие изменения всегда требуют пересчета формул DAX —

• Значения из внешнего источника данных были обновлены.

• Сама формула DAX изменена.

• Имена таблиц или столбцов, на которые есть ссылки в формуле DAX, были изменены.

• Отношения между таблицами были добавлены, изменены или удалены.

• Добавлены новые вычисляемые поля или вычисляемые столбцы.

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

• Строки были вставлены или удалены в таблице.

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

Значения из внешнего источника данных были обновлены.

Сама формула DAX изменена.

Имена таблиц или столбцов, на которые есть ссылки в формуле DAX, были изменены.

Отношения между таблицами были добавлены, изменены или удалены.

Добавлены новые вычисляемые поля или вычисляемые столбцы.

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

Строки были вставлены или удалены в таблице.

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

Когда использовать режим ручного пересчета?

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

Вы можете использовать ручной пересчет формул DAX в следующих условиях:

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

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

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

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

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

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

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

• Любые новые формулы, добавленные в рабочую книгу, будут помечены как содержащие ошибку.

• Результаты не будут отображаться в новых вычисляемых столбцах.

Любые новые формулы, добавленные в рабочую книгу, будут помечены как содержащие ошибку.

Результаты не будут отображаться в новых вычисляемых столбцах.

Настройка рабочей книги для ручного пересчета

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

• Перейдите на вкладку «Дизайн» на ленте в окне Power Pivot.
• Нажмите Параметры расчета в группе Расчеты.
• Выберите ручной режим расчета в раскрывающемся списке.

Пересчет формул DAX вручную

Чтобы пересчитать формулы DAX вручную, выполните следующие действия:

• Перейдите на вкладку «Дизайн» на ленте в окне Power Pivot.
• Щелкните поле «Параметры расчета» в группе «Расчеты».
• Щелкните поле «Рассчитать сейчас» в раскрывающемся списке.

Устранение неполадок пересчета формулы DAX

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

Power Pivot выполняет следующие действия при пересчете формул DAX:

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

зависимости

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

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

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

Последовательность пересчета для зависимых столбцов

Зависимости рассчитываются до любого пересчета. Если есть несколько столбцов, которые зависят друг от друга, Power Pivot следует последовательности зависимостей. Это гарантирует, что столбцы обрабатываются в правильном порядке с максимальной скоростью.

операции

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

Пересчет летучих функций

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

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

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

Excel DAX — ошибки формулы

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

Ошибки формулы DAX могут быть синтаксическими или семантическими ошибками. Ошибки могут возникать либо во время разработки, либо во время выполнения.

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

Ошибка DAX: расчет прерван

Следующая ошибка может возникнуть при попытке создать (во время разработки) или использовать (во время выполнения) вычисляемое поле с функцией анализа времени DAX. В каждом случае несмежный диапазон дат передается функции анализа времени.

«Ошибка DAX: ВЫЧИСЛЕНО ВЫЧИСЛЕНИЕ: MdxScript (экземпляр) (00, 0) Функция« DATEADD »работает только с непрерывным выбором даты».

Причина во время выполнения

Эта ошибка может отображаться, когда вычисляемое поле с функцией анализа времени DAX помещается в область VALUES сводной таблицы и поля даты, такие как месяц или квартал, выбираются в качестве срезов или фильтров перед выбором года. Например, если у вас есть данные за три года — 2014, 2015 и 2016, и вы пытаетесь использовать только месяц Март без выбора поля Год, тогда эти значения не являются непрерывными значениями данных, и вы получите ошибку.

Как исправить ошибку во время выполнения?

В приведенном выше примере

• Сначала добавьте год в качестве среза или фильтра и выберите год.

• Затем добавьте месяц или квартал в качестве среза или фильтра.

• Затем выберите один или несколько месяцев или кварталов для нарезки или фильтрации для выбранного года.

Сначала добавьте год в качестве среза или фильтра и выберите год.

Затем добавьте месяц или квартал в качестве среза или фильтра.

Затем выберите один или несколько месяцев или кварталов для нарезки или фильтрации для выбранного года.

Причина во время разработки

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

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

Как исправить эту ошибку во время разработки?

Чтобы исправить ошибку во время разработки, сделайте следующее:

• Если ваша таблица дат импортирована из источника данных, используйте «Обновить в окне Power Pivot», чтобы повторно импортировать любые изменения, найденные в источнике.

• Проверьте значения в столбце даты, чтобы убедиться, что они находятся в непрерывном порядке. Если будет обнаружено, что какое-либо значение отсутствует, оно должно быть исправлено в источнике, а таблица дат должна быть обновлена.

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

Если ваша таблица дат импортирована из источника данных, используйте «Обновить в окне Power Pivot», чтобы повторно импортировать любые изменения, найденные в источнике.

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

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

Семантическая ошибка DAX — пример

Следующая ошибка DAX является семантической ошибкой —

«Функция« CALCULATE »была использована в выражении« истина-ложь », которое используется в качестве выражения фильтра таблицы. Это запрещено ».

причина

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

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

Как исправить такие ошибки?

Такие ошибки можно исправить, используя функцию DAX FILTER, чтобы определить фильтры как табличное выражение, которое затем можно использовать в качестве аргумента функции DAX CALCULATE.

Excel DAX — Time Intelligence

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

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

Почему Time Intelligence делает DAX мощным?

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

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

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

Требования к функциям DAX Time Intelligence

Функции разведки времени DAX предъявляют определенные требования. Если эти требования не выполняются, вы можете получить ошибки или они могут работать неправильно. Следовательно, вы можете ссылаться на эти требования как на правила или ограничения. Ниже приведены определенные требования / правила / ограничения функций времени DAX —

• Вам необходимо иметь таблицу дат в вашей модели данных.

• Таблица дат должна включать столбец, который DAX считает столбцом «Дата». Вы можете назвать столбец так, как хотите, но он должен соответствовать следующим условиям: o Столбец даты должен содержать непрерывный набор дат, который охватывает каждый день в течение периода времени, когда вы анализируете данные.

  • Каждая дата должна существовать один раз и только один раз в столбце даты.

  • Вы не можете пропустить ни одной даты (например, вы не можете пропустить даты выходных).

• Функции анализа времени DAX работают только по стандартному календарю и предполагают, что начало года — 1 января, а конец года — 31 декабря, а месяцы года и дни каждого месяца — календарного года.

Вам необходимо иметь таблицу дат в вашей модели данных.

Таблица дат должна включать столбец, который DAX считает столбцом «Дата». Вы можете назвать столбец так, как хотите, но он должен соответствовать следующим условиям: o Столбец даты должен содержать непрерывный набор дат, который охватывает каждый день в течение периода времени, когда вы анализируете данные.

Каждая дата должна существовать один раз и только один раз в столбце даты.

Вы не можете пропустить ни одной даты (например, вы не можете пропустить даты выходных).

Функции анализа времени DAX работают только по стандартному календарю и предполагают, что начало года — 1 января, а конец года — 31 декабря, а месяцы года и дни каждого месяца — календарного года.

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

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

Функции DAX Time Intelligence — Категории

Функции DAX Time Intelligence можно разделить на следующие категории:

• Функции DAX, которые возвращают одну дату.
• DAX-функции, которые возвращают таблицу дат.
• Функции DAX, которые оценивают выражения за период времени.

Функции DAX, которые возвращают одну дату

Функции DAX в этой категории возвращают одну дату.

В этой категории 10 функций DAX.

Sr.No.	Функция DAX и возвращаемое значение
1	FIRSTDATE (Date_Column) Возвращает первую дату в столбце Date_Column в текущем контексте.
2	LASTDATE (Date_Column) Возвращает последнюю дату в столбце Date_Column в текущем контексте.
3	FIRSTNONBLANK (Date_Column, Expression) Возвращает первую дату, когда выражение имеет непустое значение.
4	LASTNONBLANK (Date_Column, Expression) Возвращает последнюю дату, когда выражение имеет непустое значение.
5	STARTOFMONTH (Date_Column) Возвращает первую дату месяца в текущем контексте.
6	ENDOFMONTH (Date_Column) Возвращает последнюю дату месяца в текущем контексте.
7	STARTOFQUARTER (Date_Column) Возвращает первую дату квартала в текущем контексте.
8	ENDOFQUARTER (Date_Column) Возвращает последнюю дату квартала в текущем контексте.
9	STARTOFYEAR (Date_Column, [YE_Date]) Возвращает первую дату года в текущем контексте.
10	ENDOFYEAR (Date_Column, [YE_Date]) Возвращает последнюю дату года в текущем контексте.

FIRSTDATE (Date_Column)

Возвращает первую дату в столбце Date_Column в текущем контексте.

LASTDATE (Date_Column)

Возвращает последнюю дату в столбце Date_Column в текущем контексте.

FIRSTNONBLANK (Date_Column, Expression)

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

LASTNONBLANK (Date_Column, Expression)

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

STARTOFMONTH (Date_Column)

Возвращает первую дату месяца в текущем контексте.

ENDOFMONTH (Date_Column)

Возвращает последнюю дату месяца в текущем контексте.

STARTOFQUARTER (Date_Column)

Возвращает первую дату квартала в текущем контексте.

ENDOFQUARTER (Date_Column)

Возвращает последнюю дату квартала в текущем контексте.

STARTOFYEAR (Date_Column, [YE_Date])

Возвращает первую дату года в текущем контексте.

ENDOFYEAR (Date_Column, [YE_Date])

Возвращает последнюю дату года в текущем контексте.

Функции DAX, которые возвращают таблицу дат

Функции DAX в этой категории возвращают таблицу дат. Эти функции будут в основном использоваться в качестве аргумента SetFilter для функции DAX — CALCULATE.

В этой категории 16 функций DAX. Восемь (8) из этих функций DAX — это «предыдущая» и «следующая» функции.

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

• «Предыдущие» функции работают в обратном направлении от первой даты в текущем контексте, а «следующие» функции перемещаются вперед от последней даты в текущем контексте.

• Функции «предыдущая» и «следующая» возвращают итоговые даты в виде таблицы из одного столбца.

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

«Предыдущие» функции работают в обратном направлении от первой даты в текущем контексте, а «следующие» функции перемещаются вперед от последней даты в текущем контексте.

Функции «предыдущая» и «следующая» возвращают итоговые даты в виде таблицы из одного столбца.

Sr.No.	Функция DAX и возвращаемое значение
1	PREVIOUSDAY (Date_Column) Возвращает таблицу, которая содержит столбец всех дат, представляющих день, предшествующий первой дате в столбце Date_Column в текущем контексте.
2	СЛЕДУЮЩИЙ (Date_Column) Возвращает таблицу, которая содержит столбец всех дат следующего дня, основываясь на первой дате, указанной в столбце Date_Column в текущем контексте.
3	PREVIOUSMONTH (Date_Column) Возвращает таблицу, которая содержит столбец всех дат за предыдущий месяц, основываясь на первой дате в столбце Date_Column в текущем контексте.
4	NEXTMONTH (Date_Column) Возвращает таблицу, которая содержит столбец всех дат следующего месяца на основе первой даты в столбце Date_Column в текущем контексте.
5	PREVIOUSQUARTER (Date_Column) Возвращает таблицу, содержащую столбец всех дат предыдущего квартала, основываясь на первой дате в столбце Date_Column в текущем контексте.
6	NEXTQUARTER (Date_Column) Возвращает таблицу, которая содержит столбец всех дат в следующем квартале, основываясь на первой дате, указанной в столбце Date_Column в текущем контексте.
7	PREVIOUSYEAR (Date_Column, [YE_Date]) Возвращает таблицу, которая содержит столбец всех дат за предыдущий год, учитывая последнюю дату в столбце Date_Column в текущем контексте.
8	NEXTYEAR (Date_Column, [YE_Date]) Возвращает таблицу, которая содержит столбец всех дат в следующем году, основываясь на первой дате в столбце Date_Column в текущем контексте.

PREVIOUSDAY (Date_Column)

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

СЛЕДУЮЩИЙ (Date_Column)

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

PREVIOUSMONTH (Date_Column)

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

NEXTMONTH (Date_Column)

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

PREVIOUSQUARTER (Date_Column)

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

NEXTQUARTER (Date_Column)

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

PREVIOUSYEAR (Date_Column, [YE_Date])

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

NEXTYEAR (Date_Column, [YE_Date])

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

Четыре (4) функции DAX вычисляют набор дат в периоде. Эти функции выполняют вычисления, используя последнюю дату в текущем контексте.

Sr.No.	Функция DAX и возвращаемое значение
1	DATESMTD (Date_Column) Возвращает таблицу, которая содержит столбец дат за месяц до даты в текущем контексте.
2	DATESQTD (Date_Column) Возвращает таблицу, которая содержит столбец дат за квартал до даты в текущем контексте.
3	DATESYTD (Date_Column, [YE_Date]) Возвращает таблицу, которая содержит столбец дат за год до даты в текущем контексте.
4	SAMEPERIODLASTYEAR (Date_Column) Возвращает таблицу, которая содержит столбец дат, смещенных на год назад от дат в указанном столбце Date_Column в текущем контексте. Примечание. SAMEPERIODLASTYEAR требует, чтобы текущий контекст содержал непрерывный набор дат. Если текущий контекст не является непрерывным набором дат, то SAMEPERIODLASTYEAR вернет ошибку.

DATESMTD (Date_Column)

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

DATESQTD (Date_Column)

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

DATESYTD (Date_Column, [YE_Date])

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

SAMEPERIODLASTYEAR (Date_Column)

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

Примечание. SAMEPERIODLASTYEAR требует, чтобы текущий контекст содержал непрерывный набор дат.

Если текущий контекст не является непрерывным набором дат, то SAMEPERIODLASTYEAR вернет ошибку.

• Четыре (4) функции DAX используются для перехода от набора дат в текущем контексте к новому набору дат.

  Эти функции DAX более мощные, чем предыдущие.

  • Функции DAX — DATEADD, DATESINPERIOD и PARALLELPERIOD смещают некоторое количество временных интервалов из текущего контекста. Интервал может быть днем, месяцем, кварталом или годом, представленным ключевыми словами — ДЕНЬ, МЕСЯЦ, КВАРТАЛ и ГОД соответственно.

    Например:

• Сдвиг назад на 2 дня.

• Продвинуться на 5 месяцев.

• Двигайтесь вперед на один месяц с сегодняшнего дня.

• Вернуться в тот же квартал в прошлом году.

  Если аргумент функции — число интервалов (целочисленное значение) положителен, сдвиг вперед, а если он отрицателен, сдвиг назад.

  • Функция DAX — DATESBETWEEN вычисляет набор дат между указанной датой начала и датой окончания.

Четыре (4) функции DAX используются для перехода от набора дат в текущем контексте к новому набору дат.

Эти функции DAX более мощные, чем предыдущие.

Функции DAX — DATEADD, DATESINPERIOD и PARALLELPERIOD смещают некоторое количество временных интервалов из текущего контекста. Интервал может быть днем, месяцем, кварталом или годом, представленным ключевыми словами — ДЕНЬ, МЕСЯЦ, КВАРТАЛ и ГОД соответственно.

Например:

Сдвиг назад на 2 дня.

Продвинуться на 5 месяцев.

Двигайтесь вперед на один месяц с сегодняшнего дня.

Вернуться в тот же квартал в прошлом году.

Если аргумент функции — число интервалов (целочисленное значение) положителен, сдвиг вперед, а если он отрицателен, сдвиг назад.

Функция DAX — DATESBETWEEN вычисляет набор дат между указанной датой начала и датой окончания.

Sr.No.	Функция DAX и возвращаемое значение
1	DATEADD (Date_Column, Number_of_Intervals, Interval) Возвращает таблицу, содержащую столбец дат, сдвинутый во времени вперед или назад на указанное количество интервалов от дат в текущем контексте.
2	DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval) Возвращает таблицу, которая содержит столбец дат, который начинается с start_date и продолжается в течение указанного number_of_intervals.
3	PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval) Возвращает таблицу, которая содержит столбец дат, представляющий период, параллельный датам в указанном столбце Date_Column в текущем контексте, с датами, сдвинутыми на несколько интервалов вперед или назад во времени.
4	DATESBETWEEN (Date_Column, Start_Date, End_Date) Возвращает таблицу, которая содержит столбец дат, который начинается с start_date и продолжается до end_date.

DATEADD (Date_Column, Number_of_Intervals, Interval)

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

DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval)

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

PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval)

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

DATESBETWEEN (Date_Column, Start_Date, End_Date)

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

Функции DAX, которые оценивают выражения за период времени

Функции DAX в этой категории оценивают выражение в течение определенного периода времени.

В этой категории девять (9) функций DAX —

• Три (3) функции DAX в этой категории могут использоваться для оценки любого данного выражения в течение определенного периода времени.

Три (3) функции DAX в этой категории могут использоваться для оценки любого данного выражения в течение определенного периода времени.

Sr.No.	Функция DAX и возвращаемое значение
1	TOTALMTD (выражение, дата_колонка, [SetFilter]) Оценивает значение выражения для дат в месяце до даты в текущем контексте.
2	TOTALQTD (выражение, дата_колонка, [SetFilter]) Оценивает значение выражения для дат в квартале до даты в текущем контексте.
3	TOTALYTD (Выражение, Date_Column, [SetFilter], [YE_Date]) Оценивает значение выражения для дат в году до даты в текущем контексте

TOTALMTD (выражение, дата_колонка, [SetFilter])

Оценивает значение выражения для дат в месяце до даты в текущем контексте.

TOTALQTD (выражение, дата_колонка, [SetFilter])

Оценивает значение выражения для дат в квартале до даты в текущем контексте.

TOTALYTD (Выражение, Date_Column, [SetFilter], [YE_Date])

Оценивает значение выражения для дат в году до даты в текущем контексте

• Шесть (6) функций DAX в этой категории могут использоваться для расчета начального и конечного сальдо.

  • Начальное сальдо за любой период совпадает с начальным сальдо за предыдущий период.

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

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

• Момент времени, о котором мы заботимся, всегда является последним возможным значением даты в календарном периоде.

• Начальное сальдо основывается на последней дате предыдущего периода, тогда как конечное сальдо основывается на последней дате в текущем периоде.

• Текущий период всегда определяется последней датой в контексте текущей даты.

Шесть (6) функций DAX в этой категории могут использоваться для расчета начального и конечного сальдо.

Начальное сальдо за любой период совпадает с начальным сальдо за предыдущий период.

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

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

Момент времени, о котором мы заботимся, всегда является последним возможным значением даты в календарном периоде.

Начальное сальдо основывается на последней дате предыдущего периода, тогда как конечное сальдо основывается на последней дате в текущем периоде.

Текущий период всегда определяется последней датой в контексте текущей даты.

Sr.No.	Функция DAX и возвращаемое значение
1	OPENINGBALANCEMONTH (выражение, дата_колонка, [SetFilter]) Оценивает выражение по первой дате месяца в текущем контексте.
2	CLOSINGBALANCEMONTH (выражение, дата_колонка, [SetFilter]) Оценивает выражение в последнюю дату месяца в текущем контексте.
3	OPENINGBALANCEQUARTER (выражение, дата_колонка, [SetFilter]) Оценивает выражение на первую дату квартала в текущем контексте.
4	CLOSINGBALANCEQUARTER (выражение, дата_колонка, [SetFilter]) Оценивает выражение на последнюю дату квартала в текущем контексте.
5	OPENINGBALANCEYEAR (Выражение, Date_Column, [SetFilter], [YE_Date]) Оценивает выражение в первую дату года в текущем контексте.
6	CLOSINGBALANCEYEAR (выражение, Date_Column, [SetFilter], [YE_Date]) Оценивает выражение в последнюю дату года в текущем контексте.

OPENINGBALANCEMONTH (выражение, дата_колонка, [SetFilter])

Оценивает выражение по первой дате месяца в текущем контексте.

CLOSINGBALANCEMONTH (выражение, дата_колонка, [SetFilter])

Оценивает выражение в последнюю дату месяца в текущем контексте.

OPENINGBALANCEQUARTER (выражение, дата_колонка, [SetFilter])

Оценивает выражение на первую дату квартала в текущем контексте.

CLOSINGBALANCEQUARTER (выражение, дата_колонка, [SetFilter])

Оценивает выражение на последнюю дату квартала в текущем контексте.

OPENINGBALANCEYEAR (Выражение, Date_Column, [SetFilter], [YE_Date])

Оценивает выражение в первую дату года в текущем контексте.

CLOSINGBALANCEYEAR (выражение, Date_Column, [SetFilter], [YE_Date])

Оценивает выражение в последнюю дату года в текущем контексте.

Excel DAX — Функции фильтра

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

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

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

Функции фильтра DAX

Ниже приведены функции фильтра DAX —

Sr.No.	Функция DAX и что делает функция?
1	ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>]…, <таблица>, <groupingColumn>, [<groupingColumn>]…, [filterTable]…) Добавляет комбинации элементов из нескольких столбцов в таблицу, если они еще не существуют. Определение того, какие комбинации элементов добавить, основано на ссылках на исходные столбцы, которые содержат все возможные значения для столбцов. Чтобы определить комбинации элементов из разных столбцов для оценки — AutoExist применяется для столбцов в одной таблице. CrossJoin применяется в разных таблицах.
2	ВСЕ ({<таблица> | <столбец>, [<столбец>], [<столбец>],…}) Возвращает все строки в данной таблице или все значения в указанных столбцах таблицы, игнорируя все фильтры, которые могли быть применены. Эта функция полезна для очистки фильтров и создания вычислений для всех строк в таблице.
3	ALLEXCEPT (<таблица>, <столбец>, [<столбец>],…) Удаляет все контекстные фильтры в таблице, кроме фильтров, которые были применены к столбцам, указанным в качестве аргументов. В отличие от ALL, вы можете использовать эту функцию, когда хотите удалить фильтры для многих, но не для всех столбцов в таблице.
4	ALLNOBLANKROW (<таблица> | <столбец>) Из родительской таблицы отношений возвращает — все строки, кроме пустых строк, или все отдельные значения столбца, кроме пустых строк Функция игнорирует любые фильтры контекста, которые могут существовать.
5	ALLSELECTED ([<tableName> | <columnName>]) Удаляет контекстные фильтры из столбцов и строк в текущем запросе, сохраняя при этом все остальные контекстные фильтры или явные фильтры.
6	РАСЧЕТ (<выражение>, [<фильтр1>, <фильтр2>…)] Оценивает выражение в контексте, который изменяется указанными фильтрами. Возвращает значение, являющееся результатом выражения.
7	CALCULATETABLE (<выражение>, <фильтр1>, <фильтр2>,…) Оценивает табличное выражение в контексте, измененном данными фильтрами. Возвращает таблицу значений.
8	CROSSFILTER (<columnName1>, <columnName2>, <direction>) Определяет направление перекрестной фильтрации, которое будет использоваться в расчете для отношения, существующего между двумя столбцами. Не возвращает никакого значения.
9	DISTINCT (<столбец>) Возвращает таблицу с одним столбцом, которая содержит значения, отличные от указанного столбца. Другими словами, повторяющиеся значения удаляются и возвращаются только уникальные значения. Результирующий столбец используется в качестве аргумента в другой функции DAX.
10	РАНЬШЕ (<колонка>, <число>) Возвращает текущее значение указанного столбца во внешнем проходе оценки указанного столбца, указанного числом.
11	РАННЕЕ (<колонка>) Возвращает текущее значение указанного столбца во внешнем проходе оценки указанного столбца.
12	ФИЛЬТР (<таблица>, <фильтр>) Возвращает таблицу, которая содержит только отфильтрованные строки. FILTER используется только как функция, которая встроена в другие функции, для которых в качестве аргумента требуется таблица.
13	ФИЛЬТРЫ (<columnName>) Возвращает значения, которые непосредственно применяются в качестве фильтров к columnName. FILTERS используется только как функция, которая встроена в другие функции, для которых в качестве аргумента требуется таблица.
14	HASONEFILTER (<columnName>) Возвращает значение ИСТИНА, если число напрямую отфильтрованных значений для columnName равно единице. В противном случае возвращает FALSE.
15	HASONEVALUE (<columnName>) Возвращает TRUE, когда контекст для columnName был отфильтрован только до одного отдельного значения. В противном случае возвращает FALSE.
16	ISCROSSFILTERED (<columnName>) Возвращает TRUE, когда columnName или другой столбец в той же или связанной таблице фильтруется.
17	ISFILTERED (<columnName>) Возвращает TRUE, когда columnName фильтруется напрямую. Если в столбце нет фильтра или если фильтрация происходит из-за того, что другой столбец в той же таблице или в связанной таблице фильтруется, то функция возвращает FALSE.
18	KEEPFILTERS (<выражение>) Изменяет способ применения фильтров при оценке функции CALCULATE или CALCULATETABLE.
19	СВЯЗАННЫЕ (<колонка>) Возвращает связанное значение из другой таблицы.
20	RELATEDTABLE (<tableName>) Оценивает табличное выражение в контексте, измененном данными фильтрами.
21	SUBSTITUTEWITHINDEX (<таблица>, <indexColumnName>, <indexColumnTable>, <orderBy_expression>, [<order>]) Возвращает таблицу, которая представляет левое полусоединение двух таблиц, представленных в качестве аргументов. Полусоединение выполняется с использованием общих столбцов, определяемых общими именами столбцов и общим типом данных. Столбцы, к которым присоединяются, заменяются одним столбцом в возвращаемой таблице, который имеет тип integer и содержит индекс. Индекс представляет собой ссылку на правую таблицу соединений с заданным порядком сортировки.
22	USERELATIONSHIP (<columnName1>, <columnName2>) Указывает отношение, которое будет использоваться в конкретном вычислении, как существующее между columnName1 и columnName2.
23	VALUES (<TableNameOrColumnName>) Возвращает таблицу с одним столбцом, которая содержит значения, отличные от указанной таблицы или столбца. Другими словами, повторяющиеся значения удаляются и возвращаются только уникальные значения.

ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>]…, <таблица>, <groupingColumn>, [<groupingColumn>]…, [filterTable]…)

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

Чтобы определить комбинации элементов из разных столбцов для оценки —

ВСЕ ({<таблица> | <столбец>, [<столбец>], [<столбец>],…})

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

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

ALLEXCEPT (<таблица>, <столбец>, [<столбец>],…)

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

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

ALLNOBLANKROW (<таблица> | <столбец>)

Из родительской таблицы отношений возвращает —

Функция игнорирует любые фильтры контекста, которые могут существовать.

ALLSELECTED ([<tableName> | <columnName>])

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

РАСЧЕТ (<выражение>, [<фильтр1>, <фильтр2>…)]

Оценивает выражение в контексте, который изменяется указанными фильтрами.

Возвращает значение, являющееся результатом выражения.

CALCULATETABLE (<выражение>, <фильтр1>, <фильтр2>,…)

Оценивает табличное выражение в контексте, измененном данными фильтрами.

Возвращает таблицу значений.

CROSSFILTER (<columnName1>, <columnName2>, <direction>)

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

DISTINCT (<столбец>)

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

Результирующий столбец используется в качестве аргумента в другой функции DAX.

РАНЬШЕ (<колонка>, <число>)

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

РАННЕЕ (<колонка>)

Возвращает текущее значение указанного столбца во внешнем проходе оценки указанного столбца.

ФИЛЬТР (<таблица>, <фильтр>)

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

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

ФИЛЬТРЫ (<columnName>)

Возвращает значения, которые непосредственно применяются в качестве фильтров к columnName.

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

HASONEFILTER (<columnName>)

Возвращает значение ИСТИНА, если число напрямую отфильтрованных значений для columnName равно единице. В противном случае возвращает FALSE.

HASONEVALUE (<columnName>)

Возвращает TRUE, когда контекст для columnName был отфильтрован только до одного отдельного значения. В противном случае возвращает FALSE.

ISCROSSFILTERED (<columnName>)

Возвращает TRUE, когда columnName или другой столбец в той же или связанной таблице фильтруется.

ISFILTERED (<columnName>)

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

KEEPFILTERS (<выражение>)

Изменяет способ применения фильтров при оценке функции CALCULATE или CALCULATETABLE.

СВЯЗАННЫЕ (<колонка>)

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

RELATEDTABLE (<tableName>)

Оценивает табличное выражение в контексте, измененном данными фильтрами.

SUBSTITUTEWITHINDEX (<таблица>, <indexColumnName>, <indexColumnTable>, <orderBy_expression>, [<order>])

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

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

Столбцы, к которым присоединяются, заменяются одним столбцом в возвращаемой таблице, который имеет тип integer и содержит индекс.

Индекс представляет собой ссылку на правую таблицу соединений с заданным порядком сортировки.

USERELATIONSHIP (<columnName1>, <columnName2>)

Указывает отношение, которое будет использоваться в конкретном вычислении, как существующее между columnName1 и columnName2.

VALUES (<TableNameOrColumnName>)

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

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

Excel DAX — Сценарии

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

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

Выполнение сложных расчетов

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

• Создайте пользовательские расчеты для сводной таблицы.
• Примените фильтр к формуле.
• Удалить фильтры выборочно, чтобы создать динамическое соотношение.
• Используйте значение из внешнего цикла.

Подробнее см. Главу «Сценарии — выполнение сложных вычислений».

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

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

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

Подробнее см. Главу «Сценарии — работа с текстом и датами».

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

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

• Создайте значение на основе условия.
• Проверка на ошибки в формуле.

Подробнее см. Главу «Сценарии — условные значения и проверка на ошибки».

Использование Time Intelligence

Вы узнали о функциях DAX Time Intelligence в главе — Понимание DAX Time Intelligence.

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

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

• Рассчитать совокупные продажи.
• Сравните значения с течением времени.
• Рассчитать значение за пользовательский диапазон дат.

Подробнее см. Главу «Сценарии — использование анализа времени».

Ранжирование и сравнение ценностей

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

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

У каждого из этих вариантов есть свои плюсы и минусы.

Подробнее см. В разделе Сценарии — ранжирование и сравнение значений.

Excel DAX — выполнение сложных расчетов

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

• Создайте пользовательские расчеты для сводной таблицы.
• Примените фильтр к формуле DAX.
• Удалить фильтры выборочно, чтобы создать динамическое соотношение.
• Используйте значение из внешнего цикла.

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

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

РАСЧЕТ Функция

РАСЧЕТ (<выражение>, [<фильтр1>], [<фильтр2>]…)

Функция CALCULATE оценивает данное выражение в контексте, который изменен нулем или более указанными фильтрами.

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

пример

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

• Определите вычисляемое поле — процент количества медалей, как показано на следующем снимке экрана.

Определите вычисляемое поле — процент количества медалей, как показано на следующем снимке экрана.

С помощью этой формулы DAX все строки в таблице Results учитываются в функции CALCULATE с фильтром, содержащим функцию ALL. Таким образом, у вас есть общее количество в знаменателе.

Ваша сводная таблица будет такой, как показано на следующем снимке экрана.

На приведенном выше снимке экрана «Страна» отфильтрована в США, и в сводной таблице отображаются 18 лучших значений. Далее вы можете динамически фильтровать значения в сводной таблице. Тем не менее, расчеты будут правильными по пользовательской формуле DAX, которую вы использовали.

Функция CALCULATETABLE принимает таблицу значений и выполняет то же действие, что и функция CALCULATE.

Фильтрация данных в формулах

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

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

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

пример

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

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

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

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

Подробнее об этих функциях DAX см. Главу «Функции фильтра DAX».

Добавление и удаление фильтров динамически

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

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

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

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

Excel DAX — Работа с текстом и датами

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

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

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

Модель данных в PowerPivot допускает только один ключевой столбец. Он не поддерживает составные ключи, которые вы можете найти во внешних источниках данных. Следовательно, если какие-либо составные ключи существуют в таблице в источнике данных, вам необходимо объединить их в один ключевой столбец для таблицы в модели данных.

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

= CONCATENATE ([Column1], [Column2]) 

Функция DAX CONCATENATE принимает только два аргумента. Если какой-либо из аргументов не является текстовым типом данных, он будет преобразован в текстовый. Функция DAX CONCATENATE возвращает объединенную строку.

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

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

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

Вы можете использовать следующие функции DAX для извлечения и составления дат —

ДАТА — возвращает указанную дату в формате datetime.

DATEVALUE — конвертирует дату в виде текста в дату в формате datetime.

TIMEVALUE — Преобразует время в текстовом формате в время в формате datetime.

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

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

FORMAT (<value>, <format_string>) 

Функция FORMAT возвращает строку, содержащую значение, отформатированное в соответствии с определением format_string.

Вы можете использовать предварительно определенные форматы даты и времени или создать пользовательские форматы даты и времени для аргумента format_string функции FORMAT.

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

С. Нет.	Format_String & Description
1	«Общая дата» Отображает дату и / или время. Например, 10.02.2015, 10:10:32
2	«Длинная дата» или «Средняя дата» Отображает дату в соответствии с длинным форматом даты. Например, среда, 7 марта 2016 г.
3	«Короткое свидание» Отображает дату, используя краткий формат даты. Например, 2/03/2016
4	«Много времени» Отображает время, используя длительный формат времени. Обычно включает часы, минуты и секунды. Например, 10:10:32
5	«Среднее время» Отображает время в 12-часовом формате. Например, 9:30 вечера
6	«Короткое время» Отображает время в 24-часовом формате. Например, 14:15

«Общая дата»

Отображает дату и / или время. Например, 10.02.2015, 10:10:32

«Длинная дата» или «Средняя дата»

Отображает дату в соответствии с длинным форматом даты. Например, среда, 7 марта 2016 г.

«Короткое свидание»

Отображает дату, используя краткий формат даты. Например, 2/03/2016

«Много времени»

Отображает время, используя длительный формат времени.

Обычно включает часы, минуты и секунды.

Например, 10:10:32

«Среднее время»

Отображает время в 12-часовом формате.

Например, 9:30 вечера

«Короткое время»

Отображает время в 24-часовом формате.

Например, 14:15

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

С. Нет.	Характер и описание
1	: Разделитель времени Разделитель времени Разделяет часы, минуты и секунды при форматировании значений времени.
2	/ Разделитель даты. Разделяет день, месяц и год форматирования значений даты.
3	% Используется для обозначения того, что следующий символ должен читаться как однобуквенный формат безотносительно к последним буквам Также используется для указания того, что однобуквенный формат читается как определенный пользователем формат.

:

Разделитель времени

Разделитель времени Разделяет часы, минуты и секунды при форматировании значений времени.

/

Разделитель даты.

Разделяет день, месяц и год форматирования значений даты.

%

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

Ниже приведены данные различных персонажей.

• % d — отображает день в виде числа без начального нуля (например, 5).

• % dd — отображает день в виде числа с начальным нулем (например, 05).

• % ddd — отображает день в виде сокращения (например, вс).

• % dddd — отображает день как полное имя (например, воскресенье).

• % M — отображает месяц в виде числа без начального нуля (например, январь представлен как 1).

• % MM — отображает месяц в виде числа с начальным нулем (например, январь представлен как 01).

• % MMM — отображает месяц как сокращение (например, январь представлен как январь).

• % MMMM — отображает месяц как полное название месяца (например, январь).

• % gg — отображает строку периода / эры (например, AD).

• % h — Отображение часа в виде числа без начальных нулей с использованием 12-часовых часов (например, 1:15:15 PM). Используйте % h, если это единственный символ в вашем пользовательском числовом формате.

• % чч — отображает час в виде числа с ведущими нулями, используя 12-часовые часы (например, 01:15:15 PM).

• % H — Отображение часа в виде числа без начальных нулей с использованием 24-часовых часов (например, 13:15:15, 1:15:15). Используйте% H, если это единственный символ в вашем пользовательском числовом формате.

• % ЧЧ — отображает часы в виде числа с ведущими нулями, используя 24-часовые часы (например, 13:15:15, 1:15:15).

• % m — отображает минуты в виде числа без начальных нулей (например, 2: 1: 15). Используйте% m, если это единственный символ в вашем пользовательском числовом формате.

• % mm — отображает минуты в виде числа с ведущими нулями (например, 2:01:15).

• % s — отображает секунду как число без начальных нулей (например, 2: 15: 5). Используйте% s, если это единственный символ в вашем пользовательском числовом формате.

• % ss — отображает секунду как число с ведущими нулями (например, 2:15:05).

• % f — отображает доли секунд. Например, ff отображает сотые доли секунды, тогда как ffff отображает десятитысячные доли секунды. Вы можете использовать до семи символов f в своем пользовательском формате. Используйте % f, если это единственный символ в вашем пользовательском числовом формате.

• % t — использует 12-часовые часы и отображает заглавную букву A в течение любого часа до полудня; отображает заглавную букву P в течение любого часа между полуднем и 23:59. Используйте% t, если это единственный символ в пользовательском числовом формате.

• % tt — для регионов, в которых используются 12-часовые часы, отображается заглавная буква AM с любым часом до полудня; отображает верхний регистр PM с любым часом между полуднем и 23:59. Для локалей, в которых используются 24-часовые часы, ничего не отображается.

• % y — отображает номер года (0-9) без начальных нулей. Используйте % y, если это единственный символ в пользовательском числовом формате.

• % yy — отображает год в двузначном числовом формате с начальным нулем, если это применимо.

• % yyy — отображает год в четырехзначном числовом формате.

• % yyyy — отображает год в четырехзначном числовом формате.

• % z — отображает смещение часового пояса без начального нуля (например, -8). Используйте % z, если это единственный символ в вашем пользовательском числовом формате.

• % zz — отображает. смещение часового пояса с ведущим нулем (например, -08)

• % zzz — отображает полное смещение часового пояса (например, -08: 00).

% d — отображает день в виде числа без начального нуля (например, 5).

% dd — отображает день в виде числа с начальным нулем (например, 05).

% ddd — отображает день в виде сокращения (например, вс).

% dddd — отображает день как полное имя (например, воскресенье).

% M — отображает месяц в виде числа без начального нуля (например, январь представлен как 1).

% MM — отображает месяц в виде числа с начальным нулем (например, январь представлен как 01).

% MMM — отображает месяц как сокращение (например, январь представлен как январь).

% MMMM — отображает месяц как полное название месяца (например, январь).

% gg — отображает строку периода / эры (например, AD).

% h — Отображение часа в виде числа без начальных нулей с использованием 12-часовых часов (например, 1:15:15 PM). Используйте % h, если это единственный символ в вашем пользовательском числовом формате.

% чч — отображает час в виде числа с ведущими нулями, используя 12-часовые часы (например, 01:15:15 PM).

% H — Отображение часа в виде числа без начальных нулей с использованием 24-часовых часов (например, 13:15:15, 1:15:15). Используйте% H, если это единственный символ в вашем пользовательском числовом формате.

% ЧЧ — отображает часы в виде числа с ведущими нулями, используя 24-часовые часы (например, 13:15:15, 1:15:15).

% m — отображает минуты в виде числа без начальных нулей (например, 2: 1: 15). Используйте% m, если это единственный символ в вашем пользовательском числовом формате.

% mm — отображает минуты в виде числа с ведущими нулями (например, 2:01:15).

% s — отображает секунду как число без начальных нулей (например, 2: 15: 5). Используйте% s, если это единственный символ в вашем пользовательском числовом формате.

% ss — отображает секунду как число с ведущими нулями (например, 2:15:05).

% f — отображает доли секунд. Например, ff отображает сотые доли секунды, тогда как ffff отображает десятитысячные доли секунды. Вы можете использовать до семи символов f в своем пользовательском формате. Используйте % f, если это единственный символ в вашем пользовательском числовом формате.

% t — использует 12-часовые часы и отображает заглавную букву A в течение любого часа до полудня; отображает заглавную букву P в течение любого часа между полуднем и 23:59. Используйте% t, если это единственный символ в пользовательском числовом формате.

% tt — для регионов, в которых используются 12-часовые часы, отображается заглавная буква AM с любым часом до полудня; отображает верхний регистр PM с любым часом между полуднем и 23:59. Для локалей, в которых используются 24-часовые часы, ничего не отображается.

% y — отображает номер года (0-9) без начальных нулей. Используйте % y, если это единственный символ в пользовательском числовом формате.

% yy — отображает год в двузначном числовом формате с начальным нулем, если это применимо.

% yyy — отображает год в четырехзначном числовом формате.

% yyyy — отображает год в четырехзначном числовом формате.

% z — отображает смещение часового пояса без начального нуля (например, -8). Используйте % z, если это единственный символ в вашем пользовательском числовом формате.

% zz — отображает. смещение часового пояса с ведущим нулем (например, -08)

% zzz — отображает полное смещение часового пояса (например, -08: 00).

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

Изменение типов данных выходов формулы DAX

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

Использование неявных преобразований типов данных

• Чтобы преобразовать дату или числовую строку в число, умножьте на 1,0. Например, = (СЕГОДНЯ () + 5) * 1.0. Эта формула вычисляет текущую дату плюс 5 дней и преобразует результат в целочисленное значение.

  • Чтобы преобразовать дату, число или значение валюты в строку, объедините значение с пустой строкой. Например, = Сегодня () & «»

Чтобы преобразовать дату или числовую строку в число, умножьте на 1,0. Например, = (СЕГОДНЯ () + 5) * 1.0. Эта формула вычисляет текущую дату плюс 5 дней и преобразует результат в целочисленное значение.

Чтобы преобразовать дату, число или значение валюты в строку, объедините значение с пустой строкой. Например, = Сегодня () & «»

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

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

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

Вы узнаете это в следующих разделах.

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

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

ROUND (<число>, <num_digits>) — округляет число до указанного числа цифр и возвращает десятичное число.

CEILING (<число>, <значимость>) — округляет число до ближайшего целого или кратного значению и возвращает десятичное число.

FLOOR (<число>, <значимость>) — округляет число вниз до нуля, до ближайшего кратного значения и возвращает десятичное число.

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

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

ИСПРАВЛЕНО (<number>, [<decimals>], [<no_comma>]) — округляет число и возвращает результат в виде текста. Количество цифр справа от десятичной точки равно 2 или указанному количеству десятичных знаков. Результат с запятыми или опционально без запятых.

FORMAT (<value>, <format_string>) — преобразует значение в текст в соответствии с указанным форматом.

Вы уже узнали об использовании функции Format для преобразования дат в строки.

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

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

VALUE (<текст>) — преобразует текстовую строку, представляющую число, в число.

DATEVALUE (date_text) — преобразует дату в виде текста в дату в формате datetime.

TIMEVALUE (time_text) — преобразует время в текстовом формате во время в формате datetime.

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

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

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

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

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

IF (<логический_тест>, <value_if_true>, [<value_if_false>]) — проверяет, выполняется ли условие. Возвращает одно значение, если условие TRUE, и возвращает другое значение, если условие FALSE. Value_if_false является необязательным, и если оно опущено, а условие имеет значение FALSE, функция возвращает BLANK ().

ИЛИ (<логический1>, <логический2>) — проверяет, является ли один из аргументов ИСТИНА для возврата ИСТИНА. Функция возвращает FALSE, если оба аргумента являются FALSE.

CONCATENATE (<text1>, <text2>) — объединяет две текстовые строки в одну текстовую строку. Присоединяемые элементы могут быть текстом, числами или логическими значениями, представленными в виде текста или комбинации этих элементов. Вы также можете использовать ссылку на столбец, если столбец содержит соответствующие значения.

Тестирование на ошибки в формуле DAX

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

Некоторые распространенные ошибки в формулах DAX:

• Деление на ноль.
• Аргумент функции является пустым, в то время как ожидаемый аргумент является числовым значением.

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

ISBLANK (<значение>) — проверяет, является ли значение пустым, и возвращает значение ИСТИНА или ЛОЖЬ.

IFERROR (value, value_if_error) — возвращает значение value_if_error, если выражение в первом аргументе приводит к ошибке. В противном случае возвращает значение самого выражения.

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

Excel DAX — Использование Time Intelligence

Вы узнали о мощной функции DAX Time Intelligence в главе — Понимание Time Intelligence. В этой главе вы узнаете, как использовать функции анализа времени DAX в различных сценариях.

Функции разведки времени DAX включают в себя —

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

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

• Функции, которые получают первую и последнюю дату указанного периода.

• Функции, которые помогут вам работать с начальным и конечным балансами.

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

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

Функции, которые получают первую и последнюю дату указанного периода.

Функции, которые помогут вам работать с начальным и конечным балансами.

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

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

CLOSINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>]) — оценивает выражение в последнюю дату месяца в текущем контексте.

OPENINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>]) — оценивает выражение в первую дату месяца в текущем контексте.

CLOSINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>]) — оценивает выражение на последнюю дату квартала в текущем контексте.

OPENINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>]) — оценивает выражение на первую дату квартала в текущем контексте.

CLOSINGBALANCEYEAR (<expression>, <date>, [<filter>], [<year_end_date>]) — оценивает выражение в последнюю дату года в текущем контексте.

OPENINGBALANCEYEAR (<expression>, <date>, <filter>], [<year_end_date>]) — оценивает выражение в первую дату года в текущем контексте.

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

 Стоимость инвентаризации на начало месяца: = OPENINGBALANCEMONTH ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

 Стоимость запасов на конец месяца: = CLOSINGBALANCEMONTH ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 )

 Значение инвентаризации на начало квартала: = OPENINGBALANCEQUARTER ( 
    SUMX ProductInventory, (ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

 Значение запаса на конец квартала: = CLOSINGBALANCEQUARTER ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

 Стоимость инвентаризации на начало года: = OPENINGBALANCEYEAR ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 )

 Стоимость запасов на конец года: = CLOSINGBALANCEYEAR ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

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

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

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

• PREVIOUSMONTH (<даты>) — возвращает таблицу, которая содержит столбец всех дат предыдущего месяца на основе первой даты в столбце дат в текущем контексте.

• PREVIOUSQUARTER (<даты>) — возвращает таблицу, которая содержит столбец всех дат предыдущего квартала на основе первой даты в столбце дат в текущем контексте.

• PREVIOUSYEAR (<date>, <year_end_date>]) — возвращает таблицу, которая содержит столбец всех дат за предыдущий год с учетом последней даты в столбце дат в текущем контексте.

PREVIOUSMONTH (<даты>) — возвращает таблицу, которая содержит столбец всех дат предыдущего месяца на основе первой даты в столбце дат в текущем контексте.

PREVIOUSQUARTER (<даты>) — возвращает таблицу, которая содержит столбец всех дат предыдущего квартала на основе первой даты в столбце дат в текущем контексте.

PREVIOUSYEAR (<date>, <year_end_date>]) — возвращает таблицу, которая содержит столбец всех дат за предыдущий год с учетом последней даты в столбце дат в текущем контексте.

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

 Продажи за предыдущий месяц: = РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
 ) 

 Продажи за предыдущий квартал: = РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
 ) 

 Продажи за предыдущий год: = РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
 ) 

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

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

PARALLELPERIOD (<даты>, <number_of_intervals>, <интервал>)

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

Вы можете создать следующее вычисляемое поле для расчета продаж за предыдущий год в Западном регионе —

 Продажи за предыдущий год: = РАСЧЕТ ( 
    SUM (West_Sales [SalesAmount]), PARALLELPERIOD (DateTime [DateKey], - 1, год)
 ) 

Расчет промежуточных итогов

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

• TOTALMTD (<выражение>, <даты>, [<фильтр>]) — оценивает значение выражения для месяца к дате в текущем контексте.

• TOTALQTD (<выражение>, <даты>, <фильтр>]) — оценивает значение выражения для дат в квартале от даты в текущем контексте.

• TOTALYTD (<expression>, <date>, [<filter>], [<year_end_date>]) — оценивает текущее значение выражения в текущем контексте.

TOTALMTD (<выражение>, <даты>, [<фильтр>]) — оценивает значение выражения для месяца к дате в текущем контексте.

TOTALQTD (<выражение>, <даты>, <фильтр>]) — оценивает значение выражения для дат в квартале от даты в текущем контексте.

TOTALYTD (<expression>, <date>, [<filter>], [<year_end_date>]) — оценивает текущее значение выражения в текущем контексте.

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

Сумма за месяц: = TOTALMTD (SUM (West_Sales [SalesAmount]), DateTime [DateKey])

Сумма за квартал: = TOTALQTD (SUM (WestSales [SalesAmount]), DateTime [DateKey])

Сумма за год: = TOTALYTD (SUM (WestSales [SalesAmount]), DateTime [DateKey])

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

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

DATESINPERIOD (<date>, <start_date>, <number_of_intervals>, <interval>) — возвращает таблицу, которая содержит столбец дат, который начинается с start_date и продолжается для указанного number_of_intervals.

DATESBETWEEN (<date>, <start_date>, ) — Возвращает таблицу, которая содержит столбец дат, который начинается с даты start_date и продолжается до даты end_date.

DATEADD (<даты>, <number_of_intervals>, <интервал>) — возвращает таблицу, содержащую столбец дат, сдвинутых во времени вперед или назад на указанное количество интервалов от дат в текущем контексте.

FIRSTDATE (<даты>) — возвращает первую дату в текущем контексте для указанного столбца дат.

LASTDATE (<даты>) — возвращает последнюю дату в текущем контексте для указанного столбца дат.

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

• DAX Formula для расчета продаж за 15 дней до 17 июля 2016 года.

DAX Formula для расчета продаж за 15 дней до 17 июля 2016 года.

 РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), DATESINPERIOD (DateTime [DateKey], DATE (2016,17,14), -15, день)
 ) 

• Формула DAX для создания вычисляемого поля, которое рассчитывает продажи в первом квартале 2016 года.

Формула DAX для создания вычисляемого поля, которое рассчитывает продажи в первом квартале 2016 года.

 = РАСЧЕТ (
    SUM (WestSales [SalesAmount]), DATESBETWEEN (DateTime [DateKey], DATE (2016,1,1), DATE (2016,3,31))
 )

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

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

 = FIRSTDATE (WestSales [SaleDateKey]) 

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

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

 = LASTDATE (WestSales [SaleDateKey]) 

• Формула DAX для расчета дат за один год до дат в текущем контексте.

Формула DAX для расчета дат за один год до дат в текущем контексте.

 = DATEADD (DateTime [DateKey], - 1, год) 

Excel DAX — ранжирование и сравнение ценностей

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

• Вы можете выбрать количество верхних значений в сводной таблице.

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

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

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

Применение фильтра для показа только самых верхних элементов

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

• Нажмите стрелку вниз в заголовке меток строк в сводной таблице.
• Нажмите «Фильтры значений» в раскрывающемся списке, а затем нажмите «Топ 10».

Откроется диалоговое окно Top 10 Filter (<имя столбца>).

• Под Показать выберите следующее в полях слева направо.
  • верхний
  • 18 (Количество верхних значений, которые вы хотите отобразить. По умолчанию — 10.)
  • Предметы.
  • В поле by выберите Medal Count.

• Нажмите ОК. Лучшие 18 значений будут отображены в сводной таблице.

Нажмите ОК. Лучшие 18 значений будут отображены в сводной таблице.

Преимущества и недостатки применения фильтра

преимущества

• Это просто и удобно в использовании.
• Подходит для столов с большим количеством рядов.

Недостатки

• Фильтр предназначен исключительно для отображения.

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

Фильтр предназначен исключительно для отображения.

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

Создание формулы DAX, которая динамически оценивает значения

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

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

• Нулевое значение для самого высокого значения в таблице.

• Равные значения будут иметь одинаковое значение ранга. Если n значений равно, следующее значение после равных значений будет иметь непоследовательное значение ранга, складывающее число n .

Нулевое значение для самого высокого значения в таблице.

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

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

= COUNTROWS (FILTER (Sales,  
   EARLIER (Sales [Sales Amount]) < Sales [Sales Amount])
) + 1 

Затем вы можете вставить слайсер в новый вычисляемый столбец и выборочно отображать значения по рангу.

Преимущества и недостатки динамических рангов

преимущества

• Ранжирование производится в таблице, а не в сводной таблице. Следовательно, может использоваться любое количество сводных таблиц.

• Формулы DAX рассчитываются динамически. Следовательно, вы всегда можете быть уверены в правильности ранжирования, даже если базовые данные изменились.

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

• Подходит для столов с большим количеством рядов.

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

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

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

Подходит для столов с большим количеством рядов.

Недостатки

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