Создание вычислений DAX в Power BI Desktop, часть 2
Приблизительное время выполнения лабораторной работы – 45 минут.
В этой лабораторной работе вы создадите меры с помощью выражений DAX, включая манипуляции с контекстом фильтра. После выполнения работы, вы будете знать, как:
- Использовать функцию CALCULATE () для управления контекстом фильтра.
- Использовать функции логики операций со временем
Для лабораторной работы вам понадобятся следующие материалы:
-
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
История лабораторной работы
Эта лабораторная работа – одна из многих в серии лабораторных работ, которые были разработаны в виде полного рассказа от подготовки данных до публикации в виде отчетов и информационных панелей. Вы можете выполнять лабораторные работы в любом порядке. Но, если вы намереваетесь выполнить несколько лабораторных работ, для первых 10 лабораторных работ мы предлагаем вам выполнить их в следующем порядке:
- Подготовка данных в 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: Работа с фильтром контекста
В этом упражнении вы создадите меры с помощью выражений DAX, включая манипуляции с контекстом фильтра.
Задача 1: Начало
В этой задаче вы настроите среду для лабораторной работы.
Важно: если вы продолжаете предыдущую лабораторную работу (вы ее уже успешно выполнили), не выполняйте эту задачу; вместо этого просто переходите к следующему заданию.
- Чтобы открыть Power BI Desktop, на панели задач щелкните на ярлыке Microsoft Power BI Desktop.
- Чтобы закрыть окно начала работы, в левом верхнем углу окна нажмите X.
- Чтобы открыть исходный файл Power BI Desktop, щелкните вкладку File на ленте, чтобы открыть представление Backstage.
- Выберите Open Report.
- Щелкните Browse Reports
- В окне Open перейдите в папку D:\DA100\Labs\04-configure-data-model-in-power-bi-desktop-advanced\Starter.
- Выберите файл Sales Analysis.
- Щелкните Open.
- Закройте все информационные окна, которые могут открыться.
- Чтобы создать копию файла, щелкните вкладку ленты File, чтобы открыть представление Backstage.
- Выберите Save As.
- . Если будет предложено применить изменения, нажмите Apply.
- В окне Save As перейдите в папку D:\DA100\MySolution .
- Щелкните Save.
Задача 2: Создать матричный визуал
В этой задаче вы создадите матричный визуальный элемент для поддержки тестирования ваших новых мер.
- В Power BI Desktop в представлении отчета создайте новую страницу отчета.
- На странице 3 добавьте матричный визуал.
- Измените размер визуального элемента матрицы, чтобы заполнить всю страницу.
|
** и поместите ее внутрь визуального элемента. |
|
* В лабораторной работе используют сокращенную запись для ссылки на поле или иерархию. Это будет выглядеть так: ** Region |
Regions. В этом примере ** Region – это имя таблицы, а Regions – это имя иерархии. * |
|
|
Sales **. |
- Чтобы развернуть всю иерархию, в правом верхнем углу визуального элемента матрицы дважды щелкните значок двойной стрелки с раздвоенной стрелкой.
Как вы помните, в иерархии Regions есть уровни Group, Country, и Region.
- Чтобы отформатировать визуал, под панелью Visualizations выберите панель Format.
- В поле Search введите Stepped.
- Установите для свойства Stepped Layout значение Off.
- Убедитесь, что у визуального элемента матрицы теперь четыре заголовка столбцов.
В Adventure Works регионы продаж разделены на группы, страны и регионы. Во всех странах, кроме США, есть только один регион, названный в честь страны. Поскольку Соединенные Штаты являются большой территорией сбыта, они разделены на пять регионов сбыта.
В этом упражнении вы создадите несколько мер, а затем протестируете их, добавив в матрицу визуализации.
Задача 3. Управление контекстом фильтра
В этой задаче вы создадите несколько мер с выражениями DAX, которые используют функцию CALCULATE() для управления контекстом фильтра.
- Добавьте меру в таблицу Sales на основе следующего выражения:
Для вашего удобства все определения DAX в этой лабораторной работе можно скопировать из файла D:\DA100\Labs\06-create-dax-calculations-in-power-bi-desktop-advanced\Assets\Snippets.txt.
DAX Code
Sales All Region = CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region))
Функция CALCULATE() – мощная функция, используемая для управления контекстом фильтра. Первый аргумент принимает выражение или меру (мера – это просто именованное выражение). Последующие аргументы позволяют изменять контекст фильтра.
Функция REMOVEFILTERS() удаляет активные фильтры. Она может принимать в качестве аргумента либо отсутствие аргументов, либо таблицу, столбец или несколько столбцов.
В этой формуле мера оценивает сумму столбца Sales в измененном контексте фильтра, который удаляет все фильтры, примененные к столбцам таблицы Region.
- Добавьте меру Sales All Region в визуальный элемент матрицы.
- Обратите внимание, что мера Sales All Region вычисляет общую сумму продаж по всем регионам для каждого региона, страны (промежуточный итог) и группы (промежуточный итог).
Новая мера пока не дала полезного результата. Когда продажи для группы, страны или региона делятся на это значение, получается полезное соотношение, известное как «процент от общей суммы».
- На панели Fields убедитесь, что выбрана мера Sales All Region (при выборе она будет на темно-сером фоне), а затем в строке формул замените имя меры и формулу следующей формулой:
Совет: чтобы заменить существующую формулу, сначала скопируйте фрагмент. Затем щелкните внутри строки формул и нажмите Ctrl+A, чтобы выделить весь текст. Затем нажмите Ctrl+V, чтобы вставить фрагмент и перезаписать выделенный текст. Затем нажмите Enter.
DAX Code
Sales % All Region = DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region) ) )
Мера была переименована, чтобы точно отражать обновленную формулу. Функция DIVIDE() делит меру Sales (не измененную контекстом фильтра) на меру Sales в измененном контексте, что удаляет все фильтры, примененные к таблице Region.
- В визуальном элементе матрицы обратите внимание на то, что мера была переименована и теперь отображаются разные значения для каждой группы, страны и региона.
- Отформатируйте показатель Sales % All Region как процент с двумя десятичными знаками.
- В визуальном элементе матрицы просмотрите значения меры Sales % All Region.
- Добавьте еще одну меру в таблицу Sales на основе следующего выражения и отформатируйте ее в процентах:
DAX Code
Sales % Country = DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region[Region]) ) )
- Обратите внимание на то, что формула показателя Sales % Country немного отличается от формулы показателя Sales % All Region.
Разница в том, что знаменатель изменяет контекст фильтра, удаляя фильтры в столбце Region таблицы Region, а не во всех столбцах таблицы Region. Это означает, что все фильтры, примененные к столбцам группы или страны, сохраняются. В результате будет получен результат, представляющий продажи в процентах от страны.
- Добавьте меру Sales % Country в визуальный элемент матрицы.
- Обратите внимание, что только регионы США производят значение, отличное от 100%.
Вы можете вспомнить, что только в США есть несколько регионов. Все остальные страны составляют единый регион, что объясняет, почему они все на 100%.
- Чтобы улучшить удобочитаемость этой меры в визуальном представлении, замените меру Sales % Country этой улучшенной формулой.
DAX Code
Sales % Country = IF( ISINSCOPE(Region[Region]), DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region[Region]) ) ) )
Встроенная в функцию IF() функция ISINSCOPE() используется для проверки того, является ли столбец региона уровнем в иерархии уровней. В случае True, вычисляется функция DIVIDE(). Отсутствие ложной части означает, что возвращается пустое значение, когда столбец региона не входит в область видимости.
- Обратите внимание, что мера Sales % Country теперь возвращает значение, только если регион входит в область действия.
- Добавьте еще одну меру в таблицу Sales на основе следующего выражения и отформатируйте ее в процентах:
DAX Code
Sales % Group = DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS( Region[Region], Region[Country] ) ) )
Чтобы добиться продаж в процентах от группы, можно применить два фильтра, чтобы эффективно удалить фильтры в двух столбцах.
- Добавьте меру Sales % Group в визуальный элемент матрицы.
- Чтобы улучшить удобочитаемость этой меры в визуальном представлении, замените меру Sales % Group этой улучшенной формулой.
DAX Code
Sales % Group = IF( ISINSCOPE(Region[Region]) || ISINSCOPE(Region[Country]), DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS( Region[Region], Region[Country] ) ) ) )
- Обратите внимание, что мера Sales % Group теперь возвращает значение, только если регион или страна входят в область действия.
- В представлении модели поместите три новых показателя в папку отображения с именем Ratios.
- Сохраните файл Power BI Desktop.
Меры, добавленные в таблицу Sales, изменили контекст фильтра для достижения иерархической навигации. Обратите внимание, что шаблон для вычисления промежуточного итога требует удаления некоторых столбцов из контекста фильтра, а для получения общего итога необходимо удалить все столбцы.
Упражнение 2: Работа с Time Intelligence
В этом упражнении вы создадите показатель продаж с начала года до текущей даты (YTD) и показатель роста продаж по сравнению с прошлым годом (YoY).
Задача 1. Создать показатель с начала года
В этой задаче вы создадите показатель продаж с начала года.
- В представлении отчета на Page 2 обратите внимание на визуальный элемент матрицы, который отображает различные показатели с годами и месяцами, сгруппированными в строках.
- Добавьте показатель в таблицу Sales на основе следующего выражения, отформатированный до нуля десятичных знаков:
DAX Code
Sales YTD = TOTALYTD(SUM(Sales[Sales]), 'Date'[Date], "6-30")
Функция TOTALYTD() оценивает выражение – в данном случае сумму столбца Sales – по заданному столбцу даты. Столбец даты должен принадлежать таблице дат, помеченной как таблица дат, как это было сделано в лабораторной работе «Создание вычислений DAX в Power BI Desktop, часть 1».
Функция также может принимать третий необязательный аргумент, представляющий последнюю дату года. Отсутствие этой даты означает, что 31 декабря - последняя дата года. Для Adventure Works июнь последнего месяца года, поэтому используется цифра «6–30».
- Добавьте поле Sales и показатель Sales YTD к визуальному элементу матрицы.
- Обратите внимание на накопление продаж в течение года.
Функция TOTALYTD() выполняет манипуляции с фильтрами, в частности, с временными фильтрами. Например, для расчета продаж с начала года за сентябрь 2017 г. (третий месяц финансового года) все фильтры в таблице Date удаляются и заменяются новым фильтром дат, начинающихся в начале года (1 июля 2017 г.) и продлевается до последней даты периода контекстных дат (30 сентября 2017 г.).
Обратите внимание, что в DAX доступно множество функций Time Intelligence для поддержки обычных манипуляций с фильтрами времени.
Задача 2. Создать показатель роста в годовом исчислении
В этой задаче вы создадите показатель роста продаж в годовом исчислении.
- Добавьте дополнительную меру в таблицу Sales на основе следующего выражения:
DAX Code
Sales YoY Growth = VAR SalesPriorYear = CALCULATE( SUM(Sales[Sales]), PARALLELPERIOD( 'Date'[Date], -12, MONTH ) ) RETURN SalesPriorYear
Формула измерения Sales YoY Growth объявляет переменную. Переменные могут быть полезны для упрощения логики формулы и более эффективны, когда выражение необходимо вычислять несколько раз в формуле (что будет иметь место для логики роста в годовом исчислении). Переменные объявляются по уникальному имени, и выражение меры должно выводиться после ключевого слова RETURN.
Переменной SalesPriorYear назначается выражение, которое вычисляет сумму столбца Sales в измененном контексте, использующем функцию PARALLELPERIOD() для сдвига на 12 месяцев назад с каждой даты в контексте фильтра.
Добавьте показатель Sales YoY Growth к визуальному элементу матрицы.
- Обратите внимание, что новая мера возвращает ПУСТО в течение первых 12 месяцев (поскольку до 2017 финансового года не было зарегистрировано никаких продаж).
- Обратите внимание, что значение показателя Sales YoY Growth за июль 2018 года является значением продаж за июль 2017 года.
Теперь, когда «сложная часть» формулы протестирована, вы можете заменить меру окончательной формулой, которая вычисляет результат роста.
- Чтобы завершить измерение, перезапишите показатель Sales YoY Growth этой формулой, отформатировав его как процент с двумя десятичными знаками:
DAX Code
Sales YoY Growth = VAR SalesPriorYear = CALCULATE( SUM(Sales[Sales]), PARALLELPERIOD( 'Date'[Date], -12, MONTH ) ) RETURN DIVIDE( (SUM(Sales[Sales]) - SalesPriorYear), SalesPriorYear )
- Обратите внимание, что в формуле в предложении RETURN на переменную ссылаются дважды.
- Убедитесь, что рост в годовом сопоставлении за 2018 Jul года составляет 392.83%.
Это означает, что продажи в июле 2018 года (2 411 559 долларов США) представляют собой почти 400% (почти в 4 раза) улучшение по сравнению с продажами, достигнутыми в то же время предыдущего года (489 328 долларов США).
- В представлении модели поместите две новые меры в папку отображения с именем Time Intelligence.
Задача 3: Завершение
В этом задании вы завершите лабораторную работу.
- Чтобы очистить решение, готовое для разработки отчета, в левом нижнем углу щелкните правой кнопкой мыши вкладку Page 2 и выберите страницу Delete.
- Когда будет предложено удалить страницу, нажмите Delete.
- Также удалите Page 3.
- На оставшейся странице, чтобы очистить страницу, выберите визуальный элемент таблицы и нажмите клавишу Delete.
- Сохраните файл Power BI Desktop.
- Если вы собираетесь начать следующую лабораторную работу, оставьте Power BI Desktop открытым.
Вы создадите отчет на основе модели данных из лабораторной работы «Создание отчета в Power BI Desktop, часть 1».