Лучший BI

Ресурсы

Продукты

Партнёры

Цены

Меню

VISIOLOGY 2023©️

DAX VISIOLOGY

Оттачивайте свою аналитику комфортно и доступно.

DAX VISIOLOGY

Оттачивайте свою аналитику комфортно и доступно.

ВВЕДЕНИЕ

Visiology 3 — результат многолетней разработки, модернизации алгоритмов анализа и работы с данными.

Платформа не только использует аналогичный интуитивно понятный интерфейс, как самая популярная в прошлом платформа Microsoft Power BI, но и “думает” как Power BI. В ядро системы зашита поддержка языка запросов DAX, что позволяет легко и быстро перенести на Visiology уже наработанную экспертизу.

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

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

МОДЕЛЬ ДАННЫХ

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

Модель данных (data model) – это абстрактное, логическое определение объектов, операторов и других элементов, которые в совокупности составляют абстрактную машину доступа к данным, с которой взаимодействует пользователь. Эти объекты позволяют моделировать структуру данных, а операторы — поведение данных.

VISIOLOGY 3.0 хранит данные в табличном виде (реляционные БД). Для корректной работы с данными необходимо эти таблицы связать между собой, то есть построить модель данных.
Связь (relationship) представляет собой объединение двух таблиц. Такие таблицы называются связанными.
Графически связь двух таблиц обозначается линией между ними.

Создание связи

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

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

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

Куда должна смотреть стрелка

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

Наверняка при изучении DAX вы задавались вопросом - Что подразумевает направление связи?
Ответ: это означает направление «Фильтрации».
Каким бы ни было направление отношений, это означает, что платформа фильтрует данные. В приведенном скриншоте вы можете видеть, что направление связи идет от таблицы "SPR" к "offer".
Это означает, что любой столбец в справочнике "SPR" может фильтровать данные в таблице фактов "offer".
То есть вы можете сделать срезы и посмотреть данные в таблице "offer" по каждому наименованию из таблицы "SPR".

Пример модели данных "звезда".

Важные аспекты связей между таблицами:

Таблицы, объединенные связью, выполняют разные роли. Одна из них представляет сторону «один», а вторая – «многие», которые помечаются символами «1» и «*» (звездочка).
Одной подкатегории может принадлежать несколько товаров, тогда как один товар может представлять только одну подкатегорию.
Существуют особые виды связей. Это связи «один к одному» (1:1) и слабые связи (weak relationships). В связи «один к одному» обе таблицы представляют собой сторону «один», тогда как в слабых связях они могут находиться на стороне «многие».
Столбцы, использующиеся для объединения таблиц и обычно имеющие одинаковые имена, называются ключами (keys) связи. При этом в ключевом столбце таблицы, представляющей сторону «один», должны находиться уникальные значения без пропусков.
Связи могут образовывать цепочки. Каждый товар принадлежит какой-то подкатегории, которая, в свою очередь, представляет определенную категорию товаров. Следовательно, каждый товар можно отнести к конкретной категории. Но чтобы получить ее название, необходимо пройти к ней от товаров через цепочку из двух связей.
Стрелкой посередине связи обозначается направление перекрестной фильтрации, то есть распространение фильтра по этой связи. Фильтрация может быть как однонаправленная, так и двунаправленная.

ТИПЫ ДАННЫХ

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

Типы данных в DAX:

