Calculated Column یا Measure؟ مساله این است!

اگر از کاربران Power BI هستید حتما بر سر دو راهی ایجاد ستون محاسباتی (Calculated Column) و سنجه (Measure) قرار گرفته اید. این چالش برای اکثر افراد به خصوص در بدو شروع کار با Power BI به وجود می آید و اکثرا با پیروزی Calculated Column به پایان می رسد! اما واقعا تفاوت این دو چیست؟ در چه مواقعی باید از Calculated Column و در چه مواردی از Measure استفاده کنیم؟ اگر امکان ایجاد ستون محاسباتی در M و DAX وجود داشته باشد کدام یک را انتخاب کنیم؟

Calculated Column چیست؟

Calculated Column یک ستون مانند ستون های دیگر در جداول و مدل شماست که توسط شما و با استفاده از زبان DAX در جدول ایجاد می شود. در واقع با استفاده از کد های DAX، ردیف به ردیف محاسبات انجام شده و در یک ستون جدید ذخیره می شود.

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

عبارت محاسباتی ما که هزینه را از فروش کسر کرده وسود را محاسبه می کند به صورت زیر است:

همان طور که مشاهده می کنید این محاسبه ردیف به ردیف انجام شده و در مقابل هر ردیف مقدار محاسبه شده که سود هر ردیف است را ذخیره کرده و نمایش می دهد. لازم به ذکر است ستون محاسباتی مربوط به جدول فروش حتماً باید در همان جدول ایجاد شود.

محاسبه ردیف به ردیف: Row Context

یکی از مفاهیم بسیار مهم در زمینه ایجاد Calculated Column یا همان ستون های محاسباتی، که در اکثر موارد  اتفاق می افتد، محاسبه ردیف به ردیف است. محاسبات ردیف به ردیف انجام و ذخیره می شود. به این مفهوم در واژه شناسی DAX، اصطلاحا Row Context گفته می شود.

ذخیره در RAM

ستون محاسباتی نیز مانند ستون های دیگر در RAM ذخیره می شود. محاسبات با هر به روز رسانی (Refresh) صورت می پذیرد و در RAM ذخیره می شود. در نتیجه تعداد ستون های محاسباتی بیشتر منجر به مصرف RAM بالاتر و در نتیجه زمان به روز رسانی طولانی تر خواهد شد. البته این موارد بیشتر در تعداد داده های زیاد و همچنین محاسبات پیچیده به وجود می آید و در تعداد رکورد های پایین به چشم نمی آید.

Measure چیست؟

Measure ها معمولا محاسباتی هستند که در سطح تجمیعی صورت می پذیرند. Measure ها معمولا ماهیت داینامیک دارند و از یک یا چند جدول تاثیر می پذیرند. در واقع اگر چه Measure در سطح تجمیعی محاسبه می شود اما بسته به فیلترهای اعمال شده در گزارش تغییر می کنند. پس Measure ها باید به صورت داینامیک و بسته به نیاز کاربر و فیلتر های اعمال شده محاسبه شوند. از این رو Measure ها از پیش محاسبه شده نیستند. Measure ها در زمان استفاده در گزارش محاسبه و نمایش داده می شوند و بر خلاف Calculated Column ها، از پیش ذخیره شده در مدل نیستند.

به طور مثال می توانیم مجموع فروش را از طریق Measure محاسبه کنیم. برای این منظور باید از سر برگ Modeling گزینه New Measure را انتخاب کنیم.

برای محاسبه فروش باید از Measure زیر استفاده کنیم که تعداد فروش هر کالا را در مبلغ ضرب و مجموع فروش را محاسبه می کند، اما این مقدار در جایی ذخیره نمی شود چرا که باید با توجه به فیلتر های اعمال شده توسط کاربر محاسبه شود. تجمیع در DAX از طریق توابعی مثل SUM، SUMX، AVERAGE، AVERAGEX و … صورت می پذیرد.

TotalSales= SUMX(Sales[Quantity]*Sales[UnitPrice])

چگونه می توان نتایج Measure را مشاهده نمود؟

یکی از مهمترین تفاوت های Calculated Column و Measure در این نکته نهفته است که Calculated Column محاسبه و در مدل ذخیره شده و به عنوان یکی از ستون های جدول قابل مشاهده است، در حالی که Measure از پیش محاسبه شده نیست و در مدل ذخیره نمی شود و با ایجاد گزارش و با توجه به فیلتر های اعمال شده توسط کاربر محاسبه شده و به نمایش در می آید.

