شروع حرفه‌ای DAX؛ Measure، Calculated Column، Row Context، Filter Context و CALCULATE

شروع جلسه؛ DAX فقط فرمول نیست

از اینجا وارد بخشی می‌شویم که Power BI واقعاً قدرت تحلیلی خودش را نشان می‌دهد. DAX از دور شبیه فرمول‌های Excel است، ولی اگر با ذهنیت Excel سراغش برویم خیلی زود گیج می‌شویم.

کلید فهم DAX این است که بفهمیم یک فرمول «در چه Contextی» محاسبه می‌شود.

۳۱.۱. DAX چیست؟

DAX مخفف Data Analysis Expressions است و برای ساخت Measure، Calculated Column و بعضی اشیای محاسباتی مدل استفاده می‌شود.

Total Sales = SUM(FactSales[SalesAmount])

۳۱.۲. Measure چیست؟

Measure یک محاسبه پویاست که با توجه به Filter Context همان لحظه محاسبه می‌شود.

اگر کاربر شعبه تهران را انتخاب کند، همان Measure فروش تهران را می‌دهد؛ اگر سال ۱۴۰۵ را انتخاب کند، نتیجه در Context سال ۱۴۰۵ محاسبه می‌شود.

۳۱.۳. اولین Measure فروش

Total Sales =
SUM(FactSales[SalesAmount])

۳۱.۴. Measure را کجا نگه داریم؟

Measure از نظر فنی متعلق به یک Table است. برای نظم مدل می‌توان Measureها را در جدول مشخص و Display Folderهای منطقی سازمان‌دهی کرد.

۳۱.۵. Calculated Column

Calculated Column برای هر Row جدول یک مقدار ایجاد می‌کند و نتیجه در مدل ذخیره می‌شود.

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

۳۱.۶. Measure و Calculated Column یکی نیستند

۳۱.۷. چه زمانی Measure؟

Total Sales، Profit Margin، YTD، Average Order Value و KPIهای تحلیلی معمولاً Measure هستند.

۳۱.۸. چه زمانی Column؟

اگر برای هر Row نیاز به Classification یا مقداری داری که واقعاً ویژگی همان Row است، Calculated Column می‌تواند مناسب باشد؛ البته گاهی بهتر است این کار در Power Query انجام شود.

۳۱.۹. Filter Context

Filter Context مجموعه فیلترهایی است که هنگام ارزیابی Measure فعال‌اند؛ از Slicer، محور Visual، Filter Pane، Relationship و خود DAX می‌آیند.

۳۱.۱۰. مثال Filter Context

اگر Matrix ردیف‌های Branch داشته باشد، Total Sales برای هر Branch در Context همان شعبه محاسبه می‌شود؛ فرمول Measure عوض نشده، Context عوض شده است.

۳۱.۱۱. Row Context

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

۳۱.۱۲. مثال Row Context

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

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

۳۱.۱۳. Iteratorها هم Row Context می‌سازند

Total Sales X =
SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPrice]
)

۳۱.۱۴. SUM و SUMX

۳۱.۱۵. Filter Context با Relationship

وقتی DimProduct[Category] را فیلتر می‌کنی، Relationship فیلتر را به FactSales منتقل می‌کند و Measure فقط ردیف‌های مرتبط را می‌بیند.

۳۱.۱۶. چرا Star Schema DAX را ساده می‌کند؟

چون مسیر فیلتر واضح است: Dimensionها Fact را فیلتر می‌کنند. اگر مدل Relationshipهای مبهم داشته باشد، DAX هم سخت‌تر می‌شود.

۳۱.۱۷. CALCULATE؛ قلب DAX

CALCULATE یک Expression را در Filter Context تغییر‌یافته ارزیابی می‌کند.

Sales Tehran =
CALCULATE(
    [Total Sales],
    DimBranch[City] = "Tehran"
)

۳۱.۱۸. مدل ذهنی CALCULATE

۳۱.۱۹. Expression اول

آرگومان اول CALCULATE چیزی است که می‌خواهیم محاسبه کنیم؛ معمولاً یک Measure موجود.

CALCULATE([Total Sales], ...)

۳۱.۲۰. Filter Argument

