Введение
Чтобы добавить вычисляемую таблицу в модель, можно написать формулу анализа данных (DAX). Формула может дублировать или преобразовывать имеющиеся данные модели для создания таблицы.
Примечание
Вычисляемая таблица не может подключаться к внешним данным. Для выполнения этой задачи необходимо использовать Power Query.
Формула вычисляемой таблицы должна возвращать объект таблицы. Простейшая формула может дублировать имеющуюся таблицу модели.
Вычисляемые таблицы повлекут следующие затраты: Они увеличивают размер хранилища модели и могут продлить время обновления данных. Причина заключается в том, что вычисляемые таблицы пересчитываются при наличии зависимостей формул для обновления таблиц.
Дублирование таблицы
В следующем разделе описывается распространенная задача проектирования, которую можно решить путем создания вычисляемой таблицы. Сначала необходимо скачать и открыть файл Adventure Works DW 2020 M03.pbix, а затем переключиться на схему модели.
На схеме модели обратите внимание, что в таблице Sales есть три связи с таблицей Date.
На схеме модели показаны три связи, так как таблица продаж хранит данные о продажах по дате заказа, дате отгрузки и дате выполнения. В столбцах OrderDateKey, ShipDateKey и DueDateKey обратите внимание, что одна связь представлена сплошной линией — активной связью. Другие связи, представленные пунктирными линиями, — это неактивные связи.
Примечание
Между любыми двумя таблицами моделей может существовать только одна активная связь.
На диаграмме наведите указатель мыши на активную связь, чтобы выделить связанные столбцы. Это и есть способ взаимодействия с диаграммой модели для получения сведений о связанных столбцах. В данном случае активная связь фильтрует столбец OrderDateKey в таблице Sales. Поэтому фильтры, применяемые к таблице Date, будут распространяться на таблицу Sales для фильтрации по дате заказа. Фильтрация по дате отгрузки или дате выполнения не осуществляется.
Далее следует удалить две неактивные связи между таблицей Date и таблицей Sales. Чтобы удалить связь, щелкните ее правой кнопкой мыши и выберите Удалить в контекстном меню. Убедитесь, что неактивные связи также удалены.
Затем добавьте новую таблицу, чтобы разрешить пользователям отчетов фильтровать продажи по дате отгрузки. Перейдите в представление "Отчет" и на ленте Моделирование в группе Вычисления выберите Создать таблицу.
В строке формулы (расположенной под лентой) введите следующее вычисляемое определение таблицы и нажмите клавишу ВВОД.
Ship Date = 'Date'
Определение вычисляемой таблицы дублирует данные таблицы Date для создания таблицы с именем Ship Date. Таблица Ship Date содержит те же самые столбцы и строки, что и таблица Date. После обновления данных таблицы Date таблица Ship Date пересчитывается, поэтому эти таблицы всегда синхронизированы.
Переключитесь на схему модели и обратите внимание на добавление таблицы Ship Date.
Затем создайте связь между столбцом DateKey в таблице Ship Date и столбцом ShipDateKey в таблице Sales. Чтобы создать связь, перетащите столбец DateKey в таблице Ship Date в столбец ShipDateKey в таблице Sales.
Вычисляемая таблица дублирует только данные. Она не дублирует объекты и свойства модели, такие как видимость столбцов или иерархии. При необходимости их нужно настроить для новой таблицы.
Совет
Столбцы вычисляемой таблицы можно переименовать. В этом примере рекомендуется переименовать столбцы, чтобы названия лучше описывали их назначение. Например, столбец Fiscal Year в таблице Ship Date можно переименовать в Ship Fiscal Year. Соответственно, когда поля из таблицы Ship Date используются в визуальных элементах, их имена автоматически включаются в заголовки, такие как визуальные заголовки или метки осей.
Чтобы завершить проектирование таблицы Ship Date, можно выполнить приведенные ниже действия:
- Переименуйте следующие столбцы:
- Date в Ship Date.
- Fiscal Year в Ship Fiscal Year.
- Fiscal Quarter в Ship Fiscal Quarter.
- Month в Ship Month.
- Full Date в Ship Full Date.
- Отсортируйте столбец Ship Full Date по столбцу Ship Date.
- Отсортируйте столбец Ship Month по столбцу MonthKey.
- Скройте столбец MonthKey.
- Создайте иерархию с именем Fiscal со следующими уровнями:
- Ship Fiscal Year
- Ship Fiscal Quarter
- Ship Month
- Ship Full Date
- Пометьте таблицу Ship Date как таблицу дат, используя столбец Ship Date.
Вычисляемые таблицы удобны для работы в сценариях, когда существует несколько связей между двумя таблицами, как описано выше. Они также могут использоваться для добавления таблицы дат в модель. Таблицы дат необходимы для применения специальных фильтров времени, таких как функции логики операций со временем.
Создание таблицы дат
В следующем примере будет создана вторая вычисляемая таблица, на этот раз с помощью функции DAX CALENDARAUTO.
Создайте вычисляемую таблицу Due Date с использованием следующего определения.
Due Date = CALENDARAUTO(6)
Функция DAX CALENDARAUTO принимает один необязательный аргумент, который является номером последнего месяца года, и возвращает таблицу с одним столбцом. Если вы не передаете номер месяца, предполагается, что он будет равен 12 (для декабря). Например, в Adventure Works финансовый год заканчивается 30 июня каждого года, поэтому передается значение 6 (для июня).
Функция сканирует все столбцы даты и времени в модели, чтобы определить самые ранние и последние сохраненные значения даты. Затем она создает полный набор дат, охватывающих все даты в модели, обеспечивая загрузку дат по всем годам. Например, если самая ранняя дата, сохраненная в модели, — 15 октября 2021 г., то первая дата, возвращаемая функцией CALENDARAUTO, будет иметь значение 1 июля 2021 г. Если последняя дата, сохраненная в модели, — 15 июня 2022 г., то последней датой, возвращенной функцией CALENDARAUTO, будет 30 июня 2022 г.
Фактически функция CALENDARAUTO обеспечивает соблюдение следующих требований для маркировки таблицы дат:
- Таблица должна включать столбец типа данных Date.
- Столбец должен содержать все годы.
- В столбце не должно быть отсутствующих дат.
Совет
Можно также создать таблицу дат с помощью функции DAX CALENDAR и передать два значения даты, представляющие диапазон дат. Функция создает одну строку для каждой даты в диапазоне. Вы можете передать статические значения даты или выражения, которые извлекают самые ранние или последние даты из конкретных столбцов в модели.
Затем перейдите в представление данных и в области Поля выберите таблицу Due Date. Теперь проверьте столбец дат. Вы можете упорядочить их, чтобы увидеть самую раннюю дату в первой строке, выбрав стрелку в заголовке столбца Date, а затем отсортировав их в возрастающем порядке.
Примечание
Упорядочивание или фильтрация столбцов не влияет на порядок хранения значений. Эти функции помогают исследовать и анализировать данные.
Теперь, когда выбран столбец Date, просмотрите сообщение в строке состояния в левом нижнем углу. В нем указано, сколько строк хранится в таблице и сколько различных значений найдено в выбранном столбце.
Если строки таблицы и различные значения совпадают, значит столбец содержит уникальные значения. Это важно по двум причинам: Требования к пометке таблицы дат удовлетворены, что позволяет использовать этот столбец в качестве односторонней связи модели.
Вычисляемая таблица Due Date будет повторно вычисляться каждый раз, когда обновляется таблица, содержащая столбец дат. Другими словами, когда строка загружается в таблицу Sales с датой заказа 1 июля 2022 г., таблица Due Date автоматически расширяется и включает в себя даты до конца следующего года: 30 июня 2023 г.
В таблице Due Date необходимы дополнительные столбцы для поддержки известных требований к фильтрации и группированию, в частности по годам, кварталам и месяцам.