توابع خانواده ALL

توابع خانواده ALL، جز توابع گروه فیلترها به حساب می آیند. عملکرد کلی این توابع به این صورته که فیلترهای جدول یا ستون انتخابی یا غیر انتخابی رو حذف می کنند. به عبارت دقیق تر از هر نوع فیلتر که به صورت غیر صریح (implicit) یعنی توسط شما نوشته نشده باشه صرف نظر خواهد شد. از جمله مهم ترین توابع این خانواده عبارتند از :

• ALL
• ALLEXCEPT
• ALLSELECTED

از جمله مهم ترین کاربردهای این توابع در محاسبات رایج، می توان به موارد زیر اشاره کرد:

• محاسبه درصد از کل (percentage of total)
• مقایسه های دوره ای (period comparisons)
• جمع های تجمعی (running totals)
• ….

فیلترهای implicit و explicit

در این مقاله فیلتر implicit، به فیلترهایی گفته میشه که اطلاعات جداول با استفاده از قرار دادن ستون ها در ویژوال ها (visuals) و یا به واسطه رابطه بین جداول، فیلتر می شوند. به فیلترهای implicit فیلتر context هم گفته می شود.

اما فیلتر explicit، فیلترهایی هستند که توسط نویسنده فرمول (DAX) نوشته می شود و توسط شما در فرمول تصریح می شود. به طور مثال اعمال فیلتر اعداد بیشتر از ۱۰ در بازه اعداد ۱ تا ۲۰ یک نوع قیلتر explicit است.

نحوه عملکرد ALL

ساختار کلی تابع ALL، شامل آرگومان های “نام جدول” و “نام ستون” هستش که به شکل زیره:

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

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

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

جدول fact

جدول DimDate

رابطه بین دو جدول

تابع ALL در صورتی که بر روی جدول مورد نظر اعمال شود باعث حذف فیلترهای غیر صریح (implicit) جدول و ارتباط های اون جدول میشه. در فرمول زیر، در صورت اعمال ALL بر روی جدول fact تمام فیلتر های جدول fact را که شامل دو مورد زیر می شود در محاسبات صرف نظر می شوند.

• فیلتر جدول DimDate که به واسطه رابطه ۱ به چند بر روی fact اعمال می شود
• فیلتر دسته بندی visual که در این مثال یک جدول است

Sum of all (Fact) =
CALCULATE ( SUM ( 'fact'[Qty] ), ALL ( 'fact' ) )

همچنین با اعمال فرمول زیر بر روی جدول DimDate تمامی فیلترهای اعمال شده که شامل ستون های جدول DimDate و در مثال ما، ستون ماه می باشد حذف می شود.

Sum of all (Dim Date) =
CALCULATE ( SUM ( 'fact'[Qty] ), ALL ( DimDate ) )

که خروجی به شکل زیر خواهد بود :

1. شماره یک؛ در measure اول با نام Sum of all (Dim Date)، با توجه به داده هایی که در دو جدول داریم مجموع تعدادی مقادیر رو با حذف فیلترهای جدول DimDate محاسبه می کند. در خروجی این فرمول، مقادیر ستون Qty بدون اعمال فیلتر ستون Month جمع خواهند شد. به همین دلیل است که در تصویر بالا این مقدار برای همه ماه ها تکرار شده است و به طور مثال مقدار ۵۸ محصول B مقدار مجموع Qty همه ماه ها می باشد در صورتی که مقدار فروردین ماه آن ۱۹ می باشد. در نتیجه با اعمال ALL بر روی DimDate از اعمال فیلتر ماه بر روی محاسبات صرف نظر می شود.
2. شماره دو؛ فرمول دوم (ستون دوم) با نام Sum of all (Fact) که در آن تابع ALL بر روی جدول fact اعمال گردیده باعث شده تا علاوه بر حذف فیلتر ماه که توسط رابطه یک به چند جدول DimDate اعمال می شود، تمامی فیلترهای ستون های جدول fact نیز حذف شوند. در نتیجه مجموع کل اعداد ستون Qty باز گردانده می شود و گروه بندی های ویژوال تاثیری بر روی محاسبات نمی گذارد.

اگر مثال بالا رو با کمی تغییرات و اعمال یک فیلتر explicit انجام دهیم، فیلتر تصریحی ما حذف نمی شود و در محاسبات در نظر گرفته می شود. به عنوان نمونه با اعمال یک تغییر در فیلتر فرمول های بالا خروجی به صورت زیر خواهد شد:

Sum (15 or more) = 
CALCULATE(SUM('fact'[Qty]),'fact'[Qty] >= 15)

Sum of all (Explicit) Sales = 
CALCULATE([Sum (15 or more)], ALL('fact'))