آرگومان‌های بعدی مشخص می‌کنند Context چطور تغییر کند.

DimProduct[Category] = "Laptop"

۳۱.۲۱. Measure پایه بساز

بهتر است اول Measureهای پایه مثل Total Sales، Total Cost و Total Quantity بسازی و Measureهای پیچیده‌تر را از آن‌ها ترکیب کنی.

۳۱.۲۲. Measure Branching

Total Sales = SUM(FactSales[SalesAmount])
Total Cost = SUM(FactSales[CostAmount])
Profit = [Total Sales] - [Total Cost]
Profit % = DIVIDE([Profit], [Total Sales])

۳۱.۲۳. DIVIDE بهتر از /

DIVIDE امکان مدیریت بهتر تقسیم بر صفر یا Blank را فراهم می‌کند.

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

۳۱.۲۴. COUNTROWS

Sales Lines = COUNTROWS(FactSales)

این Measure تعداد Rowهای Fact را می‌شمارد؛ معنی کسب‌وکاری آن وابسته به Grain Fact است.

۳۱.۲۵. DISTINCTCOUNT

Order Count =
DISTINCTCOUNT(FactSales[OrderNo])

اگر Grain خط سفارش است، برای تعداد سفارش احتمالاً DISTINCTCOUNT شماره سفارش معنی‌دارتر از COUNTROWS است.

۳۱.۲۶. Average Order Value

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

۳۱.۲۷. Blank

در DAX مفهوم BLANK مهم است. Blank همیشه معادل صفر نیست و در Visual رفتار متفاوتی می‌تواند داشته باشد.

۳۱.۲۸. COALESCE

Sales Display =
COALESCE([Total Sales], 0)

فقط وقتی Blank را صفر کن که از نظر کسب‌وکار نمایش صفر درست باشد.

۳۱.۲۹. Variables

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

۳۱.۳۰. چرا VAR؟

Variable کد را خواناتر می‌کند، تکرار Expression را کم می‌کند و Debug فرمول‌های پیچیده را ساده‌تر می‌کند.

۳۱.۳۱. نام Variable

اسم‌هایی مثل Sales، CurrentYearSales و PreviousYearSales بهتر از x و a هستند؛ DAX باید مثل یک داستان قابل خواندن باشد.

۳۱.۳۲. REMOVEFILTERS

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

۳۱.۳۳. درصد از کل

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

۳۱.۳۴. چرا درصد از کل در هر Row فرق دارد؟

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

۳۱.۳۵. KEEPFILTERS

KEEPFILTERS در سناریوهایی کمک می‌کند Filter جدید به جای جایگزینی ساده، با Filter موجود تقاطع پیدا کند. استفاده‌اش را وقتی Context را خوب فهمیدی انجام بده.

۳۱.۳۶. ALL

ALL هم برای حذف Filter و سناریوهای خاص استفاده می‌شود. برای خوانایی، در بسیاری از موارد REMOVEFILTERS هدف حذف فیلتر را واضح‌تر بیان می‌کند.

۳۱.۳۷. FILTER

High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        FactSales,
        FactSales[SalesAmount] > 10000000
    )
)

۳۱.۳۸. FILTER یک Table برمی‌گرداند

FILTER روی یک Table حرکت می‌کند و Table فیلترشده برمی‌گرداند. استفاده بی‌دلیل روی Fact بزرگ می‌تواند هزینه داشته باشد.

۳۱.۳۹. Boolean Filter ساده‌تر

اگر شرط ساده روی یک ستون است، معمولاً Filter Argument مستقیم خواناتر است.

CALCULATE([Total Sales], FactSales[SalesAmount] > 10000000)

۳۱.۴۰. Context Transition

یکی از مفاهیم مهم DAX این است که CALCULATE می‌تواند Row Context موجود را به Filter Context تبدیل کند. این موضوع در Calculated Column و Iteratorهای پیشرفته اهمیت زیادی دارد.

۳۱.۴۱. RELATED

Product Category =
RELATED(DimProduct[Category])

در Calculated Column و با Relationship مناسب می‌توان مقدار سمت One را به Row سمت Many آورد؛ ولی قبل از اضافه کردن ستون تکراری به Fact، نیاز واقعی را بررسی کن.

