فیلتر کردن روزهای هفته در DAX
هنگام استفاده از توابع هوش زمانی ، REMOVEFILTERS خودکار روی جدول تاریخ می‌تواند حفظ فیلترها روی جدول تاریخ را چالش‌برانگیز کند. این مقاله تکنیکی را برای مدیریت ستون‌های حفظ فیلتر در DAX نشان می‌دهد.
محاسبه محاسبات هوش زمانی در DAX نسبتاً ساده است. با این حال، به محض اینکه نیازها پیچیده‌تر می‌شوند، پیچیدگی فرمول‌ها به طور چشمگیری افزایش می‌یابد و برای به دست آوردن یک فرمول خوب، لازم است که درک خوبی از جزئیات مختلف DAX داشته باشید. در این مقاله، یک نیاز ساده را نشان می‌دهیم: نیاز به حفظ فیلتر روی روزهای هفته هنگام انجام محاسبات هوش زمانی. همانطور که خواهید خواند، این کار نیاز به چندین مرحله پیچیده دارد، اگرچه این یک نیاز ساده است؛ اما بیایید ابتدا آنچه را که می‌خواهیم به دست آوریم و ستون حفظ فیلتر را روشن کنیم.
ستون “روز هفته” باید به عنوان یک ستون حفظ فیلتر در نظر گرفته شود: ستونی که فیلتر انتخاب شده (در آن ستون) را صرف نظر از فیلترهای اعمال‌شده به دیگر ستون‌ها، حفظ می‌کند. برای مثال، فیلتر روی “روز هفته” باید صرف نظر از فیلتر روی “ماه” حفظ شود. در یک جدول تاریخ، ستون‌هایی (مانند ماه) وجود دارند که ستون‌های حفظ فیلتر نیستند زیرا با فیلترهای دیگر (مانند سال) ترکیب می‌شوند و تاثیری در محاسبات هوش زمانی دارند که ترکیب فیلترها (مانند سال و ماه) را در نظر می‌گیرند. فیلتر روز هفته، فیلتر است که نباید بر محاسبات هوش زمانی تأثیر بگذارد.
برای مثال، محاسبه “ماه گذشته” را در نظر بگیرید که روی انتخاب “دوشنبه تا جمعه، ژانویه 2020” انجام می‌شود: هدف این است که نتیجه‌ای بدست آید که “دوشنبه تا جمعه، دسامبر 2019” باشد. همانطور که می‌بینید، ماه و سال ممکن است به دلیل فیلتر تغییر کنند (به طور فنی، این فیلترها حذف می‌شوند و با فیلتر جدیدی که روزهای از اول دسامبر 2019 تا 31 دسامبر 2019 را شامل می‌شود، جایگزین می‌شود) در حالی که فیلتر روز هفته تحت تأثیر محاسبات هوش زمانی قرار نمی‌گیرد. در نهایت، فیلتر واقعی به این صورت است: “دوشنبه تا جمعه (این ستون روز هفته حفظ فیلتر است)، از 1 دسامبر 2019 تا 31 دسامبر 2019 (فیلتر روی تاریخ جایگزین فیلتر روی ماه و سال پس از محاسبات هوش زمانی می‌شود).”
فرمول نهایی در این مقاله کوتاه و ساده است. با این حال، کد شامل جزئیات بسیاری است که هر کدام بسیار مهم هستند. علاوه بر این، نکته‌ای که باید از این مقاله آموخت این است که چگونه می‌توان از KEEPFILTERS برای جلوگیری از REMOVEFILTERS خودکار استفاده کرد که توسط DAX هنگام استفاده از توابع هوش زمانی انجام می‌شود.
این فقط ۱۰ خط کد است. می‌توانید آن را کپی کرده و در مدل‌های خود قرار دهید، اما ما توصیه می‌کنیم که این کار را نکنید. وقت بگذارید و مقاله را مرحله‌به‌مرحله بخوانید تا همه جزئیات را درک کنید. ممکن است این روند خسته‌کننده به نظر برسد، و واقعاً هم در بعضی بخش‌ها خسته‌کننده باشد. با این حال، جزئیات بسیار مهم هستند و ما نمی‌خواهیم هیچ‌کدام از آن‌ها را از دست بدهید.
جالب‌ترین خط این فرمول، استفاده از KEEPFILTERS در اطراف آرگومان دوم تابع CALCULATE است. این خط هم جذاب است و هم خطرناک؛ وسوسه‌انگیز ولی ترسناک. ما باید آن را رام کنیم و عمیقاً بفهمیم که معنای آن چیست، همراه با باقی فرمول.
بس است از هشدارها؛ بیایید برویم سراغ محتوای اصلی!
اگر بخواهید ماه جاری را با ماه قبل مقایسه کنید، ساده‌ترین راه رسیدن به این هدف استفاده از فرمولی مانند نمونه زیر است:
این فرمول به‌خوبی کار می‌کند؛ زمانی که آن را در یک ماتریس استفاده کنید، نتیجه درستی تولید می‌کند.
آیا به نظر ساده می‌رسد؟ بله، واقعاً هم ساده است. اما این سادگی فقط به این دلیل است که چندین ویژگی کوچک در سکوت با هم کار می‌کنند تا این کد درست عمل کند.
تابع DATEADD یک جدول شامل ستون Date[Date] را برمی‌گرداند. این جدول شامل همه تاریخ‌های ماه قبل است. فیلتر روی Date[Date]، فیلترهای موجود در ماتریس مانند Date[Year] یا Date[Month] را بازنویسی نمی‌کند. با این حال، زمانی که رابطه بین جدول Sales و Date بر اساس ستونی از نوع تاریخ (Date) برقرار باشد، DAX به‌صورت خودکار هنگام اعمال فیلتر جدید روی Date[Date]، از REMOVEFILTERS استفاده می‌کند. بنابراین، DATESYTD به‌طور غیرمستقیم REMOVEFILTERS را روی جدول Date اعمال می‌کند، چون رابطه بین Sales و Date بر اساس ستونی از نوع DateTime (مثل Sales[Order Date]) است. اگر این رابطه بر اساس یک عدد صحیح (integer) یا نوع داده دیگری باشد، باید جدول Date را به‌عنوان جدول تاریخ (Date Table) علامت‌گذاری کنید تا همین رفتار خودکار ایجاد شود.
این رفتار خودکار در اغلب سناریوها بسیار مفید است. اما زمانی مشکل‌ساز می‌شود که جدول تاریخ شامل ستون‌هایی باشد که می‌خواهیم هنگام اعمال یک تبدیل هوش زمانی، فیلتر روی آن‌ها حفظ شود.
برای مثال، ببینید چه اتفاقی می‌افتد اگر یک slicer اضافه کنیم که ستون Date[Day of Week] را فقط روی روزهای کاری (دوشنبه تا جمعه) فیلتر کند.
خیلی سریع متوجه می‌شوید که مقدار گزارش‌شده در Sales PM دیگر برابر با مقدار Sales Amount در ماه قبل نیست.
برای اینکه بهتر درک کنیم چه اتفاقی در حال رخ دادن است، بیایید فقط یک روز از هفته را فیلتر کنیم و سپس ستون تاریخ را گسترش دهیم تا دقیق‌تر ببینیم چه داده‌هایی در حال نمایش هستند.
تاریخ‌های انتخاب‌شده برای آگوست ۲۰۲۱، تمام دوشنبه‌های آن ماه هستند. این انتخاب توسط تابع DATEADD به عقب منتقل می‌شود و در نتیجه، همان روزها در ماه جولای ۲۰۲۱ به‌دست می‌آید. به عبارت دیگر، برای تاریخ ۲۳ آگوست، مقدار Sales PM برابر با ۲,۸۷۶.۵۷ است، که در واقع همان مقدار فروش ۲۳ جولای است.
مقداری که توسط Sales PM محاسبه می‌شود از نظر محاسباتی درست است، اما احتمالاً چیزی نیست که کاربر انتظار دارد. آنچه که کاربر می‌خواهد، احتمالاً مقایسه مجموعه‌ای از روزهای خاص هفته در یک ماه با همان روزهای هفته در ماه قبل است. به عبارت دیگر، آن‌ها می‌خواهند دوشنبه‌های آگوست را با دوشنبه‌های جولای مقایسه کنند.
آنچه در حال حاضر اتفاق می‌افتد این است که:
فیلتر روی Date[Day of Week] باعث می‌شود مجموعه‌ای از تاریخ‌ها انتخاب شود، و سپس این تاریخ‌ها با DATEADD به ماه قبل منتقل می‌شوند.
اما چیزی که می‌خواهیم این است که ابتدا انتخاب ماه را به عقب ببریم (ماه قبل را فیلتر کنیم)، و سپس فیلتر روزهای هفته (مثلاً فقط دوشنبه‌ها) را اعمال کنیم.
اگر مراحل را به این ترتیب دنبال کنیم، رفتار نهایی همان چیزی خواهد بود که کاربران انتظار دارند.
با این ایده، حالا می‌توانیم اولین نسخه از فرمول را بنویسیم — که البته اشتباه خواهد بود. سپس، در چند مرحله کد و مدل را اصلاح می‌کنیم تا چالش‌ها را یکی‌یکی بررسی کنیم:
در نگاه اول، این معیار (Measure) به‌خوبی کار می‌کند، زیرا در سطح ماه، نتایج همان چیزی هستند که انتظار می‌رود.
اما اگر ماتریس را در سطح روز گسترش دهید، یک مشکل جدی ظاهر می‌شود: تمام مقادیر خالی (Blank) هستند، با اینکه جمع کل (Total) عدد درستی را نشان می‌دهد.
اینجا دقیقاً مشکل مشخص می‌شود: در سطح روز، تابع VALUES('Date'[Day of Week]) روز خاص هفته را برای آن تاریخ برمی‌گرداند.
برای مثال، در تاریخ ۵ آگوست ۲۰۲۱، مقدار برگشتی چهارشنبه (Wednesday) است.
تابع DATEADD جدولی تولید می‌کند که شامل تاریخ ۵ ژوئن است — که به‌طور خاص دوشنبه (Monday) است.
در نتیجه، فیلتر روی Day of Week که به دنبال چهارشنبه است، تاریخ ۵ ژوئن را حذف می‌کند چون روز آن با فیلتر هماهنگ نیست.
در نهایت، این باعث می‌شود که فرمول در همه ردیف‌ها مقدار خالی (Blank) برگرداند.
می‌توانید این موضوع را با بررسی داده‌های مارس ۲۰۲۲ دوباره بررسی کنید:
چون ماه قبلی، فوریه است و فوریه شامل تعداد کامل هفته‌هاست، بنابراین روزهای هفته در مارس دقیقاً با همان روزهای هفته در فوریه تطابق دارند.
در این شرایط، فرمول به‌درستی کار می‌کند و مقادیر درستی بازمی‌گرداند.
مشکل استفاده از VALUES این است که این تابع مقادیر قابل مشاهده‌ی یک ستون را با در نظر گرفتن cross-filtering بازمی‌گرداند.
در اینجا، فیلتر روی Date[Date] باعث cross-filter شدن ستون Date[Day of Week] می‌شود؛ به همین دلیل، VALUES('Date'[Day of Week]) فقط روز هفته‌ی متناظر با تاریخ جاری را برمی‌گرداند.
اما DAX تابع دیگری به نام FILTERS ارائه می‌دهد که رفتاری شبیه به VALUES دارد با یک تفاوت مهم:FILTERS cross-filtering را نادیده می‌گیرد.
بنابراین، با جایگزین کردن VALUES با FILTERS، کد به درستی کار می‌کند —
هرچند فرمول زیر هنوز نسخه نهایی نیست، اما با این جایگزینی، عملکرد بهتری خواهد داشت:
این اولین نسخه‌ی کاربردی و قابل اجرا از فرمول است. این نسخه به رفتار خودکار REMOVEFILTERS که توسط DAX اجرا می‌شود متکی است
(توجه داشته باشید که ما فیلترهای مربوط به Date[Year] و Date[Month] را حذف نمی‌کنیم)
و سپس فیلتر روی روزهای هفته را بازمی‌گرداند.
اما این کد بهترین گزینه نیست، زیرا:
جدولی که توسط
FILTERSبازگردانده می‌شود، همیشه باید به‌عنوان فیلتر اضافه اعمال شود — حتی اگر هیچ فیلتری فعال نباشد.زمانی که روی روزهای هفته فیلتری اعمال نشده باشد،
FILTERSهمچنان ۷ مقدار (برای هر روز هفته) برمی‌گرداند.اعمال این جدول به‌عنوان فیلتر، هرچند ساده است، اما نیاز به زمان محاسبه دارد.
راه‌حل بهتر:
DAX تابعی به نام ALLEXCEPT دارد که تمام فیلترهای یک جدول را حذف می‌کند، به جز فیلترهایی که روی ستون‌های مشخصی فعال هستند.
بنابراین، می‌توانیم به جای FILTERS از ALLEXCEPT استفاده کنیم تا فیلتر روزهای هفته را حفظ کنیم:
اگر فیلتری روی
Day of Weekنباشد،ALLEXCEPTهیچ کاری نمی‌کند.اما اگر فیلترهایی فعال باشند، همه فیلترها را حذف می‌کند به‌جز فیلتر روی روزهای هفته.
با این حال…
نسخه‌ای از کد که از ALLEXCEPT استفاده می‌کند، چند ایراد دارد و به درستی کار نمی‌کند.
زمانی که از این کد در یک ماتریس استفاده می‌شود، همیشه تمام ماه قبلی را برمی‌گرداند، انگار که هیچ فیلتر خاصی روی روزهای هفته اعمال نشده است.
این مشکل به این دلیل است که تابع ALLEXCEPT به درستی فیلتر روزهای هفته را نگه نمی‌دارد و به جای آن، تمام داده‌های ماه قبلی را نشان می‌دهد.
با وجود اینکه این گام به سمت درستی پیش رفته است، فرمول هنوز به درستی کار نمی‌کند، زیرا DAX به‌طور خودکار REMOVEFILTERS را اجرا می‌کند.
تابع ALLEXCEPT تمام فیلترها را از جدول تاریخ حذف می‌کند (از جمله ستون Date[Date]) و فقط فیلتر روی روزهای هفته را حفظ می‌کند.
با این حال، زمانی که نتیجه‌ی DATEADD به فیلتر کانتکست اضافه می‌شود، DAX به‌طور خودکار REMOVEFILTERS را روی جدول تاریخ اجرا کرده و اثر ALLEXCEPT از بین می‌رود.
راه‌حل:
نکته‌ی آخر این است که ALLEXCEPT یک تغییر‌دهنده (modifier) برای تابع CALCULATE است.
این تغییر‌دهنده قبل از اعمال فیلتر صریح ایجاد شده توسط CALCULATETABLE/DATEADD روی فیلتر کانتکست اجرا می‌شود.
بنابراین، استفاده از KEEPMILTERS در اطراف CALCULATETABLE برای اطمینان از اینکه فیلتر روی Date[Date] هیچ فیلتر قبلی را حذف نکند کافی است.
آخرین فیلتر روی Date توسط ALLEXCEPT اعمال شده که هر فیلتر دیگری (به‌جز فیلتر روزهای هفته) را حذف کرده است.
فرمول نهایی:
در این فرمول، ترتیب فیلترها در CALCULATE تغییر داده شده است، هرچند این تغییر ضروری نیست، چون به‌عنوان انسان‌ها، خواندن ALLEXCEPT قبل از KEEPMILTERS/CALCULATETABLE/DATEADD برای ما راحت‌تر است.
باید توجه داشت که ترتیب فیلترها مهم نیست، زیرا ترتیب اجرای فیلترها توسط قوانین CALCULATE تعریف می‌شود، نه ترتیب ارایه پارامترها.
این فرمول نهایی به درستی کار می‌کند. همانطور که از خواندن مقاله متوجه شدید، هر خط از فرمول معنای عمیقی دارد و تغییر هر جزئی ممکن است کد را خراب کند. ما این نسخه آخر فرمول را در ابتدای مقاله نشان دادیم، اما جزئیات آن بسیار مهم هستند. وقتی این جزئیات را به‌خوبی درک کنید، می‌توانید کد را برای مدل خود انطباق داده یا تغییر دهید و اثر هر تغییر کوچک را بدانید.
نتیجه‌گیری‌ها
همانطور که دیدید، یک نیاز ساده مانند حفظ فیلترها روی جدول تاریخ در محاسبات هوش زمانی در DAX به چالش واقعی تبدیل می‌شود. DAX چندین رفتار خودکار دارد که هدف آن‌ها ساده‌سازی نوشتن فرمول‌هاست. اما زمانی که این مکانیزم‌ها خواسته نمی‌شوند، لازم است که آستین‌ها را بالا بزنید، تمام جزئیات مهم و ظریف DAX را به یاد بیاورید و سپس کد را بنویسید.
این کد ساده نیست و ممکن است این سوال پیش بیاید که نوشتن چنین کدی هر بار برای یک محاسبه هوش زمانی ساده یک کار اضافی به نظر برسد.
خیلی خوب خواهد بود که یک تابع تعریف شود که تمام این مراحل را انجام دهد تا بتوانید آن را در هر اندازه‌گیری که نیاز به محاسبات هوش زمانی دارد، فراخوانی کنید.
دیدگاهتان را بنویسید