CALCULATE پیشرفته و مدیریت حرفه‌ای Filter Context در DAX

شروع جلسه؛ اینجا DAX جدی می‌شود

در جلسه قبل گفتیم CALCULATE قلب DAX است. این جلسه می‌خواهیم دقیق‌تر بفهمیم وقتی چند Filter همزمان داریم، کدام Filter حفظ می‌شود، کدام جایگزین می‌شود و چطور Measureهایی مثل سهم از کل، سهم داخل گروه، فروش انتخاب‌شده و KPIهای مقایسه‌ای می‌سازیم.

هدف حفظ کردن فرمول نیست؛ هدف این است که قبل از اجرای DAX بتوانی Context نتیجه را روی کاغذ پیش‌بینی کنی.

۳۲.۱. یادآوری CALCULATE

CALCULATE(
    [Total Sales],
    DimProduct[Category] = "Laptop"
)

Expression در Contextی ارزیابی می‌شود که CALCULATE آن را تغییر داده است.

۳۲.۲. فیلتر جدید روی همان ستون

اگر Visual روی Category فیلتر داشته باشد و CALCULATE روی همان ستون Category مقدار دیگری اعمال کند، باید بدانیم Filter Argument جدید می‌تواند Filter موجود آن ستون را جایگزین کند.

۳۲.۳. فیلتر روی ستون دیگر

اگر Context فعلی Year=2026 باشد و CALCULATE فقط Category را فیلتر کند، فیلتر Year سر جایش می‌ماند. CALCULATE قرار نیست همه Context را پاک کند.

۳۲.۴. مثال ترکیب Context

Laptop Sales =
CALCULATE(
    [Total Sales],
    DimProduct[Category] = "Laptop"
)

در Visual شعبه تهران و سال ۲۰۲۶، نتیجه همچنان تهران و ۲۰۲۶ است، ولی Category روی Laptop تنظیم می‌شود.

۳۲.۵. REMOVEFILTERS روی یک ستون

Sales All Categories =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(DimProduct[Category])
)

۳۲.۶. REMOVEFILTERS روی کل Dimension

Sales All Products =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(DimProduct)
)

این بار همه Filterهای DimProduct حذف می‌شوند؛ مثلاً Category، Brand و Product.

۳۲.۷. فرق حذف ستون و حذف Table

اگر فقط Category را حذف کنی، Filter روی Brand ممکن است باقی بماند. اگر کل DimProduct را حذف کنی، Filterهای محصولی گسترده‌تری برداشته می‌شوند.

۳۲.۸. درصد از کل محصول

Product Share % =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(DimProduct)
    )
)

۳۲.۹. Contextهای غیرمحصولی حفظ می‌شوند

در Measure بالا، Date و Branch همچنان می‌توانند Context را فیلتر کنند. بنابراین سهم محصول از کل فروش «در همان بازه زمانی و شعبه جاری» محاسبه می‌شود.

۳۲.۱۰. درصد از Category

گاهی نمی‌خواهیم سهم محصول از کل شرکت باشد؛ می‌خواهیم سهم هر محصول از Category خودش باشد. اینجا باید فقط سطح مناسب فیلتر را حذف کنیم.

۳۲.۱۱. ALLEXCEPT

Sales Within Category =
CALCULATE(
    [Total Sales],
    ALLEXCEPT(DimProduct, DimProduct[Category])
)

ALLEXCEPT فیلترهای Table را حذف می‌کند به جز ستون‌هایی که مشخص کرده‌ایم.

۳۲.۱۲. سهم محصول داخل Category

Product Share in Category % =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALLEXCEPT(DimProduct, DimProduct[Category])
    )
)

۳۲.۱۳. ALLSELECTED؛ مفهوم مهم

ALLSELECTED در سناریوهای Visual Total و انتخاب کاربر کاربرد دارد؛ یعنی می‌خواهیم Context داخلی Row/Column Visual را کنار بزنیم ولی انتخاب‌های بیرونی کاربر را تا حد مناسب حفظ کنیم.

۳۲.۱۴. مثال ALLSELECTED

Sales % of Selected =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALLSELECTED(DimProduct[ProductName])
    )
)

۳۲.۱۵. ALL یا ALLSELECTED؟

۳۲.۱۶. KEEPFILTERS

Premium Sales =
CALCULATE(
    [Total Sales],
    KEEPFILTERS(DimCustomer[Segment] = "Premium")
)

KEEPFILTERS باعث می‌شود Filter جدید با Filter موجود روی همان ستون به شکل تقاطعی رفتار کند.

۳۲.۱۷. بدون KEEPFILTERS

Filter Argument ساده روی همان ستون معمولاً Filter قبلی را جایگزین می‌کند. این رفتار در بعضی Measureها دقیقاً چیزی است که می‌خواهیم.

