تابع OFFSET در DAX

تابع OFFSET در Power BI

تابع OFFSET در Power BI یکی از توابع جدید زبان DAX است که فقط در محاسبات تصویری (Visual Calculations) قابل استفاده است. این تابع برای دسترسی به مقدار ردیف‌های قبلی یا بعدی در یک مجموعه مرتب‌شده کاربرد دارد و عملکردی مشابه توابع LEAD و LAG در SQL دارد.

تعریف تابع OFFSET

OFFSET ( <delta>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

پارامترها:

• offset: عدد صحیح (مثلاً -1 برای ردیف قبلی، +1 برای ردیف بعدی)
• delta: تعداد ردیف‌های قبل (مقدار منفی) یا بعد (مقدار مثبت) ردیف فعلی که داده‌ها از آن به دست می‌آیند. می‌تواند هر عبارت DAX باشد که یک مقدار اسکالر برمی‌گرداند.
• orderBy (اختیاری): یک عبارت ORDERBY() شامل عباراتی که نحوه مرتب‌سازی هر پارتیشن را تعریف می‌کنند.

  در صورت حذف:
  – رابطه باید به صراحت مشخص شود.
  – پیش‌فرض مرتب‌سازی بر اساس هر ستون در رابطه‌ای است که قبلاً در partitionBy مشخص نشده است.

• blanks (اختیاری): یک نوع شمارش که نحوه مدیریت مقادیر خالی را هنگام مرتب‌سازی رابطه یا محور تعریف می‌کند.

  مقادیر پشتیبانی‌شده عبارتند از:

  • DEFAULT (مقدار پیش‌فرض)، که در آن رفتار برای مقادیر عددی این است که مقادیر خالی بین مقادیر صفر و منفی مرتب می‌شوند. رفتار برای رشته‌ها این است که مقادیر خالی قبل از همه رشته‌ها، از جمله رشته‌های خالی، مرتب می‌شوند.
  • اول، مقادیر خالی همیشه در ابتدا، صرف نظر از ترتیب مرتب‌سازی صعودی یا نزولی، مرتب می‌شوند.
  • آخر، مقادیر خالی همیشه در انتها، صرف نظر از ترتیب مرتب‌سازی صعودی یا نزولی، مرتب می‌شوند.

توجه داشته باشید، هنگامی که پارامتر blanks و blanks در تابع ORDERBY() روی عبارات منفرد هر دو مشخص شوند، blanks در عبارت orderBy منفرد برای عبارت orderBy مربوطه اولویت دارند و عبارات orderBy بدون مشخص شدن blanks، پارامتر blanks را در تابع والد رعایت می‌کنند.

OFFSET dax

• partitionBy (اختیاری):  یک عبارت PARTITIONBY() شامل ستون‌هایی که نحوه پارتیشن‌بندی رابطه را تعریف می‌کنند. در صورت حذف، رابطه به عنوان یک پارتیشن واحد در نظر گرفته می‌شود.
• matchBy (اختیاری): یک عبارت MATCHBY() شامل ستون‌هایی که نحوه تطبیق داده‌ها و شناسایی ردیف فعلی را تعریف می‌کنند.
• reset (اختیاری): فقط در محاسبات بصری موجود است. نشان می‌دهد که آیا محاسبه بازنشانی می‌شود و در کدام سطح از سلسله مراتب ستون شکل بصری قرار دارد. مقادیر پذیرفته شده عبارتند از: ارجاع فیلد به ستونی در شکل بصری فعلی، NONE (پیش‌فرض)، LOWESTPARENT، HIGHESTPARENT یا یک عدد صحیح. رفتار به علامت عدد صحیح بستگی دارد:
  – اگر صفر یا حذف شود، محاسبه بازنشانی نمی‌شود. معادل NONE.
  – اگر مثبت باشد، عدد صحیح ستون را از بالاترین، مستقل از دانه، مشخص می‌کند. HIGHESTPARENT معادل ۱ است.
  – اگر منفی باشد، عدد صحیح ستون را از پایین‌ترین، نسبت به دانه فعلی، مشخص می‌کند. LOWESTPARENT معادل -۱ است.
• relation (اختیاری): یک عبارت جدولی که ردیف خروجی از آن بازگردانده می‌شود. اگر در محاسبات بصری استفاده شود، این پارامتر یک محور در شکل بصری را می‌پذیرد.

  در صورت مشخص شدن، تمام ستون‌های partitionBy باید از آن یا یک جدول مرتبط باشند.

  در صورت حذف:
  – orderBy باید به صراحت مشخص شود.

  – تمام عبارات orderBy و partitionBy باید نام ستون‌های کاملاً واجد شرایط باشند و از یک جدول واحد باشند.

  – پیش‌فرض برای تمام ستون‌های orderBy و partitionBy روی ALLSELECTED() است.

عملکرد چگونه است؟

تابع OFFSET مقدار یک ستون را از ردیفی که با فاصله مشخصی از ردیف فعلی قرار دارد بازمی‌گرداند. این فاصله می‌تواند مثبت (برای ردیف‌های بعدی) یا منفی (برای ردیف‌های قبلی) باشد. این تابع برای تحلیل‌های زمانی، مقایسه‌ای، و رتبه‌بندی بسیار مفید است.

✅ مثال ساده: مقدار ماه قبل

فرض کنید در یک ماتریس تصویری، ستون SalesAmount دارید و می‌خواهید مقدار فروش ماه قبل را برای هر ماه نمایش دهید:

OFFSET(
    -1,
    ORDERBY(Sales[Month]),
    PARTITIONBY(Sales[ProductID]),
    MATCHBY(Sales[Month])
)

در این مثال، مقدار فروش از یک ردیف قبل (ماه قبل) برای هر محصول بازیابی می‌شود.

✅ مثال دیگر: مقدار ماه بعد

OFFSET(
    1,
    ORDERBY(Sales[Month]),
    PARTITIONBY(Sales[ProductID]),
    MATCHBY(Sales[Month])
)

این مثال مقدار فروش ماه بعد را برای هر محصول نمایش می‌دهد.

مثال های کاربردی

DEFINE
VAR vRelation = SUMMARIZECOLUMNS ( 
                    DimProductCategory[EnglishProductCategoryName], 
                    DimDate[CalendarYear], 
                    "CurrentYearSales", SUM(FactInternetSales[SalesAmount]) 
                  )
EVALUATE
ADDCOLUMNS (
    vRelation, 
    "PreviousYearSales", 
    SELECTCOLUMNS(
        OFFSET ( 
                -1, 
                vRelation, 
                ORDERBY([CalendarYear]), 
                PARTITIONBY([EnglishProductCategoryName])
        ),
        [CurrentYearSales]
    )
)

DEFINE
MEASURE DimProduct[CurrentYearSales] = SUM(FactInternetSales[SalesAmount])
MEASURE DimProduct[PreviousYearSales] = CALCULATE(SUM(FactInternetSales[SalesAmount]), OFFSET(-1, , ORDERBY(DimDate[CalendarYear])))
EVALUATE
SUMMARIZECOLUMNS (
    DimDate[CalendarYear],
    "CurrentYearSales", DimProduct[CurrentYearSales],
    "PreviousYearSales", DimProduct[PreviousYearSales]
)

EVALUATE
ADDCOLUMNS (
    FactInternetSales,
    "Previous Sales Amount",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                FactInternetSales,
                ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                PARTITIONBY ( FactInternetSales[ProductKey] ),
                MATCHBY( FactInternetSales[SalesOrderNumber], FactInternetSales[SalesOrderLineNumber] )
            ),
            FactInternetSales[SalesAmount]
        )
)

SalesRelativeToPreviousMonth = [SalesAmount] - CALCULATE(SUM([SalesAmount]), OFFSET(-1, ROWS, HIGHESTPARENT))

 کاربردهای رایج

• مقایسه مقدار فعلی با مقدار قبلی یا بعدی
• تحلیل تغییرات ماهانه یا سالانه
• ساخت KPIهای تصویری پویا
• تحلیل روندها در داده‌های زمانی یا رتبه‌ای

⚠️ نکات مهم

• تابع OFFSET فقط در Visual Calculations قابل استفاده است.
• اگر ردیف قبلی یا بعدی وجود نداشته باشد، مقدار BLANK() بازگردانده می‌شود.
• استفاده از ORDERBY و MATCHBY برای عملکرد دقیق ضروری است.
• این تابع در معیارها (Measures) یا ستون‌های محاسبه‌شده پشتیبانی نمی‌شود.

اگر تمام ستون‌های رابطه توسط توابع جدول DAX اضافه شده باشند، خطایی برگردانده می‌شود.

OFFSET function

اگر matchBy وجود داشته باشد، OFFSET سعی می‌کند از ستون‌های matchBy و partitionBy برای شناسایی ردیف استفاده کند. و اگر matchBy وجود نداشته باشد و ستون‌های مشخص شده در orderBy و partitionBy نتوانند به طور منحصر به فرد هر ردیف در رابطه را شناسایی کنند، آنگاه:

• تابع OFFSET سعی می‌کند کمترین تعداد ستون اضافی مورد نیاز برای شناسایی منحصر به فرد هر سطر را پیدا کند.
• اگر چنین ستون‌هایی پیدا شوند، OFFSET به طور خودکار این ستون‌های جدید را به orderBy اضافه می‌کند و هر پارتیشن با استفاده از این مجموعه جدید از ستون‌های OrderBy مرتب می‌شود.
• اگر چنین ستون‌هایی پیدا نشوند، خطایی برگردانده می‌شود.

یک جدول خالی برگردانده می‌شود اگر:

• مقدار بیرونی متناظر یک ستون OrderBy یا PartitionBy در رابطه وجود نداشته باشد.
• مقدار دلتا باعث تغییر به ردیفی می‌شود که در پارتیشن وجود ندارد.

اگر OFFSET در یک ستون محاسبه‌شده که در همان جدول به عنوان رابطه تعریف شده است، استفاده شود و orderBy حذف شود، خطایی برگردانده می‌شود.

reset فقط در محاسبات بصری قابل استفاده است و نمی‌توان آن را در ترکیب با orderBy یا partitionBy استفاده کرد. اگر reset وجود داشته باشد، می‌توان axis را مشخص کرد اما relation را نمی‌توان.

اگر مقدار reset مطلق باشد (یعنی یک عدد صحیح مثبت، HIGHESTPARENT یا یک مرجع فیلد) و محاسبه در سطح هدف یا بالاتر از آن در سلسله مراتب ارزیابی شود، محاسبه برای هر عنصر جداگانه بازنشانی می‌شود. یعنی تابع در یک پارتیشن که فقط شامل آن عنصر خاص است، ارزیابی می‌شود.

برای خرید لایسنس نرم افزار Power BI ، می‌توانید از خدمات ما استفاده نموده و درخواست خود را از طریق فرم زیر ثبت نمایید.

فرم درخواست لایسنس Power BI

میتوانید پاور بی آی دسکتاپ رایگان را دانلود کنید : Power BI desktop download