Підручник з мови DAX LuckyTemplates на зворотному TOPN

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

Щоб пояснити сценарій, який ми збираємося розглянути, у нас є проста таблиця як приклад. У першому стовпці ми маємо Фінансовий рівень 1 . У другому стовпці ми маємо дату під назвою «Завершено». В останньому стовпці ми маємо комбінацію фінансового рівня та завершеної дати, яку ми назвемо Дохід .

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

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

У першій версії показника я перерахував дати, менші за 29.04.2021, у які ми маємо певний дохід.

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

Зміст

• Моделювання даних
• Версія 1
• Підготовка контексту фільтра
• Перевірка результату обчислення LuckyTemplates мовою DAX
• Інші обчислення LuckyTemplates мовою DAX
• Версія 2
• Використання функції KEEPFILTERS
• Версія 3
• Висновок

Моделювання даних

Перш ніж писати фактичний DAX, давайте швидко поглянемо на модель даних, яка містить кілька таблиць. Нас цікавлять лише таблиці «Дата» , таблиця «Дані» , таблиця категорії роботи та таблиця «Клієнт» .

Зауважте, що таблиця Customer не дуже важлива, оскільки вона використовується лише всередині слайсера.

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

Версія 1

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

Підготовка контексту фільтра

Перше, що ми збираємося зробити, це отримати всі значення фінансового рівня з контексту фільтра. Ми створимо та назвемо його як FinancialLevelInFilterContext .

Щоб отримати значення з контексту фільтра, ми збираємося використовувати в таблиці категорії роботи, яка містить стовпець фінансового рівня. Потім нам потрібно створити іншу змінну та назвати її Result . Потім напишіть і закрийте його також.

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

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

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

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

Потім ми використаємо таблицю «Дані», щоб узагальнити ці дві таблиці: «Категорія роботи» та «Дата».

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

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

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

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

А потім ми збираємося отримати дату зі змінної RemoveZeroes . Для цього ми можемо створити іншу змінну та назвати її KeepOnlyDates .

Я можу використовувати, щоб позбутися повторюваних дат, які повертає .

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

Потім ми використаємо останні три дати в стовпці дати, а потім використаємо роздільник.

Давайте перетягнемо нашу щойно створену міру всередину цієї матриці. Результат ми отримуємо: 31/03/2021, 07/04/2021 та 02/04/2021. Це означає, що наш захід працює.

Давайте повернемося до редактора та позбудемося частини RETURN CONCATENATEX. Щоб отримати останні три дати, ми можемо перевірити, які рядки зі змінної RemoveZeroes насправді є частиною цих трьох останніх дат.

І для цього ми створимо ще одну змінну DatesInLast3Dates і використаємо функцію FILTER для RemoveZeroes . Це повертає таблицю, яка складається з категорії роботи та дати.

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

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

Ми використаємо змінну, яку ми створили на початку, щоб перевірити, чи кожен рядок змінної DatesInLast3Dates доступний у контексті фільтра. Ми можемо написати іншу змінну та назвати її IsInFilterContext .

Це призведе до фільтрації дат у змінній DatesInLast3Dates . Потім я поверну SUMX через контекст IsInFilter і підсумую стовпець доходу.

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

Перевірка результату обчислення LuckyTemplates мовою DAX

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

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

І ми можемо просто написати RETURN DatesInLast3Dates . Якщо я натисну «Підтвердити» , ми отримаємо таблицю, але ми знаємо, що вона насправді не повертає правильний результат, оскільки нам насправді потрібно імітувати поведінку зрізу.

Ми можемо загорнути цей код у CALCULATETABLE і зробити відступ у всьому. В останній частині я збираюся написати, що дата має бути більшою або дорівнювати 2021-03-15, а ім’я клієнта має дорівнювати DHL Supply Chain .

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

Інші обчислення LuckyTemplates мовою DAX

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

Версія 2

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

І замість того, щоб повертати скалярне значення, цього разу ми повертатимемо DatesInLast3Dates . Це поверне той самий CALCULATETABLE, який ми створили раніше. Ми збираємося використовувати функцію CALCULATE, і в першому аргументі ми збираємося обчислити показник доходу та додати CALCULATETABLE як контекст фільтра.

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

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

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

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

Після того, як ми натиснемо «Підтвердити», ми побачимо, що ми повідомляємо правильне значення для кожної комірки, і що версія 1 і версія 2 повертають правильне значення.

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

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

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

Версія 3

Давайте розглянемо інший спосіб обчислення того самого обчислення за допомогою мови DAX LuckyTemplates. Давайте створимо копію міри версії 2 і створимо іншу міру. Ми назвемо цю версію 3.

Ідея цього обчислення полягає в тому, що оскільки ми обчислюємо дохід за допомогою функції CALCULATE, нам не потрібно використовувати частину ADDCOLUMNS, оскільки ми дублюємо те саме всередині та поза CALCULATE. Замість цього ми можемо просто написати, що Дохід не повинен дорівнювати 0.

Коли я натискаю «Підтвердити», нам потрібно переконатися, що код працює, тому давайте перетягнемо міру всередину матриці.

Ви бачите, що код фактично повертає однакове значення для кожної комірки.

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

Висновок

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