۳۲.۱۸. با KEEPFILTERS

اگر کاربر Segment دیگری را انتخاب کرده باشد و Measure فقط Premium را با KEEPFILTERS بخواهد، تقاطع ممکن است خالی شود؛ یعنی Measure انتخاب کاربر را نادیده نمی‌گیرد.

۳۲.۱۹. FILTER برای شرط پیچیده

Large Orders Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        FactSales,
        FactSales[Quantity] >= 10 &&
        FactSales[SalesAmount] >= 50000000
    )
)

۳۲.۲۰. FILTER را بی‌دلیل روی Fact بزرگ اجرا نکن

اگر شرط را می‌توان با Filter Argument ساده‌تر بیان کرد، معمولاً همان گزینه را اول بررسی کن. FILTER برای منطق Table Expression پیچیده‌تر بسیار قدرتمند است.

۳۲.۲۱. FILTER روی Dimension

High Value Product Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        DimProduct,
        DimProduct[ListPrice] > 100000000
    )
)

۳۲.۲۲. Measure داخل FILTER

می‌توان در سناریوهای پیشرفته از Measure داخل FILTER استفاده کرد، ولی Context Evaluation پیچیده‌تر می‌شود و باید Performance را هم بررسی کرد.

۳۲.۲۳. CALCULATETABLE

TopRegionRows =
CALCULATETABLE(
    FactSales,
    DimBranch[Region] = "North"
)

CALCULATETABLE شبیه CALCULATE است ولی Expression آن Table برمی‌گرداند.

۳۲.۲۴. چرا CALCULATETABLE مهم است؟

در DAX پیشرفته خیلی از منطق‌ها ابتدا یک Virtual Table می‌سازند و بعد روی آن محاسبه انجام می‌دهند.

۳۲.۲۵. Virtual Table

Virtual Table الزاماً Table فیزیکی ذخیره‌شده در Model نیست؛ یک Table Expression موقت برای محاسبه است.

۳۲.۲۶. VALUES

VALUES(DimCustomer[CustomerKey])

VALUES مجموعه مقادیر متمایز موجود در Context را برمی‌گرداند و پایه خیلی از محاسبات Context-aware است.

۳۲.۲۷. تعداد مشتری فعال در Context

Active Customers =
COUNTROWS(
    FILTER(
        VALUES(DimCustomer[CustomerKey]),
        [Total Sales] > 0
    )
)

۳۲.۲۸. DISTINCT در برابر VALUES

هر دو برای مقادیر متمایز کاربرد دارند ولی رفتارشان در بعضی سناریوهای Blank/Relationship تفاوت‌هایی دارد. برای فرمول حرفه‌ای باید Function را بر اساس نیاز Context انتخاب کنیم.

۳۲.۲۹. SELECTEDVALUE دوباره

Selected Region =
SELECTEDVALUE(DimBranch[Region], "All / Multiple")

۳۲.۳۰. HASONEVALUE

Show Detail =
IF(
    HASONEVALUE(DimBranch[BranchName]),
    [Total Sales],
    BLANK()
)

۳۲.۳۱. ISINSCOPE

برای تشخیص سطح فعلی Hierarchy در Matrix بسیار کاربردی است؛ مثلاً بفهمیم اکنون روی Category هستیم یا Product.

۳۲.۳۲. مثال ISINSCOPE

Level Label =
SWITCH(
    TRUE(),
    ISINSCOPE(DimProduct[ProductName]), "Product",
    ISINSCOPE(DimProduct[Category]), "Category",
    "Total"
)

۳۲.۳۳. Context Transition عمیق‌تر

وقتی CALCULATE داخل Row Context اجرا می‌شود، Row Context موجود می‌تواند به Filter Context تبدیل شود. این رفتار یکی از دلایل مهم قدرت CALCULATE است.

۳۲.۳۴. SUMX + Measure

Customer Sales Sum =
SUMX(
    VALUES(DimCustomer[CustomerKey]),
    [Total Sales]
)

Measure داخل Iterator در Context هر Customer ارزیابی می‌شود.

۳۲.۳۵. Measure و Context Transition

وقتی Measure را داخل Row Context صدا می‌زنیم، رفتار Context Transition در ارزیابی Measure اهمیت پیدا می‌کند. در مراحل بعدی DAX این موضوع را با مثال‌های Ranking و Customer Analysis بیشتر استفاده می‌کنیم.

۳۲.۳۶. CALCULATE تو در تو

CALCULATEهای تو در تو ممکن‌اند ولی خوانایی خیلی سریع افت می‌کند. قبل از Nesting سنگین، Measure Branching و VAR را بررسی کن.

۳۲.۳۷. VAR برای Contextهای مختلف

