فصل 36 از 50 درس 1 از 1

Session 36 — Advanced DAX Table Functions؛ Virtual Tables، SUMMARIZE، ADDCOLUMNS و GROUPBY

Advanced DAX Table Functions؛ Virtual Tables، SUMMARIZE، ADDCOLUMNS و GROUPBY

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

تا اینجا بیشتر Measureهایی ساختیم که در نهایت یک عدد برمی‌گرداندند. ولی پشت خیلی از DAXهای حرفه‌ای، اول یک جدول مجازی ساخته می‌شود، روی آن ستون اضافه می‌کنیم، فیلترش می‌کنیم و بعد از دل آن KPI نهایی را درمی‌آوریم.

اگر Virtual Table را بفهمی، Ranking، Segmentation، Cohort، Pareto و خیلی از محاسبات پیچیده دیگر قابل فهم‌تر می‌شوند.

۳۶.۱. Table Expression چیست؟

در DAX بعضی Functionها به جای Scalar یک Table برمی‌گردانند. این Table می‌تواند موقت باشد و فقط هنگام محاسبه وجود داشته باشد.

VALUES(DimCustomer[CustomerKey])

۳۶.۲. Virtual Table

Virtual Table جدول فیزیکی جدیدی در Model View نیست؛ یک نتیجه جدولی موقت در جریان ارزیابی DAX است.

شکل ۳۶-۱ — از Context فعلی یک Virtual Table می‌سازیم و سپس روی آن محاسبه می‌کنیم

۳۶.۳. VALUES

VALUES(DimProduct[ProductKey])

لیست مقادیر متمایز موجود در Context را برمی‌گرداند.

۳۶.۴. DISTINCT

DISTINCT(DimProduct[ProductKey])

برای مقادیر متمایز کاربرد دارد؛ تفاوت‌های ظریف VALUES و DISTINCT در Blank/Relationship را در سناریوهای واقعی باید در نظر گرفت.

۳۶.۵. FILTER

FILTER(
    VALUES(DimCustomer[CustomerKey]),
    [Total Sales] > 100000000
)

یک Table می‌گیرد و فقط Rowهایی را برمی‌گرداند که شرط برایشان True باشد.

۳۶.۶. COUNTROWS روی Virtual Table

High Value Customers =
COUNTROWS(
    FILTER(
        VALUES(DimCustomer[CustomerKey]),
        [Total Sales] > 100000000
    )
)

۳۶.۷. ADDCOLUMNS

ADDCOLUMNS به یک Table Expression ستون محاسباتی موقت اضافه می‌کند.

ADDCOLUMNS(
    VALUES(DimCustomer[CustomerKey]),
    "Sales", [Total Sales],
    "Profit", [Profit]
)

۳۶.۸. جدول مشتری با KPI

الگوی ADDCOLUMNS برای ساخت Virtual Table از Entityها همراه Measureهایشان فوق‌العاده کاربردی است.

۳۶.۹. مثال Average مشتری‌های فعال

Avg Active Customer Sales =
VAR T =
    FILTER(
        ADDCOLUMNS(
            VALUES(DimCustomer[CustomerKey]),
            "CustomerSales", [Total Sales]
        ),
        [CustomerSales] > 0
    )
RETURN
    AVERAGEX(T, [CustomerSales])

۳۶.۱۰. SELECTCOLUMNS

SELECTCOLUMNS یک Table جدید با ستون‌هایی که خودت تعریف می‌کنی می‌سازد.

SELECTCOLUMNS(
    DimProduct,
    "ProductId", DimProduct[ProductKey],
    "Name", DimProduct[ProductName]
)

۳۶.۱۱. ADDCOLUMNS vs SELECTCOLUMNS

ADDCOLUMNS	SELECTCOLUMNS
ستون‌های Table ورودی را نگه می‌دارد	از ستون‌های انتخابی/تعریفی خروجی می‌سازد
برای enrich کردن Table	برای projection/shape کردن Table

۳۶.۱۲. SUMMARIZE

SUMMARIZE(
    FactSales,
    DimBranch[Region],
    DimProduct[Category]
)

برای Grouping و ساخت مجموعه‌ای از ترکیب‌های متمایز استفاده می‌شود.

۳۶.۱۳. SUMMARIZE با Expression

SUMMARIZE(
    FactSales,
    DimBranch[Region],
    "Sales", [Total Sales]
)

امکان اضافه کردن Expression وجود دارد، ولی در طراحی Measureهای مدرن معمولاً باید تفاوت آن با الگوهای ADDCOLUMNS و SUMMARIZECOLUMNS را خوب بفهمیم.

۳۶.۱۴. SUMMARIZECOLUMNS

SUMMARIZECOLUMNS(
    DimBranch[Region],
    DimProduct[Category],
    "Sales", [Total Sales],
    "Profit", [Profit]
)

۳۶.۱۵. SUMMARIZECOLUMNS چه می‌کند؟

یک Table خلاصه‌شده بر اساس GroupBy Columns و Expressionهای Measure می‌سازد و در Queryهای DAX و بعضی محاسبات تحلیلی بسیار مهم است.

۳۶.۱۶. SUMMARIZE یا SUMMARIZECOLUMNS؟

هیچ پاسخ یک‌خطی برای همه سناریوها وجود ندارد. SUMMARIZECOLUMNS برای Queryهای گروه‌بندی‌شده و Measureها بسیار رایج است؛ SUMMARIZE هم در الگوهای خاص کاربرد دارد. Context و هدف Table Expression تعیین‌کننده‌اند.

۳۶.۱۷. GROUPBY

GROUPBY(
    FactSales,
    FactSales[ProductKey]
)

۳۶.۱۸. CURRENTGROUP

GROUPBY همراه CURRENTGROUP برای Aggregation روی گروه فعلی کاربرد دارد.

GROUPBY(
    FactSales,
    FactSales[ProductKey],
    "Qty", SUMX(CURRENTGROUP(), FactSales[Quantity])
)

۳۶.۱۹. GROUPBY چه زمانی؟

وقتی واقعاً می‌خواهی روی Rowهای Table ورودی گروه‌بندی و Aggregation خاص انجام دهی. برای بسیاری از گزارش‌های معمول Measure + Dimension Model ساده‌تر است.

۳۶.۲۰. CROSSJOIN

CROSSJOIN(
    VALUES(DimBranch[Region]),
    VALUES(DimProduct[Category])
)

تمام ترکیب‌های دو مجموعه را می‌سازد؛ باید مراقب انفجار تعداد Rowها باشی.

۳۶.۲۱. Cartesian Explosion

اگر ۱۰۰۰ مشتری را با ۵۰۰ محصول CROSSJOIN کنی، ۵۰۰ هزار ترکیب می‌سازی؛ حتی اگر بخش بزرگی هیچ تراکنشی نداشته باشد.

۳۶.۲۲. UNION

UNION(TableA, TableB)

Rowهای چند Table سازگار را زیر هم قرار می‌دهد.

۳۶.۲۳. INTERSECT

INTERSECT(TableA, TableB)

اعضای مشترک دو مجموعه را برمی‌گرداند.

۳۶.۲۴. EXCEPT

EXCEPT(TableA, TableB)

اعضایی از TableA را برمی‌گرداند که در TableB نیستند.

۳۶.۲۵. مشتری جدید با EXCEPT

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

۳۶.۲۶. مشتریان جاری

VAR CurrentCustomers =
    CALCULATETABLE(
        VALUES(DimCustomer[CustomerKey]),
        /* current period */
    )

۳۶.۲۷. مشتریان دوره قبل

VAR PreviousCustomers =
    CALCULATETABLE(
        VALUES(DimCustomer[CustomerKey]),
        DATEADD(DimDate[Date], -1, YEAR)
    )

۳۶.۲۸. New Customers Set

VAR NewCustomers =
    EXCEPT(CurrentCustomers, PreviousCustomers)

۳۶.۲۹. شمارش مشتری جدید

RETURN COUNTROWS(NewCustomers)

۳۶.۳۰. مشتریان Retained

VAR RetainedCustomers =
    INTERSECT(CurrentCustomers, PreviousCustomers)
RETURN
    COUNTROWS(RetainedCustomers)

۳۶.۳۱. Lost Customers

VAR LostCustomers =
    EXCEPT(PreviousCustomers, CurrentCustomers)
