Построение вычисляемых столбцов в Power BI

Узнайте, как создавать вычисляемые столбцы в Power BI с помощью различных сценариев использования, в этой статье Мануэля Кинтана, соавтора Краткое руководство по началу работы с Microsoft Power BI.

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

Создание вычисляемого столбца

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

Эта встроенная функция автоматически дополняет код по ходу работы, а также помогает исследовать и открывать новые функции языка DAX. Чтобы воспользоваться преимуществами IntelliSense, вам просто нужно начать печатать в строке формул. Теперь вы готовы начать писать DAX!

Щелкните представление данных — оно расположено в левой части экрана Power BI Desktop. Затем щелкните таблицу клиентов в списке полей. После выбора таблицы клиентов нажмите «Новый столбец» — он находится под лентой моделирования, как показано на следующем снимке экрана:

1.png

Теперь вы увидите текст Column = в строке формул. Сначала назовите новый столбец, заменив текст столбца по умолчанию полным именем. Затем переместите курсор после знака равенства и введите одинарную кавычку.

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

2.png

Затем введите открывающую квадратную скобку в строку формул, а затем заглавную букву F. И снова вам сразу же будут представлены параметры автозаполнения. Список параметров ограничен только столбцами, содержащими букву f, и первый параметр, доступный в раскрывающемся списке, — «Имя». Нажмите вкладку для автозаполнения. Теперь строка формул должна содержать следующую формулу:

Full Name="Customer"[First Name]

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

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

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

Full Name="Customer"[First Name] & " " & 'Customer'[Last Name]

Строковые функции – Месяц, Год

Теперь, когда вы завершили свой первый вычисляемый столбец, пришло время построить вычисляемый столбец, в котором хранится значение месяца-года. Цель состоит в том, чтобы вернуть столбец месяц-год с двухзначным месяцем и четырехзначным годом, разделенными тире, что делает его «ММ-ГГГГ».

Выберите таблицу «Дата (порядок)», а затем нажмите «Новый столбец» на ленте моделирования. Напишите следующий код в строке формул и нажмите Enter:

Month Year="Date (Order)"[Month Number of Year]

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

Month Year = "0" & 'Date (Order)'[Month Number of Year]

Это будет работать для однозначных месяцев; однако месяцы с двузначным числом теперь будут возвращать три цифры. Взгляните на следующий скриншот:

3.png

Чтобы улучшить это и вернуть только двузначный месяц, вы можете использовать функцию ПРАВИЛЬНО. Функция RIGHT возвращает указанное количество символов с правой стороны строки. Измените существующую формулу DAX, чтобы она выглядела следующим образом:

Month Year = RIGHT("0" & 'Date (Order)'[Month Number of Year], 2)

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

Month Year = RIGHT("0" & 'Date (Order)'[Month Number of Year], 2) & "-"

Завершите формулу «Месяц-Год», объединив текущую строку со столбцом календарного года:

RIGHT("0" & 'Date (Order)'[Month Number of Year], 2) & "-" & 'Date (Order)'[Year]) 

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

Функция формата – Месяц Год

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

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

Убедитесь, что у вас выбрана таблица «Дата (порядок)», а затем нажмите «Создать новый вычисляемый столбец», выбрав «Новый столбец» на ленте моделирования. В строке формул напишите следующее выражение:

Month Year Format = FORMAT('Date (Order)'[Date], "MM-YYYY")

Расчет возраста

Как насчет определения возраста каждого клиента сейчас? Таблица Customer в настоящее время содержит столбец с датой рождения каждого клиента. Этот столбец вместе с функцией СЕГОДНЯ и некоторым DAX позволит вам определить возраст каждого клиента. Ваша первая попытка выполнить это вычисление может заключаться в использовании функции DATEDIFF в вычислении, которое выглядит примерно так:

Customer Age = DATEDIFF('Customer'[Birth Date], TODAY(), YEAR)

Функция СЕГОДНЯ возвращает текущую дату и время. Функция DATEDIFF возвращает количество указанного интервала между двумя датами; однако он не смотрит на день и месяц и поэтому не всегда возвращает правильный возраст для каждого клиента.

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

4.png

Функция FORMAT используется для возврата двухзначного числа месяца и двузначного числа для каждой даты (даты рождения и текущей даты). За логической тестовой частью оператора IF следуют два выражения. Первое выражение запускается, если логическая проверка дает истинное значение, а второе выражение запускается, если результат проверки оказывается ложным.

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

SWITCH() – разбивка по возрасту

Теперь, когда у вас есть возраст клиента, пришло время поместить каждого клиента в сегмент возраста. В этом примере будет четыре отдельных сегмента возраста:
• 18-34
• 35-44
• 45-54
• 55 +

Функция SWITCH предпочтительнее функции IF при выполнении нескольких логических тестов в одной формуле DAX. Это связано с тем, что функция SWITCH легче читается и значительно упрощает отладку кода.

Выбрав таблицу Customer, щелкните New Column на ленте моделирования. Введите заполненную формулу DAX для следующего примера:

5.png

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

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

Однако, как только строка оценивается как истина, она становится недоступной для последующих логических тестов. Кто-то, кому 75 лет, получил бы истину в первом логическом тесте (55+) и больше не был бы доступен для каких-либо дальнейших тестов.

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

6.png

К сожалению, ни один из этих двух столбцов в настоящее время не существует в таблице Internet Sales. Однако таблица «Интернет-продажи» связана с таблицей «Территория продаж», а таблица «Территория продаж» имеет отношение к региону.

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

На следующем снимке экрана показан новый столбец в таблице «Интернет-продажи», пытающийся вернуть название региона из таблицы «Территория продаж». Взгляните на следующий скриншот:

7.png

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

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

Если вы перепишете следующую формулу DAX с помощью СВЯЗАННОЙ функции, вы заметите, что IntelliSense вернулся вместе с функцией автозаполнения, которая обсуждалась ранее:

8.png

Теперь пришло время создать столбец «Температурный ключ» в таблице «Интернет-продажи». Создайте новый столбец в таблице «Интернет-продажи» и введите следующую формулу DAX:

9.png

Теперь, когда в таблице Internet Sales создан температурный ключ, создайте связь. Нажмите «Управление отношениями» на главной ленте, а затем нажмите «Создать…», чтобы открыть окно «Создать отношение». Затем выполните следующие шаги, чтобы создать новую связь. Соответствующие поля и записи для каждого шага выделены на следующем снимке экрана:

  1. Выберите «Интернет-продажи» в первом раскрывающемся списке.
  2. Выберите ключ температуры из списка столбцов.
  3. Выберите «Температура» во втором раскрывающемся списке выбора (прокрутите вправо).
  4. Выберите ключ из списка столбцов
  5. Нажмите OK, чтобы сохранить новые отношения:

10.png

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *