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, созданными до внесения изменений в данные.

Если вы обеспечите ссылочную целостность, вы можете предотвратить следующие ловушки —

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

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

Удаление строк из первичной таблицы при наличии совпадающих значений данных в строках связанной таблицы.