RETURN
    COUNTROWS(LostCustomers)

۳۶.۳۲. اینجا Table Thinking مهم است

به جای اینکه دنبال یک Function جادویی برای New/Lost Customer بگردیم، مسئله را به عملیات روی مجموعه‌ها تبدیل کردیم.

۳۶.۳۳. GENERATE

GENERATE برای هر Row از Table اول، Table Expression دوم را ارزیابی و نتایج را ترکیب می‌کند. ابزار قدرتمندی است ولی باید با Cardinality و Context محتاط بود.

۳۶.۳۴. TOPN

TOPN(
    10,
    ADDCOLUMNS(
        VALUES(DimProduct[ProductKey]),
        "Sales", [Total Sales]
    ),
    [Sales], DESC
)

۳۶.۳۵. TOPN + SUMX

Top 10 Sales =
VAR T =
    TOPN(
        10,
        ADDCOLUMNS(
            VALUES(DimProduct[ProductKey]),
            "Sales", [Total Sales]
        ),
        [Sales], DESC
    )
RETURN
    SUMX(T, [Sales])

۳۶.۳۶. Pipeline حرفه‌ای

شکل ۳۶-۲ — یک الگوی رایج: مجموعه → ستون KPI → Filter/TopN → Iterator → نتیجه

۳۶.۳۷. Iterator روی Virtual Table

SUMX، AVERAGEX، MINX و MAXX می‌توانند روی Tableهای مجازی کار کنند.

۳۶.۳۸. MAXX نمونه

Best Customer Sales =
MAXX(
    VALUES(DimCustomer[CustomerKey]),
    [Total Sales]
)

۳۶.۳۹. MINX نمونه

Lowest Active Customer Sales =
MINX(
    FILTER(
        VALUES(DimCustomer[CustomerKey]),
        [Total Sales] > 0
    ),
    [Total Sales]
)

۳۶.۴۰. CONCATENATEX

Top Product Names =
CONCATENATEX(
    TOPN(
        5,
        VALUES(DimProduct[ProductName]),
        [Total Sales], DESC
    ),
    DimProduct[ProductName],
    "، "
)

۳۶.۴۱. کاربرد CONCATENATEX

برای Tooltip، عنوان Dynamic یا نمایش فهرست کوتاه اعضای منتخب کاربردی است؛ نه برای نمایش هزاران مقدار.

۳۶.۴۲. NATURALINNERJOIN

برای Join کردن Table Expressionهایی که ستون مشترک سازگار دارند استفاده می‌شود. در Measureهای روزمره کمتر از مدل Relationship استاندارد استفاده می‌شود.

۳۶.۴۳. NATURALLEFTOUTERJOIN

مشابه Left Outer Join روی Table Expressionهاست؛ ابزار پیشرفته‌ای که باید ساختار Lineage ستون‌ها را بشناسی.

۳۶.۴۴. TREATAS و Virtual Table

CALCULATE(
    [Total Sales],
    TREATAS(
        SelectedRegions,
        DimBranch[Region]
    )
)

مقادیر یک Table Expression را به Filter روی ستون مقصد تبدیل می‌کند.

۳۶.۴۵. Disconnected Table

Tableهای بدون Relationship برای Parameter، Scenario و Selection بسیار کاربردی‌اند و TREATAS می‌تواند انتخاب آن‌ها را به مدل اعمال کند.

۳۶.۴۶. Table Variable

VAR CustomerTable =
    ADDCOLUMNS(
        VALUES(DimCustomer[CustomerKey]),
        "Sales", [Total Sales],
        "Profit", [Profit]
    )
RETURN ...

Table Variable باعث می‌شود منطق پیچیده مرحله‌بندی و قابل خواندن شود.

۳۶.۴۷. Debug Virtual Table

در Measure مستقیماً Table را نمی‌توان در Card نمایش داد. برای Debug می‌توان COUNTROWS، CONCATENATEX، MINX/MAXX یا DAX Query ابزارهای مناسب را استفاده کرد.

۳۶.۴۸. DAX Query View

برای بررسی Table Expressionها، DAX Query View و ابزارهای تخصصی مثل DAX Studio در مراحل حرفه‌ای بسیار مفیدند.

