Функція CALCULATE DAX: проблеми та рішення

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

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

Зміст

• Огляд функції CALCULATE DAX
• ОБЧИСЛИТИ формулу DAX
• Код DAX для середнього значення за категорією
• Тоді в чому проблема?
• Простіше рішення
  • Використання функції SUMMARIZE
  • Використання функції GROUPBY
• Порівняння результатів: функції SUMMARIZE проти GROUPBY та CALCULATE DAX
• Висновок

Огляд функції CALCULATE DAX

Кілька місяців тому я представив на LuckyTemplates. Він називався «Контракультура DAX», де я надав деякі альтернативні погляди на DAX. 

Одна з основних стосується використання функції CALCULATE. Я говорив про те, чому вам не слід використовувати CALCULATE, особливо якщо ви новачок у DAX. 

CALCULATE — це дуже складна функція для розуміння та використання. Він робить певні припущення щодо вашої моделі даних і може створити для вас багато проблем. 

Я також копався в Microsoft для Running Total, оскільки він не працює в ситуаціях з однією таблицею, і для належної роботи йому потрібна схема зірок.

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

ОБЧИСЛИТИ формулу DAX

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

У спливаючому вікні швидкого вимірювання виберіть « Середнє за категорією» у розкривному меню «Обчислення» . Потім перетягніть ? Значення в текстовому полі «Базове значення» та « Місяць» у полі «Категорія» .

Потім натисніть OK , і ви отримаєте візуальний вигляд, який виглядає так. 

На осі х розташовані чверті — 1, 2, 3 і 4. 

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

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

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

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

У цих візуальних зображеннях ми використовуємо Чверть з першої таблиці…

 …у той час як цей використовує Квартал із нашої таблиці дат. Це працює в будь-який спосіб. 

Але проблема із середнім значенням за категорією полягає просто в коді DAX. 

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

Насправді, це одне з тих моїх улюбленців, коли я бачу людей на форумах, які використовують CALCULATE, який просто обертає суму без причини. Але в даному випадку це дійсно має причину.

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

Код DAX для середнього значення за категорією

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

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

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

Вони також зберігають функцію CALCULATE для виконання в контексті KEEPFILTERS. Через те, як працює AVERAGEX, він бере другий вираз і виконує його в контексті першого виразу. 

Тоді в чому проблема?

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

Швидкі вимірювання — чудова ідея, наприклад «Давайте напишемо для вас кілька загальних показників із різними обчисленнями, оскільки ви новачок у DAX і знаєте не все, що можна знати про DAX». Але навіщо їм будувати їх такими дивними, складними способами? 

Як хтось новачок у DAX має поглянути на це та зрозуміти, що відбувається, коли навіть, ймовірно, професіонали DAX трохи чухають голови над цим? 

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

Простіше рішення

Використання функції SUMMARIZE

Як я вже згадував раніше, є кращий і простіший спосіб зробити це.

Спочатку створіть змінну таблиці за допомогою VAR_Table . Потім ми візьмемо таблицю, узагальнимо її за місяцями, створимо стовпець «Значення» та підсумуємо наші значення.

Нарешті, ми візьмемо середнє значення наших значень за допомогою функції AVERAGEX . 

Цей код простіший і набагато логічніший. Він не містить CALCULATE, який вам і так не потрібен. 

Ви можете натрапити на статтю в блозі, яка критикує SUMMARIZE. У блозі сказано, що SUMMARIZE працює всередині досить складно.

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

Принаймні так стверджується в статті блогу. І це добре, якщо ви хочете ухилятися від SUMMARIZE. У такому випадку скористайтеся цією функцією.

Використання функції GROUPBY

Ні в кого немає проблем із використанням функції GROUPBY, тому давайте навчимося використовувати її. Знову створіть змінну Table за допомогою VAR_Table. Потім ми GROUPBY Month і створюємо стовпець Value.

Далі ми подаємо заявку за допомогою CURRENTGROUP , як і працює GROUPBY. Давайте підсумуємо наше значення та знову візьмемо AVERAGEX по ньому. 

Наш код має виглядати так.

Порівняння результатів: функції SUMMARIZE проти GROUPBY та CALCULATE DAX

Тепер давайте перевіримо, чи ярлики, які ми створили, повертають ті самі результати. 

Як ви можете бачити на зображеннях нижче, SUMMARIZE з позначкою « Краще середнє за категорією» та GROUPBY з позначкою « Краще середнє за категорією 2» повертають однакові числа. 

Обидві наші формули повертають 3,4 тис. для Q1, 3,6 тис. для Q2, 3,4 тис. для Q3 і 3,5 тис. для Q4. І знову ж таки, вони працюють у моделі даних однієї таблиці, використовуючи Quarters для нашої таблиці.

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

Висновок

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

Насправді, ймовірно, у 80% – 90% випадків немає жодних причин використовувати CALCULATE для чогось. Замість цього ви можете використовувати функції SUMMARIZE та GROUPBY, які є простішими та логічнішими.

Якщо ви хочете вивчити цей файл PBIX, я вже опублікував його в Галереї швидких заходів і назвав файл « Краще середнє за категорією» . Просто прокрутіть сторінку до кінця, щоб знайти файл PBIX, який можна завантажити, і грати з собою. 

Все найкраще,

Грег Деклер