۳۱.۴۲. RELATEDTABLE

RELATEDTABLE مجموعه Rowهای مرتبط سمت Many را برمی‌گرداند و در بعضی محاسبات Row Context کاربرد دارد.

۳۱.۴۳. SELECTEDVALUE

Selected Branch =
SELECTEDVALUE(DimBranch[BranchName], "Multiple")

وقتی دقیقاً یک مقدار در Context باشد آن را برمی‌گرداند و در غیر این صورت مقدار جایگزین می‌دهد.

۳۱.۴۴. HASONEVALUE

برای تشخیص اینکه Context فقط یک مقدار از یک ستون دارد یا نه کاربرد دارد؛ مخصوصاً در Measureهای نمایشی.

۳۱.۴۵. ISFILTERED

می‌تواند مشخص کند ستونی مستقیماً Filter شده یا نه. برای رفتارهای UI محور گاهی مفید است.

۳۱.۴۶. Measure شرکت هانا

Total Sales = SUM(FactSales[SalesAmount])
Total Qty = SUM(FactSales[Quantity])
Order Count = DISTINCTCOUNT(FactSales[OrderNo])
Avg Order Value = DIVIDE([Total Sales], [Order Count])

۳۱.۴۷. فروش فقط یک Region

North Sales =
CALCULATE(
    [Total Sales],
    DimBranch[Region] = "North"
)

۳۱.۴۸. فروش محصولات فعال

Active Product Sales =
CALCULATE(
    [Total Sales],
    DimProduct[IsActive] = TRUE()
)

۳۱.۴۹. KPI Profit

Total Cost = SUM(FactSales[CostAmount])
Profit = [Total Sales] - [Total Cost]
Profit Margin = DIVIDE([Profit], [Total Sales])

۳۱.۵۰. Format Measure

Amount، Percentage و Count باید Format مناسب داشته باشند. Format بخشی از تجربه مصرف گزارش است، نه تزئین آخر کار.

۳۱.۵۱. Measure Name

۳۱.۵۲. Display Folder

Measureها را می‌توان در پوشه‌هایی مثل Sales، Profitability، Customer و Time Intelligence مرتب کرد تا مدل برای گزارش‌ساز قابل استفاده‌تر شود.

۳۱.۵۳. Explicit Measure

برای مدل حرفه‌ای بهتر است KPIهای مهم را به صورت Measure صریح تعریف کنی و صرفاً به Sum خودکار ستون‌ها متکی نباشی.

۳۱.۵۴. Implicit Measure

کشیدن یک ستون عددی داخل Visual ممکن است Power BI آن را خودکار Sum کند. این برای کار سریع خوب است، ولی کنترل و استانداردسازی Measureهای صریح بیشتر است.

۳۱.۵۵. Home Table

Home Table Measure را منطقی انتخاب کن تا Field List مرتب بماند. در پروژه تیمی Convention مشترک داشته باشید.

۳۱.۵۶. Measure و Grain

هر Measure باید با Grain Fact سازگار باشد. Average روی Rowهای Fact همیشه معادل Average کسب‌وکاری نیست.

۳۱.۵۷. Average Price اشتباه

Wrong Avg = AVERAGE(FactSales[UnitPrice])

اگر Quantityها متفاوت باشند، Average ساده قیمت شاید KPI موردنظر نباشد.

۳۱.۵۸. Weighted Average

Weighted Avg Price =
DIVIDE(
    SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice]),
    [Total Qty]
)

۳۱.۵۹. Total Row عجیب نیست

در DAX Total معمولاً «جمع Rowهای قابل مشاهده» نیست؛ Measure دوباره در Context Total ارزیابی می‌شود. این تفاوت در Ratioها و Measureهای غیرجمع‌پذیر مهم است.

۳۱.۶۰. مثال Margin Total

اگر هر محصول Margin درصدی دارد، جمع کردن درصدهای Rowها اشتباه است. Total Margin باید از Total Profit تقسیم بر Total Sales دوباره محاسبه شود.

۳۱.۶۱. Debug Measure