در فرمول اول، تمام مقادیر بزرگتر از ۱۵ رو جمع زدیم که به طور مثال برای فروردین ماه مقدار ۱۹ خواهد شد (شماره ۱) و در فرمول دوم با توجه به محاسبات فرمول اول یعنی استفاده از measure اول در فرمول دوم و اعمال فیلتر ALL بر روی fact خواهیم دید که فیلتر مقادیر بزرگتر از ۱۵ (explicit) در این فرمول اعمال میشه و فیلترهای implicit که در مثال قبل آوریم از محاسبات حذف می شوند. در نتیجه مقدار ۱۱۵ در این محاسبات مجموع کل ستون Qty بدون در نظر گرفتن نوع کالا و گروه کالا و ماه می باشد ولی تنها مقادیر بزرگتر از ۱۵ در این ستون جمع شده اند.

نحوه محاسبه Percent Of Total

همونطور که گفته شد یکی از کاربردهای خانواده ALL، محاسبه Percent Of Total است که در این بخش به بررسی یک مثال ساده از نحوه محاسبه آن می پردازیم. از جمله قابلیت هایی که این تابع در محاسبات Percent Of Total دارا است، امکان محاسبه Percent Of Total در بین دسته های تقسیمی به طور مثال گروه کالا یا ماه می باشد.

PercentTotal =
SUM ( 'fact'[Qty] ) / CALCULATE ( SUM ( 'fact'[Qty] ), ALL ( 'fact'[Product] ) )

در صورت اعمال فرمول بالا، درصد از کل با صرف نظر از اعمال فیلتر محصول بین گروه کالا ها محاسبه می شود. در GROUP-1 مقادیر ۲۵، ۵۸ و ۶۱ تقسیم بر ۱۴۴ که مجموع این سه عدد است می شود و در GROUP-2 مقادیر ۵۷ و ۲۱ به ترتیب تقسیم بر ۷۸ می شوند. و مشخص است که مجموع درصد ها در هر گروه کالا ۱۰۰ درصد خواهد شد.

نحوه عملکرد ALLEXCEPT

این تابع هم همانند تابع ALL عمل میکنه با این تفاوت که ستون هایی که نمی خواهیم فیلتر از آن ها برداشته شوند رو در این تابع باید بنویسید. به طور مثال اگر با تابع ALL تعداد ۱۰ ستون از یک جدول ۱۵ ستونی رو انتخاب کنید با تابع ALLEXCEPT تنها نیازه ۵ ستون دیگر رو بنویسید. ساختار کلی تابع به این صورت است:

ALLEXCEPT(<table>,<column>[,<column>[,…]])

مثال Percent Of Total رو در صورتی که بخواهیم با ALLEXCEPT حل کنیم خواهیم داشت:

PercentTotalExcept =
SUM ( 'fact'[Qty] )
    / CALCULATE (
        SUM ( 'fact'[Qty] ),
        ALLEXCEPT (
            'fact',
            'fact'[ProductGroup]
        )
    )

در این فرمول برعکس ALL که ستون Product انتخاب شد، تمامی ستون های به غیر از Product که می خواهیم گروه بندی براساس آن انجام شود و فیلترهای آن حذف نشود انتخاب می شوند.

نحوه عملکرد ALLSELECTED

تابع ALLSELECTED کاملا مشابه ALL می باشد و تفاوت اصلی که ایجاد می کند در فیلترهایی است که توسط کاربر می تواند بر روی آن اعمال شود. ساختار کلی تابع به شکل زیر است:

ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )

و اما تفاوت ALL و  ALLSELECTED:

Sum of allselected (fact) = 
CALCULATE(SUM('fact'[Qty]),ALLSELECTED('fact'))

تفاوت ALLSELECTED و ALL در تصاویر بالا کاملا مشهوده! این تابع مقادیری که توسط Slicer یا فیلترهای دیگر اعمال می شوند را در محاسبات لحاظ و در حقیقت فیلترها بر روی ستون های انتخاب شده عمل می کنند.

نحوه محاسبه Running Total

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

Running Total (all) =
CALCULATE (
    SUM ( 'fact'[Qty] ),
    FILTER (
        ALL ( DimDate ),
        DimDate[YearMonth]
            <= MAX ( DimDate[YearMonth] )
    )
)

Running Total (allselected) =
CALCULATE (
    SUM ( 'fact'[Qty] ),
    FILTER (
        ALLSELECTED ( DimDate ),
        DimDate[YearMonth]
            <= MAX ( DimDate[YearMonth] )
    )
)

در مثال بالا تغییر خاصی رو در خروجی مشاهده نمی کنید. ولی در صورتی که فیلتر ماه رو بر روی جدول اعمال کنیم، خواهیم داشت:

در صورت استفاده از فیلتر بر روی جدول، تابع ALL با توجه به این که در محاسبات تمام فیلترها اعلامی رو حذف می کند در نتیجه انتخاب ماه هیچ تفاوتی در نتیجه ایجاد نمی کند. به عبارت دیگر در مثال بالا با عدم انتخاب ماه خرداد (۱۳۹۹۰۳) جمع تجمعی در فرمول حاوی ALLSELECTED بین مقادیر انتخاب شده صورت می گیرد و مقدار خرداد ماه در نظر گرفته نمی شود ولی در صورتی که در فرمول حاوی ALL به خاطر حذف شدن همه فیلترهای جدول fact، اعمال فیلتر تاثیری بر محاسبات ندارد.