فیلتر کردن روزهای هفته در DAX | راهنمای پیشرفته محاسبات زمان هوشمند در Power BI
فهرست مطالب
انجام محاسبات زمانی (Time Intelligence) در DAX نسبتاً ساده است. اما به محض اینکه نیازمندی‌ها کمی پیچیده‌تر شوند، فرمول‌ها بسیار پیچیده می‌شوند و برای نوشتن فرمول درست و بهینه، نیاز به درک عمیق چندین جزئیات از DAX خواهید داشت.
در این مقاله، یک نیاز ساده را بررسی می‌کنیم: حفظ فیلتر روی روزهای هفته (Weekdays) هنگام انجام محاسبات زمان هوشمند. همانطور که در ادامه خواهید دید، با وجود ساده بودن این نیاز، مراحل اجرای آن پیچیده خواهد بود. اما ابتدا بیایید روشن کنیم که هدف ما چیست و منظور از ستون فیلترنگهدار (Filter-Preserving Column) چیست.
ستون روز هفته به عنوان یک ستون فیلترنگهدار
ستون Day of Week باید به عنوان یک ستون فیلترنگهدار در نظر گرفته شود؛ یعنی ستونی که فیلتر انتخاب‌شده روی آن را حفظ می‌کند، حتی اگر فیلترهای دیگری روی ستون‌های دیگر اعمال شوند.
مثلاً اگر روی ستون Day of Week فیلتر اعمال شده باشد، این فیلتر باید حتی با وجود فیلتر روی ستون Month حفظ شود.
در جدول تاریخ (Date Table) برخی ستون‌ها مانند Month، فیلترنگهدار نیستند چون با فیلترهای دیگری مانند Year ترکیب می‌شوند و تأثیرشان روی محاسبات زمان هوشمند به صورت ترکیبی (مثلاً Year و Month با هم) اعمال می‌شود.
اما فیلتر روی Day of Week نباید در محاسبات زمان هوشمند تاثیرگذار باشد و باید حفظ شود.
مثال کاربردی
فرض کنید می‌خواهیم محاسبه «ماه قبلی» را روی انتخاب “دوشنبه تا جمعه، ژانویه ۲۰۲۰” انجام دهیم. هدف این است که نتیجه، برابر با “دوشنبه تا جمعه، دسامبر ۲۰۱۹” باشد.
همانطور که می‌بینید، فیلترهای Month و Year ممکن است تغییر کنند (از طریق حذف و جایگزینی با فیلتر تاریخ از ۱ دسامبر ۲۰۱۹ تا ۳۱ دسامبر ۲۰۱۹)، اما فیلتر روی Day of Week دست‌نخورده باقی می‌ماند.
در نهایت، فیلتر واقعی اعمال‌شده شامل:
روزهای هفته از دوشنبه تا جمعه (این ستون فیلترنگهدار است)
بازه زمانی ۱ تا ۳۱ دسامبر ۲۰۱۹ (فیلتر تاریخ جایگزین فیلتر ماه و سال شده است)
نکات مهم فرمول و تکنیک‌های استفاده شده
فرمول نهایی این مقاله کوتاه و ساده است، اما کد آن شامل جزئیات ریز و مهمی است که هرکدام نقش مهمی دارند.
یکی از نکات کلیدی این مقاله، نحوه استفاده از تابع KEEPFILTERS برای جلوگیری از اجرای خودکار تابع REMOVEFILTERS توسط DAX هنگام استفاده از توابع زمان هوشمند است.
Sales PM =
CALCULATE (
[Sales Amount],
ALLEXCEPT ( 'Date', 'Date'[Day of Week Number], 'Date'[Day of Week] ),
KEEPFILTERS (
CALCULATETABLE (
DATEADD ( 'Date'[Date], -1, MONTH ),
REMOVEFILTERS ( 'Date'[Day of Week Number], 'Date'[Day of Week] )
)
)
)
فرمول نهایی این مقاله تنها حدود ۱۰ خط کد است که می‌توانید به راحتی آن را در مدل‌های خود کپی و پیست کنید. اما توصیه ما این است که سریع اقدام نکنید و به جای آن، با حوصله مقاله را مطالعه کنید و هر مرحله را دنبال کنید تا جزئیات آن را به خوبی درک کنید.
ممکن است این روند در برخی قسمت‌ها کمی خسته‌کننده به نظر برسد، اما جزئیات اهمیت زیادی دارند و ما نمی‌خواهیم هیچ نکته مهمی از دست برود.
نکته کلیدی فرمول: KEEPFILTERS
بخش جذاب و در عین حال خطرناک فرمول، استفاده از تابع KEEPFILTERS در اطراف آرگومان دوم تابع CALCULATE است.
این تابع هم وسوسه‌انگیز است و هم می‌تواند در صورت عدم درک صحیح، باعث مشکلات شود.
بنابراین لازم است به خوبی معنای آن را بفهمیم و با دقت، نحوه کارکرد آن را همراه با بقیه فرمول بررسی کنیم.
شروع محاسبه مقایسه ماه جاری با ماه قبل
حالا وقت آن است که وارد محتوای اصلی شویم!
اگر بخواهیم ماه جاری را با ماه قبل مقایسه کنیم، ساده‌ترین راه استفاده از فرمولی مانند نمونه زیر است:
Sales PM =
CALCULATE (
[Sales Amount],
DATEADD ( 'Date'[Date], -1, MONTH )
)
این فرمول به خوبی کار می‌کند و زمانی که در یک ماتریس استفاده شود، نتیجه‌ی صحیح را تولید می‌کند.
ظاهر ساده این فرمول به این دلیل است که چندین ویژگی کوچک به صورت نامحسوس با هم کار می‌کنند تا این کد به درستی اجرا شود.
نحوه عملکرد توابع و فیلترها
تابع DATEADD جدولی شامل ستون Date[Date] بازمی‌گرداند که شامل تمام تاریخ‌های ماه قبلی است.
فیلتر روی Date[Date] باعث حذف یا نادیده گرفتن فیلترهای Date[Year] یا Date[Month] که در ماتریس وجود دارند، نمی‌شود.
اما وقتی رابطه بین جداول Sales و Date بر اساس ستونی از نوع Date برقرار باشد، DAX به طور خودکار تابع REMOVEFILTERS را هر بار که فیلتر جدیدی روی Date[Date] اعمال می‌شود، اضافه می‌کند.
در نتیجه، تابع DATESYTD به صورت غیرمستقیم REMOVEFILTERS را به ستون تاریخ اعمال می‌کند؛ چون رابطه بین جداول Sales و Date مبتنی بر ستونی مانند Sales[Order Date] است که از نوع DateTime است.
نکته مهم درباره نوع ستون رابطه
اگر رابطه بین جداول بر اساس نوع داده عدد صحیح (integer) یا هر نوع داده دیگری باشد، لازم است که جدول Date به عنوان جدول تاریخ (Date Table) مشخص شود تا رفتار مشابه برقرار شود.
مزایا و محدودیت‌های این رفتار خودکار
این رفتار خودکار در اکثر سناریوها بسیار مفید و کارآمد است، اما زمانی که جدول تاریخ شامل ستون‌هایی باشد که می‌خواهیم فیلتر آن‌ها حفظ شود در هنگام اعمال تغییرات زمان هوشمند، می‌تواند مشکل‌ساز شود.
مثال کاربردی: فیلتر روزهای کاری در Slicer
برای نمونه، تصور کنید یک Slicer اضافه کنیم که فقط روزهای کاری (Working Days) را بر اساس ستون Date[Day of Week] فیلتر می‌کند. در این حالت، ممکن است فیلتر روی روزهای هفته با محاسبات زمان هوشمند تداخل پیدا کند.
به سرعت متوجه می‌شوید که مقدار گزارش‌شده در Sales PM دیگر برابر با مقدار Sales Amount در ماه قبلی نیست. برای درک بهتر اتفاقات، اجازه دهید فقط یک روز از هفته را فیلتر کنیم و بازه تاریخ را گسترش دهیم.
تاریخ‌های انتخاب‌شده برای ماه اوت ۲۰۲۱، تمام روزهای دوشنبه آن ماه هستند. این انتخاب تاریخ‌ها توسط تابع DATEADD به عقب منتقل می‌شود و در نتیجه به همان روزهای دوشنبه در ماه جولای ۲۰۲۱ می‌رسیم.
به عنوان مثال، برای تاریخ ۲۳ اوت، مقدار محاسبه‌شده در Sales PM برابر با ۲,۸۷۶.۵۷ است که همان مقدار فروش در تاریخ ۲۳ جولای می‌باشد.
اما آیا این نتیجه همان چیزی است که کاربران انتظار دارند؟
مقدار محاسبه‌شده از نظر فنی درست است، اما به سختی می‌توان گفت که این همان چیزی است که کاربر می‌خواهد.
کاربران معمولاً قصد دارند مجموعه‌ای از روزهای هفته در یک ماه را با همان روزهای هفته در ماه قبل مقایسه کنند. به عبارت دیگر، می‌خواهند دوشنبه‌های ماه اوت را با دوشنبه‌های ماه جولای مقایسه کنند.
مشکل اصلی چیست؟
آنچه در حال رخ دادن است این است که فیلتر روی ستون Date[Day of Week] ابتدا اعمال می‌شود و انتخاب تاریخ‌ها مشخص می‌شود، سپس این تاریخ‌ها به ماه قبل منتقل می‌شوند.
اما آنچه ما می‌خواهیم این است که:
ابتدا فیلتر ماه را به ماه قبل منتقل کنیم، سپس فیلتر روزهای هفته را اعمال کنیم.
با دنبال کردن این ترتیب، رفتار مورد انتظار کاربران به دست می‌آید.
گام بعدی: نوشتن فرمول آزمایشی اولیه
با این ایده در ذهن، اولین تلاش برای نوشتن فرمول را آغاز می‌کنیم که البته در ابتدا اشتباه است.
در ادامه، کد و مدل را به صورت مرحله‌ای اصلاح می‌کنیم تا چالش‌ها و راهکارهای هر مرحله را به خوبی نشان دهیم.
Sales PM =
CALCULATE (
[Sales Amount],
--
-- Compute the previous month after removing the filter over the day
-- of the week to retrieve the full previous month
--
CALCULATETABLE (
DATEADD ( 'Date'[Date], -1, MONTH ),
REMOVEFILTERS ( 'Date'[Day of Week Number], 'Date'[Day of Week] )
),
--
-- Apply the filter over the day of the week again, because
-- the DATEADD filter removes all the filters
--
VALUES ( 'Date'[Day of Week] )
)
در نگاه اول، این Measure به درستی کار می‌کند چون در سطح ماه، نتایج همانطور که انتظار می‌رود نمایش داده می‌شوند.
با این حال، اگر ماتریس را تا سطح روز گسترش دهیم، یک مشکل جدی مشاهده می‌شود: تمامی مقادیر خالی هستند، در حالی که مقدار کل (Total) عدد درستی را نشان می‌دهد.
در سطح روز، تابع VALUES(‘Date'[Day of Week]) روز هفته مربوط به هر تاریخ را برمی‌گرداند.
برای مثال، در تاریخ ۵ اوت ۲۰۲۱، مقدار بازگشتی Wednesday (چهارشنبه) است.
اما تابع DATEADD جدولی شامل تاریخ ۵ جولای را برمی‌گرداند که این تاریخ در واقع Monday (دوشنبه) است.
بنابراین، فیلتر روی روز هفته، تاریخ ۵ جولای را به عنوان مقدار معتبر قبول نمی‌کند و در نتیجه فرمول در این روز مقدار خالی (Blank) برمی‌گرداند.
می‌توانید این موضوع را با بررسی داده‌های مارس ۲۰۲۲ بررسی کنید؛ زیرا ماه قبلی یعنی فوریه، شامل تعداد کاملی از هفته‌ها است و روز هفته تاریخ‌های مارس، با روز هفته تاریخ‌های متناظر فوریه یکسان است، بنابراین فرمول در این حالت مقادیر درست را باز می‌گرداند.
مشکل استفاده از تابع VALUES چیست؟
تابع VALUES مقادیر قابل مشاهده یک ستون را با توجه به Cross-filtering برمی‌گرداند.
در اینجا، فیلتر روی ستون Date[Date] باعث Cross-filtering روی ستون Date[Day of Week] می‌شود؛
به همین دلیل است که تابع VALUES('Date'[Day of Week]) تنها روز هفته تاریخ جاری را برمی‌گرداند.
راهکار جایگزین: استفاده از تابع FILTERS
DAX تابع دیگری به نام FILTERS ارائه می‌دهد که عملکردی مشابه VALUES دارد، اما تفاوت مهم آن این است که FILTERS، Cross-filtering را نادیده می‌گیرد.
از این رو، جایگزینی VALUES با FILTERS باعث می‌شود کد کار کند، گرچه فرمول زیر هنوز نسخه نهایی نیست:
Sales PM =
CALCULATE (
[Sales Amount],
--
-- Compute the previous month after removing the filter over the day
-- of the week to retrieve the full previous month
--
CALCULATETABLE (
DATEADD ( 'Date'[Date], -1, MONTH ),
REMOVEFILTERS ( 'Date'[Day of Week Number], 'Date'[Day of Week] )
),
--
-- Apply the filter over the day of the week again, because
-- the DATEADD filter removes all the filters. We use FILTERS
-- to ignore cross-filtering on 'Date'[Day of Week]
--
FILTERS ( 'Date'[Day of Week] )
)
این نسخه‌ی اولیه کارا به اجرای خودکار REMOVEFILTERS توسط DAX تکیه دارد (توجه داشته باشید که فیلترهای روی Date[Year] و Date[Month] حذف نمی‌شوند) و فیلتر روی روز هفته را بازیابی می‌کند.
محدودیت‌های این کد
این کد بهترین گزینه نیست چون جدولی که توسط تابع FILTERS بازگردانده می‌شود، همیشه باید به عنوان یک فیلتر اضافی اعمال شود، حتی زمانی که هیچ فیلتری فعال نیست.
وقتی روی روز هفته هیچ فیلتری اعمال نشده باشد، FILTERS باز هم هفت مقدار (تمام روزهای هفته) را بازمی‌گرداند.
این جدول باید به عنوان یک فیلتر اعمال شود و اگرچه این کار ساده است، اما زمان‌بر است. بنابراین این کد بهینه نیست.
جایگزین بهتر: استفاده از ALLEXCEPT
تابع ALLEXCEPT در DAX به منظور حذف همه فیلترها از یک جدول به جز فیلترهایی که روی مجموعه‌ای از ستون‌ها اعمال شده‌اند، ارائه شده است.
پس می‌توانیم به جای FILTERS از ALLEXCEPT استفاده کنیم تا فیلتر روی روز هفته را بازیابی کنیم.
اگر هیچ فیلتری فعال نباشد، ALLEXCEPT کاری انجام نمی‌دهد، اما اگر فیلتری فعال باشد، همه به جز فیلترهای روی روز هفته حذف می‌شوند.
نسخه بعدی کد که از ALLEXCEPT استفاده می‌کند، با چند مشکل مواجه است و به درستی کار نمی‌کند.
Sales PM =
CALCULATE (
[Sales Amount],
--
-- Compute the previous month after removing the filter over the day
-- of the week to retrieve the full previous month
--
CALCULATETABLE (
DATEADD ( 'Date'[Date], -1, MONTH ),
REMOVEFILTERS ( 'Date'[Day of Week Number], 'Date'[Day of Week] )
),
--
-- Removes all the filters from Date, except the ones on the
-- day of the week, if present
--
ALLEXCEPT ( 'Date', 'Date'[Day of Week Number], 'Date'[Day of Week] )
)
زمانی که این فرمول در یک ماتریس استفاده می‌شود، همیشه کل ماه قبلی را برمی‌گرداند، انگار که هیچ فیلتری روی روزهای هفته حفظ نشده باشد.
اگرچه این فرمول قدمی در جهت درست است، اما هنوز به درستی کار نمی‌کند، زیرا DAX به طور خودکار تابع REMOVEFILTERS را اجرا می‌کند.
تابع ALLEXCEPT تمام فیلترها را از جدول Date حذف می‌کند (شامل ستون Date[Date]) و فقط فیلتر روی روز هفته را حفظ می‌کند.
اما وقتی نتیجه‌ی DATEADD به کانتکست فیلتر اضافه می‌شود، DAX به صورت خودکار REMOVEFILTERS را روی جدول Date اجرا می‌کند و اثر ALLEXCEPT را از بین می‌برد.
راهکار نهایی: استفاده از KEEPFILTERS
نکته مهم این است که ALLEXCEPT یک Modifier برای تابع CALCULATE است.
این تابع قبل از اعمال فیلترهای صریحی که توسط CALCULATETABLE/DATEADD به کانتکست فیلتر اضافه می‌شوند، اجرا می‌شود.
بنابراین، استفاده از KEEPFILTERS اطراف CALCULATETABLE کافی است تا اطمینان حاصل شود که فیلتر روی Date[Date]، هیچ فیلتر قبلی‌ای را حذف نکند.
آخرین فیلتر روی جدول Date توسط ALLEXCEPT اعمال شده که همه فیلترها را حذف کرده به جز فیلتر روی روز هفته.
فرمول نهایی
در فرمول نهایی، هرچند تغییر ترتیب دو فیلتر در تابع CALCULATE از نظر اجرایی ضروری نیست، اما به دلیل راحتی خواندن، ابتدا ALLEXCEPT و سپس KEEPFILTERS/CALCULATETABLE/DATEADD آورده شده است.
نکته مهم: ترتیب پارامترها در CALCULATE تاثیری بر ترتیب اجرای فیلترها ندارد؛
ترتیب اجرای فیلترها توسط قوانین CALCULATE تعریف می‌شود، نه ترتیب پارامترهای ورودی.
Sales PM =
CALCULATE (
[Sales Amount],
--
-- Removes all the filters from Date, except the ones on the
-- day of the week, if present
--
ALLEXCEPT ( 'Date', 'Date'[Day of Week Number], 'Date'[Day of Week] ),
--
-- Compute the previous month after removing the filter over the day
-- of the week, to retrieve the full previous month.
-- KEEPFILTERS prevents the automatic REMOVEFILTERS added by DAX
-- and it makes sure that the previous ALLEXCEPT maintains the
-- filter on the day of the week
--
KEEPFILTERS (
CALCULATETABLE (
DATEADD ( 'Date'[Date], -1, MONTH ),
REMOVEFILTERS ( 'Date'[Day of Week Number], 'Date'[Day of Week] )
)
)
)
این فرمول نهایی به درستی اجرا می‌شود. همانطور که با مطالعه مقاله آموختید، هر خط از این فرمول معنای عمیقی دارد و تغییر هر جزئی کوچک ممکن است باعث شکست کد شود.
در ابتدای مقاله نسخه آخر فرمول را نشان دادیم، اما جزئیات آن بسیار حیاتی هستند. زمانی که این جزئیات را به خوبی درک کنید، می‌توانید کد را متناسب با مدل خود تغییر دهید یا سازگار کنید، با اطمینان از اینکه هر تغییر کوچک چه تاثیری خواهد داشت.
نتیجه‌گیری
همانطور که مشاهده کردید، نیاز ساده‌ای مانند حفظ برخی فیلترها روی جدول تاریخ در محاسبات زمان هوشمند در DAX، می‌تواند به یک چالش واقعی تبدیل شود.
DAX چندین رفتار خودکار ارائه می‌دهد که هدف آن‌ها ساده‌سازی نوشتن فرمول‌ها است، اما زمانی که این مکانیزم‌ها مورد نظر ما نیستند، لازم است آستین‌ها را بالا زده و به تمام جزئیات مهم و ظریف DAX توجه کنیم و کد مناسب را بنویسیم.
نکته پایانی
نوشتن چنین کدی ساده نیست و ممکن است برخی بگویند که نوشتن این همه کد برای انجام یک محاسبه ساده زمان هوشمند، بیش از حد پیچیده است.
عالی خواهد بود اگر بتوان یک تابع تعریف کرد که تمام این مراحل را انجام دهد و شما بتوانید آن را در هر Measure مربوط به محاسبات زمان هوشمند فراخوانی کنید.
این موضوع در یک مقاله آینده بررسی خواهد شد؛ پس منتظر آن باشید!
دیدگاهتان را بنویسید