Sales Share =
VAR CurrentSales = [Total Sales]
VAR AllProductSales =
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(DimProduct)
    )
RETURN
    DIVIDE(CurrentSales, AllProductSales)

۳۲.۳۸. Measureهای مقایسه‌ای

۳۲.۳۹. Actual vs Target

Target = SUM(FactTarget[TargetAmount])
Variance = [Total Sales] - [Target]
Variance % = DIVIDE([Variance], [Target])

۳۲.۴۰. Grain Actual و Target

اگر Actual روزانه-محصول و Target ماهانه-شعبه است، قبل از DAX باید مدل و Dimensionهای مشترک درست باشند. CALCULATE نمی‌تواند مدل بد را جادو کند.

۳۲.۴۱. Target در سطح شعبه

اگر Target فقط در Branch-Month تعریف شده، نمایش آن در Product Level ممکن است از نظر کسب‌وکار معنی نداشته باشد. Measure باید Scope مناسب را کنترل کند.

۳۲.۴۲. کنترل Scope KPI

Target Visible =
IF(
    ISINSCOPE(DimProduct[ProductName]),
    BLANK(),
    [Target]
)

۳۲.۴۳. سهم شعبه از کل Region

Branch Share of Region =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(DimBranch[BranchName])
    )
)

۳۲.۴۴. چرا Region حفظ شد؟

چون فقط Filter BranchName حذف شده است؛ Filter Region که از Visual یا Slicer آمده می‌تواند باقی بماند.

۳۲.۴۵. سهم شعبه از کل شرکت

Branch Share of Company =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(DimBranch)
    )
)

۳۲.۴۶. تفاوت دو سهم

در اولی مخرج کل Region است؛ در دومی مخرج کل Branch Dimension. این تفاوت کوچک در Syntax، معنی KPI را کاملاً عوض می‌کند.

۳۲.۴۷. فروش مشتریان دارای خرید

Customers With Sales =
COUNTROWS(
    FILTER(
        VALUES(DimCustomer[CustomerKey]),
        [Total Sales] > 0
    )
)

۳۲.۴۸. Average Sales per Active Customer

Avg Sales per Active Customer =
DIVIDE(
    [Total Sales],
    [Customers With Sales]
)

۳۲.۴۹. Top Customer Context

برای تحلیل Top N در جلسات بعد از TOPN و Ranking استفاده می‌کنیم، ولی پایه آن همین Virtual Table + Context-aware Measure است.

۳۲.۵۰. TREATAS؛ معرفی

TREATAS برای اعمال مقادیر یک Table Expression به عنوان Filter روی ستون دیگری استفاده می‌شود و در مدل‌های Disconnected یا سناریوهای خاص بسیار قدرتمند است.

۳۲.۵۱. مثال ساده TREATAS

Selected Scenario Sales =
CALCULATE(
    [Total Sales],
    TREATAS(
        VALUES(Scenario[Region]),
        DimBranch[Region]
    )
)

۳۲.۵۲. TREATAS جای Relationship سالم نیست

اگر Relationship طبیعی و درست می‌توان ساخت، معمولاً اول مدل را درست کن. TREATAS برای سناریوهای طراحی‌شده است، نه پوشاندن مشکل مدل.

۳۲.۵۳. USERELATIONSHIP؛ معرفی

اگر FactSales هم OrderDateKey و هم ShipDateKey دارد و رابطه OrderDate Active است، برای Measure مبتنی بر ShipDate می‌توان رابطه Inactive را فعال کرد.

۳۲.۵۴. فروش بر اساس Ship Date

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        FactSales[ShipDateKey],
        DimDate[DateKey]
    )
)

۳۲.۵۵. CROSSFILTER؛ معرفی

CROSSFILTER می‌تواند جهت یا رفتار Relationship را برای یک محاسبه تغییر دهد. این ابزار پیشرفته است و باید با مدل روشن استفاده شود.

۳۲.۵۶. DAX نباید مدل را وصله کند

اگر برای هر Measure مجبور به TREATAS، CROSSFILTER و FILTERهای پیچیده هستی، دوباره Data Model را بررسی کن.

۳۲.۵۷. پروژه شرکت هانا؛ Measure Layer

۳۲.۵۸. تست با Matrix

یک Matrix با Region → Branch و Category → Product بساز و Measureهای Share را در سطح‌های مختلف بررسی کن. اگر معنی Total را نمی‌توانی توضیح بدهی، Measure هنوز آماده Production نیست.

۳۲.۵۹. تست Slicer

Year، Region، Segment و Category را Slicer کن و برای هر Measure مشخص کن کدام Selection باید حفظ و کدام باید نادیده گرفته شود.

۳۲.۶۰. Context Specification

۳۲.۶۱. این جدول چرا مهم است؟