Measure پیچیده را به Measureهای کوچک‌تر بشکن. ابتدا Total Sales را تست کن، بعد Filter، بعد Ratio و در نهایت Measure نهایی.

۳۱.۶۲. Table Visual برای Debug

یک Table ساده با Keyها و Measureها گاهی بهترین ابزار برای فهم Context است. قبل از Dashboard زیبا، محاسبه را در Visual ساده Validate کن.

۳۱.۶۳. DAX Studio

در مراحل پیشرفته‌تر ابزارهایی مثل DAX Studio برای بررسی Query و Performance مفیدند. فعلاً تمرکز ما روی درست بودن مدل ذهنی Context است.

۳۱.۶۴. خطای رایج؛ Measure به جای Column

اگر واقعاً به مقدار ثابت Row-level برای Sort، Relationship یا Category نیاز داری، Measure جای آن را نمی‌گیرد.

۳۱.۶۵. خطای رایج؛ Column به جای Measure

ساخت Total Sales به صورت Calculated Column و بعد Sum کردنش می‌تواند غیرضروری باشد. KPI پویا را معمولاً Measure بساز.

۳۱.۶۶. خطای رایج؛ CALCULATE بدون فهم Context

حفظ کردن Syntax کافی نیست. هر بار بپرس: Context فعلی چیست؟ CALCULATE کدام Filter را اضافه، حذف یا تغییر می‌دهد؟

۳۱.۶۷. خطای رایج؛ SUMX همه‌جا

Iteratorها قدرتمندند ولی اگر SUM ساده جواب می‌دهد، بی‌دلیل محاسبه Row-by-Row نساز.

۳۱.۶۸. خطای رایج؛ صفر کردن همه Blankها

Blank گاهی معنی «داده‌ای وجود ندارد» دارد. صفر کردنش ممکن است پیام کسب‌وکار را تغییر دهد.

۳۱.۶۹. Best Practiceها

Measureهای پایه را اول بساز.

Measure Branching استفاده کن.

VAR برای خوانایی فرمول‌های پیچیده به کار ببر.

DIVIDE را برای Ratioها ترجیح بده.

Grain Fact را هنگام Count/Average فراموش نکن.

Filter Context را قبل از CALCULATE تحلیل کن.

Calculated Column را فقط وقتی واقعاً Row-level نیاز داری بساز.

Totalها را جدا Validate کن.

۳۱.۷۰. تمرین پایه

Measureهای Total Sales، Total Quantity، Order Count و Average Order Value را برای FactSales بساز.

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

یک Matrix با Category روی Row و Total Sales در Values بساز. توضیح بده چرا یک Measure واحد برای هر Category عدد متفاوت نشان می‌دهد.

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

Measure فروش فقط Region شمال را بساز و بعد آن را در Visualی که Branch روی Row است بررسی کن.

۳۱.۷۳. تمرین درصد از کل

Sales % of Total را طوری بساز که Filter محصول از مخرج حذف شود ولی Filter تاریخ و شعبه حفظ شوند.

۳۱.۷۴. تمرین Weighted Average

با Quantity و UnitPrice یک Weighted Average Price بساز و آن را با AVERAGE(UnitPrice) مقایسه کن.

۳۱.۷۵. تمرین Total

Profit Margin را برای Productها و Total بررسی کن. توضیح بده چرا Total نباید جمع درصدهای محصولات باشد.

۳۱.۷۶. پروژه Session؛ KPI Layer شرکت هانا

در پایان یک صفحه تست بساز که Date، Branch، Product و Customer به عنوان فیلتر داشته باشد و رفتار همه Measureها را در Contextهای مختلف کنترل کنی.

جمع‌بندی Session 31

سه مفهوم این جلسه پایه تقریباً تمام DAXهای بعدی‌اند: Measure، Filter Context و CALCULATE. اگر این سه را واقعاً بفهمی، Time Intelligence و DAX پیشرفته دیگر مجموعه‌ای از فرمول‌های حفظی نیست.

Measure + Context + CALCULATE = Core DAX thinking

Session 32 را روی CALCULATE پیشرفته، FILTER، ALL/REMOVEFILTERS، KEEPFILTERS، Context Transition و طراحی Measureهای تحلیلی چندلایه ادامه می‌دهیم.