# DAX基础入门 – 30分钟从SQL到DAX — PowerBI 利器

## 正文：

• SUMMARIZE
• FILTER
• CALCULATE 与 CALTULATETABLE

 `　DAX` `SQL` ```-- list all the Products EVALUATE Product``` ```show all the Products SELECT * FROM tblProduct```

## DAX Filter vs SQL Filter

 DAX SQL ```EVALUATE FILTER ( Product, RELATED ( Category[Product Category Name] ) = "Bike" )``` ```SELECT * FROM Product P JOIN Category c on P.[Category_KEY] = c.[Category_KEY] WHERE c.[Product Category Name] = 'Bikes'```

FILTER是一个MUST know的语句

 DAX SQL ```-- 显示每个产品销售数目 EVALUATE SUMMARIZE ( 'Transaction', 'Transaction'[ProductId], "Total qty", SUM ( 'Transaction'[Quantity] ) )``` `-- 显示每个产品销售数目` ```SELECT ProductId, SUM(Quantity) AS 'Total Qty' FROM tblTransaction GROUP BY ProductId```

## SUMMERIZE

 DAX SQL ```-- list selected columns EVALUATE SUMMARIZE( Product, Product[Name], Product[Size])``` ```-- show selected columns SELECT Name, Size FROM tblProduct```

 DAX SQL ```-- show products in name order EVALUATE Product ORDER BY Product[ProductName]``` ```-- show products by name SELECT * FROM tblProduct ORDER BY ProductName```

 DAX SQL ```-- show 5 most expensive products EVALUATE TOPN ( 5, Product, Product[FullPrice] )``` ```SELECT TOP 5 FROM Product ORDER BY FullPrice```

SUMMERIZE vs Group BY

SUMMARIZE是一个比较重要语句：

```EVALUATE
SUMMARIZE(
源表名,
Group by 列 1,
...,
Group by 列 N,
汇总列名1,
汇总列名1所对应的表达式,
...,
汇总列名N,
汇总列名N所对应的表达式
)```

MSDN的例子

 DAX SQL ```EVALUATE SUMMARIZE('Internet Sales'         , ROLLUP('Date'[Calendar Year], 'Product Category'[Product Category Name])         , "Sales Amount", SUM('Internet Sales'[Sales Amount])         , "Discount Amount", SUM('Internet Sales'[Discount Amount])   )``` ```SELECT D.[Calendar Year], PC.[Product Category Name] , SUM(F.[Sales Amount]) 'Sales Amount' , SUM(F.[Discount Amount]) 'Discount Amount' FROM [Internet Sales] F JOIN DATE D ON S.[DAY_KEY] = F.[DAY_KEY] JOIN [Product Category] PC ON PC.[Category_KEY] = F.[Category_KEY] GROUP BY D.[Calendar Year],PC.[Product Category Name]```
Date[Calendar Year] Product Category[Product Category Name] [Sales Amount] [Discount Amount]
2005 Bikes 6958251.043 4231.1621
2006 Bikes 18901351.08 178175.8399
2007 Bikes 24256817.5 276065.992
2008 Components 2008052.706 39.9266

SUMMARIZE还有其他的Option，这里就不做详细介绍。

SUMMARIZE详情参考：https://msdn.microsoft.com/en-us/library/gg492171.aspx

## CALCULATETABLE vs Sub Query

 DAX SQL ```EVALUATE SUMMARIZE ( CALCULATETABLE ( 'Internet Sales', 'Product Category'[Product Category Name] = "Bikes" ), -- field to group by Product[Product Name], "Quantity sold", SUM ( 'Internet Sales'[Order Quantity] ) )``` ```SELECT P.[Product Name], SUM(Fact.[Quantity]) as 'Quantity sold' FROM (SELECT F.* FROM [Transaction] F JOIN Category c ON F.[Category_Key] = C.[Category_Key] WHERE C.[Product Category Name] = 'Bikes' ) Fact join Product P ON P.[Product_Key] = Fact.[Product_Key] GROUP BY P.[Product Name]```

## DAX ADDCOLUMNS vs SQL Derived Column

 DAX SQL ```EVALUATE ADDCOLUMNS ( 'Product Category', "Number transactions", COUNTROWS ( RELATEDTABLE ( 'Internet Sales' ) ) )``` ```SELECT [Product Category Name], count(t.Id) AS 'Number transactions' FROM Transaction F JOIN [Product Category] C on F.[Category_Key] = C.[Category_Key] GROUP BY C.[Product Category Name]```

## 小结：

DAX语法十分灵活，有些看起来晦涩难懂，但是，如果能够以SQL为基础的角度去切入会事半功倍。

DAX基础 – 30分钟掌握从SQL到DAX 作者：马丁叔叔