Изучите основы DAX в Power BI Desktop

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

Что такое DAX?

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

Почему DAX так важен?

Легко создать новый файл Power BI Desktop и импортировать в него некоторые данные. Вы даже можете создавать отчеты, которые показывают ценные аналитические сведения без использования формул DAX вообще. Но что делать, если вам нужно проанализировать процент роста по категориям продуктов и для разных диапазонов дат? Или вы должны вычислить годовой рост по сравнению с рыночными тенденциями? Формулы DAX предоставляют эту возможность и многие другие важные возможности. Изучение создания эффективных формул DAX поможет вам максимально эффективно использовать ваши данные. Когда вы получите необходимую информацию, вы можете начать решать реальные бизнес-проблемы, влияющие на ваш чистый доход.

Необходимые условия

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

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

Пример рабочей книги

Лучший способ узнать DAX — создать некоторые основные формулы, использовать их с фактическими данными и просмотреть результаты самостоятельно. В этих примерах и задачах используется файл образца продаж Contoso для Power BI Desktop. Этот образец файла аналогичен тому, который используется в руководстве «Создание собственных мер в Power BI Desktop».

Начнем

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

Синтаксис

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

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

А. Имя меры, Total Sales.

В. Оператор знака равенства (=), указывающий начало формулы. При вычислении будет возвращен результат.

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

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

Е. Указанная таблица Продажи.

F. Указанный столбец [SalesAmount] в таблице Sales. С помощью этого аргумента функция SUM знает, на каком столбце следует агрегировать сумму.

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

Для меры с именем Total Sales вычислите (=) сумму значений в столбце [SalesAmount] в таблице Sales.

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

Возможно, вы думаете: "Не так ли эта мера делает то же самое, как если бы я просто добавить поле SalesAmount в мой отчет?" Ну, да. Но есть хорошая причина для создания нашей собственной меры, которая суммирует значения из поля SalesAmount: мы можем использовать его в качестве аргумента в других формулах. Это решение может показаться немного запутанным сейчас, но по мере развития ваших навыков формул DAX знание этой меры сделает ваши формулы и модели более эффективными. На самом деле, вы увидите показатель Total Sales, отображаемый как аргумент в других формулах позднее.

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

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

Замечание

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

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

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

Задача. Создание формулы меры

1. Скачайте и откройте файл Power BI Desktop с примером продаж Contoso.

2. В представлении отчета в списке полей щелкните правой кнопкой мыши таблицу "Продажи " и выберите "Создать меру".

3. В строке формул замените "Меру ", введя новое имя меры, предыдущий квартал продаж.

4. После знака равенства введите первые несколько букв CAL, а затем дважды щелкните нужную функцию. В этой формуле необходимо использовать функцию CALCULATE .

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

5. После открытия скобки (для функции CALCULATEвведитеSUM, за которым следует еще одна открываемая скобка ().

   Затем мы передадим аргумент функции SUM.

6. Начните вводить Sal, а затем выберите Sales[SalesAmount], за которым следует закрывающая скобка ).

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

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

   Вы будете использовать функцию аналитики времени PREVIOUSQUARTER для фильтрации результатов СУММ по предыдущему кварталу.

8. После открытия скобки ( для функции PREVIOUSQUARTER введите Calendar[DateKey].

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

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

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

   Продажи предыдущего квартала = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey]))

10. Выберите в строке формул или нажмите клавишу ВВОД, чтобы проверить формулу и добавить ее в таблицу «Продажи».

Вы справились! Вы только что создали сложную меру с помощью DAX. То, что будет делать эта формула, — вычислить общий объем продаж за предыдущий квартал в зависимости от фильтров, примененных в отчете. Например, мы можем поместить SalesAmount и нашу новую меру продаж предыдущего квартала из таблицы «Продажи» в объединённую столбчатую диаграмму. Затем в таблице "Календарь" добавьте год в виде среза и выберите 2011. Затем добавьте QuarterOfYear в качестве другого среза и выберите 4, и мы получаем диаграмму, как показано ниже:

Имейте в виду, что пример модели содержит только небольшое количество данных о продажах с 1.1.2011 по 1.19.2013. Если вы выберете год или квартал, где SalesAmount не может быть суммирована, или новая мера не может вычислить данные о продажах за текущий или предыдущий квартал, данные за этот период не отображаются. Например, если вы выбрали 2011 год и 1 для QuarterOfYear, данные не отображаются для продаж предыдущего квартала, так как данные за четвертый квартал 2010 года отсутствуют.

Вы ознакомились с несколькими важными аспектами формул DAX:

• Эта формула включала две функции. PREVIOUSQUARTER, функция аналитики времени, вложена в качестве аргумента, передаваемого в CALCULATE, функцию фильтра.

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

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

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

Синтаксис QuickQuiz

1. Что делает эта кнопка в строке формул?

   

2. Что всегда окружает имя столбца в формуле DAX?

Ответы приведены в конце этой статьи.

Функции

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

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

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

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

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

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

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

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

Функции QuickQuiz

1. На что всегда ссылается функция?
2. Может ли формула содержать несколько функций?
3. Какую категорию функций можно использовать для объединения двух текстовых строк в одну строку?

Ответы приведены в конце этой статьи.

Контекст

Контекст является одним из наиболее важных понятий DAX для понимания. В DAX есть два типа контекста: контекст строки и контекст фильтра. Сначала рассмотрим контекст строки.

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

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

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

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

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

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

Почему контекст фильтра так важен для DAX? Вы видели, что контекст фильтра можно применить, добавив поля в визуализацию. Контекст фильтра также можно применить в формуле DAX, определив фильтр с такими функциями, как ALL, RELATED, FILTER, CALCULATE, по связям, а также по другим мерам и столбцам. Например, давайте рассмотрим следующую формулу в мере по имени Store Sales:

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

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

А. Имя меры, Продажи в магазине.

В. Оператор знака равенства (=), указывающий начало формулы.

В. Функция CALCULATE , которая вычисляет выражение в качестве аргумента в контексте, который изменяется указанными фильтрами.

Д. Скобки (), которые окружают выражение, содержащее один или несколько аргументов.

Е. Мера [Итог продаж] в той же таблице, что и выражение. Мера Total Sales имеет формулу: =SUM(Sales[SalesAmount]).

F. Запятая (,), которая отделяет первый аргумент выражения от аргумента фильтра.

G. Полностью квалифицированный ссылочный столбец Channel[ChannelName]. Это наш контекст строки. Каждая строка в этом столбце указывает канал, например Store или Online.

H. Конкретное значение, Store, в качестве фильтра. Это наш контекст фильтра.

Эта формула гарантирует, что значения продаж, определенные мерой Total Sales, вычисляются исключительно для строк в столбце Channel[ChannelName] с использованием фильтра, где значение Store.

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

Контекст QuickQuiz

1. Каковы два типа контекста?
2. Что такое контекст фильтра?
3. Что такое контекст строки?

Ответы приведены в конце этой статьи.

Рекомендации

Учитывайте, что в то время как редактор Power Query учитывает регистр, DAX и подсистема служб Analysis Services, которые работают с Power BI, не учитывают регистр после загрузки данных в интерфейсную часть. Имейте в виду это различие при написании запросов DAX.

Сводка

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

Так как DAX уже несколько лет работает в других инструментах Microsoft BI, таких как табличные модели Power Pivot и analysis Services, существует множество отличных источников информации. Дополнительные сведения можно найти в книгах, технической документации и блогах как майкрософт, так и ведущих специалистов бизнес-аналитики. Центр ресурсов DAX также является отличным местом для начала.

Ответы QuickQuiz

Syntax:

1. Проверяет и вводит меру в модель.
2. Скобки [].

Функции:

1. Таблица и столбец.
2. Да. Формула может содержать до 64 вложенных функций.
3. Текстовые функции.

Контекст:

1. Контекст строк и контекст фильтра.
2. Один или несколько фильтров в вычислении, определяющих одно значение.
3. Текущая строка.