Все, що вам потрібно знати про DAX для тих, хто має досвід роботи з Excel

Хоча DAX на перший погляд може здаватися схожим на Excel, він працює зовсім по-іншому.

У найкращому випадку DAX схожий на кришталевий шар 🔮, який має відповіді на всі ваші найважчі бізнес-проблеми. Але у найгіршому випадку це може бути дійсно дратівливим і заплутаним. Я знаю, оскільки я бачив обидві сторони під час своїх 10 років написання DAX. З цим посібником у вас буде всі інструменти, які вам потрібно ефективно використовувати “кришталевий шар” і уникати розчарування. Спочатку я розгляну деякі основні концепції, які вам потрібно зрозуміти, а потім наведу кілька прикладів цих концепцій на практиці, разом із візуальним представленням того, що відбувається на поверхні.

Перша концепція, яку вам потрібно зрозуміти, – це модель даних.

Ви не зможете писати DAX, якщо ви не розумієте модель даних

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

Наприклад, якщо ви купуєте, зберігаєте і продавати продукти, у вас є таблиці, такі як ‘Продажі'(Sales), ‘Закупівлі'(Purchase), ‘Продукт'(Product), ‘Склад/Залишки'(Stock), ‘Клієнт'(Customer), ‘Постачальник'(Supplier) і ‘Дата'(Data). Ось як зазвичай виглядає модель даних (розгорніть зображення для кращого їх перегляду):

Таблиці в моделі даних розділяються на таблиці фактів(Fact Tables) та таблиці вимірів (Dimension Tables).

Таблиці фактів(Fact Tables) містять вимірювальні показники (наприклад, скільки продуктів ми продали), тоді як таблиці вимірів містять описові дані (наприклад, ім’я клієнта, якому ми його продали). Кожний стовпець у таблиці має тип даних, такий як текст, ціле число, десяткове число або дата.

Таблиці вимірів(Dimension Tables) — короткі та “товсті” 🍕. Наприклад, таблиця ‘Клієнт’ є таблицею вимірів, де є один рядок для кожного клієнта, але багато стовпців, які описують цього клієнта. Таблиця ‘Клієнт’ може містити стовпці для ідентифікатора клієнта (= унікальний ідентифікатор(unique identifier) клієнта), імені клієнта, галузі, контактної особи, торгового представника, адреси, країни, континенту, умов оплати і багато іншого.

Таблиці фактів, навпаки, високі та “стрункі” 💪. Наприклад, таблиця ‘Продажі’ є таблицею фактів і може містити стовпці, такі як дата виставлення рахунку, ідентифікатор продукту, ідентифікатор клієнта, кількість продажів, сума продажів та маржа продажів. Але, оскільки у кожного клієнта є багато операцій з продажу (сподіваємося), таблиці фактів довші за таблиці вимірів.

Таблиці вимірів та таблиці фактів з’єднані за допомогою відносин(relationships). Ці відносини у 99% випадків повинні мати лише один напрямок. Якщо ви, як і я, отримуєте односторонні вказівки від свого чоловіка або дружини вдома, у вас не повинно виникнути проблем з тим, щоб зрозуміти цю концепцію.

Лінії та стрілки ⬇️, які ви бачите в моделі даних, відображають напрямок потоку взаємозв’язку (один клієнт ‘1’ → багато продажів ‘*’). Таблиці зазвичай з’єднуються за допомогою стовпців “…Key” – це просто ботанічний(nerd) термін для позначення стовпця, який використовується для з’єднання двох таблиць.

Наразі цього достатньо для розуміння моделей даних.

Перестаньте створювати обчислювані стовпці, перш ніж ви навіть почнете… серйозно

Далі існують дві категорії обчислень DAX: обчислювані стовпці(calculated columns) (які схожі на статуї 🗽) та міри (measures) (які схожі на хамелеонів 🦎).

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

Загальна помилка, яку роблять люди, які приходять з Excel, полягає в тому, що вони починають додавати обчислювані стовпці 🗽 зліва та справа, але це не те, що ви хочете робити ❌. Обчислюваний стовпець слід використовувати лише у випадках:

1. Динамічний розрахунок міри займає вічність (що практично ніколи не відбувається).
   
2. Або вам потрібен стовпець для групування або фільтрації даних, але ви не можете створити його за допомогою Power Query (що практично ніколи не буває).

Отже, якщо це все ще неясно, то у 99% випадків вам слід створити міру 🦎, а не обчислюваний стовпець.

Функції DAX працюють за двома основними способами для створення таблиць або значень

Більшість функцій DAX повертають значення(value) (скаляр) або таблицю в результаті і досягають цього результату шляхом ітерації або агрегації.

Цей жахливий жаргон даних 🤓, насправді означає, що ви можете виконувати обчислення рядків за рядком (ітерація) або шляхом підсумовування (агрегат). Виходячи з мислення Excel, SUM ( A1:A10 ) – це агрегатор, A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + A9 + A10 – це ітератор.

Це призводить до 4 категорій функцій:

1. Таблиця обчислюється ітерацією (FILTER, ADDCOLUMNS, TOPN)
2. Значення обчислюється ітерацією (SUMX, MINX, MAXX, AVERAGEX, RANKX)
3. Таблиця обчислюється агрегацією (VALUES, DISTINCT, SUMMARIZE, ALL)
4. Значення обчислюється агрегацією (SUM, MIN, MAX, COUNTROWS, DISTINCTCOUNT)

З усіх цих категорій, це те, що зазвичай збиває з пантелику новачків, які приходять в DAX, – це табличні функції. Причина, по якій це може бути важко зрозуміти, полягає в тому, що ви не можете легко відобразити результат табличної функції у себе перед очима. Це означає, що вам потрібно почати думати в термінах таблиць і “бачити” 👀 результат у своїй уяві.

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

Зауважте, що обчислення в DAX не відбувається так, як ви можливо уявляєте

Подивіться на підсвічений розрахунок нижче і вгадайте, як він обчислюється?

Просте сумування значень, які ви бачите вище його?

Ні.

DAX є “сліпим” 🧑‍🦯 і не враховує оточуючі клітинки. Замість посилань на клітинки, як в Excel, в DAX кожна точка даних (data point) обчислюється незалежно від інших. Правильний спосіб думати про DAX – це спочатку визначити, які фільтри впливають на цю конкретну точку даних, потім застосувати ці фільтри до моделі даних, і тільки на останньому етапі виконати обчислення.

Давайте спробуємо розібрати цей приклад у три етапи:

1. Які фільтри впливають на дані, виділені червоним? — Фільтр може бути внутрішнім для рядків і стовпців у таблиці, а також зовнішнім, наприклад, з панелі фільтрів або інших візуалів, які здійснюють перехресне фільтрування нашої точки даних. У цьому прикладі є лише один фільтр, що впливає на виділену точку даних – це фільтр за Рік = 2022, який надходить з панелі фільтрів.
   
2. Застосуйте фільтри до моделі даних відповідно до відносин — Тепер ваше розуміння моделі даних стає корисним, оскільки ви знаєте, що коли ви накладаєте фільтр на “Дата”[Рік], він буде переміщатися з таблиці “Дата” до таблиці “Продажі” відповідно до зв’язку.
   
3. Обчисліть результат — Останнім кроком є агрегування суми решти рядків у стовпці “Продажі”[SalesAmount].

Це може бути легше зрозуміти, подивившись на модель даних:

Час почати бачити таблиці у своїй уяві

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

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

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

1. Які фільтри впливають на виділену червоним точку даних? — Ті ж фільтри, тільки Year = 2022
   
2. Фільтруйте модель даних, відповідно до зв’язків — Таблиця ‘Date’ фільтрує таблицю ‘Sales’ точно так само, як і в першому прикладі
   
3. Обчисліть результат — Щоб отримати результат, вам потрібно знати, скільки унікальних значень є в стовпці ‘Sales’ [ProductKey]. VALUES – це функція, яка робить саме це. Результат VALUES буде таблицею з одним стовпцем, яка містить три значення (2001, 2002, 2003). Але через те, що це таблиця, ви не можете відобразити цей результат на візуалізації. Зараз настав час “побачити” цю таблицю у вашому розумі 🧐. Щоб дістатися до кінцевого результату, вам потрібно обгорнути результат VALUES у функцію COUNTROWS, яка обчислить, скільки рядків є в цій містичній таблиці, яку ви не бачите.

Знову ось ілюстрація того, що відбувається в моделі даних:

Маніпулюйте фільтрами та зустріньте свого нового кращого друга, змінні (variables)

Щоб продовжити аналіз клієнтів, обчисліть частку продажів %, проте цього разу з фокусом на конкретного клієнта, Acme Corp.

Для досягнення цього результату нам потрібно розділити продажі Acme Corp. на загальну кількість клієнтів. Тут корисні змінні. Змінні є заповнювачами для проміжних результатів обчислень, які обчислюються один раз, але можуть бути використані багато разів. Змінні можуть зберігати як таблиці, так і скалярні значення.

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

Змінна _TotalSales — Продажі Acme Corp.:

1. Які фільтри впливають на виділений червоний пункт даних? — Ви вже знаєте, що є фільтр Рік = 2022, але цього разу також є фільтр, який надходить з рядка таблиці CustomerName = “Acme Corp.”.
   
2. Відфільтруйте модель даних за допомогою зв’язків — Обидві таблиці ‘Date’ і ‘Customer’ будуть фільтрувати таблицю ‘Sales’ відповідно до зв’зків між ними.
   
3. Обчисліть результат — Підсумуйте стовпець ‘Sales’ [SalesAmount], що призведе до значення 6 105,00. Це значення буде збережено в змінній _TotalSales.

Модель даних для змінної _TotalSales виглядає наступним чином:

Змінна _SalesAllCustomers — Продажі всіх клієнтів:

1. Крім того, які фільтри впливають на виділений червоний пункт даних? — Спочатку застосовується фільтр Рік = 2022 та CustomerName = “Acme Corp.”, АЛЕ потім видаляється будь-який фільтр з таблиці клієнтів за допомогою CALCULATE з REMOVEFILTERS (Customer). Це означає, що залишається лише фільтр за Роком. Фільтр за Клієнтом застосовується та знімається. Ось так ви маніпулюєте фільтрами, мов чарівник.
   
2. Відфільтруйте модель даних, дотримуючись зв’зяки — Тільки таблиця ‘Date’ буде фільтрувати таблицю ‘Sales’.
   
3. Обчисліть результат — Підсумуйте стовпець ‘Sales’ [SalesAmount], що призведе до значення 20 226,90. Це значення буде збережено в змінній _SalesAllCustomers.

Діаграма моделі даних для змінної _SalesAllCustomers ідентична діаграмі “Приклад 1: Модель даних загальних продажів”. У разі потреби ви можете повернутися до цієї діаграми, щоб оновити свої знання.

Змінна _ShareOfSales — Відсоток від загальних продажів для Acme Corp.:

Змінні залишаються незмінними після обчислення. З цієї причини немає потреби повторювати кроки 1, 2 і 3 для змінної _ShareOfSales. Ви можете просто використовувати вже обчислені змінні і поділити змінну _TotalSales на змінну _SalesAllCustomer, щоб отримати кінцевий результат 30,18%.

Коли агрегація не працює, настав час йти ряд за рядом

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

Оскільки я зробив жахливу роботу при трансформації даних у Power Query, у таблиці ‘Stock’ відсутній стовпець для вартості запасів 😧. Тут є лише стовпці залишку запасів та ціни на запаси, які вам потрібно помножити, щоб отримати фактичну вартість запасів. Ви могли б додати обчислений стовпець, але стримайтеся від цього! Просто не робіть цього ⛔. Це можна виправити за допомогою міри!

Якщо ви спробуєте написати міру такого виду: ‘Stock'[StockBalance] * ‘Stock'[StockPrice], DAX люб’язно надасть вам помилку:

“Мілорде, це не показник, потрібно або агрегувати, або ітерувати⚠️.”

Якщо ви замість цього спробуєте SUM (‘Stock’ [StockBalance]) * SUM (‘Stock’ [StockPrice]), ви отримаєте результат, але це абсолютний нісенітниця 💩. Вам потрібно йти рядок за рядком, і для цього вам допоможе функція SUMX.

Ось розбір рішення за допомогою двох змінних:

Змінна _MaxDate — Спочатку знайдіть останню дату, на яку є значення запасів:

1. Які фільтри впливають на виділений червоний пункт даних? — Фільтр з панелі фільтрів Рік = 2022 І видаліть будь-які фільтри з таблиці продуктів за допомогою функції CALCULATE та REMOVEFILTERS (Product).
   
2. Відфільтруйте модель даних, дотримуючись зв’язки — Тільки таблиця ‘Date’ буде фільтрувати таблицю ‘Stock’. (Якщо б фільтр був на таблиці ‘Product’, функція CALCULATE його видалила б.)
   
3. Обчисліть результат — Максимальне значення стовпця ‘Stock’ [DateKey], що призведе до значення 31.01.2022.

Ось як модель даних фільтрується для змінної _MaxDate:

Змінна _StockValue — Обчисліть суму вартості запасів, рядок за рядком, для останньої дати, на яку є запаси:

1. Які фільтри впливають на виділений червоний пункт даних? — Рік = 2022 з панелі фільтрів І ‘Date'[DateKey] = _MaxDate за допомогою функції CALCULATE.
   
2. Відфільтруйте модель даних, дотримуючись зв’язків — Тільки таблиця ‘Date’ буде фільтрувати таблицю ‘Stock’.
   
3. Обчисліть результат — У таблиці ‘Stock’ залишається два рядки. Обчисліть ‘Stock'[StockBalance] * ‘Stock'[StockPrice] рядок за рядком, а потім підсумуйте результат.

• Рядок 1: 284 * 0,80 = 227,20
  
• Рядок 2: 5905 * 0,11 = 649,55
  
• Загальна сума = 227,20 + 649,55 = 876,75

Модель для змінної _StockValue виглядає наступним чином:

Загальне висновок

Сподіваюся, що цей посібник виявився корисним для розуміння того, як працює DAX.

Якщо нічого іншого ви не винесете з цього матеріалу, тоді: завжди пам’ятайте, що DAX – це сліпий хамелеон, який має кришталеву кулю, щоб відповісти на всі ваші бізнес-проблеми 🧑‍🦯🦎🔮.

ОРИГІНАЛ СТАТТІ:Everything You Need To Know About DAX Coming From Excel Background

АВТОР СТАТІ:LASSE MALO

🚀Долучайтесь до нашої спільноти Telegram:

• Data Life UA
• Group Data Analysis UA
• Group Data Engineering UA

🚀Долучайтесь до нашої спільноти FaceBook:

• Data Life UA