Оптимизация DAX: где найти скрытую ловушку DAX

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

Давайте сначала посмотрим, с чем мы здесь работаем. У нас есть данные за 10 лет из базы данных фильмов в Интернете. Таблицы фактов довольно просты, и у нас есть рейтинговые данные, бюджетные данные и валовые данные.

Сегодня мы рассмотрим мировые сборы.

У нас также есть расширенная таблица дат. Это будет анализ времени, основанный на таблице дат и таблице фактов.

Оглавление

• Общий мировой валовой доход в год
• Общий мировой валовой доход за квартал
• Общий мировой валовой доход в месяц и год
• Заключение

Общий мировой валовой доход в год

Мы хотим сделать что-то действительно обычное и простое. Наш Total Worldwide Gross — это просто очень простая совокупная сумма, которую мы хотим преобразовать в проценты.

Для этого мы берем числитель ( Показатель Total Worldwide Gross ) и знаменатель ( Показатель Total Worldwide Gross , но контекст из фильтра года удален).

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

Если мы возьмем полученную меру и поместим ее в нашу таблицу, вы увидите, что она делает именно то, что мы от нее ожидаем. Мы получаем 100% внизу, и мы получаем годы, конвертированные в их отдельные проценты. Пока все хорошо, и мы еще не столкнулись с какими-либо проблемами.

Общий мировой валовой доход за квартал

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

Опять же, у нас есть точно такая же мера с функцией REMOVEFILTERS для номеров кварталов вместо года.

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

Общий мировой валовой доход в месяц и год

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

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

Давайте бросим это в нашу таблицу. Вдруг не работает.

Мы можем сказать, что не работает об этом. Мы знаем, что показатель Worldwide Gross работает, поэтому это означает, что числитель в порядке, а знаменатель — нет. В каждом из предыдущих случаев функция REMOVEFILTERS удаляла фильтр должным образом, но в данном случае это явно не так.

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

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

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

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

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

В расширенной таблице дат давайте взглянем на поле под названием « Месяц и год» . Если мы посмотрим на него в колонке Sort by , то увидим кое-что интересное.

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

Давайте вернемся к нашему показателю и удалим контекст месяца и года, которые мы используем для сортировки.

Теперь мы получаем именно то, что должны, а именно месяц и год, рассчитанные в правильном процентном соотношении.

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

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

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

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

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

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

Для области года мы должны удалить контекст для года и удалить контекст для всей таблицы.

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

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

Заключение

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

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

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