З більш ніж 250 функціями DAX, як ви визначите, які з них варто вивчати?
Проста відповідь: без досвіду ви цього не зможете зробити. Ця стаття вирішить цю біль 💊, проведучи вас через список з 34 відібраних функцій, які я особисто використовував для вирішення реальних завдань. Маю вас попередити, як побічний ефект, ви можете заощадити роки зусиль і уникнути всіх болісних спроб і помилок.
Усі функції будуть супроводжуватися коротким розповідями, що пояснять логіку того, чому саме ця конкретна функція потрапила в список.
Що вам слід знати, щоб отримати максимальну користь з цієї статті
Давайте почнемо з того, щоб визначити, наскільки ви впевнені в DAX, швидко викинувши кілька термінів.
Скалярне значення, таблиця, ітератор, агрегатор, модель даних, зв’язок. Як ви відчулися від цих слів? Якщо вони викликали яку-небудь тривогу, я раджу вам спочатку прочитати мою попередню статтю, щоб вивчити деякі основні концепції DAX. Якщо всі терміни звучали вам знайомо, ви можете переходити далі. Якщо ви хочете отримати копію файлу pbix, використаного в наступних прикладах, ви можете отримати її ТУТ.
Як ви знаєте, без знання того, як виглядає модель даних на задньому плані, буде неможливо розуміти DAX, тому будь ласка, спочатку запам’ятайте 😳 нижче наведене зображення.
Гаразд, час зануритися. Нехай вас не вводять в оману перші кілька простих прикладів. У наступних прикладах на вас чекають складні трюки з DAX 🔞.
Перш за все, мені хотілося б знати, скільки грошей ми заробляємо?
Давайте почнемо з деяких основних агрегаторів 👶🍼, щоб розігрітися.
SUM, MIN, MAX та AVERAGE – це основний набір функцій, які ви повинні використовувати, коли вам потрібна проста агрегація І вам не потрібно обчислення ряд за рядком. Для обчислення [Sales Amount] (Сума продажів) ми можемо просто використовувати функцію SUM для агрегації стовпця ‘SalesAmount’. Використовуючи схожу логіку, можна додавати інші показники, такі як [Sales Quantity] (Кількість продажів) або [Purchase Value] (Вартість закупівлі).
Щоб полегшити собі життя в майбутньому, я буду зберігати всі показники у спеціальній таблиці з логічною та легкою для відстеження структурою.
Чудово, у нас є деякі продажі, а щодо кількості транзакцій з продажу?
Продовжуючи з основами, COUNTROWS – це ще одна корисна функція, але ви, можливо, не знаєте про неї, якщо ви новачок у DAX.
COUNTROWS просто підраховує, скільки рядків є в таблиці (не в стовпці), яку ви надаєте, і повертає ціле число (або ви коли-небудь чули про таблицю, яка містить 10,5 рядків? 🤔). Використовуйте це завжди, коли вам потрібно знати загальну кількість рядків у таблиці (з дублікатами) — [Count Of Sales Transactions] (Кількість транзакцій з продажу) є чудовим прикладом цього.
Зі створеним виміром (measure), ще одне нагадування — завжди дають своїм DAX-вимірам описову назву, навіть якщо це означає, що назва стає довшою. Можна перейменувати вимір у візуалізації, як я це зробив нижче з #Transactions.
Я починаю уявляти картину, але скільки продуктів ми продаємо?
Здається, що COUNTROWS привів свого двоюрідного брата на вечірку 🥳.
Давайте тепло вітати DISTINCTCOUNT! У той час як COUNTROWS підраховує рядки в таблиці, DISTINCTCOUNT підраховує унікальні значення в стовпці. Це може бути досить очевидним, коли вам потрібно підрахувати унікальні значення, а не всі значення, як зараз, коли ми обчислюємо унікальну кількість ключів продуктів в таблиці ‘Sales’ (Продажі).
Крім того, окрім того, щоб надавати вимірам описові назви, також надайте їм зрозумілий опис у модельному вигляді, щоб користувачі бізнесу також розуміли, що робить ваш вимір.
Оскільки ми розмовляємо про унікальні VALUES (ЗНАЧЕННЯ) тут, має сенс згадати функцію VALUES.
VALUES – це функція, яка повертає одноколоночну таблицю, що містить унікальні значення стовпця, який ви надаєте. Цей тип таблиць вам знадобиться досить часто. *Також можливо надати цілу таблицю VALUES, замість одного стовпця, але на мою думку, це більше рідкісний випадок (якщо вам потрібні деталі, перевірте це тут).
Оскільки VALUES повертає таблицю, неможливо відобразити результат у візуалізації. Але чому б нам не випробувати зовсім новий Query View в Power BI Desktop, щоб побачити, як виглядає таблиця унікальних ключів продуктів.
Наразі цього достатньо для унікальних значень, але пам’ятайте, що робить VALUES, оскільки ми будемо використовувати його знову пізніше в цій статті.
Чи можливо також побачити середню ціну продажу для кожного продукту?
Звісно! Оскільки я великий прихильник використання вимірів, створених до цього часу, давайте спробуємо використати їх.
Щоб обчислити середню ціну продажу, нам потрібно поділити [Total Sales] (Загальні продажі) на [Sales Transaction Count] (Кількість транзакцій продажу) за допомогою (це вас може вразити 😲) функції DIVIDE. DIVIDE є зручною функцією, оскільки ви можете вказати значення, яке повинно бути повернено в разі помилки ділення на нуль. Достатньо років у Excel справжньо навчає вас ненавидіти той клятий #DIV/0! 🤬. Якщо вам не потрібно обробляти ділення на нуль, класичне ділення знаком “/” забезпечить кращу продуктивність.
Ви також можете використовувати DIVIDE для повернення порожнього значення замість нуля за допомогою цього синтаксису: ВашеЗначення * DIVIDE ( ВашеЗначення, _ВашеЗначення ). Якщо ви вирішите використовувати це, супроводьте це змінною, щоб вам не довелося обчислювати одне і те ж значення тричі.
Перед тим як ми продовжимо ✋, пам’ятайте завжди задавати формат вашим вимірам. Немає нічого менш професійного, ніж випадкова кількість десяткових розрядів або відсутність роздільника тисяч 😤.
Вибачте, але середня ціна продажу не має сенсу на рівні клієнта та в загальному розмірі
Але ви, можливо, вже помітили це, ЯКЩО (IF) ви дійсно фокусуєтеся на читанні цієї статті 🧐
Середня ціна продажу на загальному рівні абсолютно не має сенсу, оскільки, коли ви ділите продажі кількох продуктів на кількість продажів кількох продуктів, результат не має жодного значення. Давайте виправимо це, використовуючи функції IF та HASONEVALUE. IF – це чудова функція для використання тоді, коли у вас є лише одне логічне правило для перевірки, і в парі з HASONEVALUE ми можемо легко перевірити, чи маємо ми лише один продукт в поточному контексті фільтрації.
Говорячи про цю техніку, функція SELECTEDVALUE – це ще одна корисна функція, яка виконує ту саму перевірку IF HASONEVALUE, і якщо є лише одне значення, повертає це значення. У випадку багатьох значень ви можете визначити, яке значення повертати, якщо таке існує. Ось як це все виглядає:
Було б гарно також позначити середню ціну продажу як дешеву чи дорогу
Ми могли б зробити це, написавши кілька вкладених операторів IF, але є й кращий спосіб – використовуючи функцію SWITCH.
SWITCH відноситься до родини логічних функцій, але, в суперечність до IF, він може перевіряти кілька 🤹♂️ логічних правил і повертати значення, коли логічна перевірка є TRUE. Погляньте на малюнок нижче, щоб побачити, наскільки легко визначити продукти в трьох різних цінових категоріях.
Якщо ви цікавитесь причиною використання знаку _ перед іменами змінних, це найкраща практика, щоб ви могли чітко відрізняти змінні. Це також полегшує написання DAX, оскільки IntelliSense буде пропонувати вам тільки змінні, коли ви починаєте вводити _.
Повертаючись до продажів, я забув запитати, чи можливо відображати частку продажів % між клієнтами?
Перестаньте вже питати, чи це можливо, у DAX все можливо! 🔥
Ми просто повинні попросити допомоги від супергероя DAX 🦸♂️, функції CALCULATE. За допомогою CALCULATE можна додавати, видаляти або змінювати фільтри, які впливають на ваш розрахунок. Крім того, CALCULATE виконує перехід контексту, але це справжньо заслуговує на власну статтю. Але зараз знову до історії… Давайте використаємо CALCULATE спільно з його товаришем REMOVEFILTERS для розрахунку продажів для всіх клієнтів, щоб ми могли порівняти його з продажами для поточно відфільтрованого клієнта. Важливо зрозуміти, що REMOVEFILTERS є лише прикріпленим до CALCULATE помічником, він сам по собі не повертає жодного значення. Якщо вам потрібна таблиця з усіма видаленими фільтрами, використовуйте функцію ALL для цієї роботи.
Якщо ви вже десь натрапляли на функцію CALCULATETABLE, то корисно знати, що її єдине відмінність від CALCULATE полягає в тому, що вона повертає таблицю як результат, а не одне значення.
Хей, хей, хей, зачекайте хвилинку, чому частка продажів не є 100% на загальному рівні?
Гарне питання! Здається, що у мене був фільтр на сторінці, щоб показати лише двох клієнтів, і за допомогою REMOVEFILTERS ми розрахували продажі для кожного окремого клієнта в усій моделі даних.
Якщо ви хочете отримати частку продажів % від того, що ви бачите, вам слід використовувати функцію ALLSELECTED спільно з CALCULATE. (Насправді, ALLSELECTED робить складні речі за кадром, відновлюючи останній тіньовий контекст фільтрації 👻, але це справа досвідченого воїна DAX 🎖️). Те, що вам потрібно знати, це те, що ви можете використовувати ALLSELECTED з душею для обчислення “всього, що ви бачите 👀” загальної суми.
Якщо, з якоїсь причини, ALLSELECTED дозволяє вам отримати удар 🥊 у обличчя, зайдіть на цю сторінку та пов’язані статті.
Наступне, я б хотів побачити динаміку зростання продажів порівняно з попереднім роком, оскільки, якщо ви не зротаєте, ви вмираєте
Час представити нашу першу функцію для роботи з часом ⌛️, DATEADD, мій особистий фаворит завдяки своїй універсальності.
Тепер, щоб функції для роботи з часом працювали належним чином, завжди пам’ятайте використовувати належну таблицю дат у своєму моделі даних! Після того, як ви перевірили цей пункт, ви можете використовувати DATEADD, щоб розпочати подорож в часі 👽 у днях, місяцях, кварталах або роках (але не в тижнях). DATEADD повертає одноколоночну таблицю дат, зсунутих назад або вперед у часі, яку ви потім можете використовувати як фільтр у CALCULATE для обчислення продажів попереднього року, а в кінцевому підсумку – зростання продажів.
Якщо вам більше до вподоби, ви можете також використовувати функцію SAMEPERIODLASTYEAR замість DATEADD – 1 YEAR.
Чи це дійсно правильно? Зростання від’ємне, але, але на мою думку, у нас був добрий старт в продажах на початку року?
Ага! Це через те, що у мене є відносний фільтр за датою “Цього року” (1.1.2024 – 31.12.2024) на сторінці.
DATEADD переміщує цей весь фільтр на один рік назад (1.1.2023 – 31.12.2023), і, оскільки у мене є продажі лише в січні 2024 року, я завершую порівнювати їх з повнорічними продажами за 2023 рік. Дозвольте мені швидко виправити це, змінивши відносний фільтр за датою на “цей рік до цього часу”. Але… Але де він? Microsoft? Чи хто-небудь? Немає опції для цього? Серйозно?.. Ок, давайте тоді зробимо це з DAX 🤷♂️.
Ми можемо зробити це, використовуючи функцію KEEPFILTERS. KEEPFILTERS – це ще одна функція, яку можна використовувати лише всередині CALCULATE, і вона не повертає безпосередньо значення чи таблицю, вона змінює поведінку фільтрів. Стандартна поведінка CALCULATE – це заміщення фільтрів, тому, якщо ми просто скажемо ‘Date’Date ≤ _LastSalesDate, ми повністю замінимо відносний фільтр за датою. Ми цього не хочемо. Однак, використовуючи KEEPFILTERS, можна додавати нові фільтри і УТРИМУВАТИ (KEPP) існуючі фільтри в тому випадку, якщо вони також проходять новий фільтр.
Отже, щоб сформувати таблицю порівняння дат для обох років, давайте обернемо ‘Date’Date ≤ _LastSalesDate в KEEPFILTERS. Це збереже дати, які надходять з відносного фільтра за датою, але тільки у випадку, якщо ці дні співпадають або раніше останньої дати рахунку-фактури.
Потім ми просто використовуватимемо цю таблицю порівняння дат як фільтр для обчислення переглянутої, фактично порівняльної версії існуючого виміру [Сума продажів попереднього року].
Це змусило мене замислитися, яка частина цього зростання продажів пов’язана з підвищенням цін для клієнтів?
Щоб розрахувати ефект підвищення цін, перш за все, нам потрібно знати, які продукти продаються якому клієнту.
У цьому випадку ми звертаємося до функції SUMMARIZE. SUMMARIZE групує таблицю на основі стовпців, які ви надаєте їй, і повертає узагальнену версію цієї таблиці як результат. Знову ж таки, оскільки SUMMARIZE повертає таблицю, єдиний спосіб легко відобразити, як працює результат, – це показати його у режимі перегляду запитів. Ось, як виглядає таблиця продажів, згрупована за клієнтом та продуктом:
Але як ви використовуєте цю таблицю для розрахунку підвищення цін?
Час познайомитися з родичами основних агрегаторних функцій: SUMX, MAXX, MINX, AVERAGEX. Не питайте мене, звідки походить закінчення X (можливо, хтось розумніший за мене може залишити коментар з цього приводу). Всі функції X ітерують таблицю, яку ви надаєте їй, рядок за рядком і виконують суму, мінімум, максимум або середнє значення результатів на рівні рядка в залежності від того, яку функцію X ви використовуєте.
У нашому прикладі ми використовуватимемо SUMX, тому що це звучить класно 😎. Просто жартую, ми використовуємо його, оскільки нам потрібна сума підвищення цін. SUMX буде ітерувати нашу таблицю комбінацій клієнт-продукт рядок за рядком, порівнювати різницю середньої ціни продажу між цього та минулого року, множити різницю на кількість продажів, а потім підсумовувати результат.
BOOM 💣, і ви вже точно знаєте, який вплив має підвищення цін на ваш бізнес.
Один клієнт має від’ємне підвищення цін. Будь ласка, скажіть мені, що ми не продаємо жодних продуктів з низьким відсотком маржі продажів?
Щоб дати відповідь на це питання, нам потрібно обчислити кількість продуктів, які були продані за визначеною маржою продажу %.
Я пропоную розпочати будувати рішення цього завдання зсередини. Знову ж таки, як базу нам потрібна таблиця проданих продуктів на кожного клієнта (ви вже знаєте, як це зробити завдяки попередньому прикладу). Але нам також потрібно додати стовпець, щоб знати маржу продажів % для кожної комбінації клієнта та продукту. Для цього ми використовуватимемо функцію ADDCOLUMNS (нарешті, ім’я функції, яке чітко описує те, що вона робить 😌). Як ім’я вказує, ADDCOLUMNS додає нові стовпці до таблиці, яку ви надаєте, ітеруючи рядок за рядком.
Ось, як виглядає результат ADDCOLUMNS у режимі перегляду запитів:
Як ви бачили, багато разів сила DAX проявляється при використанні кількох функцій разом. Так що може, додамо ще один рівень до цього обчислення.
Тепер у нас є таблиця всіх комбінацій клієнта та продукту, проданих, збагачених маржею продажів %. Далі нам потрібно знайти спосіб відфільтрувати цю таблицю так, щоб залишилися тільки рядки з низькою маржею продажів. Для цього ми використовуватимемо 🥁…. функцію FILTER! Яке несподіване відкриття! Тепер.. що вважається низькою маржею, досить сильно залежить від галузі, але в нашому бізнесі продажу чарівницьких паличок і кришталевих куль 🔮 давайте скажемо, що наш поріг для низької маржі продажу – це 40%.
Зрештою, нам потрібно знати, скільки рядків залишається в кінцевій таблиці. Для цього ми просимо послугу від нашого старого друга і обгортаємо весь цей механізм всередині COUNTROWS.
Очевидно, що нам потрібно ще трохи попрацювати над ціноутворенням, але чи виставляємо ми рахунки нашим клієнтам одразу після доставки?
Ви знаєте, що модель даних за основою DAX – це справжній казанова 🤵♂️, і не рідко можна побачити кілька відносин між двома таблицями.
Ця модель даних не є винятком, оскільки таблиця ‘Sales’ має два відношення з таблицею ‘Date’, використовуючи дату виставлення рахунку та дату доставки. Використовуючи цей факт, має бути досить просто створити показник для суми продажів на основі дати доставки. Фактично, це так, ми просто повинні використати функцію USERELATIONSHIP всередині функції CALCULATE, щоб активувати зв’язок між датою та датою доставки продажів. Потім ми можемо легко відобразити [Sales Amount By Delivery Date] (Сума продажів за датою доставки) поруч із [Sales Amount] (Сума продажів) за датою виставлення рахунку.
Ііі, звісно, виглядає так, ніби ми не виставляємо рахунки в день доставки (могли б і здогадатись на цей момент 🤦♂️).
Оскільки, схоже, існує розрив, як довго в середньому затримується виставлення рахунків після доставки?
Добре, це буде просто.
Ми просто використаємо DATEDIFF, яка порівнює різницю між двома датами, датою доставки та датою виставлення рахунку. Щоб отримати правильний середній показник, ми виконаємо обчислення рядка за рядком за допомогою AVERAGEX.
Єдине, що я забув, це те, що дата доставки зберігається як ціле число в таблиці “Sales” (Продажі), тому що я зробив дзвінок, щоб не порушувати згортання запиту (query folding). Мені дуже подобається рішення зберігати дати в базі даних цілими числами ❤️. Ну що ж, здається, що перед нами знову чекає робота з DAX, оскільки DATEDIFF не любить працювати з цілими числами. Щоб обійти цю проблему, ми вдамося до допоміжних функцій тексту LEFT, MID та RIGHT, щоб витягти рік, місяць і день з цілого числа і конвертувати це в правильну дату за допомогою функції DATE.
Тепер ми нарешті можемо побачити середню затримку виставлення рахунків. І це, шановні пані та панове, – це залишання грошей на столі 💵 при сьогоднішніх процентних ставках.
Можливо, ми також можемо подивитися, скільки товарів у нас є для кожного клієнта?
Звісно, це буде просто сума вартості запасів.
Ми просто повинні бути уважні й усвідомлювати, що таблиця ‘Stock’ відрізняється від таблиць ‘Sales’ та ‘Purchase’. Різниця полягає в тому, що таблиця ‘Stock’ містить щомісячні знімки (snapshots), які не можна агрегувати. Замість цього нам потрібно обчислити вартість запасу для останнього обраного знімка. Це можна легко зробити за допомогою функцій, які ми вже використали в цьому матеріалі: MAX, SUM та CALCULATE.
Але зачекайте, чому вартість запасу однакова для всіх клієнтів і товарів?
Ну от, тепер, коли ви це сказали, я бачу, що немає зв’язків між таблицею ‘Customer’ та ‘Stock’. Коли я фільтрую таблицю ‘Customer’, вона фільтрує лише таблицю ‘Sales’, а не ‘Stock’ 🤔. Я міг би змінити взаємозв’язок між “Sales” і “Product” на двосторонній, щоб виправити це, але оскільки мудра людина 🧙♂️ колись мені сказала ніколи цього не робити, я цього не зроблю. На щастя, немає проблеми, яку не можна вирішити за допомогою DAX💡!
Коли ви зіткнетеся з проблемами зв’язків, що робите? Ви викликаєте Доктора Філа 👴🏻! Для DAX це еквівалент функції TREATAS. TREATAS дозволяє вам “телепортувати” фільтри з однієї таблиці в іншу, незалежно від того, як налаштовані зв’язки між таблицями. Отже, щоб виправити цю проблему, спочатку ми створимо таблицю товарів, які були продані конкретному клієнту (з таблиці ‘Sales’), а потім відфільтруємо таблицю ‘Product’, використовуючи цю віртуальну магічну таблицю. Таблиця ‘Product’ буде природно фільтрувати таблицю ‘Stock’, слідуючи звичайним відносинам.
Добре, схлже, ми ще не зовсім там.
Хоча наша формула технічно працює, на жаль, кожен продукт був проданий кожному клієнту, що в сумі дає результат, який мало про що говорить. No bueno 🙅♂️! Крім того, вартість запасу на загальному рівні тепер неправильна, оскільки один з товарів ніколи в історії не був проданий жодному клієнту. Час вдягнути свій DAX Gi 🥋 і, нарешті, внести цю формулу.
Щоб вирішити першу проблему, ми хочемо розподіляти вартість акцій на основі того, який клієнт купив найбільше акцій за всю історію. Для цього ми будемо ітерувати всі товари і множити показник [Stock Value Customer] на інший вже створений показник [Share Of Sales Amount % All History]. Щоб виправити другу проблему, ми використаємо функцію ISFILTERED, щоб перевірити, чи є фільтр у таблиці “Клієнт”, і якщо його немає, повернемо початковий показник [Stock Value], який також відображає значення для товарів, які не були продані жодному клієнту.
В яблучко (Bulls eye) 🎯, нарешті, формула працює так, як нам хочеться.
Це магія, і останнє, що я хотів би знати, це скільки часу потрібно, щоб огорнути запас один раз?
Потрібно подати вам кулак 🤜🤛 за те, що ви дійшли аж до останнього прикладу в цьому довгому матеріалі (можливо, мені варто зробити з цього книгу 🤔).
Для обчислення класичного показника запасів [Days Inventory Outstanding] (Дні наявності запасів) ми звертаємося до ще однієї дивовижної функції аналізу часу – DATESINPERIOD. Це велика функція, коли вам потрібно створити таблицю послідовних дат, починаючи з вказаної дати. У нашому прикладі ми створюємо таблицю дат попередніх 12 місяців, починаючи з останньої вибраної дати в звіті. Потім ми використовуємо цю таблицю як фільтр у функції CALCULATE для обчислення як вартості проданих товарів, так і середньої вартості запасу за попередні 12 місяців.
За цим ми можемо побачити, що на оборот запасів в середньому потрібно 302 дні, трошки повільно на мою думку.
На цьому я завершую статтю. Якщо вам вдалося виконати кожен приклад, я гарантую, що ви будете на шляху до того, щоб стати справжнім ніндзя DAX!
ОРИГІНАЛ СТАТТІ:The 34 DAX Functions I Use To Answer 95% Of Business Problems
АВТОР СТАТІ:LASSE MALO
🚀Долучайтесь до нашої спільноти Telegram:
🚀Долучайтесь до нашої спільноти FaceBook: