گروه‌بندی داده‌ها با تابع SUMMARIZECOLUMNS در DAX

نویسنده: علی اصغر شجاعی · دورهٔ هوش تجاری، کد ۱۵

دوره هوش تجاری کد 15

مقاله SUMMARIZECOLUMNS

علی اصغر شجاعی

در این مقاله به تابع SUMMARIZECOLUMNS می پردازیم که جایگزین مناسبی در عملکرد برای تابع SUMMARIZE می باشد.

از ژوئن ۲۰۲۴، نسخه‌های به‌روز موتور DAX از SUMMARIZECOLUMNS در Measure و هنگام انتقال زمینه پشتیبانی می‌کنند. اگر از موتور قدیمی‌تر استفاده می‌کنید، سازگاری نسخه را بررسی کنید.

تابع قدیمی که در DAX برای تجمیع و خلاصه از آن استفاده می شد تابع SUMMARIZE بود.این تابع در ورودی اول خودش یک جدول می گیرد که قرار است روی تجمیع یا خلاصه اتفاق می افتد در ورودی بعدی نام ستون هایی که قرار است تجمیع و خلاصه شوند قرار می گیرند حتی می شود از ستون هایی از جدول دیگر که با این جدول رابطه دارند هم استفاده کرد. برای مثال خلاصه جدول زیر برای سال و رنگ و مقدار فروش را در نظر بگیرید:

Sales by Year and Color =
SUMMARIZE (
    'Fact Sale',
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color],
    "Sales Amount", SUMX ( 'Fact Sale','Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )
)

که کوئری بصورت زیر می دهد:

تابع SUMMARIZE به دلیل مشکلات عملکردی که دارد بهتر است فقط برای در آوردن لیستی منحصر به فرد از ستون های مورد خلاصه استفاده شود و ستون های محاسباتی را با تابع ADDCOLUMNS اضافه کرد مانند مثال زیر:

Sales by Year and Color optimized =
ADDCOLUMNS(
SUMMARIZE(
    'Fact Sale',
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color]),
    "Sales Amount", CALCULATE(SUMX ( 'Fact Sale','Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] ))
    )

توجه داشته باشید که برای اعمال فیلتر در تابع ADDCOLUMNS باید از تابع CALCULATE استفاده کرد که در تابع SUMMARIZE لازم نیست.

در Excel 2016 یا Power Bi 2016 به بعد می توانید از تابع SUMMARIZECOLUMNS استفاده کرد که جایگزینی برای تابع SUMMARIZE است و ویژگی های بیشتری نیز ارائه می کند با این تفاوت که در ورودی اول نام جدول را نمی خواهد. برای مثال:

Sales by Year and Color new style =
SUMMARIZECOLUMNS(
  'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color],
    "Sales Amount", SUMX ( 'Fact Sale','Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )
    )

در واقع تابع SUMMARIZECOLUMNS شکل بهینه ای از کد زیر است که از تابع FILLTER برای اعمال شرط BLANK نبودن مقدار فروش در جدولی توسط تابع SUMMARIZE که از ضرب دکارتی و ترکیب های مختلف توسط تابع CROSSJOIN از مقادیر منحصر به فرد دو ستون سال و رنگ توسط تابع VALUES ، مقدار فروش محاسبه می شود:

Sales by Year and Color using Crossjoin =
FILTER (
    SUMMARIZE (
        CROSSJOIN (
            VALUES ( 'Dimension Date'[Calendar Year] ),
            VALUES ( 'Dimension Stock Item'[Color] )
        ),
        'Dimension Date'[Calendar Year],
        'Dimension Stock Item'[Color],
        "Sales Amount", SUMX ( 'Fact Sale', 'Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )
    ),
    NOT ( ISBLANK ( [Sales Amount] ) )
)

همانطور که مشاهده کردید بهینه تر بودن و عملکرد بهتر SUMMARIZECOLUMNS ایجاب می کند که از آن به جای SUMMARIZE/ADDCOLUMNS استفاده کنیم اما برای استفاده از SUMMARIZECOLUMNS باید جزییاتی را در نظر گرفت که در ادامه به آن ها خواهیم پرداخت.

فیلتر جدول در SUMMARIZECOLUMNS

در تابع SUMMARIZECOLUMNS ورودی جدول وجود ندارد در نتیجه جدول را بر اساس ستون های خلاصه شده و ستون های محاسباتی قرار می دهد فیلتری بر اساس جدول نیز اعمال نمی کند که اگر ستون های محاسباتی را در ورودی های تابع قرار ندهیم صرفا یک ضرب دکارتی یا CROSSJOIN انجام می شود که حاصل تمام ترکیب های مختلف ستون های تجمیع و خلاصه شده است.برای مثال:

Sales by Year and Color crossjoin implicit =
SUMMARIZECOLUMNS (
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color]
)

که این کد برابر است با:

Sales by Year and Color crossjoin explicit =
CROSSJOIN (
    VALUES ( 'Dimension Date'[Calendar Year] ),
    VALUES ( 'Dimension Stock Item'[Color] )
)

در موارد بالا تمامی ترکیبات مختلف رنگ و سال را در نظر می گیرد که در مثال اینجا شامل 45 ردیف می شود اما هنگامی فیلتر جدول اعمال می شود فقط ردیف های رنگ و سال مربوط به جدول را می آورد که شامل 32 ردیف می شود که کد آن به شکل زیر است:

Sales by Year and Color summarize =
SUMMARIZE (
    'Fact Sale',
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color]
)

اما برای SUMMARIZECOLUMNS که ورودی اول جدول ندارد ورودی دیگری به عنوان فیلتر در نظر گرفته شده که بعد ستون های خلاصه شده و قبل از ستون های محاسباتی می باشد که وارد کردن این فیلتر اختیاری است مانند زیر که این مثال ستون محاسباتی ندارد:

Sales by Year and Color summarizecolumns =
SUMMARIZECOLUMNS (
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color],
    'Fact Sale'
)

