Поделиться через

DAX в табличных моделях

  • Статья

Применимо к: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

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

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

Формулы DAX в вычисляемых столбцах, вычисляемых таблицах, мерах и фильтрах строк

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

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

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

Примечание

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

Если вычисляемый столбец содержит допустимую формулу DAX, значения вычисляются для каждой строки сразу после ввода формулы. Затем значения сохраняются в базе данных. Например, в таблице Date, если в строку формул вводится формула =[Calendar Year] & " Q" & [Calendar Quarter] , для каждой строки таблицы значение рассчитывается следующим образом: берется значение из столбца Calendar Year (в той же таблице Date), к нему добавляется пробел и заглавная буква Q, а затем добавляются значения из столбца Calendar Quarter (в той же таблице Date). Результат для каждой из строк в вычисляемом столбце рассчитывается немедленно и выглядит, к примеру, как 2010 Q1. Значения столбца вычисляются повторно лишь при повторной обработке данных.

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

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

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

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

Меры

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

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

Результаты вычисления (отфильтрованные) не отображаются немедленно потому, что результат меры нельзя определить без контекста. Для вычисления меры требуется клиентское приложение создания отчетов, которое может предоставить контекст, необходимый для получения данных для каждой из ячеек, а затем вычислить выражения для ячеек. Это может быть сводная таблица Excel или сводная диаграмма, отчет Power BI или запрос многомерных выражений. Вне зависимости от используемого клиентского средства создания отчетов для каждой из ячеек в результатах выполняется отдельный запрос. Это означает, что каждое сочетание заголовков строки и столбца в сводной таблице или каждый набор срезов и фильтров в отчете Power BI дает особое подмножество данных, на основе которых вычисляется мера. Например, в мере с формулой Total Sales:=SUM([Sales Amount]), когда пользователь помещает меру TotalSales в окно Значения в сводной таблице, а затем помещает столбец DimProductCategory из таблицы DimProduct в окно Фильтры, сумма суммы sales Amount вычисляется и отображается для каждой категории продуктов.

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

Дополнительные сведения см. в статье Меры.

Фильтры строк

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

В фильтре строк формула DAX, результат вычисления которой должен быть равен логическому значению TRUE или FALSE, определяет строки, которые могут возвращаться результатами запроса, выполняемого членами определенной роли. Строки, не включенные в формулу DAX, возвращать нельзя. Например, при использовании в таблице Customers (Клиенты) выражения DAX =Customers[Country] = "USA"члены роли Sales (Продажи) смогут просматривать только данные для клиентов из США, а агрегаты (SUM и др.) возвращаются только для клиентов из США.

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

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

Дополнительные сведения см. в разделе Роли.

Типы данных 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.

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

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

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

Операторы DAX

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

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

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

Формулы DAX

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

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

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

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

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

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

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

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

Функции даты и времени в DAX схожи с функциями даты и времени Microsoft Excel. Однако функции DAX основаны на типах данных datetime, используемых microsoft SQL Server. Дополнительные сведения см. в разделе Функции даты и времени (DAX).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функции с табличным значением

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

Функции фильтрации — возвращают столбец, таблицу или значения, связанные с текущей строкой.

Агрегатные функции — агрегирование любого выражения по строкам таблицы.

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

Контекст в формулах DAX

Контекст является важным понятием, которое следует учитывать при создании формул на языке 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 (DAX).

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

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

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

Предупреждение

Для модели в режиме DirectQuery вычисляется контекст, а затем заданные операции для получения правильного подмножества данных и вычисления результатов преобразуются в инструкции SQL. Затем эти инструкции непосредственно выполняются в реляционном хранилище данных. Таким образом, хотя метод извлечения данных и вычисления результатов отличен, сам контекст остается неизменным.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры контекста в формулах

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

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

Функция ALL (DAX) задает контекст в формуле. Ее можно использовать для переопределения фильтров, которые применяются к результату контекста запроса.

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

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

Формулы и табличная модель

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обновление результатов формул (процесс)

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

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

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

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

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

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

Устранение ошибок в формулах

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

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

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

  • Формула ссылается на несуществующий столбец, таблицу или функцию.

  • Формула верна, но подсистема данных в процессе поиска данных обнаруживает несоответствие типов и возвращает ошибку.

  • Формула передает функции неверное число или тип параметров.

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

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

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

См. также раздел

Руководство по выражениям анализа данных (DAX)
Меры
Вычисляемые столбцы
Роли
Ключевые показатели эффективности
Поддерживаемые источники данных