【DAX】一、DAX入门

测试工具：DAX Studio。

DAX有几个比较重要的函数：CALCULATE、CALTULATETABLE、SUMMARIZE、FILTER。

SUMMARIZE是核心，语法如下：

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

 

例1：查询表

EVALUATE
Product

 

例2：对表进行过滤

EVALUATE
FILTER (
    Product,
    RELATED(Category[Product Category Name]) = "Bike"
)

 

例3：分组计算

EVALUATE
SUMMARIZE (
    'Transaction',
    'Transaction'[ProductId],
    "Total qty", SUM('Transaction'[Quantity] 
) 

再来一个：

EVALUATE
    SUMMARIZE(
        Product,
        Product[Name],
        Product[Size]
)    

 

例4：排序

EVALUATE
Product
ORDER BY Product[ProductName]

 

例5：TopN

EVALUATE
TOPN(
    5, 
    Product, 
    Product[FullPrice]
)

 

例8：对产品的分类，颜色，产品名字进行统计：交易单量，销售数目

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])
) 

注：ROLLUP与ROLLUPGROUP请看下面的语句和结果：

SUMMARIZE(
    ResellerSales_USD, 
    ROLLUP(
        ROLLUPGROUP(
            DateTime[CalendarYear], 
            ProductCategory[ProductCategoryName]
        )
    ), 
    "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD]), 
    "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
)

SUMMARIZE(
    ResellerSales_USD, 
    ROLLUP(
        ROLLUPGROUP(
            DateTime[CalendarYear], 
            ProductCategory[ProductCategoryName]
        )
    ), 
    "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD]), 
    "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
)

注：还有一个ISSUBTOTAL。如果行中包含作为参数提供的列的小计值，则返回 True 值，否则返回False

SUMMARIZE(
    ResellerSales_USD, 
    ROLLUP(
        DateTime[CalendarYear], 
        ProductCategory[ProductCategoryName]
    ), 
    "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD]), 
    "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount]), 
    "Is Sub Total for DateTimeCalendarYear", ISSUBTOTAL(DateTime[CalendarYear]), 
    "Is Sub Total for ProductCategoryName", ISSUBTOTAL(ProductCategory[ProductCategoryName])
)

 

例9：CALCULATETABLE vs Sub Query

下面语句统计Bike这个类别的产品的销售数目

SQL:

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:

EVALUATE
SUMMARIZE (
    CALCULATETABLE (
        'Internet Sales',
        'Product Category'[Product Category Name] = "Bikes" 
    ),
    Product[Product Name],
    "Quantity sold", SUM ('Internet Sales'[Order Quantity]
)

 

例10：ADDCOLUMNS等同于衍生列

ADDCOLUMNS跟Calculated Column类似：即给指定的表加入计算列。不一样的地方在于Addcolumn所加的只在它所在的语句有效。

SQL:

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:

EVALUATE
ADDCOLUMNS (
    'Product Category',
    "Number transactions", COUNTROWS(RELATEDTABLE('Internet Sales'))
)