Расширенные таблицы в LuckyTemplates

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

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

С левой стороны у нас есть отношения «один ко многим» между таблицами продуктов, категорий, подкатегорий и продаж. У нас также есть отношение «один ко многим» между клиентом и продажами, а также датами и продажами.

Оглавление

• Пример №1
• Пример #2
• Красная распродажа №1
• Красная распродажа №2
• Красная распродажа №3
• Проверка развернутых таблиц
• Определение расширенных таблиц
• Разделение столбца категории и использование развернутых таблиц
• Объяснение Red Sales 2
• Объяснение красных продаж 3
• Использование DAX Studio для проверки развернутых таблиц
• Заключение

Пример №1

Допустим, мы хотим создать отчет из этого столбца категории в таблице категорий и использовать меру, которая вычисляет количество клиентов в таблице клиентов. Мы хотим разбить число для таблицы клиентов по категориям.

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

Вернемся к отчету, чтобы исправить этот расчет. Мы можем обернуть этот расчет внутри функции CALCULATE, а затем написать Sales. Мы видим, что на этот раз мы не повторяем одно и то же число для каждой ячейки визуального элемента.

Чтобы проверить правильность этого расчета, мы можем ввести ключ клиента из таблицы продаж для агрегирования, а затем выполнить над ним подсчет (различный).

Вы можете видеть, что мы возвращаем одно и то же значение для каждой строки. Используя таблицу продаж внутри функции РАСЧЕТ, мы смогли исправить расчет.

Пример #2

Перейдем к следующему примеру. Для этого мы хотим определить объем продаж красных продуктов в 2007, 2008 или 2009 году. В зависимости от выбора слайсера я также хочу вернуться на один год назад.

Например, если я выберу 2009 год, я хочу сообщить о продажах красного цвета за 2008 год. Если я выберу 2008 год, я также хочу сообщить о продажах красного цвета за 2007 год.

Красная распродажа №1

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

Давайте создадим новую меру и назовем ее Red Sales. Мы собираемся написать ВЫЧИСЛИТЬ, затем общий объем продаж. Мы собираемся таблицу продаж, чтобы показать, что цвет продуктов равен красному.

Затем мы напишем над датами в столбце даты.

Когда мы вносим этот расчет в карточку, мы получаем пустое место.

Красная распродажа №2

Что здесь происходит? Давайте попробуем переписать этот расчет и посмотрим, сможем ли мы получить результат. Мы собираемся создать новую меру и назвать ее Red Sales 2 и использовать первую часть расчета для Red Sales 1.

Мы собираемся инициировать еще один CALCULATE поверх первого CALCULATE.

Давайте занесем эту меру в матрицу и посмотрим на результаты. Если мы выберем 2008 год, мы получим 51 947. Если мы выберем 2009 год, мы получим 24 343. Наконец, если мы выберем 2010 год, мы получим 39 724.

Красная распродажа №3

Есть и другой способ записать этот расчет. Мы напишем новую меру и назовем ее Red Sales 3, а затем воспользуемся функцией ВЫЧИСЛИТЬ.

Мы собираемся вычислить общий объем продаж и написать, что для продуктов цвет равен красному. Затем используйте функцию SAMEPERIODLASTYEAR для дат.

Если мы перетащим меру № 3 в карточку, вы увидите, что эти два значения карточки возвращают одно и то же значение, и это правильно.

Но что-то не так с нашим первым вычислением, потому что мы возвращаем пустое, а не правильное значение.

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

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

Итак, когда эти таблицы станут расширенными таблицами? После создания отношения «многие к одному» между одной и другой таблицами базовая таблица становится расширенной таблицей.

Проверка развернутых таблиц

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

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

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

Мы видим, что таблица клиентов и таблица продаж имеют отношение «многие к одному». Мы также можем увидеть список столбцов таблицы клиентов, столбцов таблицы дат и столбцов продукта.

Когда мы выбираем Products[Color], мы можем создать новый столбец в таблице продаж с помощью ключевого слова RELATED. RELATED дает нам доступ только к тем столбцам таблицы, до которых может фактически расширяться базовая таблица.

Если мы изменим природу этого отношения с «многие к одному» на «многие ко многим», этот расчет перестанет работать.

Давайте изменим природу этого отношения на многие ко многим. Внизу мы видим предупреждающий символ.