۳۶.۴۹. EVALUATE

EVALUATE
SUMMARIZECOLUMNS(
    DimBranch[Region],
    "Sales", [Total Sales]
)

EVALUATE در DAX Query برای برگرداندن Table Result استفاده می‌شود؛ داخل تعریف Measure به این شکل استفاده نمی‌شود.

۳۶.۵۰. ORDER BY در Query

EVALUATE
SUMMARIZECOLUMNS(
    DimProduct[Category],
    "Sales", [Total Sales]
)
ORDER BY [Sales] DESC

۳۶.۵۱. DEFINE MEASURE در Query

در DAX Query می‌توان Measure موقت برای تست تعریف کرد و بدون تغییر دائمی مدل رفتار آن را بررسی کرد.

۳۶.۵۲. Customer Cohort Preview

Virtual Table Thinking پایه Cohort Analysis است: ابتدا مجموعه مشتریان Acquisition Period را پیدا می‌کنیم، بعد رفتارشان را در Periodهای بعدی اندازه می‌گیریم.

۳۶.۵۳. Product Basket Preview

برای Basket Analysis هم مجموعه سفارش‌ها، محصولات و هم‌رخدادی‌ها مهم‌اند. این موضوع را در فصل‌های تحلیلی پیشرفته‌تر توسعه می‌دهیم.

۳۶.۵۴. New Customer Sales

New Customer Sales =
VAR CurrentCustomers = VALUES(DimCustomer[CustomerKey])
VAR PreviousCustomers =
    CALCULATETABLE(
        VALUES(DimCustomer[CustomerKey]),
        DATEADD(DimDate[Date], -1, YEAR)
    )
VAR NewCustomers = EXCEPT(CurrentCustomers, PreviousCustomers)
RETURN
    CALCULATE(
        [Total Sales],
        TREATAS(NewCustomers, DimCustomer[CustomerKey])
    )

۳۶.۵۵. مفهوم Lineage

Tableهای DAX فقط مقدار ندارند؛ ارتباط ستون‌ها با مدل یا Data Lineage در رفتار Filter و Joinها مهم است. این موضوع در DAX خیلی پیشرفته اهمیت بیشتری پیدا می‌کند.

۳۶.۵۶. SUMMARIZE برای ساخت Table فیزیکی؟

Calculated Table می‌تواند با SUMMARIZE ساخته شود، اما قبل از ساخت Table فیزیکی جدید بررسی کن آیا همان نیاز با Measure/Virtual Table حل می‌شود یا نه.

۳۶.۵۷. Calculated Table

ProductSummary =
SUMMARIZECOLUMNS(
    DimProduct[Category],
    "Sales", [Total Sales]
)

۳۶.۵۸. Calculated Table چه زمانی محاسبه می‌شود؟

Calculated Table در Refresh مدل محاسبه و ذخیره می‌شود؛ مثل Measure با هر Interaction کاربر دوباره شکل نمی‌گیرد.

۳۶.۵۹. Virtual vs Calculated Table

Virtual Table	Calculated Table
موقت در Query/Measure	در مدل ذخیره می‌شود
Context-aware	در Refresh محاسبه
حافظه دائمی اضافه نمی‌کند	حافظه مدل مصرف می‌کند

۳۶.۶۰. Grouping را کجا انجام دهیم؟

اگر Aggregation ثابت و بخشی از Data Pipeline است، SQL/Power Query/Data Warehouse را بررسی کن. اگر تحلیل به Context کاربر وابسته است، DAX Table Expression ارزش بیشتری دارد.

۳۶.۶۱. Performance؛ Materialization

Virtual Table بزرگ ممکن است در زمان Query هزینه داشته باشد. «مجازی» به معنی «رایگان» نیست.

۳۶.۶۲. Performance؛ Cardinality

CROSSJOIN، ADDCOLUMNS و FILTER روی مجموعه‌های بسیار بزرگ می‌توانند تعداد ارزیابی‌ها را بالا ببرند.

۳۶.۶۳. Performance؛ Filter early

تا جای ممکن مجموعه‌ای که واقعاً لازم داری بساز. اگر فقط Customerهای فعال لازم‌اند، قبل از Iterator مجموعه را محدود کن.