Integer - Целое число.
В DAX есть только один целочисленный тип данных ,Integer, позволяющий хранить 64-битные значения.
Decimal - Десятичный.
Тип данных хранит числа с плавающей запятой в формате двойной точности.
Currency- Валюта (или десятичное число с фиксированной запятой)*.
Числа с четырьмя знаками после запятой, которые хранятся в виде 64-битных целых чисел, деленных на 10 000. Суммирование и вычитание значений с участием типа Currency игнорирует десятичные знаки в количестве больше четырех, а умножение и деление дают на выходе число с плавающей запятой, тем самым увеличивая точность значения. По умолчанию тип данных Currency включает в себя символ валюты. Но можно использовать этот символ и с типами Целое число и Десятичный так же, как применять этот тип без указания валюты.
DateTime - Дата и время.
Внутренне этот тип хранится в виде чисел с плавающей запятой, целая часть которых равна количеству дней, прошедших до означенной даты с 30 декабря 1899 года, а дробная отражает долю последнего дня. Таким образом, часы, минуты и секунды переводятся в долю прошедшего дня.
Boolean - Логический**.
Тип данных предназначен для хранения логических выражений. Также значения типа Boolean могут быть отражены как числа: TRUE как 1, а FALSE как 0. Такая нотация иногда оказывается полезной – например, для сортировки, поскольку TRUE > FALSE.
String - Строка.
Все строки в DAX хранятся в кодировке Unicode, в которой каждый символ занимает 16 бит. По умолчанию операция сравнения между строками в DAX не чувствительна к регистру.
Variant - Вариант*.
Этот тип данных используется для выражений, которые могут возвращать значения разных типов в зависимости от внешних условий. Например, следующее выражение может вернуть как целое число, так и строку. Вариант не может использоваться для столбцов в обычных таблицах.
Binary - Двоичный*.
Тип данных используется в модели данных для хранения изображений и другой неструктурированной информации. В DAX этот тип недоступен.

* На данный момент тип данных не поддерживается.

** Временно используется тип данных "строка".

Важно

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

Советую вам вовсе избегать автоматического приведения типов данных.

ViTalk GPT

Виртуальный помощник

ОПЕРАТОРЫ DAX

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

Существует несколько различных типов вычислительных операторов. На данный момент платформа поддерживает такие математические операторы, как сложение, вычитание, умножение, деление.

1) Арифметические операторы
Выполняют основные математические операции, такие как добавление, вычитание, умножение; объединение чисел; и выведение числовых результатов.

2) Операторы сравнения*
Сравнить два значения можно с помощью приведенных ниже операторов. Результатом является логическое значение (TRUE или FALSE).
Все операторы сравнения, кроме ==, считают значение BLANK равным числу 0, пустой строке "", дате DATE(1899, 12, 30) или значению FALSE. Поэтому выражение [Столбец] = 0 будет иметь значение true, если значение [Столбец] равно 0 или BLANK. Для сравнения [Столбец] == 0 имеет значение true, только когда значение [Столбец] равно 0.

3) Оператор объединения текста*
Используйте амперсанд (&) для объединения или сцепления двух или нескольких текстовых строк в один фрагмент текста.

4) Логические операторы*
Используйте логические операторы (&&) и (||) для объединения выражений с целью получить один результат.

* На данный момент не поддерживается.

В версии 3.4:

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

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

Важно

ТИПЫ ВЫЧИСЛЕНИЙ DAX

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

1.Расчётный (вычисляемый) столбец*

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

*Расчётные столбцы пока не поддерживаются (но планируются) в Visiology, но говоря про DAX, невозможно про них не упомянуть.

2.Меры

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

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

Мера рассчитывается в контексте видимого элемента или в контексте запроса DAX, тогда как вычисляемый столбец – в контексте строки таблицы, которой он принадлежит.

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

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

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

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

СИНТАКСИС

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

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

Посмотрим на формулу, написанную с помощью DAX.

Она состоит из следующих элементов:

A: Название новой меры.

B: Оператор (=). Как и в Excel он указывает на начало вычисляемого высказывания.

C: SUM - это функция DAX. С ее помощью можно найти сумму значений.

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

E: Название таблицы, из которой берется поле или столбец.

F: Название поля, из которого формула будет брать значения.

У нас создается новая мера «План продаж», которая будет равна сумме значений столбца [Quantity] из таблицы [Sales].

G: Оператор умножения.

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

Чтобы проще было понять формулу, можно разбить ее на элементы и проговорить их.

