Розширені таблиці в LuckyTemplates

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

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

На лівій стороні ми маємо зв’язок «один-до-багатьох» між продуктами, категоріями, підкатегоріями та таблицями продажів. У нас також є зв’язок «один до багатьох» між клієнтом і продажами, а також датами та продажами.

Зміст

• Приклад №1
• Приклад №2
• Червоні продажі №1
• Червоні продажі №2
• Червоні продажі №3
• Перевірка розгорнутих таблиць
• Визначення розгорнутих таблиць
• Розрізання стовпця категорії та використання розгорнутих таблиць
• Пояснення Red Sales 2
• Пояснення Red Sales 3
• Використання DAX Studio для перевірки розгорнутих таблиць
• Висновок

Приклад №1

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

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

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

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

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

Приклад №2

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

Наприклад, якщо я виберу 2009 рік, я хочу повідомити про червоні продажі за 2008 рік. Якщо я виберу 2008 рік, я також хочу повідомити про червоні продажі за 2007 рік.

Червоні продажі №1

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

Давайте створимо новий показник і назвемо його Red Sales. Ми напишемо CALCULATE, потім Total Sales. Ми переходимо до таблиці Продажі, щоб показати, що колір продуктів дорівнює червоному.

Потім ми напишемо дати в стовпці дат.

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

Червоні продажі №2

Що тут відбувається? Давайте спробуємо переписати цей розрахунок і перевіримо, чи зможемо ми отримати результат. Ми створимо новий показник і назвемо його Red Sales 2 і використаємо першу частину обчислення для Red Sales 1.

Ми збираємося ініціювати інший CALCULATE поверх першого CALCULATE.

Давайте внесемо цю міру в матрицю і побачимо результати. Якщо ми виберемо 2008 рік, отримаємо 51 947. Якщо ми виберемо 2009 рік, отримаємо 24 343. Нарешті, якщо ми виберемо 2010 рік, ми отримаємо 39 724.

Червоні продажі №3

Існує інший спосіб записати цей розрахунок. Ми напишемо новий показник і назвемо його Red Sales 3, а потім використаємо функцію CALCULATE.

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

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

Але з нашим першим обчисленням щось не так, оскільки ми повертаємо порожнє, а не правильне значення.

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

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

Отже, коли ці таблиці стануть розгорнутими? Після створення зв’язку «багато до одного» між однією та іншою таблицями базова таблиця стає розгорнутою.

Перевірка розгорнутих таблиць

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

Давайте перейдемо до таблиці продажів і створимо новий обчислюваний стовпець.

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

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

Коли ми вибираємо Products[Color], ми можемо створити новий стовпець у таблиці продажів за допомогою ключового слова RELATED. RELATED дає нам доступ лише до тих стовпців таблиці, до яких фактично можна розширити базову таблицю.

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

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

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

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

І оскільки ми використовуємо зв’язок «багато-до-багатьох», розгортання таблиці не відбувається, і RELATED не працює.

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

Визначення розгорнутих таблиць

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

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

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

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

Розгорнута таблиця з’являється лише тоді, коли ви посилаєтеся на таблицю (яка є базовою таблицею) і коли у вас є зв’язок «багато до одного» з іншими таблицями.

Розрізання стовпця категорії та використання розгорнутих таблиць

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

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

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

Давайте повернемося до наших розрахунків червоних продажів і спробуємо зрозуміти, що насправді відбувається. Почнемо з порожнього розрахунку. Якщо ми виберемо цей показник, ви побачите, що ми пишемо вкладений код. У нас є РОЗРАХУНОК, потім Загальний обсяг продажів, ФІЛЬТР за обсягом продажів і САМИЙ ПЕРІОДЛАСНІЙ РІК.

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

Ми вибрали 2008 рік у розділювачі номерів календарних років.

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

SAMEPERIODLASTYEAR оцінюється в контексті фільтра, де рік – 2008. Він отримає список дат у 2008 році та перемістить ці дати з 2008 року на 2007 рік. Таблиця, яку буде повернуто цим, міститиме лише дати на 2007 рік.

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

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

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

