Обзор DAX

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

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

Расчеты

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

Меры

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

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

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

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

DAX
Total Sales = SUM([Sales Amount])

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

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

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

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

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

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

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

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

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

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

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

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

Дополнительные сведения см. в следующем разделе:
Вычисляемые таблицы в Power BI Desktop
вычисляемые таблицы в службах "Analysis Services".

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

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

DAX
= Customers[Country] = "USA"

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

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

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

Дополнительные сведения см. в следующем разделе:
безопасность на уровне строк (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 — это оценка инструкции. Например

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

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

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

Формулы

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

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

В этом примере рассмотрим формулу в метрике с именем Days в текущем квартале:

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

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

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

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

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

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

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

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

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

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

Функции

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

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

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

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

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

Функции агрегирования

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функции отношений

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

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

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

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

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

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

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

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

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

Переменные

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

DAX
VAR
    TotalQty = SUM ( Sales[Quantity] )

Return

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

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

Типы данных

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

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

Тип данных в модели Тип данных в DAX Описание
Whole Number 64-разрядное (восемь байт) целочисленное значение 1, 2 Числа без десятичных разрядов. Целые числа могут быть положительными или отрицательными, но должны быть целыми числами от -9 223 372 036 854 775 808 (-2^63) и 9 223 372 036 854 775 807 (2^63-1).
Decimal Number 64-разрядное (восемь байт) вещественное число 1, 2 Реальные числа — это числа, которые могут иметь десятичные разряды. Реальные числа охватывают широкий диапазон значений:

Отрицательные значения от -1.79E +308 до -2.23E -308

Нуль

Положительные значения от 2.23E -308 до 1.79E + 308

Однако количество значимых цифр ограничено 17 десятичными цифрами.
Boolean Логический Значение либо True, либо False.
Text Струна Строка данных символов Юникода. Может быть строками, числами или датами, представленными в текстовом формате.
Date Дата и время Даты и время в принятом представлении даты и времени.

Допустимые даты — это все даты после 1 марта 1900 года.
Currency Валюта Тип данных валюты разрешает значения от –922 337 203 685 477,5808 до 922 337 203 685 477,5807 с четырьмя десятичными цифрами фиксированной точности.
N/A Пустой Пустое значение — это тип данных в DAX, который представляет и заменяет NULL в SQL. Вы можете создать пустое с помощью функции BLANK и проверить наличие пустых с помощью логической функции ISBLANK.

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

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

Контекст

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

См. подробный пример этой формулы в разделе ранее указанном в.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Операторы

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

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

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

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

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

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

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

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

DAX

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

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

Связи таблиц

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

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

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

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

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

Процесс и обновление

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

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

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

  • Значения в вычисляемом столбце вычисляются и хранятся в модели. Чтобы обновить значения в вычисляемом столбце, необходимо обработать модель с помощью одной из трех команд обработки — Process Full, Process Data или Process Recalc. Результат формулы всегда должен пересчитываться для всего столбца при её изменении.

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

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

Обновления

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

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

Power Pivot в Excel

PowerPivot в Excel

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

Visual Studio

Visual Studio

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

SQL Server Management Studio

SQL Server Management Studio

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

DAX Studio

значок DAX Studio

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

Табличный редактор

значок табличного редактора

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

Учебные ресурсы

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

видео

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

Окончательное руководство по DAX, написанное Альберто Феррари и Марко Руссо (Microsoft Press). Теперь в своем втором выпуске это обширное руководство предоставляет основы инновационных высокопроизводительных методов для начала моделирователей данных и специалистов бизнес-аналитики.

Окончательное руководство по DAX

Сообщество

DAX имеет активное сообщество, готовое всегда делиться своим опытом. сообщества Microsoft Power BI имеет специальный форум для обсуждения только для DAX, команды DAX и советы.