В таблице Sales для каждой строки в мере «План продаж» рассчитай результат, умножая значения из столбца [Quantity] на 1,5.

Все просто!

КОНТЕКСТ

Для начала давайте разберемся, что из себя представляет контекст вычисления. В DAX все выражения вычисляются внутри определенного контекста. Контекст – это своеобразное «окружение», в котором выполняется формула.

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

Для начала давайте разберемся, что из себя представляет контекст вычисления. В DAX все выражения вычисляются внутри определенного контекста. Контекст – это своеобразное «окружение», в котором выполняется формула.

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

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

Есть два типа контекстов DAX: контекст фильтра и контекст строки.

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

Контекст фильтра ограничивает выводимые данные, тогда как контекст строки осуществляет итерации по таблице. Когда в DAX идут итерации по таблице, фильтрация не осуществляется, и наоборот. Несмотря на всю кажущуюся простоту этой концепции усвоить ее бывает непросто. Наш мозг всегда стремится пробиться к знаниям кратчайшим путем – когда он видит какие-то сходства в концепциях, он предпочитает для упрощения объединять эти концепции в одну. Не попадайтесь на эту удочку!
Возьмём для примера следующую меру:


Cost of sales = SUMX ( Sales; Sales[Quantity] * Sales[Net Price] )

В этой формуле вычисляется сумма произведений значений столбцов количества и цены из таблицы Sales. Мы можем использовать созданную меру в отчете, мера "Cost of sales" без контекстов показывает итоговый показатель.

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

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

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

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

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

Не забывайте, что контекст фильтра всегда активен и влияет на все расчеты.

ViTalk GPT

Виртуальный помощник

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

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

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

Приведём пример. Мы можем написать для подсчета валовой прибыли меру следующего содержания:


Gross Margin :=
SUMX (
Sales;
Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )
)

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

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

ФУНКЦИИ

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

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

Типы функций DAX

Функция даты
Функция даты DATE ( <Year>, <Month>, <Day> ) выполняет вычисления значений даты. Тип данных этих значений всегда является типом данных. Возвращает указанную дату в формате datetime.

DATE

Пример


CALCULATE (
            SUM(Sales[SalesAmount]),
            FILTER (
                'Sales',
                AND ( 'Sales'[DateKey] > DATE ( 2012, 1, 1 ), 'Sales'[DateKey] < DATE ( 2012, 12, 31 ) )
            )
        )

Статистические функции
Эти функции выполняют статистические функции и функции агрегирования значений данных в выражении DAX в Power BI. Список доступных статистических функций приведен ниже.

SUMX

Для чего нужна формула SUMX? Она позволяет суммировать значения выражения по строкам.
Синтаксис формулы:
SUMX ( <таблица>, <выражение> )

<таблица> - таблица (или выражение, определяющее таблицу);
<выражение> - выражение для каждой строки таблицы.

Если вам нужно просто посчитать сумму по столбцу, используйте формулу SUM. Если нужно рассчитать построчно показатели в таблице и затем сложить их, вам потребуется SUMX.

Самый простой пример вычислений с SUMX – расчет суммы продаж по формуле = количество х цена:


продажи =
SUMX (
'продажи',
'продажи'[количество] * 'продажи'[цена] )

В таблице в каждой строке количество умножается на цену, а полученные результаты суммируются. В данном случае формула SUMX сработала очень похоже на «обычную» формулу Excel – СУММПРОИЗВ

Пример

В примере ниже SUMX построчно умножает стоимость за единицу товара unitprice на количество товара salesquantity и вычитает скидку discountamount. После выполнения всех построчных операций все значения суммируются:


SUMX(factonlinesales, factonlinesales[unitprice]*factonlinesales[salesquantity] - factonlinesales[discountamount])

Функции фильтров

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

1. CALCULATE

CALCULATE рассчитывает выражение с учетом измененного контекста фильтра.
Это самая главная формула в языке DAX. Ее название так и переводится – «вычислить», поэтому без такой формулы отчеты точно сделать не получится. Записывается формула просто:

CALCULATE ( <выражение> [<фильтр1>, <фильтр2> … ] )
где,
<выражение> - вычисляемое выражение
<фильтр1>, <фильтр2> - логические фильтры, табличные выражения или функции изменения фильтра (модификаторы)

В вычислениях CALCULATE часто используют вместе с функциями фильтрации, например, с FILTER. Или с модификаторами, например, REMOVEFILTERS.

2.FILTER

FILTER выбирает данные в таблице или в выражении по заданному критерию. Результат ее вычисления — таблица.

Синтаксис функции:
FILTER ( <таблица>, <фильтр> )
где,
<таблица> - таблица, из которой выбираются данные (или выражение, определяющее таблицу);
<фильтр> - логическое выражение, с помощью которого выполняется отбор.

В мерах FILTER обычно используют вместе с другими формулами, например, вместе с CALCULATE и др.

3.REMOVEFILTERS

REMOVEFILTERS очищает фильтры в указанной таблице или столбце.
Синтаксис формулы:
REMOVEFILTERS ( [ <таблица> или <столбец>[, <столбец>[, …] ] ] )
где,
<таблица> - таблица, в которой нужно очистить фильтры;
<столбец> - столбец, в котором нужно очистить фильтры.

С помощью этой функции нельзя получить «промежуточную» таблицу, а можно только убрать фильтры. Формула REMOVEFILTERS применяется в вычислениях с CALCULATE, так же

В рамках CALCULATE поддерживаются несколько REMOVEFILTERS.
Можно комбинировать с FILTER в рамках одного CALCULATE.

Например, с ее помощью можно посчитать долю (%) продаж товара от общей суммы продаж, как в примере с ALL.
Пример: найти долю от продаж товара по отношению к общей сумме выручки.

Пример

Расчет доли от общего:


SUM ( factonlinesales[salesamount] )
              / CALCULATE (
                  SUM ( factonlinesales[salesamount] ),
                  REMOVEFILTERS ( dimcustomer )
              )

Табличные функции

Эти функции возвращают таблицу или обрабатывают существующие таблицы.

SUMMARIZE

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

Пример


CALCULATE (
            SUM ( factonlinesales[salesamount] ),
            REMOVEFILTERS ( dimproduct ),
            SUMMARIZE (
                dimproduct,
                dimproduct[colorname]
            )
        )

Итерационные функции или X-функции

Изучая функции вы наверняка заметили, что в DAX есть функции SUM и SUMX, COUNT и COUNTX и т.д.
Сейчас разберёмся в чём отличия на примере SUM и SUMX.

SUM - это простая агрегатная функция. Она суммирует значение, основанное на контексте фильтра. Эта мера просто вычисляет суммарное значение по всей таблице фактов, когда фильтр не выбран. И если у нас где-нибудь в нашей визуализации есть фильтр, тогда он будет вычислять сумму отфильтрованного контекста.
Average, Min, Max, Count и т. д. работают также одинаково.

SUMX - это сумма выражения, X в конце этой функции - для eXpression. Эта функция дает вам сумму любого выражения.
Как SUMX технически делает расчет? SUMX будет проходить через каждую запись таблицы ввода и запускать выражение для этой записи, он сохраняет результат этого во временную память.
По окончании разбора таблицы и вычисления всех значений для каждого отдельного столбца она суммирует их все вместе, поскольку SUMx освобождает временную память и визуализирует результат.
Из-за этого свойства ITERATION функции SUMX он также называется функцией- итератором.
Другие функции-итераторы:AverageX, MinX, MAXX, CountaX и т. д.

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

Ключевые моменты о функциях DAX

Некоторые уникальные факты о функциях DAX, которые вы должны знать, чтобы лучше их понимать:

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

