پانزده تابع DAX که باید حتما یاد بگیرید

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

مجموعه داده

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

برای دانلود پایگاه داده و انبار داده Adventureworks می توانید به لینک های زیر مراجعه نمایید.

دانلود پایگاه داده و انبار داده Adventureworks

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

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

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

در طراحی انبار داده سعی بر این است تا حد ممکن به معماری ستاره ای (Star) یا دانه برفی (Snowflake) دست پیدا کنیم.

برای اطلاعات بیشتر می توانید مطالب زیر را مطالعه نمایید.

مبانی پایگاه داده رابطه ای

انبار داده (دیتاورهاوس – Data Warehouse) چیست؟

مدل سازی داده؛ به سوی مدل ستاره ای

تابع SUM

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

Quantity =
SUM ( FactSales[OrderQuantity] )

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

تابع AVERAGE

تابع AVERAGE تابعی است که عملیات میانگین گیری را بر روی یک ستون انجام می دهد. ورودی این تابع یک ستون عددی است و خروجی آن یک مقدار عددی می باشد. فرض کنید می خواهیم میانگین ستون قیمت در جدول کالا را محاسبه کنیم.

AvgPrice =
AVERAGE ( FactSales[UnitPrice] )

تابع MIN

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

MinimumPrice =
MIN ( DimProduct[ListPrice] )

تابع MAX

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

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

MaxName =
MAX ( DimProduct[ProductName] )

تابع COUNT

این تابع عملیات شمارش را بر روی یک ستون انجام می دهد. فرض کنید می خواهیم ستون Category که حاوی نام گروه کالا است را مورد شمارش قرار دهیم.

ProductNumber =
COUNT ( DimProduct[ProductName] )

تابع COUNTROWS

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

CustomerNumber =
COUNTROWS ( DimCustomer )

توجه کنید تفاوت COUNT و COUNTROWS در این است که با تابع COUNT مقادیر یک ستون مورد شمارش قرار می گیرد اما با COUNTROWS تعداد ردیف های یک جدول.

تابع DISTINCTCOUNT

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

DistPrice =
DISTINCTCOUNT ( DimProduct[ListPrice] )

تابع SUMX

در ادامه معرفی پانزده تابع DAX که باید حتما یاد بگیرید به توابع X دار می رسیم. گفتیم تابع SUM عملیات جمع را بر روی یک ستون انجام می دهد. اگر قرار باشد، ردیف به ردیف عملیاتی بر روی یک ستون انجام شود و سپس عملیات جمع انجام شود باید از تابع SUMX استفاده کنیم.

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

تابع SUMX ردیف به ردیف جدول فروش را پیمایش می کند و برای هر ردیف قیمت را در تعداد ضرب نموده و در نهایت حاصل همه آن ها را با هم جمع می کند.

SalesAmount =
SUMX ( FactSales, FactSales[OrderQuantity] * FactSales[UnitPrice] )

تابع AVERAGEX

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

AvgProfit =
AVERAGEX ( DimProduct, DimProduct[ListPrice] – DimProduct[StandardCost] )

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

تابع MINX

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

MinProfit =
MINX ( DimProduct, DimProduct[ListPrice] – DimProduct[StandardCost] )

تابع MAXX

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

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

MaxFullName =
MAXX ( DimCustomer, DimCustomer[FirstName] & ” “ & DimCustomer[LastName] )

در واقع جدول مشتریان به عنوان ورودی اول به تابع MAXX معرفی شده و برای هر ردیف عملیات چسباندن نام و نام خانوادگی صورت گرفته و در نهایت بیشترین مقدار بر اساس حروف الفبا توسط استخراج می شود.

تابع FILTER

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

فرض کنید می خواهیم جدولی حاوی اطلاعات مشتریانی که درآمد آن ها بالای 160000 دلار است را در مدل داشته باشیم. تمام مثال های پیشین ما در قالب Measure پیاده سازی شد. اما این مثال ما باید با استفاده از new table پیاده سازی می شود چرا که خروجی تابع FILTER یک جدول است.

CustomerHighIncome =
FILTER ( DimCustomer, DimCustomer[YearlyIncome] > 160000 )

در واقع با استفاده از این کد، جدول مشتریان ردیف به ردیف پیمایش می شود و در صورتی که هر مشتری درآمد بالای 160000 دلار داشته باشد به عنوان خروجی در جدول جدید نمایش داده می شود. 112 مشتری درآمد بالای 160000 دلار دارند پس تعداد ردیف های جدول جدید ما 112 ردیف است.