همانطور که دیدیم می شود در SUMMARIZECOLUMNS جداول فیلتر اعمال کنید که در مدل داده های پیچیده در کوئری ها انجام شود.نکته دیگر در دو مثال آخر این است که اگر ستون های محاسباتی در دو تابع SUMMARIZECOLUMNS و SUMMARIZE وارد نشود این دو تابع هیچ فرقی باهم در عملکرد ندارند و لازم به جایگزینی نیست به شرطی که فیلتر جدول در تابع SUMMARIZECOLUMNS قرار گیرد.

عدم وجود ROW CONTEXTدر SUMMARIZECOLUMNS

یکی دیگر از تفاوت های دو تابع SUMMARIZE و SUMMARIZECOLUMNS میتوان بهROW CONTEXT و FILTER CONTEXT اشاره کرد که در SUMMARIZE هر دو مورد ROW CONTEXT و FILTER CONTEXT در ستون های خلاصه و محاسبات اعمال می شود اما در SUMMARIZECOLUMNS فقط FILTER CONTEXT اعمال می شود. به عنوان مثال به کد زیر که برای اصلاح کردن مقدار فروش توسط عدد سال با تابع SUMMARIZE انجام می شود نگاه کنید:

Sales by Year and Color correction summarize =
SUMMARIZE (
    'Fact Sale',
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color],
    "Sales Amount",
    VAR delta = 'Dimension Date'[Calendar Year] - 2013
    VAR correction = 1 + ( delta / 100 )
    RETURN

correction

        * SUMX ( 'Fact Sale', 'Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )
        )

اما برای استفاده همین محاسبات در تابع SUMMARIZECOLUMNS بدلیل عدم وجود ROW CONTEXT باید از تابع VALUES در FILTER CONTEXT استفاده کنیم که بطور صحیح عمل کند:

Sales by Year and Color correction summarizecolumns =
SUMMARIZECOLUMNS (
    'Dimension Date'[Calendar Year],
    'Dimension Stock Item'[Color],
    "Sales Amount",
    VAR delta =  VALUES( 'Dimension Date'[Calendar Year] ) - 2013
    VAR correction = 1 + ( delta / 100 )
    RETURN

correction

            * SUMX ( 'Fact Sale', 'Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )
)

اگر یک مجموعه فعال در SUMMARIZECOLUMNS وجود داشته باشد باید در نظر داشت که چندین مقدار در FILTER CONTEXT وجود دارد که برای بررسی این چند مقدار از تابع HASONEVALUE قبل از تابع VALUES استفاده می شود.

Sales by Year and Color correction summarizecolumns with subtotals =
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( 'Dimension Date'[Calendar Year], "All Years" ),
    'Dimension Stock Item'[Color],
    "Sales Amount",
    VAR delta =
        IF (
            HASONEVALUE ( 'Dimension Date'[Calendar Year] ),
            VALUES ( 'Dimension Date'[Calendar Year] ) - 2013,

        )
    VAR correction = 1 + ( delta / 100 )
    RETURN

correction

            * SUMX ('Fact Sale','Fact Sale'[Quantity] * 'Fact Sale'[Unit Price] )
)

جدول زیر نتیجه کد بالا می باشد، ستون ALLYEAR که حاوی مقادیر TRUE و FALSE می باشد که برای ردیف های اضافی هر رنگ برای همه سال هاست.

تصویر ۳؛ جدول زیر نتیجه کد بالا می باشد، ستون ALLYEAR که حاوی مقادیر TRUE و FALSE می باشد…

نسخه های پشتیبان از SUMMARIZECOLUMNS

جدول زیر نشان دهنده نرم افزارهایی است که از زبان DAX استفاده می کنند و تابع SUMMARIZECOLUMNS را پشتیبانی می کنند:

not supported	Excel 2010
not supported	Excel 2013
supported only without an external filter context	Excel 2016
not supported	Analysis Services 2012
not supported	Analysis Services 2014
supported	Analysis Services 2016
supported	Power BI Desktop

نتیجه گیری

SUMMARIZE همچنان برای گروه‌بندی ردیف‌های یک جدول مشخص کاربرد دارد. SUMMARIZECOLUMNS برای ساخت جدول خلاصه از ستون‌های گروه‌بندی و ارزیابی عبارت‌ها مناسب است و در ورودی اول به جدول مبنا نیاز ندارد. انتخاب بین آن‌ها و الگوی SUMMARIZE همراه ADDCOLUMNS به مدل، فیلترها و هدف محاسبه بستگی دارد.

منبع:

https://www.sqlbi.com/articles/introducing-summarizecolumns/

منبع: مستند SUMMARIZECOLUMNS در Microsoft Learn.

نتیجه گیری

دیدگاه‌ خود را بنویسید لغو پاسخ