Моделирование данных в Power BI: связи, DAX и проверка на примерах

Обновлено 3 сентября 2026 года.

Хорошая модель Power BI даёт объяснимые результаты при смене товара, периода и уровня детализации. Проверять нужно не только общий итог: ошибочная связь может оставить его правильным, но распределить сумму по категориям неверно. Ниже — практикум со схемой, формулами DAX, контрольными числами и намеренно ошибочным объединением таблиц.

Скачать учебные CSV, меры DAX и независимую проверку. Набор создан для этого практикума и руководства для начинающих. Это синтетические заказы, не клиентский кейс. Арифметика и примеры JOIN проверяются скриптом Python/SQLite; выполнение DAX в движке Power BI в проверку архива не входило. Готового PBIX нет: связи и меры создаются по инструкции.

Содержание: детализация · схема и ключи · ошибка JOIN · меры и итоги · контекст фильтра · дата заказа и отгрузки · взвешенное среднее · планы и остатки · производительность · проверка модели.

Начните с определения одной строки

До выбора DAX-функций запишите, что означает строка каждой таблицы, чем она идентифицируется и какие суммы разрешено складывать.

В нашем наборе 6 позиций, 5 заказов и 5 платежей. Совпадение количества заказов и платежей случайно: это разные сущности. У O1001 две позиции и два платежа, у O1004 платежей нет. Показатель «число продаж» без уточнения единицы учёта здесь неоднозначен.

Revenue в примерах — сумма заказанных позиций в одной условной валюте. Налоги, доставка, возвраты и валютные курсы не моделируются. Если в вашей выгрузке сумма заказа повторена в каждой позиции, её нельзя суммировать по строкам Sales. Рассчитывайте сумму позиции или храните заказный показатель отдельно на уровне заказа.

Соберите схему и проверьте ключи

Импортируйте Sales, Products и Calendar по инструкции README. Даты приведите к типу «Дата», идентификаторы — к согласованным типам; пустая ShipDate должна стать null. Основная модель использует две активные связи 1:* с фильтрацией от справочника к фактам.

Products[ProductID]  1 ────→ * Sales[ProductID]
Calendar[Date]       1 ────→ * Sales[OrderDate]   активная
Calendar[Date]       1 - - → * Sales[ShipDate]    неактивная

Третью связь добавьте для упражнения с отгрузкой. Products и Calendar используются для группировки и срезов; Sales содержит события и числовые поля. Направление Both здесь не требуется. Связь передаёт фильтр и не является командой физического слияния таблиц. Свойства связей в Power BI.

Дубли, неизвестные ключи и история справочника

• Повтор ProductID в Products. Определите причину: дубль загрузки, товар другой организации или историческая версия. Переключение на many-to-many не исправляет неопределённость справочника.
• ProductID из Sales отсутствует в Products. Найдите такие строки левым антисоединением в Power Query, посчитайте их сумму и исправьте источник. Если используете категорию «Неизвестный товар», сохраняйте исходный ключ для расследования.
• OrderDate содержит время. Дата 30 января 12:00 не равна календарной дате 30 января 00:00. Согласуйте тип и правило преобразования, прежде чем искать ошибку в мере.

Не удаляйте строки без справочника ради чистого графика: получите занижение суммы. В обычной Import-связи 1:* такие записи могут попадать в пустой элемент измерения; для limited relationships и DirectQuery с Assume referential integrity последствия отличаются. Поэтому контроль целостности выполняйте до визуализации. Как движок обрабатывает связи.

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

Почему JOIN заказов с платежами удваивает суммы

Возьмём только O1001. Его позиции стоят 2 000 и 3 000, итого 5 000. Платежи — 3 000 и 2 000, тоже 5 000. Если соединить таблицы по OrderID и развернуть все совпадения, каждая позиция встретится с каждым платежом.

После такого JOIN обе суммы станут 10 000. Ошибка возникает ещё при подготовке данных. DISTINCT по сумме не является решением: два разных платежа могут иметь одинаковую сумму и оба должны учитываться.

Для отчёта по заказам можно сначала независимо агрегировать позиции и платежи до одного OrderID, затем соединить агрегаты. Левое соединение от заказов сохранит и неоплаченные заказы. Следующий SQL иллюстрирует именно этот вариант; названия полей соответствуют CSV.

WITH SalesByOrder AS (
    SELECT OrderID, SUM(Quantity * UnitPrice) AS OrderAmount
    FROM Sales
    GROUP BY OrderID
), PaymentsByOrder AS (
    SELECT OrderID, SUM(Amount) AS PaidAmount
    FROM Payments
    GROUP BY OrderID
)
SELECT s.OrderID, s.OrderAmount, p.PaidAmount
FROM SalesByOrder s
LEFT JOIN PaymentsByOrder p ON p.OrderID = s.OrderID;

Для O1001 получится 5 000 и 5 000; у неоплаченного O1004 PaidAmount будет NULL. Если бизнес определяет отсутствие платежа как нулевую оплату, преобразуйте NULL в 0 явно. Наличие NULL само по себе не доказывает отсутствие долга или полноту загрузки.

