Что можно создать с помощью формул dax
Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
Полный справочник основных функций (формул) DAX для Power BI и Power Pivot на русском языке
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. Я часто встречаю вопросы про функции и формулы в DAX (Power BI и Power Pivot). Многие пишут эти вопросы и не всегда правильно понимают что является функциями, а что формулами.
На самом деле все просто — функции, это то, из чего состоит сам DAX, а формулы — это тот код, который мы пишем на основе функции в Power BI или Power Pivot.
В следующей статье мы подробно поговорим о том, как правильно писать и форматировать формулы, чтобы затем их можно было легко читать и понимать, а сейчас, в данной статье мы остановимся на функциях, проведем их общий обзор и для удобства составим справочник функций DAX в Power BI и Power Pivot.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.
Функции (functions) в DAX
В прошлой статье, посвященной пониманию того, что такое DAX, я писал, что это функциональный язык, состоящий полностью из встроенных в него функций. И Вы, как его пользователь, не сможете создавать свои функции, но зато сможете создавать на основе этих встроенных функции свои формулы.
С одной стороны, мы имеем некую несвободу в связи с тем, что не можем создавать какие-то функции в DAX, но, с другой стороны, свобода сильно расширяется при составлении формул, ведь, создавая формулы, мы можем сочетать друг с другом встроенные функции, включая их многоуровневую вложенность друг в друга, и, на самом деле, в этом и заключается сила DAX.
Но, несмотря на то, что функции мы сами создавать не можем, год от года в DAX можно наблюдать появление совершенно новых функций. Так как разработчики не стоят на месте, постоянно улучшая и дорабатывая этот язык. Поэтому, DAX’ом год от года пользоваться все удобнее и интереснее.
В основе своей, все функции DAX можно разделить на несколько категорий:
Сами функции в обязательном порядке состоят из 2 частей:
Параметров у функции может быть от 1 до нескольких, а в некоторых случаях параметров может и не быть вовсе, просто пустые круглые скобки.
В качестве параметров можно указывать:
Если в функции параметров несколько, то в русской версии DAX все параметры друг от друга отделяются точкой с запятой, в английской версии — просто запятой.
Общая схема функции DAX: ИМЯ (Параметр 1; Параметр 2; …; Параметр N)
Частая проблема и ошибка при написании кода DAX — это пропуск точки с запятой между параметрами или отсутствие закрывающей скобки. Чтобы избежать этих частых ошибок, в следующей статье мы с Вами разберем необязательные правила форматирования кода DAX, которые помогут не только избежать вышеперечисленных проблем, но и улучшить понимание и чтение всего кода.
Итак, ниже представлен справочник из основных функций DAX. По мере описания каждой из функций на нашем блоге, в справочнике ниже будут появляться активные гиперссылки на соответствующие статьи по подробному описанию функции.
Полный справочник основных функций (формул) DAX для Power BI и Excel (Powerpivot) на русском языке
Справочник ниже содержит список функции для Power BI и Excel (Powerpivot), состоящий из их названия и краткого описания на русском. Если на нашем блоге появляется отдельная статья с подробным описанием функции, то в справочнике ниже у соответствующей функции будет активная гиперссылка.
На этом, в этой статье все, переходите к следующей статье, где мы обсудим правила форматирования кода DAX в Power BI и Power Pivot, которые Вам помогут писать, читать и понимать код легко и быстро.
Подробное ВИДЕО «Правила форматирования DAX функций в Power BI и Power Pivot»»
Ссылки из видео:
1) [Регистрируйтесь в бесплатном экспресс курсе] Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot: зарегистрироваться
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи? 
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
Справочник по функциям DAX
В этом справочнике по функциям DAX представлены подробные сведения, включая синтаксис, параметры, возвращаемые значения и примеры, для каждой из более чем 250 функций, используемых в формулах DAX.
Не все функции DAX поддерживаются или имеются в более ранних версиях Power BI Desktop, Analysis Services и Power Pivot в Excel.
Содержимое раздела
Новые функции DAX — это новые или существенно переработанные функции.
Статистические функции — эти функции вычисляют (скалярное) значение, например число, сумму, среднее, минимальное или максимальное значение для всех строк в столбце или таблице в соответствии с заданным выражением.
Функции даты и времени — эти функции в DAX похожи на функции даты и времени в Microsoft Excel. Однако функции DAX основаны на типах данных datetime, используемых в Microsoft SQL Server.
Функции фильтрации — эти функции возвращают определенные типы данных, ищут значения в связанных таблицах и выполняют фильтрацию по связанным значениям. Функции уточняющих запросов работают с таблицами и связями между ними. Функции фильтрации позволяют управлять контекстом данных для создания динамических вычислений.
Финансовые функции — эти функции используются в формулах, которые выполняют финансовые вычисления, такие как чистая приведенная стоимость и норма прибыли.
Информационные функции — эти функции просматривают таблицу или столбец, предоставленные в качестве аргумента в другую функцию, и сообщают, соответствует ли значение ожидаемому типу. Например, функция ISERROR возвращает значение TRUE, если значение, на которое указывает ссылка, содержит ошибку.
Логические функции — эти функции возвращают сведения о значениях в выражении. Например, функция TRUE позволяет определить, возвращает ли выражение значение TRUE.
Математические и тригонометрические функции — математические функции в DAX похожи на математические и тригонометрические функции Excel. Однако числовые типы данных, используемые в функциях DAX, немного отличаются.
Другие функции — эти функции выполняют уникальные действия, и поэтому их нельзя отнести к другим категориям.
Родительские и дочерние функции — эти функции помогают пользователям управлять данными, представленными иерархией «родители — потомки», в своих моделях.
Функции отношений — эти функции предназначены для использования связей между таблицами и управления ими. Например, можно указать определенную связь для использования в вычислении.
Статистические функции — такие функции вычисляют значения, связанные со статистическими распределениями и вероятностями, например стандартное отклонение и число перестановок.
Функции обработки таблиц — эти функции возвращают таблицу или обрабатывают существующие таблицы.
Функции работы с текстом — эти функции позволяют возвращать часть строки, выполнять поиск текста в строке или сцеплять строковые значения. Есть также дополнительные функции для управления форматами дат, времени и чисел.
Функции логики операций со временем — эти функции позволяют создавать вычисления с использованием встроенных знаний о календарях и датах. Используя диапазоны времени и дат в сочетании с агрегатами или вычислениями, можно производить осмысленные сравнения объемов продаж, запасов и других показателей за сопоставимые периоды времени.
Выражения анализа данных (DAX) в Power Pivot
Название «Язык выражений анализа данных» (DAX) поначалу немного пугает, но на самом деле все не так страшно. Основные принципы DAX просты для понимания. Прежде всего нужно уточнить, что DAX не является языком программирования. DAX — это язык формул. С помощью DAX можно определять настраиваемые вычисления для вычисляемые столбцы и меры (вычисляемые поля). В DAX предусмотрены некоторые функции, используемые в формулах Excel, а также дополнительные функции, предназначенные для работы с реляционными данными и выполнения динамического агрегирования.
Основные сведения о формулах DAX
Формулы DAX во многом схожи с формулами Excel. Для создания формулы DAX введите знак равенства и затем имя функции или выражения и необходимые значения или аргументы. Как и Excel, DAX поддерживает разнообразные функции, которые позволяют работать со строками, выполнять вычисления с использованием даты и времени или создавать условные значения.
Однако формулы DAX отличаются по следующим важным параметрам.
Если необходимо настроить вычисления на уровне строк, то DAX предусматривает функции, позволяющие использовать текущее значение строки или связанное с ним значение для вычислений, зависимых от контекста.
DAX включает тип функции, которая возвращает таблицу в виде результата, а не единичное значение. Эти функции также можно использовать для передачи данных другим функциям.
Функции аналитики времени в DAX можно использовать для вычислений диапазоны дат и сравнивать результаты по параллельным периодам.
Область применения формул DAX
В Power Pivot формулы можно создавать в вычисляемых с толбцах или вычисляемых п олях.
Вычисляемые столбцы
Вычисляемый столбец — это столбец, добавляемый в существующую таблицу Power Pivot. Вместо вставки или импорта значений в столбец создается формула DAX, которая определяет значения столбца. Если включить таблицу Power Pivot в сводную таблицу (или сводную диаграмму), вычисляемый столбец можно использовать так же, как любой другой столбец данных.
Формулы в вычисляемом столбце похожи на формулы, которые создаются в Excel. Однако в Excel нельзя создать другую формулу для разных строк таблицы. вместо этого формула DAX автоматически применяется во всем столбце.
Если столбец содержит формулу, значение вычисляется для каждой строки. Результаты вычисляются для столбца, как только создается формула. Значения столбца повторно вычисляются только при обновлении базовых данных или повторного пересчета вручную.
Вы можете создавать вычисляемые столбцы, основанные на мерах и других вычисляемом столбце. Однако не следует использовать одно и то же имя для вычисляемом столбца и меры, так как это может привести к путанице с результатами. При ссылке на столбец лучше использовать полную ссылку на него, чтобы предотвратить случайное обращение к мерам.
Дополнительные сведения см. в статье Вычисляемые столбцы в Power Pivot.
Мера — это формула, созданная специально для использования в сводная диаграмма, в Power Pivot данных. Меры могут быть основаны на стандартных агрегатных функциях, таких как СЧЁТ или СУММ, или вы можете определить собственную формулу с помощью DAX. Мера используется в области «Значения» в области «Значения» в одной из них. Если вы хотите разместить вычисляемые результаты в другой области pivotttable, используйте вычисляемый столбец.
Когда вы определяете формулу для явного измерения, ничего не происходит, пока вы не добавите меру в сотовую. При добавлении меры формула вычисляется для каждой ячейки в области Значения в pivotTable. Поскольку результат создается для каждого сочетания строк и столбцов, результат для меры может быть разным в каждой ячейке.
Определение создаемой меры будет сохранено в таблице исходных данных. Оно отображается в списке полей сводной таблицы и доступно для всех пользователей книги.
Дополнительную информацию см. в статье Меры в Power Pivot.
Создание формул с использованием строки формул
Power Pivot, как и Excel, включает строку формул, которая упрощает создание и изменение формул, а также функцию автозаполнения, ускоряющую ввод данных и уменьшающую число синтаксических ошибок.
Ввод имени таблицы Начните вводить имя таблицы. Автозавершение формул представляет собой список, содержащий допустимые имена, начинащиеся с этих букв.
Ввод имени столбца Введите скобку и выберите столбец из списка столбцов в текущей таблице. Для столбца из другой таблицы начните вводить первые буквы имени таблицы, а затем выберите столбец в списке Автозаполнеть.
Дополнительные сведения и пошаговые инструкции по построению формул см. в статье Создание формул для вычислений в Power Pivot.
Советы по использованию функции автозаполнения
Функцию автозаполнения формул можно использовать в середине существующей формулы со вложенными функциями. Текст, расположенный непосредственно перед точкой вставки, используется для отображения значений раскрывающегося списка, а остальной текст остается без изменений.
Определяемые имена, созданные для констант, не отображаются в раскрывающемся списке автозаполнения, однако их можно вводить с клавиатуры.
Power Pivot не добавляет закрываюю скобки функций и не автоматически соответствует скобкам. Необходимо убедиться, что каждая функция синтаксически правильна или вы не можете сохранить или использовать формулу.
Использование нескольких функций в одной формуле
Функции можно вкладывать, то есть использовать результаты одной функции в качестве аргумента другой функции. В вычисляемых столбцах поддерживается до 64 уровней вложенности функций. Тем не менее вложенность функций может затруднить создание формул и устранение в них ошибок.
Многие функции DAX предназначены для использования исключительно в качестве вложенных. Эти функции возвращают таблицу, которая не может быть непосредственно сохранена в качестве результата, но может быть передана табличной функции в качестве входного параметра. Например, функции SUMX, AVERAGEX и MINX в качестве первого аргумента требуют таблицу.
Примечание: В рамках мер существуют некоторые ограничения на вложенность функций, чтобы на производительность не повлияло множество вычислений, требующихся зависимостей между столбцами.
Сравнение функций DAX с функциями Excel
Библиотека функций DAX основана на библиотеке функций Excel, однако между этими библиотеками имеется много различий. В этом разделе кратко описаны сходства и различия между функциями Excel и функциями DAX.
Многие функции DAX имеют такие же имена, как функции Excel, и работают схожим образом, однако они принимают входные аргументы других типов и в некоторых случаях могут возвращать другие типы данных. Обычно функции DAX невозможно использовать в формуле Excel, а формулы Excel в — Power Pivot, не внося изменений.
Функции DAX никогда не принимают в качестве аргумента ссылку на ячейку или диапазон, но могут принимать в качестве аргумента столбец или таблицу.
Функции даты и времени DAX возвращают данные типа datetime. В противоположность этому функции даты и времени Excel возвращают целые числа, представляющие дату в виде порядкового номера.
Многие новые функции DAX возвращают таблицу значений или выполняют вычисления на основании входной таблицы значений. В противоположность этому в Excel нет функций, возвращающих таблицу, хотя некоторые функции могут работать с массивами. Простое создание ссылок на полные таблицы и столбцы является новой возможностью Power Pivot.
DAX предоставляет новые функции уточняющего запроса, аналогичные функциям поиска массива и вектора в Excel. Однако для функций DAX требуется, чтобы таблицы были связанными.
Предполагается, что данные в столбце всегда имеют один и тот же тип данных. Если в столбце представлены данные разных типов, DAX меняет тип данных всего столбца на тот, который наиболее полно соответствует всем значениям.
Типы данных DAX
Вы можете импортировать данные в модель данных Power Pivot из множества различных источников данных, которые поддерживают разные типы данных. При импорте или загрузке данных и последующем использовании их в вычислениях или в сводных таблицах данные преобразуются в один из типов данных Power Pivot. Список типов данных см. в статье Типы данных в моделях данных.
Тип данных table — это новый тип данных в DAX, которые используются в качестве входных или выходных данных для многих новых функций. Например, функция FILTER принимает в качестве входного аргумента таблицу и возвращает другую таблицу, которая содержит только строки, удовлетворяющие условиям фильтра. Объединение табличных и агрегатных функций позволяет выполнять сложные вычисления с динамически определяемыми наборами данных. Дополнительные сведения см. в разделе Агрегаты в Power Pivot.
Формулы и реляционная модель
Окно Power Pivot — это область, в которой можно работать с несколькими таблицами данных и связывать таблицы в реляционную модель. Внутри этой модели данных таблицы соединяются друг с другом связями, что позволяет создавать корреляции со столбцами в других таблицах и создавать более интересные вычисления. Например, можно создать формулы, которые суммируют значения для связанной таблицы, а затем сохранить полученное значение в одной ячейке. Можно также применить фильтры к таблицам и столбцам для управления строками из связанной таблицы. Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Поскольку таблицы можно связывать с помощью связей, ваши сводные таблицы также могут включать в себя данные из нескольких столбцов различных таблиц.
Однако поскольку формулы DAX могут работать с целыми таблицами и столбцами, необходимо строить вычисления не так, как это делается в Excel.
В большинстве случаев формула DAX в столбце всегда применяется ко всему набору значений в столбце (но никогда только к нескольким строкам или ячейкам).
Таблицы в Power Pivot всегда должны иметь одинаковое количество столбцов в каждой строке. Кроме того, все строки в столбце должны содержать данные одного типа.
Если таблицы соединены связью, нужно убедиться, что значения в двух столбцах, использующихся в качестве ключей, по большей части совпадают. Так как Power Pivot принудительно не обеспечивает целостность данных, можно создать связь даже при наличии несовпадающих значений в ключевом столбце. Тем не менее наличие пустых или несовпадающих значений может повлиять на результаты формул и вид сводных таблиц. Дополнительные сведения см. в статье Подстановка в формулах Power Pivot.
При соединении таблиц с помощью связей увеличивается область или к онтекст, в котором вычисляются формулы. Например, на формулы в сводной таблице могут влиять любые фильтры или заголовки столбцов и строк в сводной таблице. Можно создавать формулы, управляющие контекстом, но он также может стать причиной изменения результатов неожиданным образом. Дополнительные сведения см. в статье Контекст в формулах DAX.
Обновление результатов формул
Обновление д анных и повторное вычисление представляют собой две отдельные, но связанные между собой операции, которыми необходимо уметь пользоваться при создании модели данных, содержащей сложные формулы, большой объем данных или данные из внешних источников данных.
Обновление данных — это процесс замещения данных в книге новыми данными из внешнего источника данных. Можно также обновлять данные вручную через указанные интервалы времени. Если книга опубликована на сайте SharePoint, можно настроить расписание автоматического обновления из внешних источников.
Повторное вычисление — это процесс обновления результатов формул для отражения любых изменений в самих формулах, а также отображения изменений в базовых данных. Повторное вычисление может повлиять на общую производительность следующим образом.
Для вычисляемого столбца результат формулы всегда должен повторно вычисляться для всего столбца при каждом изменении формулы.
Для меры результаты формулы не вычисляются до тех пор, пока мера не будет помещена в контексте сводная диаграмма. Формула также будет повторно пересчитана при изменении любого заголовка строки или столбца, влияющего на фильтры данных, или при ручном обновлении сводной таблицы.
Решение проблем с формулами
Ошибки при записи формул
Если при вводе формулы вы получаете сообщение об ошибке, формула, возможно, содержит синтаксическую, семантическую ошибку или ошибку в вычислении.
Синтаксические ошибки устранять проще всего. Они обычно вызваны пропущенной скобкой или запятой. Справку по синтаксису отдельных функций см. в Справочнике по функциям DAX.
Ошибки другого типа возникают, когда синтаксис задан правильно, но значение упоминаемого столбца не имеет смысла в контексте формулы. Семантические ошибки могут вызываться следующими причинами.
Формула ссылается на несуществующий столбец, таблицу или функцию.
Формула верна, но обработчик в процессе поиска данных обнаруживает несоответствие типов и возвращает ошибку.
Формула передает функции неверное число или тип параметров.
Формула ссылается на другой столбец, который содержит ошибку, поэтому ее значения недопустимы.
Формула ссылается на необработанный столбец, т. е. она содержит метаданные, но не сами данные для вычислений.
В первых четырех случаях DAX помечает весь столбец, содержащий недопустимую формулу. В последнем случае DAX выделяет имя столбца серым цветом, чтобы показать, что он находится в необработанном состоянии.
Неверные или необычные результаты при ранжировании или упорядочении значений столбца
При ранжировании или упорядочении столбца, который содержит значение «не число», вы можете получить неверные или неожиданные результаты. Например, при вычислении результата деления 0 на 0 возвращается значение «не число».
Это происходит, потому что при упорядочении и ранжировании обработчик формул сравнивает числовые значения, а значение «не число» невозможно сравнить с другими числами в столбце.
Чтобы подтвердить правильность результатов, используйте условные операторы (с функцией ЕСЛИ) для выявления значений «не число» (с возвратом числового значения 0).
Совместимость с табличными моделями служб Analysis Services и режимом DirectQuery
В общем случае формулы DAX, создаваемые в Power Pivot, полностью совместимы с табличными моделями служб Analysis Services. Тем не менее при миграции модели Power Pivot в экземпляр служб Analysis Services и развертывании модели в режиме DirectQuery существуют некоторые ограничения.
Некоторые формулы DAX могут возвращать другие результаты при развертывании модели в режиме DirectQuery.
Некоторые формулы могут вызывать ошибки проверки при развертывании модели в режиме DirectQuery, поскольку они содержат функции DAX, неподдерживаемые в реляционных источниках данных.
Дополнительные сведения см. в документации по табличному моделированию служб Analysis Services в электронном руководстве SQL Server 2012 BooksOnline.
Примените основные сведения о DAX в Power BI Desktop
Это руководство предназначено для пользователей, не знакомых с Power BI Desktop. Оно предоставляет краткие и простые вводные сведения о том, как можно использовать язык выражений анализа данных (DAX) для решения ряда задач анализа данных и основных вычислений. Здесь будут предоставлены некоторые концептуальные сведения, ряд задач, которые вы можете выполнить, и несколько контрольных вопросов для проверки, что вы узнали. После изучения этой статьи вы должны хорошо понимать наиболее важные основные понятия в DAX.
Что такое DAX
DAX — это коллекция функций, операторов и констант, которые можно использовать в формуле или выражении для вычисления и возврата одного или нескольких значений. Проще говоря, DAX помогает создавать новую информацию из данных, уже существующих в вашей модели.
Почему так важен DAX?
Довольно просто создать новый файл Power BI Desktop и импортировать в него некоторые данные. Можно даже создать отчеты, показывающие ценные сведения, без использования каких-либо формул DAX вообще. Но что делать, если необходимо проанализировать процент роста по категориям продуктов, а также для разных диапазонов дат? Или если требуется вычислить годовой рост в сравнении с тенденциями рынка? Формулы DAX предоставляют такую возможность, как и многие другие важные возможности. Научившись создавать эффективные формулы DAX, вы сможете наиболее эффективно использовать свои данные. Получив нужную информацию, вы сможете начать решать реальные бизнес-задачи, влияющие на производительность. Это преимущество Power BI, и DAX поможет вам получить его.
Предварительные требования
Возможно, вы уже создавали формулы в Microsoft Excel. Эти знания пригодятся для понимания DAX, но даже если у вас нет опыта работы с формулами Excel, описанные здесь принципы помогут практически сразу приступить к созданию формул DAX и решению реальных задач бизнес-аналитики.
Основное внимание мы уделим объяснению формул DAX, используемых в вычислениях, в частности в мерах и вычисляемых столбцах. Вы должны быть знакомы с использованием Power BI Desktop, уметь импортировать данные, добавлять поля в отчет, а также понимать основные принципы мер и вычисляемых столбцов.
Пример книги
Лучший способ изучения DAX — создать некоторые простые формулы, использовать их с реальными данными и его фактическими данными и посмотреть результаты. В примерах и задачах здесь используется пример файла Contoso Sales for Power BI Desktop. Это тот же файл примера, который используется в статье Учебник. Создание собственных мер в Power BI Desktop.
Давайте начнем!
Мы будем строить наше объяснение DAX вокруг трех фундаментальных понятий: синтаксиса, функций и контекста. В DAX есть и другие важные понятия, но, разобравшись в этих трех основных понятиях, вы получите наилучшую основу для построения навыков работы с DAX.
Синтаксис
Прежде чем создавать собственные формулы, давайте рассмотрим синтаксис формул DAX. Синтаксис включает различные элементы, которые составляют формулу, или проще говоря, это способ записи формулы. Например, рассмотрим простую формулу DAX для меры.
Эта формула содержит следующие элементы синтаксиса.
Ответ. Имя меры Total Sales (Всего продаж).
Б. Оператор знака равенства ( = ), который обозначает начало формулы. При вычислении он будет возвращать результат.
В. Функция DAX SUM, которая складывает все числа в столбце Sales[SalesAmount]. Более подробно функции будут рассматриваться позднее.
Г. В скобки () заключается выражение, содержащее один аргумент или несколько. Большинству функций требуется по меньшей мере один аргумент. Аргумент передает значение в функцию.
Д. Ссылочная таблица Sales (Продажи).
Е. Ссылочный столбец [SalesAmount] в таблице Sales (Продажи). С помощью этого аргумента функция СУММ знает, по какому столбцу следует суммировать значения.
При попытке понять формулу DAX часто бывает полезно перевести каждый элемент на язык, на котором вы думаете и говорите каждый день. Например, можно прочитать эту формулу следующим образом.
Для меры с именем «Всего продаж» вычислить (=) СУММУ значений в столбце [SalesAmount] таблицы «Продажи».
При добавлении в отчет эта мера вычисляет и возвращает значения путем суммирования объемов продаж для каждого из полей, которые мы включаем, например, для поля Cell Phones in the USA (Мобильные телефоны в США).
Вы можете подумать: разве эта мера не делает то же, что было бы, если просто добавить поле SalesAmount в отчет? Да, действительно. Однако есть веская причина для создания собственной меры, которая суммирует значения из поля SalesAmount: эту меру можно использовать в качестве аргумента в других формулах. Пока это может показаться несколько запутанным, но по мере роста навыков работы с формулами DAX знание этой меры сделает ваши формулы и модель более эффективными. В дальнейшем вы увидите, как мера Total Sales (Всего продаж) используется в качестве аргумента в других формулах.
Давайте рассмотрим еще некоторые моменты относительно этой формулы. В частности, мы представили функцию СУММ. Функции — это предварительно записанные формулы, которые облегчают выполнение сложных вычислений и манипуляций с числами, датами, временем, текстом и пр. Более подробно функции будут рассматриваться позднее.
Вы также видите столбец [SalesAmount] с префиксом, указывающим таблицу Sales (Продажи), в которой находится этот столбец. Такое указание имени столбца с префиксом в виде имени таблицы называется полным именем столбца. Для ссылочных столбцов в одной и той же таблице не требуется включать имя таблицы в формулу, что позволяет сделать длинные формулы, которые ссылаются на многие столбцы, короче и удобней для чтения. Тем не менее рекомендуется включать имя таблицы в формулы мер, даже если столбцы находятся в одной и той же таблице.
Если имя таблицы содержит пробелы, зарезервированные ключевые слова или недопустимые знаки, следует заключить имя таблицы в одинарные кавычки. Также необходимо заключать в кавычки имена таблиц, если они содержат символы вне диапазона алфавитно-цифровых символов ANSI, независимо от того, поддерживает ли ваш языковой стандарт этот набор символов.
Очень важно, чтобы формулы имели правильный синтаксис. В большинстве случаев при неправильном синтаксисе будет возвращаться синтаксическая ошибка. В других случаях синтаксис может быть правильным, но возвращаемые значения — не те, что вы ожидали. Редактор DAX в Power BI Desktop включает функцию предложений, которая помогает создавать синтаксически верные формулы, подсказывая подходящие элементы.
Давайте создадим простую формулу. Эта задача поможет вам лучше разобраться в синтаксисе формул и понять, как функция предложений в строке формул может помочь.
Задача: создание формулы меры
Скачайте и откройте файл примера Contoso Sales для Power BI Desktop.
В представлении отчетов в списке полей щелкните правой кнопкой мыши таблицу Sales (Продажи), а затем выберите пункт Создать меру.
В строке формул замените Measure, введя новое имя меры, Previous Quarter Sales (Продажи за прошлый квартал).
После знака равенства введите первые несколько букв CAL, а затем дважды щелкните функцию, которую нужно использовать. В этой формуле нужно использовать функцию CALCULATE.
Вы будете использовать функцию ВЫЧИСЛИТЬ для фильтрации величин, которые требуется суммировать, по аргументу, переданному в функцию ВЫЧИСЛИТЬ. Такие функции называются вложенными. Функция «ВЫЧИСЛИТЬ» имеет по крайней мере два аргумента. Первый аргумент — это выражение для оценки, а второй — фильтр.
После открывающей скобки ( для функции CALCULATE введите SUM, а затем другую открывающую скобку (.
Далее нам нужно передать аргумент в функцию SUM.
Начните вводить Sal, а затем выберите Sales [SalesAmount] и добавьте закрывающую скобку ).
Это первый аргумент-выражение для нашей функции ВЫЧИСЛИТЬ.
Введите запятую (,) и пробел, чтобы задать первый фильтр, а затем введите PREVIOUSQUARTER.
Функция операций со временем «PREVIOUSQUARTER» будет использоваться для фильтрации результатов суммирования по предыдущему кварталу.
После открывающей скобки ( для функции PREVIOUSQUARTER введите Calendar[DateKey].
Функция «ПРЕДКВАРТАЛ» имеет один аргумент — столбец, содержащий непрерывный диапазон дат. В нашем случае это столбец DateKey из таблицы Calendar.
Завершите оба аргумента, передаваемые в функции PREVIOUSQUARTER и CALCULATE, двумя закрывающими круглыми скобками )).
Теперь ваша формула должна выглядеть следующим образом:
Продажи за прошлый квартал = ВЫЧИСЛИТЬ(СУММ(Sales[SalesAmount]) ПРЕДКВАРТАЛ(Calendar[DateKey]))
Установите флажок 
в строке формул или нажмите клавишу ВВОД, чтобы проверить формулу и добавить ее в модель.
Вы справились! Вы только что создали сложную меру с помощью DAX, и при этом не ее одну. Эта формула будет вычислять суммарный объем продаж за предыдущий квартал в зависимости от фильтров, применяемых в отчете. Например если мы поместим SalesAmount (Объем продаж) и нашу новую меру Previous Quarter Sales (Продажи за прошлый квартал) в диаграмму, а затем добавим в качестве срезов Year (Год) и QuarterOfYear (КварталГода), то получим примерно следующее:
Вы только что ознакомились с несколькими важными аспектами формулы DAX.
Эта формула включает две функции. Функция операций со временем PREVIOUSQUARTER вложена в качестве аргумента, передаваемого в функцию фильтрации CALCULATE.
Формулы DAX могут содержать до 64 вложенных функций. Однако маловероятно, что формула будет содержать так много вложенных функций. На самом деле такую формулу будет трудно создать и отладить, и, скорее всего, она не будет работать достаточно быстро.
В этой формуле также используются фильтры. Фильтры сужают диапазон вычислений. В данном случае в качестве аргумента был выбран один фильтр, который фактически является результатом другой функции. Более подробно фильтры будут рассматриваться позже.
Вы использовали функцию CALCULATE. Это одна из наиболее мощных функций в DAX. При разработке моделей и создании более сложных формул вы скорее всего многократно будете использовать эту функцию. Хотя обсуждение функции CALCULATE выходит за рамки этой статьи, по мере освоения DAX стоит обратить на эту функцию особое внимание.
Быстрый тест по синтаксису
Что делает эта кнопка в строке формул?
Что всегда окружает имя столбца в формуле DAX?
Ответы приведены в конце этой статьи.
Функции
Функции являются предопределенными формулами, которые выполняют вычисления с использованием специальных значений, именуемых аргументами, в определенном порядке или структуре. Аргументами могут быть другие функции, другие формулы, выражения, ссылки на столбцы, числа, текст, логические значения, такие как TRUE и FALSE, или константы.
В DAX имеются следующие категории функций: Дата и время, Логика операций со временем, Информационные, Логические, Математические, Статистические, Текстовые, Родительские/дочерние и Прочие. Если вы знакомы с функциями в формулах Excel, многие из функций в DAX будут казаться вам аналогичными. Однако функции DAX уникальны в следующем.
Функция DAX всегда ссылается на столбец или таблицу целиком. В формулу можно добавить фильтры, чтобы использовать только определенные значения из таблицы или столбца.
Если нужно настроить вычисления построчно, DAX предоставляет функции, позволяющие использовать текущее значение строки или связанное значение как тип аргумента для вычислений, которые зависят от контекста. Более подробно контекст будет рассматриваться позже.
В DAX имеется много функций, которые возвращают таблицу, а не значение. Таблица не отображается, но используется для предоставления входных данных в другие функции. Например, можно получить таблицу, а затем подсчитать уникальные значения в ней или вычислить динамические суммы по отфильтрованным таблицам или столбцам.
В DAX имеется множество функций логики операций со временем. Эти функции позволяют определять или выбирать диапазоны дат и выполнять динамические вычисления на их основе. Например, можно сравнить суммы по параллельным периодам.
В Excel есть популярная функция VLOOKUP (ВПР). Функции DAX не принимают в качестве ссылки ячейку или диапазон ячеек, в отличие от функции VLOOKUP в Excel. Функции DAX принимают в качестве ссылки столбец или таблицу. Не забывайте, что в Power BI Desktop вы работаете с реляционной моделью данных. Поиск значений в другой таблице довольно прост; в большинстве случаев вообще не требуется создавать какие-либо формулы.
Как можно видеть, функции в DAX помогают создавать сложные формулы. В действительности мы только коснулись основ функций. По мере освоения DAX вы будете создавать формулы, используя множество различных функций. Одним из лучших учебников, содержащих сведения о каждой функции DAX, является Справочник по функциям DAX.
Быстрый тест по функциям
Ответы приведены в конце этой статьи.
Контекст
Контекст является одним из наиболее важных понятий DAX. В DAX есть два типа контекста: контекст строки и контекст фильтра. Сначала рассмотрим контекст строки.
Контекст строки
Проще всего воспринимать контекст строки как текущую строку. Он применяется, когда формула содержит функцию, которая с помощью фильтров определяет одну строку в таблице. Эта функция будет по определению применять контекст строки для каждой строки таблицы, которую она фильтрует. Такой тип контекста строки наиболее часто применяется к мерам.
Контекст фильтра
Понять контекст фильтра немного сложнее, чем контекст строки. Проще всего представить контекст фильтра как один или несколько фильтров, примененных в вычислении, которое определяет результат или значение.
Контекст фильтра не заменяет контекст строки; скорее, он применяется в дополнение к контексту строки. Например, чтобы дополнительно сузить диапазон возможных значений, включаемых в вычисление, вы можете использовать контекст фильтра, который не только определяет контекст строки, но и задает определенное значение (фильтр) в этом контексте строки.
Контекст фильтра легко увидеть в отчетах. Например, добавляя в визуализацию TotalCost (итоговую стоимость), а затем добавляя год и регион, вы задаете контекст фильтра, который выбирает подмножество данных на основе указанного года и региона.
Почему контекст фильтра так важен в DAX? Хотя контекст фильтра проще всего применять путем добавления полей в визуализацию, его также можно использовать в формуле DAX путем определения фильтра с помощью функций, таких как ALL, RELATED, FILTER, CALCULATE, в отношениях, а также в других мерах и столбцах. Например, рассмотрим следующую формулу в мере с именем Store Sales (Продажи в магазине).
Чтобы лучше понять эту формулу, мы можем разбить ее, как и другие формулы.
Эта формула содержит следующие элементы синтаксиса.
Ответ. Имя меры — Store Sales (Продажи в магазине).
Б. Оператор знака равенства ( = ), который обозначает начало формулы.
В. Функция CALCULATE, которая вычисляет выражение, переданное в качестве аргумента, в контексте, который изменяется заданными фильтрами.
Г. В скобки () заключается выражение, содержащее один аргумент или несколько.
Д. Мера [Total Sales] (Всего продаж) в той же таблице, что и выражение. Мера Total Sales (Всего продаж) имеет формулу: = СУММ(Sales[SalesAmount]).
Е. Запятая (,) отделяет первый аргумент-выражение от аргумента-фильтра.
Ж. Полное имя ссылочного столбца Channel[ChannelName]. Это наш контекст строки. Каждая строка в этом столбце указывает канал: Store, Online и т.п.
З. Конкретное значение Store в качестве фильтра. Это наш контекст фильтра.
Эта формула гарантирует, что вычисляется сумма только значений продаж, определенных мерой Total Sales (Всего продаж), только для тех строк, в столбце Channel[ChannelName] которых имеется значение Store, используемое в качестве фильтра.
Как можно себе представить, возможность задания контекста фильтра в формуле значительно и мощно расширяет функциональность. Возможность ссылаться на определенное значение в связанной таблице — лишь один пример. Не беспокойтесь, если вы пока еще не полностью разобрались в контексте. По мере создания собственных формул вы сможете лучше понять и сам контекст и то, и почему он так важен в DAX.
Быстрый тест по контексту
Ответы приведены в конце этой статьи.
Сводка
Теперь, когда у вас есть базовое представление о наиболее важных понятиях в DAX, можно начать самостоятельно создавать формулы DAX для мер. Изучение DAX действительно может показаться несколько сложным, но существует множество ресурсов, доступных пользователю. Прочтя эту статью и поэкспериментировав с созданием нескольких собственных формул, вы можете изучить другие понятия и формулы DAX, помогающие решать ваши бизнес-задачи. Существует много доступных ресурсов DAX. Наиболее важным является Справочник по выражениям анализа данных (DAX).
Поскольку DAX используется уже несколько лет в других средствах бизнес-аналитики Майкрософт, таких как Power Pivot и табличные модели служб Analysis Services табличной модели, так что там имеется много полезных сведений. Дополнительные сведения можно также найти в книгах, технических документах и блогах корпорации Майкрософт и ведущих специалистов в области бизнес-аналитики. Также рекомендуется посетить вики-сайт Центра ресурсов DAX в TechNet.