به طور مثال اگر Measure ایجاد شده را در یک Card قرار دهیم میزان کل فروش بدون فیلتر محاسبه می شود.

اگر مطابق تصویر زیر یک Slicer به گزارش اضافه کرده و یکی از سال ها را فیلتر کنیم محاسبه با توجه به این فیلتر انجام می شود و فروش مربوط به سال 2013 را نمایش می دهد.

Filter Context

همان طور که پیش تر گفته شد Measure ها با اجرای گزارش محاسبه می شوند. اگر در Slicer مقدار 2013 را انتخاب کنیم، محاسبه فقط برای زیر مجموعه ای از داده که مربوط به سال 2013 می شوند، محاسبه می شود. اگر جدولی داشته باشیم که فروش را بر اساس تحصیلات برش بزند برای هر ردیف فیلتری جداگانه داریم. پس گمان نکنید که فیلتر تنها با Slicer اعمال می شود بلکه هر ردیف از جدول یک فیلتر محسوب می شود.  به طور مثال در ردیف سوم فیلتر بر روی مقدار High School قرار گرفته و تنها برای این درجه از تحصیلات محاسبه می شود.

در نتیجه Measure ها بر اساس زیر مجموعه ای از داده ها که توسط Slicer، جدول و … مشخص می شوند، محاسبه می شوند. پس می توان گفت Filter Context ترکیبی از فیلتر هاست که محاسبه را تحت تاثیر قرار می دهند. به طور مثال در تصویر زیر برای ردیف High School، مفهوم Filter Context ترکیبی از فیلتر های High School و سال 2013 است.

منابع مورد نیاز Measure ها

میزان محاسبه شده برای یک Measure، در مدل ذخیره نمی شود و در نتیجه از RAM استفاده نمی کند. در مقابل محاسبه یک Measure با استفاده از CPU صورت می پذیرد چرا که محاسبه در زمان ایجاد گزارش انجام می گیرد. وقتی فیلتر را تغییر می دهید یا گزارش جدیدی می سازید، محاسبات دوباره صورت انجام می شود چرا که زمان اجرای گزارش بسیار اهمیت دارد و باید سریع صورت پذیرد، لذا محاسبات در CPU انجام می شود.

وقتی تعداد زیادی Measure دارید که در آن از محاسبات پیچیده استفاده شده، با هر تغییر Slicer و فیلتر، CPU به شدت برای انجام محاسبات درگیر خواهد شد.

چه مواقعی از Calculated Column و Measure استفاده می کنیم؟

اکنون که Calculated Column و  Measure را می شناسیم با یک سوال مهم روبرو هستیم. در چه مواقعی از Calculated Column یا Measure استفاده می کنیم؟

پاسخ این سوال مانند همیشه این عبارت است: بستگی دارد! استفاده از هر کدام از این موارد به آن چه قصد محاسبه آن را دارید، بستگی دارد. همیشه از خود این سوال را بپرسید. محاسبه من ردیف به ردیف انجام می شود یا در سطح تجمیعی؟ آیا این محاسبه باید توسط فیلتر ها تحت تاثیر قرار گیرد یا خیر؟

اگر قرار است محاسبه ردیف به ردیف صورت پذیرد مثل محاسبه سود یا ایجاد ستون نام و نام خانوادگی که از ترکیب ستون نام و نام خانوادگی ایجاد می شود، بهترین گزینه Calculated Column است. اگر قرار است محاسبه در سطح تجمیعی صورت پذیرد و با فیلتر های گزارش تحت تاثیر قرار گیرد بهترین گزینه Measure است.

مثال اول: محاسبه سن مشتری

قبل از خواندن مثال اندکی تامل کنید. به نظر شما از کدام مورد برای این محاسبه استفاده کنیم؟

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

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

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

Calculated Column یا Power Query؟

وقتی بحث محاسبه ردیف به ردیف به میان می آید می توان از Power Query نیز استفاده کرد. اما کدام یک یهتر است؟در اکثر موارد Power Query بهترین گزینه است. برای بررسی کامل تر این موضوع می توانید مطلب DAX یا M؟ را مطالعه نمایید. در واقع اگر قصد ستونی برای محاسبه سن یا سود یا مبلغ ردیف هر فاکتور را دارید، بهتر است آن را در Power Query ایجاد کنید. در واقع قدرت اصلی DAX در توانایی آن در ایجاد Measure نهفته است. Measure ها داینامیک عمل می کنند و به فیلترها واکنش نشان می دهند و این ویژگی هاست که DAX را قدرتمند ساخته است.

جمع بندی

تفاوت Calculated Column و Measure در جدول زیر قابل مشاهده است.