DISTINCT и VALUES выглядят как синонимы: обе возвращают уникальные значения столбца в текущем контексте. На чистых данных они и правда дают одно и то же. Но есть один сценарий, где они расходятся, — и из-за него итоговая мера может молча потерять часть выручки. Разбираем по статье SQLBI.
Что возвращают обе
VALUES ( Регионы[Регион] ) -- уникальные регионы в контексте
DISTINCT ( Регионы[Регион] ) -- то же самое... почтиОбе принимают столбец (вернут одну колонку уникальных значений) или таблицу (вернут уникальные строки). В 99% случаев на корректной модели результат идентичен. Разница — в одной строке.
Ключевое различие: пустая строка целостности
Представим нарушение целостности (referential integrity violation): в Продажи есть КодРегиона, которого нет в справочнике Регионы. Такое бывает: в факт попал регион, забытый в справочнике. Движок в этом случае добавляет в измерение пустую строку — «корзину» для всех осиротевших продаж.
VALUES ( Регионы[Регион] )— включает эту пустую строку;DISTINCT ( Регионы[Регион] )— исключает её.
VALUES видит «сиротские» строки факта (через пустую строку целостности), DISTINCT — нет. На чистой модели разницы нет; на грязной — есть, и она тихая.
Почему это ломает итог
Когда вы итерируете (SUMX, AVERAGEX) по уникальным значениям, выбор функции решает судьбу осиротевших продаж.
Выручка со скидкой (НЕВЕРНО) =
SUMX (
DISTINCT ( Регионы[Регион] ),
[Выручка] * IF ( Регионы[Регион] = "ЮФО", 0.95, 1 )
)Здесь DISTINCT выкинул пустую строку — и продажи без региона выпали из суммы. Итог занижен, и это не бросается в глаза: цифра правдоподобная, просто неправильная.
Выручка со скидкой (ВЕРНО) =
SUMX (
VALUES ( Регионы[Регион] ),
[Выручка] * IF ( Регионы[Регион] = "ЮФО", 0.95, 1 )
)VALUES прогоняет и пустую строку — осиротевшие продажи учтены, итог сходится с общей выручкой.
Самое коварное: на тестовых (чистых) данных DISTINCT и VALUES дают одинаковый результат, и ошибку не видно. Она «выстреливает» в проде, когда в факт попадает ключ, которого нет в справочнике. Поэтому для итераторов-сумматоров берите VALUES по умолчанию.
Когда, наоборот, нужен DISTINCT
Для статистики — среднее, минимум, максимум, перцентили — пустая строка «Unknown» искажает результат: все осиротевшие записи схлопываются в одну группу и тянут среднее.
Средняя выручка по товару =
AVERAGEX ( DISTINCT ( Товары[Товар] ), [Выручка] )Тут DISTINCT уместен: мы усредняем по реальным товарам, и фиктивная «пустая» группа только испортила бы среднее.
Правило выбора
| Задача | Брать |
|---|---|
Итератор-сумматор (SUMX, SUMMARIZE для итога) | VALUES — не теряет осиротевшие строки |
Статистика (AVERAGEX, MINX, MAXX, перцентили) | DISTINCT — без фиктивной группы |
| Текущий выбор пользователя | VALUES (на ней основан SELECTEDVALUE) |
Расхождение DISTINCT/VALUES — симптом нарушенной целостности. Правильный путь: не выбирать функцию под баг, а починить источник — добить справочник недостающими ключами в ETL. Тогда обе функции дадут одно и то же, а пустая строка исчезнет.
Сравните DISTINCTCOUNT ( Продажи[КодТовара] ) и COUNTROWS ( VALUES ( Товары[КодТовара] ) ) без срезов. Совпадут ли — и чему равны на наборе курса?
Показать ответ
Обе = 25. В каталоге 25 товаров, и все встречаются в продажах, поэтому числа сходятся. Разойтись они могли бы при «осиротевших» ключах (товар в продажах есть, в справочнике нет) — тогда VALUES добавила бы пустую строку целостности. На чистом наборе курса этого нет.
Что дальше
VALUES по умолчанию для сумм, DISTINCT для статистики, а в идеале — чистые ключи, при которых вопрос снимается. Дальше в курсе — field parameters, what-if и другие приёмы. Курс пополняется.