بهترین شیوه‌های استفاده از تابع ALLSELECTED در Power BI (DAX)
تابع ALLSELECTED یکی از پیچیده‌ترین توابع زبان DAX در Power BI است. این تابع تنها تابعی در DAX است که از مفهوم “سایه فیلتر کانتکست” (shadow filter context) بهره می‌برد و همچنین رفتار آن بسته به استفاده داخل توابعی مانند SUMMARIZECOLUMNS یا درون یک حلقه/ایتراتور (مانند SUMX یا ADDCOLUMNS) کمی متفاوت خواهد بود. اگر این دو روش با هم ترکیب شوند، می‌تواند منجر به نتایج غیرمنتظره و حتی گزارش‌های مشکل‌دار شود!
در این مقاله، ابتدا دو شیوه استفاده تابع ALLSELECTED (در SUMMARIZECOLUMNS و حلقه‌ها/ایتراتورها) را به طور خلاصه توضیح می‌دهیم. سپس بهترین شیوه‌های عملی را معرفی کرده و با یک مثال، نشان می‌دهیم که چرا باید مراقب این موضوع بود.
معرفی ALLSELECTED
تابع ALLSELECTED را می‌توان به صورت شهودی استفاده کرد. فرض کنید گزارشی داریم که کاربر با استفاده از یک اسلایسر چند برند را انتخاب می‌کند. گزارش مقدار فروش هر برند و همچنین درصد فروش برند نسبت به کل فروش برندهای انتخاب‌شده را نمایش می‌دهد.
فرمول درصد برند به شرح زیر است
فهرست مطالب
Pct =
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALLSELECTED ( 'Product'[Brand] )
)
)
به طور شهودی، ALLSELECTED مقادیر برندهایی را بازمی‌گرداند که در کانتکست فیلتر بصری انتخاب شده‌اند. اما موتور DAX که Power BI به آن کوئری می‌زند، هیچ مفهومی از “ویژوال کنونی” ندارد. بنابراین هیچ کانتکست فیلتر بصری‌ای به عنوان بخشی از محاسبه Measures در DAX وجود ندارد.
در اصل، ALLSELECTED چیزی غیر از مقدار ستون‌ها یا جدول‌هایی که خارج از ویژوال فیلتر شده‌اند را باز نمی‌گرداند. کاری که انجام می‌دهد متفاوت است؛ اما اغلب، خروجی مورد انتظار را فراهم می‌کند.
همچنین ALLSELECTED، بسته به اینکه درون SUMMARIZECOLUMNS استفاده شود یا نه و همچنین در حضور سایه فیلتر کانتکست‌ها، رفتار متفاوتی دارد. حال، ببینیم وقتی ALLSELECTED درون SUMMARIZECOLUMNS باشد، چگونه کار می‌کند.
ALLSELECTED در SUMMARIZECOLUMNS
وقتی ALLSELECTED به عنوان بخشی از SUMMARIZECOLUMNS به کار رود، در واقع فیلتر ستونی را که در حال گروه‌بندی بر اساس آن هستیم پاک می‌کند و کانتکست فیلتر اصلی که توسط SUMMARIZECOLUMNS تعیین شده را باز می‌گرداند.
مثال ساده از کوئری DAX:
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Brand],
TREATAS (
{
"Adventure Works",
"Contoso",
"Fabrikam",
"Litware",
"Northwind Traders",
"Proseware"
},
'Product'[Brand]
),
"Sales_Amount", 'Sales'[Sales Amount],
"Pct",
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALLSELECTED ( 'Product'[Brand] )
)
)
)
در این کوئری، هیچ اثری از “کانتکست ویژوال” وجود ندارد. SUMMARIZECOLUMNS هم فیلتر را اعمال می‌کند و هم گروه‌بندی را انجام می‌دهد. هر ردیف فقط یک برند را در فیلتر دارد. وقتی ALLSELECTED اجرا می‌شود، فیلتر برند فعلی را حذف کرده و کل برندهای انتخاب‌شده را دوباره قابل مشاهده می‌کند.
این همان رفتاری است که Power BI تقریباً در تمام ویژوال‌هایش استفاده می‌کند.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Product'[Brand],
"Sales_Amount", 'Sales'[Sales Amount],
"Pct",
DIVIDE (
[Sales Amount],
CALCULATE ( [Sales Amount], ALLSELECTED ( 'Product'[Brand] ) )
)
),
TREATAS (
{
"Adventure Works",
"Contoso",
"Fabrikam",
"Litware",
"Northwind Traders",
"Proseware"
},
'Product'[Brand]
)
)
معرفی سایه فیلتر کانتکست‌ها
Shadow filter contexts
سایه فیلتر کانتکست‌ها نوع خاصی از کانتکست فیلتر (Filter Context) هستند که توسط ایتراتورها ایجاد می‌شوند. هر بار که یک فرآیند تکرار (ایتره) آغاز می‌شود، ایتراتور یک “سایه فیلتر کانتکست” شامل ردیف‌های جدول در حال تکرار ایجاد می‌کند. این فیلتر کانتکست به صورت غیرفعال باقی می‌ماند؛ یعنی به طور فعال هیچ محتوایی را فیلتر نمی‌کند. این کانتکست تا زمانی که تابع ALLSELECTED صراحتا از آن استفاده نکند، غیرفعال و پنهان باقی می‌ماند.
اجازه دهید همان کوئری بخش قبل را این بار بدون استفاده از SUMMARIZECOLUMNS بازنویسی کنیم:
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Product'[Brand] ),
"Sales_Amount", [Sales Amount],
"Pct",
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALLSELECTED ( 'Product'[Brand] )
)
)
),
TREATAS (
{
"Adventure Works",
"Contoso",
"Fabrikam",
"Litware",
"Northwind Traders",
"Proseware"
},
'Product'[Brand]
)
)
نتیجه این کوئری جدید دقیقا مشابه نتیجه‌ای است که پیش‌تر بررسی کردیم. اما دلیل اینکه تابع ALLSELECTED این بار شش برند را از میان تمامی برندها بازمی‌گرداند این است که ADDCOLUMNS به عنوان یک ایتراتور، سایه فیلتر کانتکست ایجاد می‌کند که توسط ALLSELECTED فعال می‌شود.
در ادامه، یک شرح گام‌به‌گام از نحوه اجرای این کوئری ارائه می‌دهیم که در آن، در گام سوم با مفهوم سایه فیلتر کانتکست آشنا می‌شویم:
CALCULATETABLE بیرونی، کانتکست فیلتری با شش برند ایجاد می‌کند.
تابع VALUES شش برند قابل مشاهده را برمی‌گرداند و آنها را به ADDCOLUMNS منتقل می‌کند.
به عنوان یک ایتراتور، ADDCOLUMNS سایه فیلتر کانتکستی شامل خروجی VALUES را دقیقاً پیش از شروع تکرار (iteration) ایجاد می‌کند.
سایه فیلتر کانتکست مشابه فیلتر کانتکست معمولی است اما غیرفعال باقی می‌ماند و در این مرحله تاثیری بر ارزیابی ندارد.
این سایه فیلتر کانتکست فقط توسط تابع ALLSELECTED قابل فعال‌سازی است (به زودی توضیح می‌دهیم). فعلاً به خاطر داشته باشید که این کانتکست شامل شش برند در حال تکرار است.
ما برای تمایز بین دو نوع کانتکست، به سایه فیلتر کانتکست همان اصطلاح را اطلاق می‌کنیم و نوع معمولی را explicit filter context یا کانتکست فیلتر صریح می‌نامیم.
هنگام تکرار، انتقال کانتکست فقط روی یک ردیف مشخص انجام می‌شود. بنابراین، در هر تکرار یک کانتکست فیلتر جدید (explicit) فقط حاوی برند در حال تکرار ایجاد می‌شود.
زمانی که در حین ارزیابی Measure «درصد» (Pct) تابع ALLSELECTED فراخوانی می‌شود، اتفاق زیر رخ می‌دهد:
ALLSELECTED آخرین سایه فیلتر کانتکست را روی ستون‌ها یا جدول‌هایی که به‌عنوان پارامتر به آن پاس داده شده فعال می‌کند؛ یا اگر بدون پارامتر باشد روی تمام ستون‌ها فعال می‌شود.
از آنجایی که آخرین سایه فیلتر کانتکست شامل شش برند بوده است، دوباره همان شش برند مشهود و قابل دسترس خواهند شد.
این مثال ساده، مفهوم سایه فیلتر کانتکست را معرفی کرد. کوئری قبل نشان داد که چگونه ALLSELECTED برای بازگردانی کانتکست فیلتر خارج از تکرار فعلی از سایه فیلتر کانتکست استفاده می‌کند.
در نسخه‌های اولیه Power BI، پیش از آنکه SUMMARIZECOLUMNS وجود داشته باشد، Power BI از ایتراتورهایی مانند ADDCOLUMNS بهره می‌گرفت و برای مدیریت کانتکست‌ها از سایه فیلتر کانتکست‌ها استفاده می‌کرد.
امروزه نیز، هرچند Measuresها عمدتاً بر الگوریتم SUMMARIZECOLUMNS متکی هستند، ایتراتورها همچنان سایه فیلتر کانتکست تولید می‌کنند. مهم است بدانید هر دو سازوکار هنوز فعال‌اند و همزمان یکدیگر را تکمیل می‌کنند؛ بنابراین اگر به درستی به کار نروند، ممکن است منجر به نتایج نادرست و غیرمنتظره در محاسبات شوند.
یادگیری بهترین شیوه‌های استفاده از ALLSELECTED
اجتناب از مشکلات مربوط به ALLSELECTED نسبتاً ساده است:
هرگز یک Measure نسازید که از ALLSELECTED درون یک ایتراتور (مثل SUMX یا ADDCOLUMNS) استفاده کند و تا جای ممکن، میزان استفاده از Measureهایی که شامل ALLSELECTED هستند را به حداقل برسانید.
دیر یا زود، مرجع (Reference) به ALLSELECTED در بین پیچیدگی Measuresها گم می‌شود و شما ناخواسته، از یک ایتراتور استفاده می‌کنید که درون خود، یک Measure را صدا می‌زند که آن Measure نیز دوباره Measure دیگری را فراخوانی می‌کند که از ALLSELECTED بهره می‌برد.
به عبارتی، اگر هر ایتراتوری فعال است، اصلاً سراغ ALLSELECTED نروید. این کار باعث می‌شود تنها سازوکار آغاز شده توسط Power BI در زمان شروع کوئری و بر پایه SUMMARIZECOLUMNS، فعال بماند.
بیایید مثالی را بررسی کنیم که نشان می‌دهد اگر به این موضوع دقت نشود و این دو سیستم با هم ترکیب شوند، چگونه نتایج محاسبات اشتباه به دست می‌آید؛ صرفاً با استفاده از ALLSELECTED در یک ایتراتور.
نیازمندی:
می‌خواهیم فقط فروش کالاهای مرتبط را محاسبه کنیم. کالایی مرتبط شناخته می‌شود که بیش از ۰٫۵ درصد از مجموع فروش انتخاب شده را به خود اختصاص دهد.
یک راه درست، استفاده از یک Measure اول برای محاسبه مجموع فروش کل با استفاده از ALLSELECTED جهت داینامیک بودن آن است.
سپس Measure دیگری ایجاد می‌کنیم که بین محصولات تکرار می‌شود و منطق شرطی را اعمال می‌کند تا فقط مقادیری را جمع بزند که سهم‌شان بالای ۰٫۵ درصد باشد.
Total Sales = CALCULATE ( [Sales Amount], ALLSELECTED () )
Relevant Sales Wrong =
SUMX (
'Product',
IF ( DIVIDE ( [Sales Amount], [Total Sales] ) >= 0.005, [Sales Amount] )
)
همان‌طور که از نامش پیداست، Measure با نام Relevant Sales Wrong (یعنی «فروش مرتبط اشتباه») نتایج دقیقی ارائه نمی‌دهد. دلیل این امر آن است که این Measure روی جدول Product تکرار (Iterate) انجام می‌دهد و در طول این تکرار، یک Measure دیگر را فراخوانی می‌کند که به صورت داخلی از تابع ALLSELECTED استفاده می‌نماید.
در نتیجه، این ترکیب باعث می‌شود محاسبات درست انجام نشوند. تصویر زیر، نتیجه این Measure را در یک ماتریس (Matrix) نشان می‌دهد:
بسیاری از ردیف‌ها (در ماتریس خروجی)، مقداری را نمایش می‌دهند که حتی از مقدار ردیف Total (جمع کل) نیز بیشتر است.
پیش از آنکه به جزئیات دلیل اشتباه بودن این Measure بپردازیم، اجازه دهید نسخه صحیح این Measure را ارائه کنیم:
Relevant Sales Correct =
VAR TotalSales = [Total Sales]
RETURN
SUMX (
'Product',
IF ( DIVIDE ( [Sales Amount], TotalSales ) >= 0.005, [Sales Amount] )
)
تنها تفاوت بین Measure نادرست (Relevant Sales Wrong) و Measure صحیح (Relevant Sales Correct) این است که Measure مربوط به Total Sales در نسخه صحیح خارج از حلقه تکرار فراخوانی می‌شود، نه داخل آن.
از آنجا که این Measure با استفاده از تابع ALLSELECTED کانتکست فیلتر را تغییر می‌دهد، ممکن است انتظار داشته باشیم که برای هر ردیف تکرار شده، خروجی یکسانی داشته باشد؛ در واقع این Measure نسبت به تغییر کانتکست تکرار حساس نیست.
اما از آنجایی که از ALLSELECTED استفاده می‌کند، این تفاوت بسیار اهمیت پیدا می‌کند و منجر به نتایج متفاوتی خواهد شد.
در تصویر زیر همان ماتریس، این‌بار همراه با Measure صحیح نمایش داده شده است:
برای درک بهتر تفاوت‌ها، از نسخه ساده‌شده‌ای از کوئری استفاده می‌کنیم که کد هر دو Measure را نیز در آن قرار داده‌ایم:
DEFINE
MEASURE Sales[TotalSales] =
CALCULATE (
[Sales Amount],
ALLSELECTED ()
)
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
'Product'[Brand],
"Total"
),
"Sales Amount", [Sales Amount],
"Wrong",
SUMX (
'Product',
--
-- Inside the iteration, ALLSELECTED restores the shadow filter context
-- created by SUMX, which is iterating over the products of the given brand
--
-- This is the shadow filter context behavior
--
IF (
DIVIDE (
[Sales Amount],
[TotalSales]
) >= 0.005,
[Sales Amount]
)
),
"Correct",
--
-- Outside of the iteration, ALLSELECTED removes the filter over Product[Brand]
-- and makes all the selected products visible
--
-- This is the SUMMARIZECOLUMNS/ALLSELECTED behavior
--
VAR TotalSales = [TotalSales]
RETURN
SUMX (
'Product',
IF (
DIVIDE (
[Sales Amount],
TotalSales
) >= 0.005,
[Sales Amount]
)
)
)
همان‌طور که در توضیحات (کامنت‌ها) داخل کد DAX می‌بینید، در محاسبه نادرست (Wrong Calculation) فراخوانی Measure TotalSales از داخل حلقه تکرار روی Product انجام می‌شود. بنابراین، این رویکرد به‌طور عملی از روش سایه فیلتر کانتکست (shadow filter context) استفاده می‌کند.
از آنجایی که SUMX روی محصولات هر برند تکرار می‌کند، Measure TotalSales فروش تمام محصولات در برند فعلی نمایش داده‌شده را محاسبه می‌کند.
در مقابل، در عبارت Correct، Measure مربوط به TotalSales در خارج از حلقه تکرار فراخوانی می‌شود. در نتیجه، هیچ سایه فیلتر کانتکستی وجود ندارد و تابع ALLSELECTED از روش SUMMARIZECOLUMNS استفاده می‌کند. این کار فیلتر روی Product[Brand] را حذف کرده و تمام محصولات را نمایان می‌کند – یعنی همه محصولاتی که توسط یک اسلایسر احتمالی فیلتر شده‌اند.
این مثال عمداً ساده انتخاب شده است: یک Measure که مستقیماً و به‌صورت صریح از ALLSELECTED در درون حلقه تکرار استفاده می‌کند.
در دنیای واقعی و در مدل‌های سمانتیک بزرگ با صدها Measure، عملاً رصد تمام مواضع استفاده از Measures داخل یا خارج از ایتراتورها ممکن نیست.
به همین دلیل قانون کار ساده است:
تابع ALLSELECTED را فقط باید در Measureهایی به کار برد که مستقیم روی گزارش قرار می‌گیرند.
از صدا زدن (Invoke) یک Measure که ALLSELECTED را در دل خود دارد خودداری کنید، چون ممکن است Measureهای دیگر، بعدها Measure شما را فراخوانی کنند، بدون اینکه اطلاعی از استفاده داخلی ALLSELECTED در آن داشته باشند؛ که این موضوع می‌تواند باعث نتایج محاسباتی نادرست شود.
جمع‌بندی
استفاده از ALLSELECTED در DAX بسیار قدرتمند و سودمند است و جای هیچ نگرانی یا ترسی وجود ندارد. تا چند سال پیش و قبل از معرفی تابع SUMMARIZECOLUMNS، استفاده از مکانیزم سایه فیلتر کانتکست کاملاً متداول و مناسب بود. اکنون، وقتی ALLSELECTED در چارچوب SUMMARIZECOLUMNS استفاده می‌شود، نتایج قابل اتکا و دقیقی ایجاد می‌کند.
اما اگر یک Measure که شامل ALLSELECTED است از درون یک ایتراتور (مانند SUMX، ADDCOLUMNS و …) فراخوانی شود، مکانیزم قدیمی به کار می‌افتد و این کار نتایجی تولید می‌کند که خطایابی و رفع اشکال آنها بسیار دشوار خواهد بود.
راه‌حل خیلی ساده است:
اطمینان حاصل کنید که هیچ‌گاه در هنگام شروع یک ایتراتور، ALLSELECTED استفاده نشود. این نکته نیازمند توجه به زنجیره فراخوانی Measures در مدل شماست. رعایت این اصل تضمین می‌کند که کد DAX شما همواره همان خروجی مورد انتظار را ارائه دهد و محاسبات شما نهایی و بدون ایراد خواهد بود.
دیدگاهتان را بنویسید