Давайте створимо нове обчислення, щоб визначити, скільки рядків є в таблиці дат для пов’язаних дат. Потім ми напишемо CALCULATE для таблиці дат. Потім ми відФІЛЬТРУЄМО ВСЮ таблицю продажів і скажемо, що ПОВ’ЯЗАНІ дати в номері календарного року мають дорівнювати 2008.

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

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

Отже, як це можливо, що з таблиці дат із 2500 рядків ми повертаємо лише 348 рядків? Якщо ми збираємося використовувати розгорнуту таблицю, то ми збираємося опосередковано відфільтрувати й іншу таблицю, яка пов’язана через відношення «багато до одного».

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

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

Пояснення Red Sales 2

Давайте перейдемо до наступного обчислення, а саме Red Sales 2. Ми почнемо із зовнішнього CALCULATE, оскільки якщо ми вкладаємо цю функцію в будь-який сценарій, зовнішній CALCULATE повинен підготувати контекст фільтра для внутрішнього CALCULATE.

У слайсері ми вибираємо 2008 календарний рік. Коли функція SAMEPERIODLASTYEAR отримує дату 2008 року, вона переносить ці дати на 2007 рік, і це буде контекст FILTER для внутрішнього обчислення. Цей внутрішній CALCULATE буде оцінювати 2007 рік.

Функція FILTER відфільтрує таблицю продажів, і таблиця продажів буде обмежена лише для рядка, де вказано 2007 рік. Отримавши рядки для 2007 року, ми перевіримо, чи пов’язані продукти дорівнюють червоному.

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

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

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

Пояснення Red Sales 3

Для третього прикладу ми маємо дуже просту функцію CALCULATE із двома висловлюваннями: одне — колір продукту дорівнює червоному, а потім — SAMEPERIODLASTYEAR на дати.

Функцію SAMEPERIODLASTYEAR буде оцінено в контексті фільтра, який міститиме лише дати 2007 року. Кольори продуктів застосовуватимуть контекст фільтра червоного кольору до таблиці продуктів, який, у свою чергу, фільтруватиме таблицю продажів і міститиме лише рядки для червоні продукти.

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

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

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

Якщо ви працюєте з DAX, завжди намагайтеся розміщувати фільтр над одним стовпцем, оскільки, коли ви використовуєте один стовпець, концепція розгорнутих таблиць не застосовується. Наприклад, якщо ви використовуєте PRODUCTS [Color] = “Red” , фільтр досягає таблиці продажів, але цей фільтр не може досягати таблиці клієнта, оскільки ми не використовуємо розширену таблицю продажів.

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

Використання DAX Studio для перевірки розгорнутих таблиць

Перш ніж закінчити, ми збираємося використати DAX Studio, щоб перевірити наш код для Red Sales 3. Давайте перейдемо на вкладку «Перегляд», клацнемо «Аналізатор продуктивності», а потім почнемо запис. Ми оновимо цей візуал і виберемо порожню картку.

Із зовнішніх інструментів ми збираємося запустити DAX studio

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

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

Ми виконуємо JOIN на таблиці продажів зліва. Ми також виконуємо JOIN для ключа продукту з таблиці продуктів.

У пункті WHERE сказано, що дати в номері календарного року мають дорівнювати 2008, а колір продукту має бути червоним.

Потім у нас є ще одна умова, яка говорить «Дати[Дата]», а потім — діапазон дат. То що це за дати? В ідеалі ці дати мають належати до 2008 року, але вони належать до 2007 року, коли ми змінюємо форматування цих чисел у Excel.

Ось чому ми повертаємо порожнє значення. Ми говоримо, що календарним роком має бути 2008, і застосовуємо умову І, що дати мають бути в 2007 році. Це 2007 рік через функцію SAMEPERIODLASTYEAR, яку ми використовували в обчисленні.

Давайте змінимо міру та посилання на Red Sales 2. Коли ми виконаємо код, ви побачите, що ми отримуємо дати з таблиці дат, а потім ми фільтруємо стовпець дат для номера календарного року 2008.

Цього разу ми можемо повернути результат, тому що ми не застосовуємо фільтр до пропозиції WHERE і ми говоримо, що дати в номері календарного року мають дорівнювати 2008.

Висновок

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

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