Когда мы вернемся к таблице продаж, мы увидим сообщение об ошибке, в котором говорится, что столбец Products[Color] либо не существует, либо не имеет связи ни с одной таблицей.

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

А так как мы используем отношение «многие ко многим», расширения таблицы не происходит, и RELATED не работает.

Вернемся к представлению диаграммы и исправим этот расчет. Мы изменим характер отношения на «многие к одному» и активируем это отношение, чтобы вычисления заработали.

Определение расширенных таблиц

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

Если у вас есть модель данных со звездообразной схемой , таблица фактов будет расширена до всех таблиц в модели данных, если между измерениями и таблицей фактов существует отношение «многие к одному».

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

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

Расширенная таблица появляется только тогда, когда вы ссылаетесь на таблицу (которая является базовой таблицей) и когда у вас есть отношение «многие к одному» с другими таблицами.

Разделение столбца категории и использование развернутых таблиц

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

Итак, что мы здесь делаем на самом деле? Мы используем расширенную таблицу продаж в качестве справки. Как только контекст фильтра содержит значение из таблицы категорий, этот фильтр попадет в таблицу продаж из продуктов подкатегории и непосредственно в продажи.

Поскольку продажи — это расширенная таблица, и мы используем эту ссылку внутри функции РАСЧЕТ, таблица продаж также будет содержать столбец таблицы клиентов. Когда мы применяем фильтр к таблице продаж, косвенно мы также фильтруем таблицу клиентов.

Вернемся к нашим красным расчетам продаж и попробуем понять, что происходит на самом деле. Начнем с пустого расчета. Если мы выберем эту меру, вы увидите, что мы пишем вложенный код. У нас есть РАСЧЕТ, затем общий объем продаж, ФИЛЬТР по продажам и SAMEPERIODLASTYEAR.

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

Мы выбрали 2008 год в слайсере Номер календарного года.

Из этого контекста фильтра будет оцениваться таблица продаж. Таблица продаж будет содержать только строку с номером календарного года 2008, а цвет продукта будет красным. Здесь у нас есть два фильтра: один создается контекстом фильтра , а другой создается фильтром .

SAMEPERIODLASTYEAR оценивается в контексте фильтра, где указан год 2008. Он получит список дат в 2008 году и сдвинет эти даты с 2008 на 2007 год. Таблица, которая будет возвращена этим, будет содержать только даты на 2007 год.

После завершения этих двух операций функция РАСЧЕТ подготавливает контекст фильтра и применяет эти два фильтра к контексту фильтра. Когда мы применяем это, у нас есть контекст фильтра, в котором цвет продукта равен красному, а в столбце года у нас есть фильтр по 2007 и 2008 годам.

Таким образом, для этой модели данных нет ни одной транзакции, которая существует за два разных года. Когда CALCULATE попытается объединить эти два фильтра в условии and , он скажет, что год должен быть 2008 и 2007, а цвет продукта должен быть красным.

Когда мы фильтруем таблицу продаж с контекстом фильтра 2008 года, мы также косвенно фильтруем таблицу дат. Стабильная продажа имеет отношение «многие к одному» к стабильной дате.

Давайте создадим новый расчет, чтобы определить, сколько строк в таблице дат для связанных дат. Мы напишем CALCULATE, затем таблицы дат. Затем мы отфильтруем всю таблицу продаж и скажем, что СВЯЗАННЫЕ даты в номере календарного года должны быть равны 2008.

Внутри CALCULATE мы не ссылались ни на какой фильтр в таблице дат. Мы просто проверяем, чтобы даты в номере календарного года были с 2008 года. В идеале этот фильтр не должен фильтровать таблицу дат. Мы также используем функцию , которая будет игнорировать контекст фильтра, поступающий от слайсера.

Давайте создадим новую карту для этого расчета. вы можете видеть, что мы возвращаем 348 строк.

Так как же возможно, что из таблицы дат из 2500 строк мы возвращаем только 348 строк? Если мы собираемся использовать расширенную таблицу, то мы будем косвенно фильтровать и другую таблицу, которая связана отношением «многие к одному».

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

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

Объяснение Red Sales 2

