استفاده از متغیر و تابع EARLIER در زبان DAX

در این مطلب به بررسی استفاده از متغیر و تابع EARLIER در زبان DAX می پردازیم. بسیار پیش آمده که از سوی کاربران Power BI با این سوال مواجه شده ایم که تابع EARLIER به چه کاری می آید و در چه زمان هایی استفاده می شود؟

در پاسخ باید گفت با امکان پذیر شدن استفاده از متغیر در زبان DAX، تقریبا این تابع به صورت کامل بازنشته شده و جای خود را به متغیر داده است.

شاید تنها کاربرد یادگیری تابع EARLIER این است که هنوز هم بسیاری از کاربران قدیمی از این تابع استفاده می کنند و همچنین ممکن است با جستجو در این حوزه با کدهایی مواجه شوید که در آن ها از این تابع استفاده شده باشد و بهتر است برای فهمیدن این کدها و استفاده از آن ها با تابع EARLIER آشنا باشید.

در هر صورت به عنوان یک شخص که با ابزار Power BI کار می کند و زبان DAX جزئی از مهارت های او به حساب می آید بهتر است از هر دو روش شناخت کافی داشته باشید.

برای پیاده سازی این مطلب مثل همیشه از انبار داده AdventureworksDW استفاده می کنیم. 3 جدول فروش و کالا و گروه کالا را در مدل بارگذاری می کنیم. این سه جدول از طریق رابطه یک به چند با یکدیگر ارتباط دارند.

ابتدا یک مژر برای میزان فروش در مدل ایجاد می کنیم.

TotalSales =
SUMX ( Sales, Sales[OrderQuantity] * Sales[UnitPrice] )

همان طور که می دانید تابع SUMX دارای 2 ورودی است. یک ورودی جدول که جدول فروش به آن معرفی شده است و یک عبارت که ضرب تعداد در قیمت به آن معرفی شده است. خروجی این تابع جمع فروش است.

حال یک ستون در جدول گروه کالا ایجاد می کنیم. این کار از طریق کلیک راست بر روی جدول Subcategory و انتخاب گزینه New Column قابل انجام است. مژر ایجاد شده را به عنوان تعریف این ستون درج می کنیم. فرمول ستون با فلش قرمز و نتیجه آن با فلش سبز در تصویر نمایش داده شده اند.

خروجی فرمول یاد شده، فروش هر گروه محصول است. عدد صفر به این منظور با مژر مذکور جمع شده که برای برخی از گروه محصول های بدون فروش به جای مقدار خالی، عدد صفر نمایش داده شود.

پس تا اینجا یک مژر برای میزان فروش ساختیم و یک ستون در جدول گروه محصول که به عنوان فرمول آن تنها مژر فروش را فراخوانی کردیم.

CategorySales =
[TotalSales] + 0

حال می خواهیم رتبه هر گروه محصول را بر اساس فروش آن محاسبه کنیم. باید فروش هر محصول را با فروش سایر محصولات مقایسه کنیم و تعداد محصولاتی که فروش بیشتر یا مساوی با آن را دارند را در نظر بگیریم. این تعداد در نهایت رتبه این گروه خواهد بود.

4 محصول A، B، C، D را در نظر بگیرید. محصول A فروش 100 دلار، محصول B فروش 200 دلار، محصول C فروش 300 دلار و در نهایت D فروش 400 دلار را برای خود ثبت کرده اند.

محصول A را در نظر بگیرید. چند محصول فروشی بیشتر از این محصول یا برابر با آن را داشته اند؟ درست است. 4 محصول. پس این محصول رتبه 4 را داراست. محصول B را در نظر بگیرید. چند محصول بیشتر از این محصول فروخته اند یا فروشی برابر با آن را داشته اند؟ 3 محصول. پس این محصول رتبه 3 را دریافت خواهد کرد.

پس رتبه هر محصول عبارت است از تعداد محصولاتی که فروش مساوی یا بیشتر از آن محصول داشته اند.

حال بیاییم و این موضوع را پیاده سازی کنیم.

استفاده از تابع EARLIER

CategoryRank =
CALCULATE (
    COUNTROWS ( DimSubcategory ),
    FILTER (
        DimSubcategory,
        DimSubcategory[CategorySales] >= DimSubcategory[CategorySales]     )
)

ابتدا قدری به توضیح کد بالا می پردازیم. داخلی ترین تابع، FILTER است که در واقع قصد دارد گروه کالاهایی را که میزان فروش آن ها از مقدار فروش ردیف جاری بیشتر یا مساوی است را فیلتر کند. تابع بیرونی COUNTROWS است که تعداد گروه کالاهای با فروش بیشتر (گروه کالاهای فیلتر شده) را می شمارد. به نظر شما این کد چه نتیجه ای خواهد داشت؟ پیش گویی را بیخیال شویم و نتیجه را با هم ببینیم.