ПРАВИЛА ФОРМАТИРОВАНИЯ ФОРМУЛ И ФУНКЦИЙ

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

Для столбцов всегда используем полное имя, состоящее из имени таблицы и имени самого столбца в [] скобках.


'Таблица'[Столбец]

Для мер всегда используем короткое имя, состоящее из имени самой меры в [] скобках.


[Мера]

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


Сумма Прибыли = 
... 
код DAX
...

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


= 2 * 3 / 4

Если в формуле больше одной функции, то разбиваем по ним код на строки.


=  
Функция 1 
+ Функция 2

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


= 
Функция (
     Параметр 1;
     Параметр 2
 )

Если в функции 1 параметр и он состоит из вложенной функции, то разбиваем код на строки.


=
 Функция 1(
     Функция 2()
 )

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


= Функция (Значение)

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


=
 'Таблица'[Столбец]
 + [Мера 1]
 - [Мера 2]

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


=
 Функция (
     Параметр 1;
     Параметр 2 )

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


=
 Функция (
     Параметр 1;
     Параметр 2
 )

Параметры, находящиеся в новых строках пишутся с отступом в 4 пробела (1 табуляция) от первой буквы имени функции.


=
 Функция (
     Параметр 1;
     Параметр 2
 )

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


=
 Функция (
     Параметр 1;
     Параметр 2
 )

Для того, чтобы удобнее было переносить курсор на новую строку с автоматическим отступов в 4 символа, воспользуйтесь сочетанием горячих клавиш клавиатуры:
ALT+ENTER — перенос курсора на начало новой строки;
SHIFT+ENTER — перенос курсора на новую строку с автоматическим отступом в 4 символа.

Создание и визуализация мер

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

Создание мер

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

SUM (factonlinesales[salesamount]) - SUM (factonlinesales[discountamount])
где,
SUM (factonlinesales[salesamount])– сумма продаж;
SUM (factonlinesales[discountamount])– сумма скидок.

Чтобы создать меру, необходимо выполнить следующие шаги:

1 - На странице создания дашбордов выберите таблицу, в которой вы хотите создать меру (в нашем примере это таблица фактов factonlinesales), нажмите (…) и кликните Создать меру.

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

3- Введите в строке формулу:
SUM (factonlinesales[salesamount]) - SUM (factonlinesales[discountamount])
и нажмите на галочку слева, чтобы закончить создание меры.
Назовем нашу меру "Чистая выручка".

Для переименования меры, кликните дважды по её названию и введите новое имя.

ОБРАБОТКА ОШИБОК И ПУСТЫХ ЗНАЧЕНИЙ

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

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

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

ВИЗУАЛИЗАЦИЯ МЕР

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

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

Если у Вас появились вопросы - напишите нам

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

DAX VISIOLOGY

ВВЕДЕНИЕ

МОДЕЛЬ ДАННЫХ

Создание связи

Куда должна смотреть стрелка

Важные аспекты связей между таблицами:

ТИПЫ ДАННЫХ

Integer - Целое число.

Decimal - Десятичный.

Currency- Валюта (или десятичное число с фиксированной запятой)*.

DateTime - Дата и время.

Boolean - Логический**.

String - Строка.

Variant - Вариант*.

Binary - Двоичный*.

ОПЕРАТОРЫ DAX

ТИПЫ ВЫЧИСЛЕНИЙ DAX

1.Расчётный (вычисляемый) столбец*

2.Меры

СИНТАКСИС

КОНТЕКСТ

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

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

ФУНКЦИИ

Типы функций DAX

Итерационные функции или X-функции

Ключевые моменты о функциях DAX

ПРАВИЛА ФОРМАТИРОВАНИЯ ФОРМУЛ И ФУНКЦИЙ

Создание и визуализация мер

Создание мер

ОБРАБОТКА ОШИБОК И ПУСТЫХ ЗНАЧЕНИЙ

ВИЗУАЛИЗАЦИЯ МЕР