DAX از صفر؛ Measure، Context و CALCULATE

کتاب جامع Power BI — آموزش پروژه‌محور و کاربردی

مقدمه؛ DAX را حفظ نکن، منطقش را بفهم

تا اینجا داده را تمیز کردیم و مدل ستاره‌ای ساختیم. حالا مدیر شرکت هانا فقط جدول و Relationship نمی‌خواهد؛ سؤال دارد: فروش کل چقدر است؟ چند مشتری خرید کرده‌اند؟ سود چقدر شده؟ شعبه تهران نسبت به هدف چه وضعی دارد؟

برای جواب دادن به این سؤال‌ها وارد DAX می‌شویم. DAX زبان فرمول‌نویسی مدل‌های تحلیلی Power BI است. قرار نیست صدها تابع را حفظ کنیم. مهم‌تر از تابع‌ها این است که بفهمیم محاسبه در چه Contextی اجرا می‌شود.

۵.۱. DAX چیست؟

DAX مخفف Data Analysis Expressions است و برای ساخت محاسبات در مدل Power BI استفاده می‌شود.

Total Sales = SUM(FactSales[SalesAmount])

سمت چپ نام Measure و سمت راست Expression است. این فرمول ساده است، ولی وقتی کاربر تاریخ یا شعبه را فیلتر کند، نتیجه آن هم تغییر می‌کند؛ همین رفتار پویا یکی از پایه‌های DAX است.

۵.۲. Measure چیست؟

Measure یک محاسبه پویاست که بر اساس Context گزارش ارزیابی می‌شود. اگر Total Sales را روی Card بگذاریم فروش کل را می‌بینیم؛ اگر همان Measure را کنار Branch قرار دهیم، فروش هر شعبه را می‌بینیم.

Total Sales = SUM(FactSales[SalesAmount])

فرمول یکی است، اما Context نتیجه را عوض می‌کند.

۵.۳. Calculated Column و Measure را قاطی نکن

مثلاً ساختن دسته «سفارش بزرگ/کوچک» می‌تواند مسئله‌ای ردیفی باشد؛ اما Total Sales معمولاً Measure است.

۵.۴. اولین Measureهای پروژه شرکت هانا

Total Quantity = SUM(FactSales[Quantity])

Total Sales = SUM(FactSales[SalesAmount])

Order Count = DISTINCTCOUNT(FactSales[OrderID])

همین سه Measure می‌توانند پایه چند KPI مهم باشند.

۵.۵. SUM، COUNT و DISTINCTCOUNT

SUM مقادیر عددی را جمع می‌کند. COUNT تعداد مقادیر عددی غیرخالی یک ستون را می‌شمارد و DISTINCTCOUNT تعداد مقادیر یکتا را می‌دهد.

Total Quantity = SUM(FactSales[Quantity])

Order Count = DISTINCTCOUNT(FactSales[OrderID])

اگر یک سفارش چند آیتم داشته باشد، COUNT روی ردیف‌ها الزاماً تعداد سفارش را نمی‌دهد؛ اینجا شناخت Grain که در فصل قبل یاد گرفتیم حیاتی است.

۵.۶. DIVIDE؛ تقسیم امن‌تر

برای نسبت‌ها معمولاً DIVIDE انتخاب خوبی است چون می‌توانیم رفتار تقسیم بر صفر را کنترل کنیم.

Average Order Value =
DIVIDE(
    [Total Sales],
    [Order Count],
    0
)

این Measure میانگین مبلغ هر سفارش را محاسبه می‌کند.

۵.۷. سود و حاشیه سود

فرض کنیم FactSales مقدار SalesAmount و CostAmount دارد.

Total Cost = SUM(FactSales[CostAmount])

Profit = [Total Sales] - [Total Cost]

Profit Margin % = DIVIDE([Profit], [Total Sales], 0)

این‌جا می‌بینیم Measureها می‌توانند روی Measureهای دیگر ساخته شوند؛ این کار خوانایی و نگهداری مدل را بهتر می‌کند.

۵.۸. IF؛ تصمیم ساده در DAX

Sales Status =
IF(
    [Total Sales] >= [Sales Target],
    "هدف محقق شده",
    "زیر هدف"
)

IF برای شرط‌های ساده مناسب است. اگر تعداد شرط‌ها زیاد شود، SWITCH معمولاً خواناتر می‌شود.

۵.۹. SWITCH

Performance Level =
SWITCH(
    TRUE(),
    [Achievement %] >= 1, "عالی",
    [Achievement %] >= 0.8, "قابل قبول",
    "نیاز به بررسی"
)

این الگو برای دسته‌بندی نتیجه KPIها کاربردی است.

۵.۱۰. Filter Context؛ مهم‌ترین مفهوم شروع DAX

فرض کن Total Sales روی Card برابر ۲۰ میلیارد است. حالا Slicer شعبه را روی تهران می‌گذاریم و Card می‌شود ۸ میلیارد. فرمول تغییر نکرده؛ Filter Context تغییر کرده است.

همه شعبه‌ها → Total Sales = 20B
تهران       → Total Sales = 8B
اصفهان      → Total Sales = 5B

فیلتر می‌تواند از Slicer، محور نمودار، خود Visual، صفحه، گزارش یا Relationshipها بیاید.

۵.۱۱. Row Context

Row Context یعنی محاسبه در فضای یک ردیف جاری. Calculated Columnها نمونه رایجی هستند.

Line Amount =
FactSales[Quantity] * FactSales[UnitPrice]

در هر ردیف Quantity و UnitPrice همان ردیف استفاده می‌شوند.

۵.۱۲. Iteratorها و SUMX

توابع خانواده X مثل SUMX روی یک جدول حرکت می‌کنند، برای هر ردیف Expression را محاسبه می‌کنند و بعد نتیجه را جمع می‌کنند.

Total Sales =
SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPrice] * (1 - FactSales[Discount])
)

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

۵.۱۳. CALCULATE؛ تابعی که باید واقعاً بفهمی

CALCULATE یکی از مهم‌ترین توابع DAX است. تعریف کاربردی و ساده‌اش این است: یک Expression را در Filter Context تغییرکرده محاسبه کن.

Tehran Sales =
CALCULATE(
    [Total Sales],
    DimBranch[BranchName] = "تهران"
)

اینجا Total Sales را داریم، ولی CALCULATE Context را به شعبه تهران محدود می‌کند.

۵.۱۴. CALCULATE با چند شرط

Tehran ProductA Sales =
CALCULATE(
    [Total Sales],
    DimBranch[BranchName] = "تهران",
    DimProduct[Category] = "A"
)

در پروژه واقعی بهتر است Measureها را طوری طراحی کنیم که تا جای ممکن قابل استفاده مجدد باشند.

۵.۱۵. FILTER

گاهی شرط ما پیچیده‌تر از یک فیلتر ساده است. FILTER یک جدول فیلترشده برمی‌گرداند.

Large Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        FactSales,
        FactSales[SalesAmount] >= 100000000
    )
)

FILTER قدرتمند است، اما نباید برای هر شرط ساده‌ای بی‌دلیل از آن استفاده کنیم.

۵.۱۶. ALL و REMOVEFILTERS

گاهی می‌خواهیم بخشی از فیلتر را نادیده بگیریم؛ مثلاً سهم فروش هر شعبه از کل شرکت.

Company Sales =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(DimBranch)
)

Branch Share % =
DIVIDE([Total Sales], [Company Sales], 0)

وقتی Branch روی Visual است، صورت کسر فروش همان شعبه و مخرج فروش کل شرکت است.

۵.۱۷. VALUES و SELECTEDVALUE

VALUES مجموعه مقادیر یکتای قابل مشاهده در Context را برمی‌گرداند. SELECTEDVALUE زمانی مفید است که انتظار داریم یک مقدار انتخاب شده باشد.

Selected Branch =
SELECTEDVALUE(
    DimBranch[BranchName],
    "همه شعبه‌ها"
)

از این Measure می‌توانیم برای عنوان پویا یا نمایش انتخاب کاربر استفاده کنیم.

۵.۱۸. VAR و RETURN؛ فرمولت را قابل‌خواندن کن

Profit Margin % =
VAR SalesValue = [Total Sales]
VAR ProfitValue = [Profit]
RETURN
    DIVIDE(ProfitValue, SalesValue, 0)

Variableها باعث می‌شوند Expressionهای طولانی خواناتر و قابل نگهداری‌تر شوند.

۵.۱۹. RELATED

در Calculated Column، وقتی Relationship مناسب داریم، RELATED می‌تواند مقداری را از جدول مرتبط بگیرد.

Product Category = RELATED(DimProduct[Category])

اما قبل از ساخت چنین ستونی بپرس آیا واقعاً لازم است مقدار Dimension را داخل Fact تکرار کنیم یا همان Relationship برای تحلیل کافی است.

۵.۲۰. Context و Relationship با هم کار می‌کنند

اگر DimBranch روی Slicer باشد، Relationship فیلتر را به FactSales منتقل می‌کند و Measure روی ردیف‌های باقی‌مانده محاسبه می‌شود.

Slicer: DimBranch
       ↓
Relationship
       ↓
FactSales filtered
       ↓
[Total Sales]

به همین دلیل مدل‌سازی خوب، DAX را ساده‌تر می‌کند.

۵.۲۱. Context Transition؛ آشنایی اولیه

CALCULATE علاوه بر تغییر Filter Context، مفهوم مهمی به نام Context Transition هم دارد؛ یعنی در شرایط مناسب Row Context را به Filter Context تبدیل می‌کند.

فعلاً لازم نیست وارد تمام جزئیات نظری شویم. همین را بدان که CALCULATE فقط یک تابع فیلتر ساده نیست و رفتار Context در DAX را عمیقاً تغییر می‌دهد.

۵.۲۲. USERELATIONSHIP؛ استفاده از رابطه غیرفعال

در فصل قبل OrderDate و DeliveryDate را به یاد بیاور. اگر رابطه DeliveryDate غیرفعال باشد، در یک Measure خاص می‌توانیم آن را فعال کنیم.

Sales by Delivery Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        FactSales[DeliveryDate],
        DimDate[Date]
    )
)

این الگو اجازه می‌دهد با یک Date Dimension چند نقش زمانی را مدیریت کنیم.

۵.۲۳. هدف فروش و Achievement

Sales Target = SUM(FactTarget[TargetAmount])

Achievement % =
DIVIDE(
    [Total Sales],
    [Sales Target],
    0
)

حالا می‌توانیم فروش واقعی را با هدف مقایسه کنیم. اما FactTarget باید Grain و Relationshipهای درستی داشته باشد؛ DAX نمی‌تواند مدل اشتباه را جادو کند.

۵.۲۴. یک KPI کامل برای مدیر فروش

این مجموعه از Measureها پایه یک صفحه مدیریتی بسیار بهتر از ده‌ها عدد پراکنده است.

۵.۲۵. خطاهای رایج DAX

خطای اول: بدون فهم Grain از COUNT استفاده می‌کنیم و تعداد سفارش را اشتباه می‌شماریم.

خطای دوم: برای هر محاسبه Calculated Column می‌سازیم.

خطای سوم: CALCULATE را حفظ می‌کنیم ولی Filter Context را نمی‌فهمیم.

خطای چهارم: منطق تکراری را در چند Measure کپی می‌کنیم و نگهداری سخت می‌شود.

خطای پنجم: فرمول پیچیده می‌نویسیم تا مشکل مدل داده را پنهان کنیم.

خطای ششم: نتیجه BLANK یا تقسیم بر صفر را از قبل طراحی نمی‌کنیم.

۵.۲۶. Best Practiceهای DAX

Measureها را با نام قابل‌فهم بساز.

Measure پایه بساز و Measureهای پیچیده‌تر را از آن‌ها ترکیب کن.

از DIVIDE برای نسبت‌ها استفاده کن.

برای Expressionهای طولانی از VAR استفاده کن.

قبل از نوشتن DAX، سؤال کسب‌وکار را به زبان ساده بنویس.

اگر DAX غیرعادی پیچیده شد، اول مدل داده را دوباره بررسی کن.

Measure را در Contextهای مختلف تست کن؛ فقط Card کل شرکت کافی نیست.

۵.۲۷. تمرین پایه

Measureهای Total Sales، Total Quantity، Order Count و Average Order Value را برای مدل شرکت هانا بساز.

هر Measure را یک بار روی Card و یک بار در جدول کنار Branch قرار بده و تفاوت نتیجه را توضیح بده.

۵.۲۸. تمرین متوسط

Measureهای Total Cost، Profit و Profit Margin % را بساز. سپس Slicer محصول و شعبه اضافه کن و بررسی کن Profit Margin در Contextهای مختلف چه تغییری می‌کند.

۵.۲۹. تمرین کاربردی

یک Measure برای فروش تهران با CALCULATE بساز. سپس یک Measure دیگر بساز که سهم فروش Context جاری از کل فروش شرکت را نشان دهد.

نتیجه را در Matrix بر اساس Branch بررسی کن.

۵.۳۰. تمرین چالشی

در FactSales دو تاریخ OrderDate و DeliveryDate داشته باش. فروش عادی را بر اساس OrderDate و یک Measure جدا برای Sales by Delivery Date بساز.

نتایج را کنار هم قرار بده و توضیح بده چرا ممکن است در یک ماه متفاوت باشند.

۵.۳۱. پروژه نهایی فصل؛ موتور KPI شرکت هانا

برای داشبورد مدیریتی شرکت هانا یک لایه Measure منظم بساز. حداقل Measureهای زیر را پیاده کن:

بعد هر Measure را در حداقل دو Context مختلف تست کن. اگر عددی عجیب بود، قبل از تغییر فرمول Relationship، Grain و فیلترهای گزارش را بررسی کن.

جمع‌بندی فصل پنجم

در این فصل از چند تابع ساده عبور کردیم و به اصل ماجرای DAX رسیدیم: Context. فهمیدیم یک Measure ثابت می‌تواند با تغییر فیلتر جواب‌های مختلف و درست بدهد.

Good Model
   +
Filter Context
   +
Reusable Measures
   +
CALCULATE
   ↓
Useful Business Analysis

در فصل بعد سراغ تحلیل زمانی می‌رویم: فروش ماه جاری، ماه قبل، سال قبل، YTD، MTD، QTD، رشد دوره‌ای و Rolling Average. آنجا Date Table فصل چهارم و CALCULATE این فصل واقعاً کنار هم قرار می‌گیرند.