۳۶.۶۴. Performance؛ Measure inside iterator

اگر Measure پیچیده را برای صدها هزار Row داخل SUMX/RANKX ارزیابی کنی، هزینه زیاد می‌شود. ابتدا Grain و الگوریتم را بازبینی کن.

۳۶.۶۵. خطای رایج؛ ساخت Table غول‌آسا

Virtual Table را صرفاً چون می‌توانی نساز. هر Row اضافی یعنی محاسبه بیشتر.

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

Grouping درست به معنی Measure درست نیست. باید بدانیم Expression در چه Filter Contextی ارزیابی می‌شود.

۳۶.۶۷. خطای رایج؛ CROSSJOIN برای پر کردن داده

ساخت همه ترکیب‌های ممکن Product×Customer معمولاً راه مناسبی برای «صفرهای گمشده» نیست.

۳۶.۶۸. خطای رایج؛ Calculated Table برای KPI پویا

اگر انتظار داری Table با Slicer کاربر تغییر کند، Calculated Table ذخیره‌شده ابزار مناسبی نیست.

۳۶.۶۹. خطای رایج؛ Set Analysis بدون تعریف دوره

New/Lost Customer بدون تعریف دقیق Current Period و Comparison Period می‌تواند عدد ظاهراً درست ولی از نظر کسب‌وکار مبهم بدهد.

۳۶.۷۰. پروژه شرکت هانا؛ Customer Movement

KPI	Table Function
New Customers	EXCEPT
Retained Customers	INTERSECT
Lost Customers	EXCEPT reverse
High Value	FILTER + VALUES
Top Customers	TOPN
Customer KPI Table	ADDCOLUMNS

۳۶.۷۱. پروژه شرکت هانا؛ Product Intelligence

تحلیل	الگو
Top Products	TOPN
Category Summary	SUMMARIZECOLUMNS
Active Products	FILTER
Product KPI Table	ADDCOLUMNS
Top Names Tooltip	CONCATENATEX

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

با ADDCOLUMNS یک Virtual Table از CustomerKey، Total Sales و Profit بساز و تعداد مشتری‌های Profit مثبت را حساب کن.

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

با TOPN و SUMX فروش ۱۰ مشتری اول را محاسبه کن.

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

با INTERSECT تعداد مشتریانی را پیدا کن که هم در سال جاری و هم سال قبل خرید داشته‌اند.

۳۶.۷۵. تمرین ۴

با EXCEPT مشتریان جدید و Lost را بساز و Sales مربوط به هر گروه را محاسبه کن.

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

یک DAX Query با SUMMARIZECOLUMNS بنویس که Region، Category، Sales و Profit را برگرداند و بر اساس Sales نزولی مرتب شود.

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

یک Virtual Table از Customerها بساز که Sales، Profit، Order Count و Rank داشته باشد؛ سپس فقط Top 20 مشتری سودآور را نگه دار و Average Order Value آن‌ها را حساب کن.

۳۶.۷۸. پروژه Session؛ Virtual Table Analytics Pack

خروجی	شرح
Customer KPI Table	ADDCOLUMNS
Set Analysis	New/Retained/Lost
Top N Table	TOPN
Grouped Query	SUMMARIZECOLUMNS
Dynamic Filter	FILTER
Scenario Mapping	TREATAS
Debug Queries	EVALUATE
QA	Context + cardinality

در پایان این Session باید بتوانی یک مسئله پیچیده را به جای یک فرمول خیلی بلند، به چند Table Variable و مرحله منطقی تبدیل کنی.

جمع‌بندی Session 36

جهش اصلی این فصل این است که DAX را فقط زبان Measure نبینیم؛ DAX یک زبان کار با Context و مجموعه‌ها هم هست. خیلی از الگوریتم‌های تحلیلی حرفه‌ای در اصل ساختن و تبدیل کردن Virtual Tableها هستند.

Build set → enrich → filter/group → iterate → return KPI

Session 37 را وارد Cohort Analysis و Retention می‌کنیم: Acquisition Cohort، Retention Matrix، Customer Churn، New/Returning/Lost Customers و تحلیل رفتار مشتری در طول زمان.