Если нужны и позиции, и платежные события, оставьте две таблицы фактов и общий справочник заказов с уникальным OrderID. Отдельно спроектируйте даты заказа и оплаты. Распределение оплаты по товарам требует бизнес-правила: платёж по заказу не сообщает, какая его часть относится к мыши, а какая — к клавиатуре. Не выводите такую детализацию из прямой связи автоматически. Рекомендации для нескольких таблиц фактов.

Меры DAX: правильный итог зависит от смысла показателя

Создавайте определения ниже по одному через «Новая мера». Для основных денежных мер используются поля Sales; названия таблиц должны совпадать.

Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Cost = SUMX(Sales, Sales[Quantity] * Sales[UnitCost])
Margin = [Revenue] - [Cost]
Margin % = DIVIDE([Margin], [Revenue])
Orders = DISTINCTCOUNT(Sales[OrderID])
Units = SUM(Sales[Quantity])
Average Unit Price = DIVIDE([Revenue], [Units])

В матрице с Products[Product] и без других фильтров результаты должны быть такими:

Revenue и Margin здесь складываются по товарам. Orders не складывается: O1001 присутствует у двух товаров, но остаётся одним заказом. Уникальное количество пересчитывается на каждом уровне. Почему DISTINCTCOUNT неаддитивен.

Средняя цена единицы — 20 100 / 10 = 2 010. Обычное среднее шести значений UnitPrice равно 2 350: оно даёт каждой позиции одинаковый вес, игнорируя количество. Оба вычисления математически допустимы, но отвечают на разные вопросы. Для сравнения цен товаров общая средняя также зависит от состава продаж: рост доли мониторов повысит её даже без изменения цены какого-либо товара.

Маржинальность итога равна 7 600 / 20 100 = 37,81%. Не складывайте проценты и не усредняйте товарные маржинальности без весов. При нулевой выручке DIVIDE оставляет BLANK, если не задан альтернативный результат. Выбор между пустым значением и нулём должен отражать смысл показателя. DIVIDE.

Вычисляемый столбец подходит для категории или признака строки, мера — для результата по текущей выборке. В Import столбец занимает место для сохранённых значений. Создание множества повторяющихся столбцов «сумма за январь», «сумма за февраль» обычно указывает на то, что календарь и меры ещё не разделены.

Контекст фильтра: откуда берётся знаменатель доли

Пусть доля товара должна считаться относительно всех товаров, сохраняя выбранный месяц и канал. Для нашей модели используйте:

Revenue All Products =
CALCULATE([Revenue], REMOVEFILTERS(Products))

Product Share % = DIVIDE([Revenue], [Revenue All Products])

В январе выручка мышей — 3 200, всех товаров — 6 200. Доля составляет 51,61%. За всё время она равна 6 500 / 20 100 = 32,34%. Снятие фильтра Products убирает и выбор категории внутри этого справочника; фильтр Calendar остаётся.

Если нужно считать долю внутри выбранной категории, такое снятие всех товарных фильтров уже не подходит. Сначала сформулируйте знаменатель словами: «все товары», «товары выбранной категории» или «только показанные пользователю». Это разные меры, даже если без срезов они случайно совпадают.

CALCULATE меняет контекст фильтров. В итераторах также важно различать контекст строки и фильтра: вызов готовой меры выполняет переход контекста автоматически, а самостоятельное агрегирующее выражение может потребовать CALCULATE. Переменная хранит результат, вычисленный в месте её объявления; она не становится формулой, автоматически пересчитываемой под новым фильтром. Правила CALCULATE, поведение VAR.

Практическая проверка: выберите январь, затем мышь; проверьте числитель 3 200 и знаменатель 6 200 отдельно. Если оба стали 3 200, нужный фильтр не снят. Если знаменатель равен 20 100, вы потеряли фильтр периода. Не отлаживайте долю только по итоговому проценту.

Дата заказа и дата отгрузки: две разные картины

В Calendar хранится полный 2026 год. Пометьте его как таблицу дат по Date. Для примеров используйте Calendar[YearMonth] в строках матрицы. Активная связь ведёт к OrderDate, неактивная — к ShipDate. Существование второй линии само по себе не меняет обычную меру Revenue.

Shipped Revenue =
CALCULATE(
    [Revenue],
    USERELATIONSHIP('Calendar'[Date], Sales[ShipDate]),
    KEEPFILTERS(NOT ISBLANK(Sales[ShipDate]))
)

USERELATIONSHIP использует уже созданную связь, а не создаёт её. В этой модели она заменяет путь через OrderDate на время вычисления меры. Условие по ShipDate отдельно исключает ещё не отгруженную строку: без него общий итог при отсутствии фильтра календаря может включить её. USERELATIONSHIP.

Разница 5 000 — позиция O1004 с пустой ShipDate. В феврале отгружены январский O1001 и февральский O1003; O1005 заказан в феврале, но отгружен в марте. Это объясняет несовпадение месячных сумм без предположения, что одна из мер ошибочна.

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

