Как писать запросы для табличных моделей с помощью DAX

Запросы к табличным моделям — это специфическая область знаний. Но каждый раз, когда вы создаёте визуализацию в отчёте, Power BI автоматически генерирует DAX-запросы. И порой полезно знать, как их писать для быстрого анализа данных. Давайте разберёмся с этим шаг за шагом.

Введение

EVALUATE — это ключевое слово для выполнения запросов к табличным моделям.

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

У EVALUATE только два «параметра»:

1. Таблица для отображения

2. Порядок сортировки (ORDER BY)

Можно передать третий параметр (START AT), но он используется редко.

Тем не менее, запрос DAX может содержать дополнительные компоненты, которые определяются в разделе DEFINE запроса.

В разделе DEFINE можно определить переменные и локальные меры.

Можно использовать ключевые слова COLUMN и TABLE в EVALUATE.

Давайте начнём с простых запросов и постепенно добавим дополнительную логику.

Но сначала давайте обсудим инструменты.

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

Есть два способа выполнения запроса к табличной модели:

1. Использование представления запросов DAX в Power BI Desktop.

2. Использование DAX Studio.

Конечно, синтаксис одинаков.

Я предпочитаю DAX Studio, поскольку он предлагает расширенные функции для анализа производительности, такие как серверный тайминг и отображение метрик модели, которые в Power BI Desktop недоступны.

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

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

В этой статье я буду использовать только DAX Studio.

Простые запросы

Самый простой запрос — это получить все столбцы и все строки из таблицы:

EVALUATE
     Customer

Этот запрос возвращает всю таблицу Customer.

Рисунок 1 — Простой запрос к таблице Customer. Количество возвращённых строк отображается в нижнем правом углу DAX Studio. Изображение автора

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

Для этого используются фигурные скобки.

Следовательно, запрос для меры будет выглядеть так:

EVALUATE<br>     { [Online Customer Count]}

Результатом будет одно числовое значение, соответствующее мере.

Рисунок 2 — Запрос меры с использованием фигурных скобок для определения таблицы. Изображение автора

Получить только первые 10 строк

Бывает, что таблицы содержат тысячи или даже миллионы строк.

Что если я хочу увидеть первые 10 строк, чтобы ознакомиться с данными внутри таблицы?

Для этого можно использовать функцию TOPN().

TOPN() принимает критерии сортировки, но сама не выполняет сортировку данных. Она просто анализирует значения и возвращает первые или последние строки, исходя из заданного порядка.

Например, давайте получим данные о десяти клиентах с самой поздней датой рождения (по убыванию):

EVALUATE<br>    TOPN(10<br>        ,Customer<br>        ,Customer[BirthDate]<br>        ,DESC)

Результат будет таким:

Рисунок 3 — Использование функции TOPN() для получения 10 строк по дате рождения. Видно, что вернулось 11 строк, так как у некоторых клиентов одинаковая дата рождения. Изображение автора

В статье на DAX.guide о TOPN() говорится следующее о ничьих в данных:

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

Это объясняет, почему из запроса возвращается 11 строк, так как у нескольких клиентов одинаковая дата рождения. При сортировке результата мы увидим ничью для последней даты — 26 ноября 1980 года.

Чтобы отсортировать результат по дате рождения, необходимо добавить оператор ORDER BY:

EVALUATE<br>    TOPN(10<br>        ,Customer<br>        ,Customer[BirthDate]<br>        ,DESC)<br>    ORDER BY Customer[BirthDate] DESC

А вот результат:

Рисунок 4 — Результат того же запроса TOPN(), но с ORDER BY, чтобы отсортировать результат по дате рождения в порядке убывания. (Изображение автора)

Теперь ничья для последних двух строках чётко видна после сортировки.

Добавление столбцов

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

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

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

• ADDCOLUMNS()

• SELECTCOLUMNS()

• SUMMARIZE()

• SUMMARIZECOLUMNS()

Из этих четырех SUMMARIZECOLUMNS() — наиболее полезная для большинства общих случаев.

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

Чтобы узнать больше, прочитайте эту статью.

Хорошо, как можно использовать SUMMARIZECOLUMNS() в запросе:

EVALUATE<br>    SUMMARIZECOLUMNS('Customer'[CustomerType])

Результат будет следующим:

Рисунок 5 — Получение уникальных значений столбца CustomerType с использованием SUMMARIZECOLUMNS(). (Изображение автора)

Как видно, запрос возвращает только уникальные значения из столбца CustomerType.

При запросе нескольких столбцов результатом будут уникальные комбинации существующих данных:

Рисунок 6 — Запрос нескольких столбцов. (Изображение автора)

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

EVALUATE<br>    SUMMARIZECOLUMNS('Customer'[CustomerType]
<br>                        ,Customer[Gender]
<br>                        ,"Number of Customers", 
[Online Customer Count])

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

Результат выполнения запроса будет следующим:

Рисунок 7 — Результат запроса с несколькими столбцами и мерой. (Изображение автора)

Можно добавить столько столбцов и мер, сколько необходимо.

Добавление фильтров

Функция CALCULATE() хорошо известна для добавления фильтров в меру.

Для запросов используется функция CALCULATETABLE(), которая работает как CALCULATE(), но первым аргументом принимает таблицу.

Это тот же запрос, что и ранее, но теперь мы фильтруем CustomerType так, чтобы включать только значения "Person":

EVALUATE<br>CALCULATETABLE(<br>    
SUMMARIZECOLUMNS('Customer'[CustomerType]
<br>                        ,Customer[Gender]
<br>                        ,"Number of Customers", 
[Online Customer Count])<br>                ,'Customer'[CustomerType] = "Person"
<br>                )

Результат:

Рисунок 8 — Запрос и результат фильтрации типа клиента на «Person». (Изображение автора)

Можно добавлять фильтры непосредственно в SUMMARIZECOLUMNS(). Запросы, генерируемые Power BI, используют такой подход. Однако этот подход сложнее и менее гибок, чем использование CALCULATETABLE().

Примеры использования этого подхода можно найти на странице DAX.guide для SUMMARIZECOLUMNS().

Power BI использует этот подход, когда строит запросы из визуализаций. Вы можете получить запросы из Performance Analyzer в Power BI Desktop.

Вы можете ознакомиться с моей статьёй о сборе данных производительности в Power BI, чтобы узнать, как использовать Performance Analyzer для получения запроса из визуализации.

Также вы можете ознакомиться с документацией Microsoft, которая объясняет этот процесс.

Определение локальных мер

С моей точки зрения, это одна из самых мощных возможностей языка запросов DAX:

Добавление локальных мер в запрос.

Для этого используется ключевое слово DEFINE.

Например, у нас есть мера Online Customer Count. Теперь я хочу применить фильтр, чтобы посчитать только клиентов типа «Person».

Я могу изменить код в модели данных или протестировать логику непосредственно в запросе DAX.

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

Для этого мне нужно поставить курсор на первую строку запроса. В идеале — добавить пустую строку в запрос.

Теперь я могу использовать DAX Studio, чтобы извлечь код меры и добавить его в запрос, нажав правой кнопкой мыши на мере и выбрав «Define Measure»:

Рисунок 9 — Использование функции «Define Measure» в DAX Studio для извлечения кода меры. (Изображение автора)

Аналогичная функция доступна и в Power BI Desktop.

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

DEFINE <br>---- MODEL MEASURES BEGIN ----<br>MEASURE 'All 
Measures'[Online Customer Count] =
<br>    CALCULATE(DISTINCTCOUNT('Online Sales'[CustomerKey])
<br>                ,'Customer'[CustomerType] = "Person"
<br>                )<br>---- MODEL MEASURES END ----

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

Рисунок 10 — Запрос и результаты с измененным DAX кодом для меры. (Изображение автора)

Как только DAX код работает как ожидается, его можно использовать для изменения меры в модели данных Power BI Desktop.

Просмотр запросов DAX в Power BI Desktop имеет свои преимущества, так как вы можете прямо нажать правой кнопкой мыши на изменённом коде и применить его обратно в модель данных. Ознакомьтесь с инструкциями, как это сделать.

DAX Studio не поддерживает функцию добавления изменений обратно в модель данных Power BI.

Собираем всё воедино

Теперь давайте объединим все части и составим следующий запрос: «Я хочу получить топ-5 продуктов, которые клиенты заказывали чаще всего».

Использую запрос из предыдущего примера, изменяю его, чтобы вывести названия продуктов, и добавляю функцию TOPN():

DEFINE 
---- MODEL MEASURES BEGIN ----
MEASURE 'All Measures'[Online Customer Count] =
    CALCULATE(DISTINCTCOUNT('Online Sales'[CustomerKey])
                ,'Customer'[CustomerType] = "Person"
                )
---- MODEL MEASURES END ----

EVALUATE
    TOPN(5
        ,SUMMARIZECOLUMNS('Product'[ProductName]
                        ,"Number of Customers", [Online Customer Count]
                        )
        ,[Number of Customers]
        ,DESC)
    ORDER BY [Number of Customers]

Обратите внимание, что я передаю метку меры, «Number of Customers», а не её имя.

Я должен сделать это так, так как в DAX синтаксисе имя меры заменяется на метку. Поэтому DAX не имеет информации о мере и знает только метку.

Вот результат запроса:

Рисунок 11 — Результат запроса с использованием функции TOPN(), комбинированного с мерой. Обратите внимание, что вместо имени меры используется метка. (Изображение автора)

Заключение

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

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

Кроме того, я могу изменять меры, не внося изменений в Power BI Desktop, и быстро проверять результаты в таблице. Я могу использовать меру из модели данных, временно создать её измененную версию и проверять результаты рядом.

Запросы DAX имеют множество сценариев применения и должны быть частью набора инструментов каждого Power BI разработчика.

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

Ссылки

Документация Microsoft о применении изменений в модели данных через представление запросов DAX:
Обновление модели с изменениями — просмотр запросов DAX — Power BI | Microsoft Learn

Я использовал набор данных Contoso. Набор данных ContosoRetailDW можно бесплатно скачать с сайта Microsoft.

Данные Contoso можно свободно использовать по лицензии MIT, как описано в этом документе. Я изменил набор данных, чтобы сдвинуть данные на современные даты.

---

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

• 30 апреля в 18:00 — Data Science — это проще, чем кажется!
  Как начать работать с машинным обучением и создать свою первую ML-модель.

• 12 мая в 20:00 — DWH, Data Lake, Data Lakehouse. Что это такое и в чём разница
  Разберитесь в архитектурах для обработки больших данных и выберите оптимальный подход.

• 12 мая в 18:00 — Kafka Connect: Легкая интеграция с внешними системами
  Узнайте, как быстро интегрировать данные с Kafka и решить типовые проблемы.

Больше актуальных навыков по аналитике и анализу вы можете получить в рамках практических онлайн-курсов от экспертов отрасли.