Общие сведения о DAX

DAX — это язык формул, используемый в Analysis Services, Power BI и Power Pivot в Excel. Формулы DAX состоят из функций, операторов и значений и предназначены для выполнения сложных вычислений и запросов к данным в связанных таблицах и столбцах в табличных моделях данных.

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

Вычисления

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

Меры

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

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

При определении формулы для меры в строке формул в компоненте подсказки отображается предварительный просмотр будущих результатов во всем текущем контексте, но в других местах результаты не обновляются сразу же. Результаты вычисления (отфильтрованные) не отображаются немедленно потому, что результат меры нельзя определить без контекста. Для вычисления меры требуется клиентское приложение создания отчетов, которое может предоставить контекст, необходимый для получения данных для каждой из ячеек, а затем вычислить выражения для ячеек. Таким клиентом может быть сводная таблица или сводная диаграмма Excel, отчет Power BI или табличное выражение в запросе DAX в SQL Server Management Studio (SSMS).

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

Total Sales = SUM([Sales Amount])

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

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

Дополнительные сведения см. на следующих ресурсах:
Меры в Power BI Desktop
Меры в Analysis Services
Меры в Power Pivot

Вычисляемые столбцы

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

= [Calendar Year] & " Q" & [Calendar Quarter]

Для получения значения для каждой строки в таблице берется значение из столбца Calendar Year (Календарный год) в той же таблице дат, добавляется пробел и заглавная буква Q, а затем добавляется значение из столбца Calendar Quarter (Календарный квартал) в той же таблице дат. Результат для каждой строки в вычисляемом столбце вычисляется и отображается немедленно, например 2017 Q1. Значения в столбце пересчитываются только в том случае, если обновляется таблица, в которой находится столбец, или любая связанная таблица либо если модель выгружается из памяти, а затем загружается снова, как, например, при закрытии и повторном открытии файла Power BI Desktop.

Дополнительные сведения:
Вычисляемые столбцы в Power BI Desktop
Вычисляемые столбцы в Analysis Services
Вычисляемые столбцы в Power Pivot

Вычисляемые таблицы

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

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

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

Дополнительные сведения:
Вычисляемые таблицы в Power BI Desktop
Вычисляемые таблицы в Analysis Services

Безопасность на уровне строк

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

= Customers[Country] = "USA"

Члены роли Sales смогут просматривать данные только по клиентам в США. Результаты статистических вычислений, таких как SUM, также возвращаются только для клиентов в США. Безопасность на уровне строк недоступна в Power Pivot в Excel.

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

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

Дополнительные сведения:
Row-level security (RLS) with Power BI (Безопасность на уровне строк (RLS) в Power BI)
Роли в Analysis Services

Запросы

Запросы DAX можно создавать и выполнять в SQL Server Management Studio (SSMS) и в средствах с открытым кодом, таких как DAX Studio (daxstudio.org). В отличие от формул вычислений DAX, которые можно создавать только в табличных моделях данных, запросы DAX также можно выполнять к многомерным моделям Analysis Services. Запросы DAX зачастую проще в написании и эффективнее, чем запросы многомерных выражений (MDX).

Запрос DAX представляет собой инструкцию, наподобие инструкции SELECT в T-SQL. Простейший тип запроса DAX — инструкция evaluate. Например,

EVALUATE
 ( FILTER ( 'DimProduct', [SafetyStockLevel] < 200 ) )
ORDER BY [EnglishProductName] ASC

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

В рамках запроса можно создавать меры. Меры существуют, только пока выполняется запрос. Дополнительные сведения см. в статье Запросы DAX.

Формулы

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

Основы формул

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

Формула	Определение
= TODAY()	Вставляет текущую дату в каждую строку вычисляемого столбца.
= 3	Вставляет значение 3 в каждую строку вычисляемого столбца.
= [Column1] + [Column2]	Складывает значения из столбцов [Column1] и [Column2] одной строки, а затем помещает результат в ту же строку в вычисляемом столбце.

Независимо от уровня сложности формулы для ее создания можно следовать приведенной ниже последовательности действий.

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

2. Можно ввести имя функции, выбрать его или ввести выражение.

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

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

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

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

6. Чтобы подтвердить ввод формулы, нажмите клавишу ВВОД.

Примечание

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

Например, рассмотрим следующую формулу в мере с именем Days in Current Quarter (Дней в текущем квартале):

Days in Current Quarter = COUNTROWS( DATESBETWEEN( 'Date'[Date], STARTOFQUARTER( LASTDATE('Date'[Date])), ENDOFQUARTER('Date'[Date])))

Эта мера используется для определения соотношения между неполным текущим периодом и предыдущим периодом. Формула должна учитывать истекшую часть периода и сравнивать ее с той же частью предыдущего периода. В этом случае соотношение определяется по формуле [Дней с начала текущего квартала]/[Дней в текущем квартале].

Эта формула содержит следующие элементы:

Элемент формулы	Описание
Days in Current Quarter	Имя меры.
=	Формула начинается со знака равенства (=).
COUNTROWS	COUNTROWS подсчитывает число строк в таблице Date.
()	В круглых скобках указываются аргументы.
DATESBETWEEN	Функция DATESBETWEEN возвращает даты между первой и последней датами для каждого значения в столбце Date таблицы Date.
'Date'	Определяет таблицу Date. Имена таблиц заключаются в одинарные кавычки.
[Date]	Определяет столбец Date в таблице Date. Имена столбцов заключаются в квадратные кавычки.
,
STARTOFQUARTER	Функция STARTOFQUARTER возвращает дату начала квартала.
LASTDATE	Функция LASTDATE возвращает последнюю дату квартала.
'Date'	Определяет таблицу Date.
[Date]	Определяет столбец Date в таблице Date.
,
ENDOFQUARTER	Функция ENDOFQUARTER
'Date'	Определяет таблицу Date.
[Date]	Определяет столбец Date в таблице Date.

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

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

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

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

Использование нескольких функций в формуле

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

Функции

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

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

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

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

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

Агрегатные функции

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

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

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

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

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

Финансовые функции

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

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

Информационная функция проверяет ячейку или строку, указанные в качестве аргумента, и сообщает, соответствует ли значение ожидаемому типу. Например, функция ISERROR возвращает значение TRUE, если упоминаемое значение содержит ошибку. Дополнительные сведения см. в разделе Информационные функции.

Логические функции

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

Математические и тригонометрические функции

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

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

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

Функции связей

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

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

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

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

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

Функции операций со временем

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

Функции обработки таблиц

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

Переменные

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

VAR
    TotalQty = SUM ( Sales[Quantity] )

Return

    IF (
        TotalQty > 1000,
        TotalQty * 0.95,
        TotalQty * 1.25
        )

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

Типы данных

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

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

Тип данных в модели	Тип данных в DAX	Описание
Whole Number	64-разрядное (8-байтовое) целочисленное значение 1, 2	Числа без десятичных разрядов. Целые числа могут быть положительными или отрицательными, но не могут содержать дробную часть в диапазоне -9,223,372,036,854,775,808 (-2^63) и 9,223,372,036,854,775,807 (2^63-1).
Десятичное число	64-разрядное (8 байтовое) вещественное число 1, 2	Вещественные числа — это числа, которые могут иметь знаки после запятой. Вещественные числа включают широкий диапазон значений. Отрицательные числа от -1.79E +308 до -2.23E -308 Нуль Положительные числа от 2.23E -308 до 1.79E + 308 Однако количество значащих цифр ограничено 17 знаками после запятой.
Логический	Логический	Значение True или False.
Текстовый	Строковый	Строка символьных данных в Юникоде. Могут быть строками, числами или датами, представленными в текстовом формате.
Дата	Дата и время	Значения даты и времени в принятом представлении даты-времени. Допустимый диапазон дат включает значения после 1 марта 1900г.
Валюта	Валюта	Тип данных "Валюта" включает значения в диапазоне от -922,337,203,685,477.5808 до 922,337,203,685,477.5807 с четырьмя десятичными знаками заданной точности.
Недоступно	Пусто	Тип данных с пустыми значениями в DAX представляет и заменяет пустые значения NULL в SQL. Пустое значение создается с помощью функции BLANK, а проверяется с помощью логической функции ISBLANK.

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

Хотя типы данных обычно устанавливаются автоматически, важно понимать, как они работают, в особенности в формулах DAX. Например, ошибки в формулах или непредвиденных результаты часто связаны с использованием определенного оператора, который недопустим для указанного в аргументе типа данных. Например, формула = 1 & 2возвращает строковое значение 12. В то же время формула = "1" + "2"возвращает целочисленный результат 3.

Контекст

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

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

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

Существуют различные типы контекста: контекст строки, контекст запросаи контекст фильтра.

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

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

Предположим, создается вычисляемый столбец = [Freight] + [Tax], который складывает значения из двух столбцов Freight и Tax одной таблицы. Эта формула автоматически возвращает только значения из текущей строки в указанных столбцах.

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

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

= [Freight] + RELATED('Region'[TaxRate])  

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

Контекст нескольких строк

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

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

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

= MAXX(FILTER(Sales,[ProdKey] = EARLIER([ProdKey])),Sales[OrderQty])  

Подробный пример см. в описании функции EARLIER.

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

Контекст запроса

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

Так как контекст меняется в зависимости от того, где находится формула, ее результаты также могут меняться. Например, предположим, что вы создаете формулу, которая суммирует значения в столбце Profit (Прибыль) таблицы Sales (Продажи): = SUM('Sales'[Profit]). Если эта формула используется в вычисляемом столбце в таблице Sales, ее результаты для всей таблицы будут такими же, так как контекстом запроса для формулы всегда является весь набор данных таблицы Sales. Результатом будет прибыль по всем регионам, всем продуктам, всем годам и т. д.

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

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

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

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

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

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