Давайте перейдем к следующему вычислению, которое называется Red Sales 2. Мы начнем с внешнего вычисления CALCULATE, потому что, если мы вкладываем эту функцию в какой-либо сценарий, внешний CALCULATE должен подготовить контекст фильтра для внутреннего CALCULATE.

В срезе мы выбираем календарный 2008 год. Когда функция SAMEPERIODLASTYEAR получает дату за 2008 год, она сдвигает эти даты на 2007 год, и это будет контекст ФИЛЬТРА для внутреннего вычисления. Этот внутренний РАСЧЕТ будет оценивать 2007 год.

Функция ФИЛЬТР отфильтрует таблицу продаж, и таблица продаж будет ограничена только строкой, в которой указан год 2007. Получив строки за 2007 год, мы проверим, что связанные продукты равны красному цвету.

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

После применения этого фильтра мы получим сумму продаж за 2007 год, а также продукты, которые равны красному цвету, тогда как в первом расчете мы возвращали 2008 и 2007 годы.

На этот раз мы подготовили контекст фильтра для таблицы продаж, используя функцию SAMEPERIODLASTYEAR и вложив функцию CALCULATE в другую функцию CALCULATE.

Объяснение красных продаж 3

Для третьего примера у нас есть очень простая функция ВЫЧИСЛИТЬ с двумя операторами: первый — цвет продукта равен красному, а второй — ОДИНАКОВЫЙПЕРИОДЛАСТНЫЙГОД для дат.

Функция SAMEPERIODLASTYEAR будет оцениваться в контексте фильтра, который будет содержать только даты 2007 года. Цвет продуктов будет применять красный контекст фильтра к таблице продуктов, которая, в свою очередь, отфильтрует таблицу продаж и будет содержать только строки для красные продукты.

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

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

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

Если вы работаете с DAX, всегда старайтесь применять фильтр к одному столбцу, потому что при использовании одного столбца концепция развернутых таблиц не применяется. Например, когда вы используете PRODUCTS [Color] = «Red» , фильтр достигает таблицы продаж, но этот фильтр не может достичь таблицы клиентов, потому что мы не используем расширенную таблицу продаж.

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

Использование DAX Studio для проверки развернутых таблиц

Прежде чем закончить, мы собираемся использовать студию DAX для проверки нашего кода для Red Sales 3. Перейдем на вкладку «Вид», щелкните «Анализатор производительности» и начнем запись. Мы обновим этот визуальный элемент и выберем пустую карточку.

Из внешних инструментов мы собираемся запустить DAX studio

Мы видим, что на экране результатов внизу мы получаем пустое место.

Мы включим синхронизацию сервера, чтобы понять, какие запросы генерируются за кулисами. Мы перейдем на вкладку Server Timings и развернем ее, чтобы увидеть все.

Мы выполняем JOIN для таблицы продаж с левой стороны. Мы также выполняем JOIN для ключа продукта из таблицы продуктов.

В предложении WHERE сказано, что даты в номере календарного года должны быть равны 2008 и что цвет продукта должен быть красным.

Затем у нас есть еще одно условие, которое говорит Dates[Date], за которым следует диапазон дат. Так что же это за даты? В идеале эти даты должны относиться к 2008 году, но они относятся к 2007 году, когда мы меняем форматирование этих чисел в Excel.

Вот почему мы возвращаем пустое значение. Мы говорим, что календарный год должен быть 2008, и применяем условие И, чтобы даты были в 2007. Это 2007 из-за функции SAMEPERIODLASTYEAR, которую мы использовали в вычислении.

Давайте изменим меру и ссылку на Red Sales 2. После того, как мы выполним код, вы увидите, что мы получаем даты из таблицы дат, а затем фильтруем столбец дат по номеру календарного года 2008.

На этот раз мы можем вернуть результат, потому что мы не применяем фильтр к предложению WHERE и говорим, что даты в номере календарного года должны быть равны 2008.

Где найти скрытую ловушку DAX,
подсчитывающую клиентов с течением времени с помощью DISTINCTCOUNT в LuckyTemplates
Анализ новых клиентов с использованием расширенного DAX

Заключение

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

Чтобы узнать больше о расширенных таблицах, не забудьте подписаться на телеканал LuckyTemplates. У нас есть огромное количество контента, который постоянно выходит от меня и ряда создателей контента, и все они посвящены улучшению того, как вы используете LuckyTemplates и Power Platform.