Для независимых срезов «месяц заказа» и «месяц отгрузки» создавайте отдельные ролевые календари с активными связями. Например, можно отобрать заказы января, отгруженные в феврале. Одно переключение меры через USERELATIONSHIP не создаёт два независимых измерения. RLS распространяется по активным связям; неактивная связь не становится путём RLS только от вызова USERELATIONSHIP. Выбор активных и неактивных связей.

Для сравнения год к году понадобятся даты и данные предыдущего года. В учебном наборе их нет. Пустой результат за прошлый год нельзя заменить выдуманным нулевым основанием и объявить ростом. Также не сравнивайте незаконченный месяц с полным без явного правила сопоставления.

Взвешенное среднее: одинаковая выборка в числителе и знаменателе

Импортируйте Observations.csv отдельной таблицей без связей. В ней значения 10 и 30 имеют веса 1 и 3; ещё три записи содержат пустое значение, нулевой и отрицательный вес. В этом учебном определении допустимы только заполненные значения с положительным весом.

Weighted Average =
VAR ValidRows =
    FILTER(
        Observations,
        NOT ISBLANK(Observations[Value])
            && NOT ISBLANK(Observations[Weight])
            && Observations[Weight] > 0
    )
RETURN
    DIVIDE(
        SUMX(ValidRows, Observations[Value] * Observations[Weight]),
        SUMX(ValidRows, Observations[Weight])
    )

Результат: (10 × 1 + 30 × 3) / (1 + 3) = 25. При фильтре ID = 1 — 10. При выборе только ID 3, 4 и 5 валидных наблюдений нет — BLANK. Если оставить вес строки с пустым Value в знаменателе, результат исказится. Поведение итератора SUMX.

Это правило не универсально для всех данных. Отрицательное количество возврата нельзя автоматически выбросить как «плохой вес»: сначала определите, считаете ли вы среднюю цену продаж, чистую выручку с возвратами или другой показатель. Один и тот же фильтр должен соответствовать бизнес-смыслу и числителя, и знаменателя.

План, остатки и many-to-many: где одной суммы недостаточно

План на месяц нельзя автоматически превратить в дневной план

Если план хранится на уровне «месяц × категория», а продажи — «день × товар», они имеют разную детализацию. Сравнивайте на общем уровне либо задавайте правило распределения: равномерно по календарным дням, рабочим дням или по отдельным весам. Эти правила дадут разные результаты.

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

Остатки можно складывать по товарам, но не всегда по времени

Если остаток одного товара в конце понедельника — 10, а во вторник — 12, сумма 22 не является остатком на конец вторника. Для снимков заранее определите, нужна ли последняя дата периода, последняя доступная запись для каждого товара или средний остаток. При пропусках дат эти варианты расходятся.

Например, у товара A последний снимок 31 января, а у B — 30 января. Выбор единой последней даты оставит только A; поиск последнего снимка отдельно для каждого товара сохранит B, но его значение будет старше. Политика актуальности должна быть видна пользователю, а не скрыта в сложной формуле.

Связь many-to-many требует правила отнесения

Если одна продажа относится к двум менеджерам, мост «продажа — менеджер» описывает принадлежность. Он не определяет, делится ли сумма пополам или целиком относится к каждому. Во втором случае сумма по менеджерам превысит уникальный общий объём, и это может быть ожидаемым поведением. Для распределения нужны веса и проверка их суммы. Направления фильтров и меры проектируют под выбранное правило, а не включают Both у всех линий.

Как оптимизировать модель по измерениям

1. Зафиксируйте медленное действие: открытие страницы, выбор месяца или конкретного товара. Запишите модель, объём данных и исходный фильтр.
2. Откройте Performance Analyzer, начните запись и повторите действие. Разделите время DAX-запроса, работы источника в подходящем сценарии и отрисовки визуала.
3. Проверьте ненужные столбцы и уникальные длинные строки, число визуалов, сложные итераторы и преобразования источника. Изменяйте по одной причине.
4. Повторите одинаковый сценарий несколько раз, учитывая влияние кэша, и сравните результаты вместе с контрольными суммами.

Performance Analyzer позволяет скопировать запрос визуала для дальнейшего анализа. Большое время отображения визуала не следует автоматически лечить переписыванием меры. Инструкция Microsoft.

У Import-модели нельзя создать пользовательский B-tree индекс командой SQL, как у исходной базы. Сначала сокращайте ненужный объём и сложность модели. Для DirectQuery дополнительно проверяйте план запроса и индексы источника. Скорость на нашем маленьком CSV не является тестом производительности на миллионах строк.

Как проверить модель перед передачей коллегам

В рабочем проекте добавьте контроль пустых ключей, возвратов, новых категорий и поздних исправлений. Проверяйте доступ под реальным Viewer: администратор или редактор рабочей области не подходит для подтверждения ограничений RLS. RLS в сервисе.

Храните определение показателя, его детализацию, источник, роль даты и контрольный пример рядом с моделью. Если коллега может объяснить, почему итог Orders равен 5 и почему отгрузки января равны 1 200, модель уже значительно легче сопровождать, чем набор формул без проверяемого смысла.