همان طور که مشاهده می کنید رتبه همه گروه های محصول علیرغم فروش متفاوت، یکسان و برابر با تعداد ردیف های جدول است. می توان گفت امکان دسترسی به مقادیر ردیف های دیگر به این صورت امکان پذیر نیست. در واقع با این روش هر ردیف نمی داند در ردیف های دیگر وضعیت از چه قرار است!

پس چگونه می توان در هر ردیف در حال محاسبه، به مقادیر سایر ردیف ها دست پیدا کرد؟ امیدوارم با مفهوم Row Context آشنا باشید. خبر خوب اینکه قبلا در مطلبی به بررسی این مفهوم پرداخته شده است.

مفهوم Context و انواع آن در زبان DAX

اما به طور خلاصه Row Context به مفهوم ردیف جاری اشاره دارد. در واقع وقتی یک ستون محاسباتی در جدول ایجاد می کنید، این محاسبه ردیف به ردیف انجام می شود. در واقع در هر لحظه یک ردیف در حال محاسبه است. در هنگام محاسبه هر ردیف امکان دسترسی به ردیف های دیگر وجود ندارد مگر با استفاده از تابع EARLIER یا متغیر.

با استفاده از متغیر و تابع EARLIER در زبان DAX می توان بر این محدودیت غلبه کرد. بیایید ابتدا از تابع EARLIER استفاده کنیم.

CategoryRank =
CALCULATE (
    COUNTROWS ( DimSubcategory ),
    FILTER (
        DimSubcategory,
        DimSubcategory[CategorySales] >= EARLIER ( DimSubcategory[CategorySales] )
    )
)

حال بار دیگر نتیجه را مشاهده می کنیم.

همان طور که مشاهده می کنید رتبه بندی به درستی انجام شده است. با استفاده از تابع EARLIER امکان دسترسی به مقادیر سایر ردیف ها ممکن شده است.

گروه محصول با فروش حدود 14 میلیون دلار، رتبه یک را دریافت کرده است، چرا که تنها محصولی که فروش بیشتر یا برابر آن را داشته است خودش بوده است. گروه محصول ها با فروش صفر نیز رتبه 37 را دریافت کرده اند. تعداد گروه های محصولات ما 37 عدد است.

استفاده از متغیر

حال همین سناریو را با استفاده از متغیر نیز می توان پیاده سازی نمود. کافی است قبل از شروع فرمول متغیری تعریف نموده و میزان فروش گروه محصول را در آن ذخیره کنیم. حال به جای استفاده از تابع ERLIER از این متغیر در کد استفاده می نماییم.

استفاده از متغیر ها مزایای بسیاری به همراه خواهد داشت. برای مطالعه بیشتر در مورد متغیر ها به لینک زیر مراجعه نمایید.

متغیر در زبان DAX

با استفاده از کد زیر می توان به این موضوع دست یافت. برای تعریف متغیر باید از کلید واژه VAR استفاده نمایید. یک نام به آن اختصاص دهید و بعد از علامت مساوی، تعریف آن را درج کنید

برای استفاده از متغیر در کد، باید از کلید واژه RETURN استفاده نماییم.

CategoryRank =
VAR Sale = DimSubcategory[CategorySales] RETURN
    CALCULATE (
        COUNTROWS ( DimSubcategory ),
        FILTER ( DimSubcategory, DimSubcategory[CategorySales] >= Sale )
    )

در توضیح این روش باید گفت در این روش نیز به رسم ایجاد ستون های محاسباتی Row Context ایجاد می شود. یعنی ردیف به ردیف این محاسبه انجام می شود. در هر ردیف میزان فروش در متغیر ذخیره می شود و فروش محصولات با مقدار ذخیره شده در این متغیر مقایسه می شود.

به طور مثال در محاسبه رتبه برای ردیف دوم، مقدار حدود 9 میلیون دلار در این متغیر ذخیره می شود. ستون مقدار فروش با این متغیر مقایسه می شود و تنها 2 محصول مقدار فروش بزرگتر یا مساوی با این متغیر را دارا می باشند، پس این گروه محصول رتبه 2 را دریافت خواهد کرد.

نتیجه را در تصویر مشاهده می کنید.

نتیجه گیری

برخی از سناریوها با استفاده از متغیر و تابع EARLIER در زبان DAX قابل پیاده سازی هستند. استفاده از تابع EARLIER در زبان DAX منسوخ شده است. امروز با وجود متغیر، تابع EALIER کاربردی ندارد و تنها به منظور تکمیل شدن دانش DAX آموزش داده می شود.

استفاده از متغیر در زبان DAX، مزایای بسیاری از جمله خوانایی کد ها و همچنین بهبود عملکرد کد ها به همراه خواهد داشت.

در این مطلب به بررسی یک سناریو با استفاده از متغیر و تابع EARLIER در زبان DAX پرداختیم. به توضیح عملکرد آن ها و برتری هر روش نسبت به دیگری پرداختیم. مطلب را به طور کامل مطالعه نمایید و سوالات خود را با ما در میان بگذارید.