Создание DAX вычислений в Power BI Desktop, часть 1
Приблизительное время выполнения лабораторной работы – 45 минут.
В этой лабораторной работе вы создадите вычисляемые таблицы, вычисляемые столбцы и простые меры с помощью выражений анализа данных (DAX). После выполнения работы, вы будете знать, как:
- Создавать расчетные таблицы
- Создавать вычисляемые столбцы.
- Создавать меры
Для лабораторной работы вам понадобятся следующие материалы:
-
SQL сервер со следующими данными:
https://docs.microsoft.com/ru-ru/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms -
Набор файлов и расширений:
https://github.com/MicrosoftLearning/DA-100KO-Analyzing-Data-with-Power-BI/tree/master/Allfiles/DA-100-Allfiles
История лабораторной работы
Эта лабораторная работа – одна из многих в серии лабораторных работ, которые были разработаны в виде полного рассказа от подготовки данных до публикации в виде отчетов и информационных панелей. Вы можете выполнять лабораторные работы в любом порядке. Но, если вы намереваетесь выполнить несколько лабораторных работ, то мы предлагаем вам выполнить их в следующем порядке:
- Подготовка данных в Power BI Desktop.
- Загрузка данных в Power BI Desktop.
- Данные модели в Power BI Desktop, часть 1
- Данные модели в Power BI Desktop, часть 2
- Создание вычислений DAX в Power BI Desktop, часть 1
- Создание вычислений DAX в Power BI Desktop, часть 2
- Создание отчета в Power BI Desktop, часть 1
- Создание отчета в Power BI Desktop, часть 2
- Создание информационной панели Power BI.
- Создание отчета Power BI с разбивкой на страницы.
- Выполнение анализа данных в Power BI Desktop.
- Обеспечение безопасности на уровне строк
Упражнение 1: Создание вычисляемых таблиц
В этом упражнении вы создадите две расчетные таблицы. Первой будет таблица Salesperson, которая обеспечивает прямую связь между ней и таблицей Sales. Второй будет таблицей дат.
Задача 1. Начало
В этой задаче вы настроите среду для лабораторной работы.
Важно: если вы продолжаете предыдущую лабораторную работу (и вы ее уже успешно выполнили), не выполняйте эту задачу; вместо этого просто переходите к следующему заданию.
- Чтобы открыть Power BI Desktop, на панели задач щелкните на ярлыке Microsoft Power BI Desktop.
- Чтобы закрыть окно начала работы, в левом верхнем углу окна нажмите X.
- Чтобы открыть исходный файл Power BI Desktop, щелкните вкладку File на ленте, чтобы открыть представление Backstage.
- Выберите Open Report.
- Щелкните Browse Reports.
- В окне Open перейдите в папку D:\DA100\Labs\05-create-dax-calculations-in-power-bi-desktop\Starter.
- Выберите файл Sales Analysis.
- Щелкните Open.
- Закройте все информационные окна, которые могут открыться.
- Чтобы создать копию файла, щелкните вкладку ленты File, чтобы открыть представление Backstage.
- Выберите Save As.
- Если будет предложено применить изменения, нажмите Apply.
- В окне Save As перейдите в папку :\DA100\MySolution.
- Щелкните Save.
Задача 2: Создать таблицу Salesperson
). В этой задаче вы создадите таблицу Salesperson (прямая связь с Sales).
- В Power BI Desktop в представлении Report на ленте Modeling в группе Calculations щелкните New Table.
- В строке формул (которая открывается непосредственно под лентой при создании или редактировании вычислений) введите Salesperson =, нажмите Shift+Enter, введите ‘Salesperson (Performance)’, а затем нажмите Enter.
Для вашего удобства все определения DAX в этой лабораторной работе можно скопировать из файла фрагментов, расположенного в D:\DA100\Labs\05-create-dax-calculations-in-power-bi-desktop\Assets\Snippets.txt.
Вычисляемая таблица создается путем ввода сначала имени таблицы, затем символа равенства (=), а затем формулы DAX, которая возвращает таблицу. Обратите внимание, что выбираемое имя таблицы не должно повторять уже существующее в модели данных.
Строка формул поддерживает ввод действительной формулы DAX. Она включает в себя такие функции, как автозаполнение и цветовое кодирование, что позволяет быстро и точно вводить формулы.
Это определение таблицы создает копию таблицы Salesperson (Performance). Оно копирует только данные, однако свойства модели, такие как видимость, форматирование и т. д., не копируются.
Совет. Рекомендуется вводить «пробелы» (т. е. Символы возврата каретки и табуляции) в формулы макета в интуитивно понятном и удобном для чтения формате, особенно когда формулы длинные и сложные. Чтобы ввести возврат каретки, нажмите Shift+Enter. «Пробел» не является обязательным.
- Обратите внимание на то, что на панели Fields значок таблицы имеет оттенок синего (обозначает вычисляемую таблицу).
Вычисляемые таблицы определяются с помощью формулы DAX, которая возвращает таблицу. Важно понимать, что вычисляемые таблицы увеличивают размер модели данных, поскольку они материализуют и хранят значения. Они пересчитываются при каждом обновлении зависимостей формул, как и в случае с этой моделью данных, когда в таблицы загружаются новые (будущие) значения дат.
В отличие от таблиц, полученных из Power Query, вычисляемые таблицы нельзя использовать для загрузки данных из внешних источников данных. Они могут преобразовывать данные только на основе того, что уже было загружено в модель данных.
- Переключитесь в режим просмотра модели.
- Обратите внимание на то, чтобы таблица Salesperson была доступна (будьте осторожны, она может быть скрыта от просмотра; в этом случае прокрутите скрол по горизонтали, чтобы найти ее).
|
EmployeeKey ** к столбцу ** Sales EmployeeKey** к столбцу **Sales |
EmployeeKey**. |
- Щелкните правой кнопкой мыши на неактивную связь между Salesperson (Performance) и Sales и выберите Delete.
- Когда будет предложено подтвердить удаление, нажмите Delete.
-
В таблице Salesperson выберите несколько следующих столбцов и затем скройте их (установите для свойства Is Hidden значение Yes):
- EmployeeID
- EmployeeKey
- UPN
- На схеме модели выберите таблицу Salesperson.
- На панели Properties в поле Description введите: Salesperson related to Sales
Вы можете вспомнить, что описания появляются в виде всплывающих подсказок на панели Fields, когда пользователь наводит курсор на таблицу или поле.
- Для таблицы Salesperson (Performance) задайте описание: Salesperson related to region(s).
Модель данных теперь предоставляет две альтернативы при анализе продавцов. Таблица Salesperson позволяет анализировать продажи, сделанные продавцом, а таблица Salesperson (Performance) позволяет анализировать продажи, сделанные в регионе (ах) продаж, назначенном продавцу.
Задача 3. Создать таблицу дат
В этой задаче вы создадите таблицу Date.
- Переключитесь на просмотр данных.
- На вкладке ленты Home в группе Calculations щелкните New Table.
- В строке формул введите следующее:
DAX Code
Date = CALENDARAUTO(6)
Функция CALENDARAUTO() возвращает таблицу с одним столбцом, состоящую из значений дат. В «автоматическом» режиме сканируются все столбцы даты модели данных, чтобы определить самые ранние и самые поздние значения даты, хранящиеся в модели данных. Затем она создает по одной строке для каждой даты в этом диапазоне, расширяя диапазон в любом направлении, чтобы обеспечить хранение данных за полные годы.
Эта функция может принимать единственный необязательный аргумент, который является номером последнего месяца в году. Если не указано, значение равно 12, и это означает, что декабрь – последний месяц года. В этом случае вводится 6, что означает, что июнь – последний месяц в году.
- Обратите внимание на столбец значений даты.
Показанные даты отформатированы с использованием региональных настроек США (т. е. мм/дд/гггг).
- В нижнем левом углу в строке состояния обратите внимание на статистику таблицы, подтверждающую, что было сгенерировано 1826 строк данных, что представляет данные за пять полных лет.
Задача 4. Создание вычисляемых столбцов
В этой задаче вы добавите дополнительные столбцы, чтобы включить фильтрацию и группировку по разным периодам времени. Вы также создадите вычисляемый столбец для управления порядком сортировки других столбцов.
Для вашего удобства все определения DAX в этой лабораторной работе можно скопировать из файла фрагментов, расположенного в D:\DA100\Labs\05-create-dax-calculations-in-power-bi-desktop\Assets\Snippets.txt.
- На контекстной ленте Table Tools в группе Calculations щелкните New Column.
- В строке формул введите следующее (или скопируйте из файла фрагментов) и нажмите Enter:
DAX Code
Year =
"FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)
Вычисляемый столбец создается путем ввода сначала имени столбца, затем символа равенства (=), а затем формулы DAX, которая возвращает однозначный результат. Имя столбца не может уже существовать в таблице.
Формула использует значение года для даты, но добавляет единицу к значению года, если месяц находится после июня. Так рассчитываются финансовые годы в Adventure Works.
- Убедитесь, что был добавлен новый столбец.
-
Используйте определения файлов фрагментов, чтобы создать следующие два вычисляемых столбца для таблицы Date :
- Quarter
- Month
- Чтобы проверить расчеты, переключитесь в представление отчета.
- Чтобы создать новую страницу отчета, в нижнем левом углу щелкните значок плюса.
- Чтобы добавить матричный визуальный элемент на новую страницу отчета, на панели Visualizations выберите тип матричного визуального элемента.
Совет: вы можете навести курсор на каждый значок, чтобы отобразить всплывающую подсказку с описанием визуального типа.
- На панели Fields внутри таблицы Date перетащите поле Year в ячейку/область Rows.
- Перетащите поле Month в ячейку/область Rows непосредственно под полем Year.
- В правом верхнем углу визуала матрицы (или внизу, в зависимости от расположения визуала) щелкните на значок раздвоенной двойной стрелки (которая будет увеличиваться за все годы на один уровень).
- Обратите внимание, что годы расширяются до месяцев, и что месяцы отсортированы в алфавитном, а не в хронологическом порядке.
По умолчанию текстовые значения сортируются в алфавитном порядке, числа сортируются от меньшего к большему, а даты сортируются от самой ранней к самой поздней.
- Чтобы настроить порядок сортировки полей Month, переключитесь в представление Data.
- Добавьте столбец MonthKey в таблицу Date.
DAX Code
MonthKey =
(YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])
Эта формула вычисляет числовое значение для каждой комбинации год/месяц.
- В представлении Data убедитесь, что новый столбец содержит числовые значения (например, 201707 для июля 2017 г. и т. д.).
- Вернитесь к просмотру отчета.
- На панели Fields убедитесь, что выбрано поле Month (при выборе оно будет иметь темно-серый фон).
- На контекстной ленте Column Tools в группе Sort щелкните Sort by Column, а затем выберите MonthKey.
- Обратите внимание на то, что в визуальном элементе матрицы месяцы отсортированы в хронологическом порядке.
Задача 5: Заполнить таблицу дат
В этой задаче вы завершите создание таблицы Date, скрыв столбец и создав иерархию. Затем вы создадите отношения с таблицами Sales и Targets.
- Переключитесь в режим просмотра модели.
- В таблице Date скройте столбец MonthKey (установите для параметра Is Hidden значение Yes).
- На правой боковой панели Fields выберите таблицу Date, щелкните правой кнопкой мыши столбец Year и выберите create hierarchy.
- Переименуйте вновь созданную иерархию в Fiscal, щелкнув правой кнопкой мыши и выбрав Rename.
-
Добавьте следующие два оставшихся поля в финансовую иерархию, выбрав их на панели полей, щелкнув правой кнопкой мыши и выбрав Add to hierarchy -> Fiscal.
- Quarter
- Month
- Создайте следующие два модельных отношения:
|
**Date |
Date** к **Sales |
OrderDate** |
|
**Date |
Date** к **Targets |
TargetMonth** |
- Скройте следующие два столбца:
|
Sales |
OrderDate |
|
Targets |
TargetMonth |
Задача 6. Отметьте таблицу дат
В этой задаче вы пометите таблицу дат как таблицу Date.
- Переключитесь в представление отчета.
- На панели Fields выберите таблицу Date (а не поле Date).
- На контекстной ленте Table Tools в группе Calendars щелкните Mark as Date Table, а затем выберите Mark as Date Table.
- В окне Mark as Date Table в раскрывающемся списке Date Column выберите Date.
- Щелкните OK
- Сохраните файл Power BI Desktop.
Power BI Desktop теперь понимает, что эта таблица определяет дату (время). Это важно, если вы полагаетесь на вычисления тайм-интеллекта. Вы будете работать с вычислениями логики времени в лабораторной работе «Создание вычислений DAX в Power BI Desktop, часть 2».
Обратите внимание, что такой подход к созданию таблицы дат подходит, когда в вашем источнике данных нет таблицы дат. Если у вас есть хранилище данных, было бы целесообразно загружать данные даты из его таблицы измерения даты, а не «переопределять» логику дат в вашей модели данных.
Упражнение 2: Создание показателей
В этом упражнении вы создадите и отформатируете несколько мер.
Задача 1. Создать простые меры
В этом задании вы создадите простые меры. Простые меры собирают значения в одном столбце или подсчитывают строки таблицы.
|
Unit Price** в визуальный элемент матрицы. |
|
*В лабораторной работе используют сокращенное обозначение для ссылки на поле. Это будет выглядеть так: ** Sales |
Unit Price. В этом примере ** Sales – это имя таблицы, а Unit Price – имя поля. * |
- Возможно, вы помните, что в лабораторной работе «Данные модели в Power BI Desktop, часть 2» в столбце Unit Price вы устанавливали суммирование по среднему значению (Average). Результатом, который вы увидите в визуальном элементе матрицы, будет среднемесячная цена за единицу (сумма значений цен за единицу, деленная на количество цен за единицу).
- На панели визуальных полей (расположенной под панелью Visualizations) в колодце/области поля Values обратите внимание на то, что указана цена за единицу.
- Щелкните стрелку вниз для пункта Unit Price и обратите внимание на доступные параметры меню.
Видимые числовые столбцы позволяют авторам отчетов во время разработки отчета решать, как будут суммироваться значения столбцов (или не будут). Это может привести к незапланированному сообщению. Однако некоторые разработчики моделей данных не любят оставлять все на волю случая и предпочитают скрывать эти столбцы и вместо этого предоставить логику агрегирования, определенную в мерах. Это тот подход, которым вы сейчас воспользуетесь в этой лабораторной работе.
- Чтобы создать меру, на панели Fields щелкните правой кнопкой мыши таблицу Sales и выберите New Measure.
- В строке формул добавьте следующее определение меры:
DAX Code
Avg Price = AVERAGE(Sales[Unit Price])
- Добавьте меру Avg Price в визуальный элемент матрицы.
- Обратите внимание, что он дает тот же результат, что и столбец Unit Price (но с другим форматированием).
- В области Values откройте контекстное меню для поля Avg Price и обратите внимание, что невозможно изменить метод агрегирования.
Изменить агрегированное поведение меры невозможно.
-
Используйте определения файлов фрагментов, чтобы создать следующие пять показателей для таблицы Sales :
- Median Price
- Min Price
- Max Price
- Orders
- Orders Lines
Функция DISTINCTCOUNT(), используемая в измерении Orders, будет подсчитывать заказы только один раз (без учета дубликатов). Функция COUNTROWS(), используемая в показателе Order Lines, работает с таблицей.
В этом случае количество заказов рассчитывается путем подсчета различных значений столбца SalesOrderNumber, а количество строк заказа – это просто количество строк таблицы (каждая строка является строкой заказа).
- Переключитесь в режим просмотра модели, а затем выберите несколько bp четырех показателей цены: Avg Price, Max Price, Median Price, и Min Price.
-
Для множественного выбора мер настройте следующие требования:
- Установите формат до двух десятичных знаков
- Назначьте отображаемой папке с именем Pricing.
- Скройте столбец Unit Price.
Столбец Unit Price теперь недоступен авторам отчетов. Они должны использовать меры ценообразования, которые вы добавили в модель. Такой подход к разработке гарантирует, что авторы отчетов не будут неправильно агрегировать цены, например, суммируя их.
-
Выберите Order Lines и заказов Orders, а затем настройте следующие требования:
- Установите формат, используя разделитель тысяч
- Назначьте отображаемой папке с именем Counts
- В представлении Report в ячейке/области Values визуального элемента «Матрица» для поля Unit Price щелкните X, чтобы удалить его.
- Увеличьте размер визуального элемента матрицы, чтобы заполнить ширину и высоту страницы.
-
Добавьте следующие пять мер к визуальному элементу матрицы:
- Median Price
- Min Price
- Max Price
- Orders
- Orders Lines
- Убедитесь, что результаты выглядят нормально и правильно отформатированы.
Задача 2: Создать дополнительные меры
В этой задаче вы создадите дополнительные меры, использующие более сложные формулы.
- В представлении отчета выберите Page 1
- Просмотрите визуальный элемент таблицы и обратите внимание на общую сумму для столбца Target.
Возможно, вы помните из предыдущей лабораторной работы, что между продавцами и регионами существует связь "многие ко многим". Это означает, что суммирование целевых значений не имеет смысла, потому что целевые показатели для продавцов устанавливаются для каждого продавца в зависимости от их региона(-ов) продаж. Целевое значение должно отображаться только при фильтрации одного продавца. Теперь вы реализуете меру для этой цели.
- Выберите визуальный элемент таблицы, а затем на панели Visualizations удалите поле Target.
|
Target ** на ** Targets TargetAmount **. |
TargetAmount**. |
- Существует несколько способов переименовать столбец в представлении Report. На панели Fields можно щелкнуть столбец правой кнопкой мыши и выбрать Rename, либо дважды щелкнуть столбец, либо нажать F2.
- Вы собираетесь создать меру с именем Target. Невозможно получить столбец и показатель в одной таблице с одрним и тем же именем.
- Создайте следующую меру в таблице Targets :
DAX Code
Target =
IF(
HASONEVALUE('Salesperson (Performance)'[Salesperson]),
SUM(Targets[TargetAmount])
)
Функция HASONEVALUE() проверяет, фильтруется ли отдельное значение в столбце Salesperson. Если установлено значение TRUE, выражение возвращает сумму целевых сумм (только для этого продавца). Если FALSE, возвращается ПУСТО.
- Отформатируйте целевую меру (Target), указав ноль десятичных знаков.
Совет: вы можете использовать контекстную ленту Measure Tools.
- Скройте столбец TargetAmount.
Совет: вы можете щелкнуть правой кнопкой мыши столбец на панели Fields и выбрать Hide.
- Добавьте целевую меру в визуальный элемент таблицы.
- Обратите внимание, что в столбце Target теперь ПУСТО.
-
Используйте определения файлов фрагментов, чтобы создать следующие две меры для таблицы Targets :
- Variance
- Variance Margin
- Отформатируйте меру Variance для нулевых десятичных знаков.
- Отформатируйте меру Variance Margin с двумя десятичными знаками.
- Добавьте в визуальный элемент таблицы меры Variance и Variance Margin.
- Измените размер визуального элемента таблицы, чтобы можно было видеть все столбцы и строки.
Хотя кажется, что все продавцы не достигли цели, помните, что визуальный элемент таблицы еще не отфильтрован по определенному периоду времени. Вы будете создавать отчеты об эффективности продаж, которые фильтруются по выбранному пользователем периоду времени в лабораторной работе «Создание отчета в Power BI Desktop, часть 1».
- В правом верхнем углу панели Fields сверните, а затем разверните и откройте панель.
Сворачивание и повторное открытие панели сбрасывает содержимое.
- Обратите внимание, что таблица Targets теперь отображается вверху списка.
Таблицы, содержащие только видимые показатели, автоматически отображаются вверху списка.
Задача 3: Завершение
В этом задании вы завершите лабораторную работу.
- Сохраните файл Power BI Desktop.
- Если вы собираетесь начать следующую лабораторную работу, оставьте Power BI Desktop открытым.
Вы улучшите модель данных с помощью более сложных вычислений с использованием DAX в лабораторной работе «Создание вычислений DAX в Power BI Desktop, часть 2».