Дополнительные сведения о создании фильтров в формулах см. в разделе Функция FILTER (DAX).
Пример очистки фильтров для получения итоговых сумм см. в разделе Функция ALL (DAX).

Примеры выборочного снятия и применения фильтров в формулах см. в описании функции ALLEXCEPT.

Определение контекста в формулах

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

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

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

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

Операторы

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

• Операторы сравнения, которые сравнивают значения и возвращают логические значения (TRUE/FALSE).
• Арифметические операторы, которые выполняют арифметические вычисления и возвращают числовые значения.
• Операторы объединения текста, которые соединяют две и более текстовые строки.
• Логические операторы, которые объединяют два и более выражения, возвращая один результат.

Подробные сведения об операторах, используемых в формулах DAX, см. в статье Операторы DAX.

Работа с таблицами и столбцами

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

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

Ссылки на таблицы и столбцы в формулах

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

= SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])  

При вычислении формулы конструктор моделей сначала проверяет общий синтаксис, а затем сравнивает указанные имена столбцов и таблиц с возможными столбцами и таблицами в текущем контексте. Если имя, использованное в формуле, определено неоднозначно либо столбец или таблица не найдены, будет выдана ошибка (строка #ERROR вместо значения данных в ячейках, в которых произошла ошибка). Дополнительные сведения о требованиях к именованию таблиц, столбцов и других объектов см. в разделе "Требования к именованию" статьи Синтаксис DAX.

Связи между таблицами

Создавая связи между таблицами, вы получаете возможность использования связанных значений в других таблицах в вычислениях. Например, с помощью вычисляемого столбца можно найти все записи об отгрузке, связанные с текущим торговым посредником, а затем суммировать затраты на доставку. Однако во многих случаях связь не является обязательной. Используя функцию LOOKUPVALUE в формуле, можно получить значение result_columnName для строки, которая удовлетворяет условиям, указанным в аргументах search_column и search_value.

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

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

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

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

Обработка и обновление

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

Обработка (обновление) — это обновление данных в модели данными из внешнего источника.

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

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

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

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

Обновления

DAX постоянно улучшается. Новые и обновленные функции выпускаются в составе следующего доступного обновления, которое обычно предоставляется ежемесячно. Сначала обновляются службы, а затем устанавливаются такие приложения, как Power BI Desktop, Excel, SQL Server Management Studio (SSMS) и расширение проекта Analysis Services для Visual Studio (SSDT). Обновления для служб SQL Server Analysis Services содержатся в следующем накопительном обновлении. Новые функции впервые объявляются и описываются в ссылке на функцию DAX, совпадающую с обновлениями Power BI Desktop.

Не все функции поддерживаются в более ранних версиях SQL Server Analysis Services и Excel.

Устранение неполадок

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

Синтаксические ошибки устранять проще всего. Они обычно вызваны пропущенной скобкой или запятой.

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

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

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

Приложения и средства

Power BI Desktop

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

Power Pivot в Excel

Конструктор моделей Power Pivot в Excel включает в себя редактор DAX для создания формул вычисления DAX.

Visual Studio

Visual Studio с расширением проектов Analysis Services (VSIX) используется для создания проектов модели Analysis Services. Конструктор табличных моделей, установленный вместе с расширением проектов, включает редактор DAX.

SQL Server Management Studio.

SQL Server Management Studio (SSMS) — это важный инструмент для работы с Analysis Services. SSMS включает в себя редактор запросов DAX для выполнения запросов как к табличным, так и к многомерным моделям.

DAX Studio

DAX Studio — это клиентское средство с открытым кодом для создания и выполнения запросов DAX к моделям Analysis Services, Power BI Desktop и Power Pivot в Excel.

Tabular Editor.

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

Учебные материалы

При изучении DAX лучше всего использовать приложение, в котором вы будете создавать свои модели данных. Для Analysis Services, Power BI Desktop и Power Pivot в Excel предлагаются статьи и учебники с уроками по созданию мер, вычисляемых столбцов и фильтров строк с помощью DAX. Ниже приведены некоторые дополнительные ресурсы.

Видео

Схема обучения Использование DAX в Power BI Desktop

Definitive Guide to DAX (Полное справочное руководство по DAX), Альберто Феррари (Alberto Ferrari) и Марко Руссо (Marco Russo), Microsoft Press. Доступное сейчас во втором выпуске, это подробное пошаговое руководство содержит как базовые сведения, так и более изощренные приемы для начинающих специалистов по моделированию данных и бизнес-аналитике.

Сообщество

В сфере DAX действует активное сообщество, члены которого всегда готовы поделиться своим опытом. В Сообществе Power BI (Майкрософт) есть специальный форум, посвященный командам и советам по DAX.