قبل از کدنویسی Measure پیچیده، Context Specification بنویس. این کار DAX را از آزمون و خطا به طراحی تبدیل می‌کند.

۳۲.۶۲. Performance؛ Filter Table بزرگ

FILTER روی Fact بزرگ و Expressionهای پیچیده می‌توانند هزینه‌بر باشند. اول مدل، سپس Filter ساده، و بعد در صورت نیاز Table Expression پیچیده را انتخاب کن.

۳۲.۶۳. Performance؛ Measure Branching

Measure Branching خوانایی را بالا می‌برد، ولی Measureهای پایه هم باید بهینه باشند. یک Measure کند اگر ده جا استفاده شود، مشکل چند برابر می‌شود.

۳۲.۶۴. Performance؛ Cardinality

Context و Filter روی ستون‌های High Cardinality می‌تواند هزینه بیشتری داشته باشد. طراحی مدل جلسه ۳۰ مستقیماً روی DAX Performance اثر دارد.

۳۲.۶۵. خطای رایج؛ REMOVEFILTERS بیش از حد

اگر برای سهم Category کل DimProduct را حذف کنی، Category هم از بین می‌رود و مخرج تبدیل به کل محصول می‌شود.

۳۲.۶۶. خطای رایج؛ ALLSELECTED بدون فهم

ALLSELECTED را فقط چون درصد Visual درست به نظر می‌رسد استفاده نکن. باید بدانی Selection بیرونی و Context داخلی چه هستند.

۳۲.۶۷. خطای رایج؛ FILTER روی کل Fact

وقتی یک Boolean Filter ساده کافی است، FILTER(FactSales, ...) ممکن است کد را سنگین‌تر و سخت‌تر کند.

۳۲.۶۸. خطای رایج؛ KPI در Grain نامناسب

Target ماهانه را در Product Row نشان دادن ممکن است عددی تولید کند، ولی عدد داشتن با معنی داشتن فرق دارد.

۳۲.۶۹. خطای رایج؛ Total را جمع Rowها فرض کردن

Measure در Total دوباره در Context Total محاسبه می‌شود. برای سهم‌ها، Averageها و Ratioها این رفتار کاملاً مهم است.

۳۲.۷۰. Best Practiceها

قبل از Measure پیچیده Context Specification بنویس.

Measure پایه را از Measure تحلیلی جدا کن.

REMOVEFILTERS را دقیقاً روی سطح لازم اعمال کن.

ALLSELECTED را برای Selection-aware scenarios آگاهانه استفاده کن.

KEEPFILTERS را برای تقاطع Filterها بشناس.

FILTER را وقتی Table Expression واقعاً لازم است استفاده کن.

USERELATIONSHIP را برای Role-playing Dateها به کار ببر.

DAX پیچیده را جایگزین مدل‌سازی صحیح نکن.

Total و Subtotal را جداگانه تست کن.

۳۲.۷۱. تمرین ۱

Product Share % از کل محصولات را بساز؛ Date و Branch باید حفظ شوند.

۳۲.۷۲. تمرین ۲

Product Share in Category را بساز؛ Category در مخرج حفظ شود ولی ProductName حذف شود.

۳۲.۷۳. تمرین ۳

Branch Share of Region و Branch Share of Company را جدا بساز و تفاوت Context آن‌ها را توضیح بده.

۳۲.۷۴. تمرین ۴

Measure فروش Premium با KEEPFILTERS بساز و رفتار آن را وقتی Slicer روی Segment=Standard است بررسی کن.

۳۲.۷۵. تمرین ۵

Sales by Ship Date را با USERELATIONSHIP بساز و با Total Sales مبتنی بر OrderDate مقایسه کن.

۳۲.۷۶. تمرین چالشی

یک Measure سهم فروش از «محصولات انتخاب‌شده توسط کاربر» با ALLSELECTED طراحی کن و رفتار آن را در Matrix Product بررسی کن.

۳۲.۷۷. پروژه Session؛ Context-Aware KPI Pack شرکت هانا

در پایان باید برای هر KPI بتوانی با زبان ساده توضیح بدهی: «چه فیلترهایی را قبول می‌کند، چه فیلترهایی را حذف می‌کند و Total آن چه معنی‌ای دارد؟»

جمع‌بندی Session 32

CALCULATE حرفه‌ای یعنی کنترل آگاهانه Context. فرق بین یک DAX نویس معمولی و کسی که مدل تحلیلی قابل اعتماد می‌سازد، اغلب همین‌جاست.

Define context → modify context intentionally → evaluate → validate totals

Session 33 را وارد Time Intelligence می‌کنیم: Date Table استاندارد، YTD/MTD/QTD، Previous Period، رشد سالانه و مقایسه‌های زمانی حرفه‌ای.