Оптимізація DAX: де знайти приховану пастку DAX

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

Давайте спочатку подивимося, з чим ми тут працюємо. Ми маємо дані приблизно за 10 років з Internet Movie Database. Таблиці фактів досить прості, і ми маємо дані про рейтинги, дані про бюджет і валові дані.

Сьогодні ми подивимося на світові прибутки.

У нас також є розширена таблиця дат. Це буде аналіз даних про час на основі таблиці дат і таблиці фактів.

Зміст

• Загальний світовий валовий прибуток за рік
• Загальний світовий валовий прибуток за квартал
• Загальний світовий валовий прибуток за місяць і рік
• Висновок

Загальний світовий валовий прибуток за рік

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

Для цього ми беремо чисельник (вимірник загального світового валового показника) і знаменник (вимірник загального світового валового показника, але вилучили контекст із фільтра року).

Я використовував, тому що я думаю, що це більш інтуїтивно зрозуміло, коли ви читаєте код, але якщо ви віддаєте перевагу , це працює так само добре. Ми просто ділимо чисельник на знаменник, щоб отримати результат.

Якщо ми візьмемо отриманий показник і перенесемо його в нашу таблицю, ви побачите, що він виконує саме те, що ми очікуємо. Ми отримуємо 100% внизу, і ми отримуємо роки, конвертовані в їхні індивідуальні відсотки. Поки що все добре, і ми ще не зіткнулися з жодними проблемами.

Загальний світовий валовий прибуток за квартал

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

Знову ж таки, ми маємо точно такий самий показник із функцією REMOVEFILTERS для чисел кварталу замість року.

І якщо ми опустимо міру, яку ми щойно зробили, у таблицю, вона також зробить саме те, що ми очікуємо.

Загальний світовий валовий прибуток за місяць і рік

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

І знову ми будемо використовувати ту саму міру, що й раніше. Але цього разу ми знімемо фільтр на місяць і рік.

Давайте опустимо це до нашого столу. Раптом не працює.

Ми можемо сказати, що тут не працює. Ми знаємо, що валовий показник у всьому світі працює, тож це означає, що чисельник правильний, а знаменник – ні. У кожному з попередніх випадків функція REMOVEFILTERS видаляла фільтр належним чином, але тут явно ні.

Ми можемо фактично перевірити це, змінивши те, що ми повернули тут у результаті.

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

Те, що ми повинні отримати для знаменника, це одне й те саме число в кожному рядку, але це не так.

Ми можемо сказати, що він не видаляє фільтр місяця та року, і думаємо, що це тому, що місяць і рік є в тексті.

Але попередній для кварталу також був виражений у тексті, тому це не просто через його текст. Треба тільки розібратися. Якщо ви опустите поле без сортування, воно буде відсортовано за алфавітом.

У розширеній таблиці дат давайте розглянемо це поле під назвою « Місяць і рік» . Якщо ми подивимося на це в стовпці «Сортувати за» , то побачимо щось цікаве.

Ми побачимо, що цей стовпець відсортовано за числовим значенням MonthnYear. Коли ви сортуєте один стовпець за іншим, цей стовпець сортування фактично стає частиною контексту фільтра. Ось що скидає цей розрахунок.

Давайте повернемося до нашої міри та видалимо контекст місяця та року, який ми використовуємо для сортування.

Зараз ми отримуємо саме те, що повинні, а саме місяць і рік, обчислені як правильний відсоток.

Під час видалення контексту фільтра під час сортування знадобляться два поля . Ви можете запитати, чому замість того, щоб заповнювати тут два поля, ми не можемо просто видалити фільтри з усієї таблиці дат?

Відповідь: ми можемо, і це спрацює для трьох прикладів, про які ми говорили, оскільки кожен із цих стовпців є частиною нашої таблиці дат. Видалення всього контексту фільтра в цій таблиці спрацює для всіх трьох випадків, але насправді це погана ідея.

Як загальний принцип оптимізації DAX, вам потрібно видалити стільки контексту фільтра, скільки потрібно, щоб отримати бажаний результат.

У більшості випадків ви не збираєтеся представляти це в табличному форматі. Ви представите це як матрицю, і вам знадобиться більш складний показник, оскільки в одному стовпці є дві різні деталізації. Цей захід виглядає складним, але насправді це не так.

Це лише розширення того, що ми вже зробили. Перша частина цього обчислення оптимізації DAX показує знаменники для різної деталізації. Прибираємо контекст фільтра за місяць, за рік і за всю таблицю.

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

Для обсягу року ми маємо видалити контекст для року та для всієї таблиці.

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

Ми побачимо, що цей показник надмірно видаляє контекст. Замість того, щоб обчислювати внесок кожного місяця в цей рік, він обчислює внесок цього місяця в увесь набір даних. Це не те, чого ми хочемо, тому що видалення контексту з усієї таблиці насправді є просто тупим інструментом, коли потрібен скальпель.

Висновок

Є багато випадків, коли у вас є матриця, і вам потрібно ретельно контролювати, який контекст ви видаляєте. Просто видалення контексту з усієї таблиці призведе до таких проблем.

Я сподіваюся, що коли ця ситуація виникне (а це обов’язково колись станеться), ви розпізнаєте її як приховану пастку, яку ми обговорювали в цій публікації про оптимізацію DAX, і зможете уникнути її без того самого розчарування що це спричинило мене, коли я вперше його побачив і не міг зрозуміти, чому мій DAX не працює належним чином.

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