البته که می توان از تابع FILTER در ایجاد مژر ها نیز استفاده نمود. به عنوان مثال فرض کنید می خواهیم تعداد مشتریانی که درآمد بالای 160000 دلار دارند را نمایش دهیم. کافی است به طریق زیر عمل کنیم.

NumberOfCustomerWithHighIncome =
COUNTROWS ( FILTER ( DimCustomer, DimCustomer[YearlyIncome] >= 1600000 ) )

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

تابع RANKX

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

IncorrectRanking =
RANKX (
ALL ( DimCategory[CategoryName] ),
SUMX ( FactSales, FactSales[OrderQuantity] * FactSales[UnitPrice] )
)

تصویر زیر نتیجه را نمایش می دهد. اگر چه میزان فروش این آیتم ها برابر نیست اما رتبه همه آن ها برابر 1 در نظر گرفته شده است.

واضح است که در کد ما اشتباهی صورت گرفته است. برای رفع مشکل ابتدا از عبارتی که مبنای رتبه بندی است مژری بسازید و از آن در تابع RANKX استفاده نمایید.

SalesAmount =
SUMX ( FactSales, FactSales[OrderQuantity] * FactSales[UnitPrice] )

حال از این مژر در RANKX استفاده نمایید.

CorrectRanking =
RANKX ( ALL ( DimCategory[CategoryName] ), [SalesAmount] )

تصویر زیر نتیجه نهایی را نمایش می دهد که کاملا درست به نظر می رسد. فکر استفاده از تابع RANKX بدون ALL و مژر را از سر خود بیرون بیندازید! در واقع با در نظر نگرفتن این موضوعات هر گروه کالا با خودش مقایسه می شود و در مقایسه با خودش رتبه 1 را دریافت خواهد کرد.

اگر می خواهید کمترین مقدار رتبه 1 را دریافت کند باید آرگومان دیگری از تابع RANKX را نیز مورد استفاده قرار دهید. تابع RANKX به طور پیش فرض به بیشترین مقدار رتبه 1 را اختصاص می دهد. برای رتبه بندی عکس پیش فرض به صورت زیر عمل می کنیم. در واقع با قرار دادن DESC بیشترین مقدار رتبه 1 را دریافت خواهد کرد و با ASC کمترین مقدار رتبه 1 را دریافت خواهد کرد.

AscRanking =
RANKX ( ALL ( DimCategory[CategoryName] ), [SalesAmount],, ASC )

فرض کنید 2 کالای ما به مقدار 17000000 تومان خریداری شده اند و رتبه 3 را به خود اختصاص داده اند. کالای بعدی ما رتبه 4 بگیرد یا 5؟ این موضوع را آرگومان بعدی RANKX تعیین می کند. شما می توانید آرگومان بعدی را بر روی Skip یا Dense قرار دهید. اگر آرگومان آخر RANKX را بر روی Dense قرار دهید کالای بعدی شما رتبه 4 را خواهد گرفت و اگر بر روی Skip قرار دهید رتبه 5 خواهد گرفت.

DenseRanking =
RANKX ( ALL ( DimCategory[CategoryName] ), [SalesAmount],, ASC, DENSE )

تابع CALCULATE و ALL

تابع CALCULATE یکی از توابع بسیار مهم در زبان DAX به شمار می رود. فرض کنید که می خواهیم درصد فروش هر گروه کالا را به کل فروش محاسبه کنیم. راه حل ترکیب تابع CALCULATE و ALL است.

PCT =
DIVIDE (
[SalesAmount],
CALCULATE ( [SalesAmount], ALL ( DimCategory[CategoryName] ) )
)

نتیجه مانند تصویر زیر خواهد بود.

حال به تشریح کد بالا بپردازیم. ابتدا از تابع DIVIDE استفاده شده که عملیات تقسیم را انجام می دهد. باید صورت و مخرج را به این تابع معرفی کنیم. مژر میزان فروش را به عنوان صورت به این تابع معرفی کردیم. اما برای مخرج از ترکیب دو تابع بسیار مهم CALCULATE و ALL استفاده نموده ایم.

در واقع با استفاده از تابع ALL به عنوان آرگومان تابع CALCULATE، فیلترهای وارده از نام گروه کالا (CategoryName) را نادیده می گیریم. با این نادیده گرفتن مخرج همیشه ثابت و معادل کل فروش است. پس در هر ردیف میزان فروش همان سطح به کل فروش تقسیم شده و یک درصد را به عنوان خروجی برمی گرداند.

در این مطلب با پانزده تابع DAX که باید حتما یاد بگیرید آشنا شدیم. به نظر شما کدام توابع در زبان DAX مهم و پرکاربرد هستند و در این لیست قرار نگرفته اند؟!