1.笔记说明

本文是在看了Mitchell的DAX Workshop后的视频、操作笔记

image.png

视频链接:
https://learning.pragmaticworkstraining.com/

Date Dimension
https://devinknightsql.com/2015/06/16/creating-a-date-dimension-with-power-query/

原始数据文件：
链接：https://pan.baidu.com/s/1E-_3UV5XsNmUA52r_6BEVQ
提取码：zmhx

2. DAX Workshop 内容

DAX Workshop Agenda

3. DAX 基础

3.1 新建列 - 仅使用本表自己字段

在Customer表上新建列

• Full Name = firstName + lastName

Full Name = 'Customer'[First Name] & " " & 'Customer'[ Last Name]

• Age Breakdown

Age Breakdown = 
IF('Customer'[Age] >= 55, "55+",
IF('Customer'[Age] >= 45, "45-55",
IF('Customer'[Age] >= 35, "35-44",
"18-34"))
)

• Format Date with MM-YYYY

Month Year = 
FORMAT(
    'Date'[Date],
    "MM-YYYY")

image.png

3.2 新建列 - 使用关联表字段 RELATED RELATEDTABLE

‘Internet Sales’[Sales Territory Key]（*）--（1） ’Sales Territory‘[Sales Territory Key]
‘Internet Sales’[Order Date] （*）--（1）'Date'[ Date ]
‘Internet Sales'[CustomerKey] （*）--（1）'Customer'[Customer Key]

• 表Internet Sales 新建列，关联到 Sales Territory 和 Date
  在多方获取1方的字段，通过RELATED函数

Temperature Key = 
RELATED('Sales Territory'[Sales Territory Region]) 
& RELATED('Date'[Day Number Of Month])

• 表Sales Territory新建列，关联到Internet Sales，获取总的transaction数
  下面的DAX写法，看到的是整个Internet Sales中的row，没有按照两表上的关联字段做任何的filter
  row context 把表上的filter context deactivate.

Total Transaction = COUNTROWS('Internet Sales')

image.png

• 表Sales Territory新建列，关联到Internet Sales，按照Sales Territory Key获取transaction数
  在1方获取多方的字段，通过RELATEDTABLE函数

Total Transaction = COUNTROWS(RELATEDTABLE('Internet Sales'))

image.png

• 基于Total Transaction 创建Region Volumn 使用SWITCH函数

Region Volumn = SWITCH(
    TRUE(),
    'Sales Territory'[Total Transaction] >= 7000, "High Volume",
    'Sales Territory'[Total Transaction] >= 4000, "Medium Volume",
    'Sales Territory'[Total Transaction] >= 1, "Low Volume",
    "NA")

image.png

• 表Customer上新建字段获取per customer级别的最后一个order时间
  DAX1，获取的是'Internet Sales' 整个表上的Order Date的最大值，而不是per customer级别的。
  由于row context把关系deactivated了。

Date Last Purchase = MAX('Internet Sales'[Order Date])

image.png

DAX2 下面的写法才是正确的。

Date Last Purchase = MAXX(
    RELATEDTABLE('Internet Sales'), 
    'Internet Sales'[Order Date]
)

image.png

3.3 新建Measure & Filter Context

新建的measure, 不属于任何table，measure的值受到Filter Context的影响而变化
powerBI中有以下几种 Filter Context

• 表头行，列
• 分片器

• DAX Formula Filter
  比如下图中GUI中就存在三个Filter Context，这些Filter Context 就会在PowerBI中自动作用到Measure上

  
  
  image.png

3.4 Calculate

Filter Context 会在PowerBI中自动作用到Measure的计算上下文中。
但是有些场景需要调整FilterContext，这个可以通过使用函数CALCULATE实现。

• 示例1 计算 Total Sales(所有国家）
  下图可以看到[Total Sales] vs [Total Sales (All Countries)]的区别
  FC1 - Customer[Age Breakdown]
  FC2 - Sales Territory[Country]
  FC3 - Date[Year]
  Total Sales 在上面三个维度的作用下，分别有不同的数据
  Total Sales(All Countries), 和Sales的差别就是由于CALCULATE中的ALL函数，导致FC2无效了。

Total Sales = SUM('Internet Sales'[Sales Amount])

Total Sales (All Countries) = 
    CALCULATE(
        [Total Sales],
       All('Sales Territory'[Sales Territory Country])   //指定 表名[列名]
        )

image.png

• 示例2 将report中的Sales Territory Country修改为Sales Territory Region
  在示例1的基础上把FC2 -> Sales Territory Group,
  这种情况下 Total Sales 和 Total Sales(All Countries)一致的。
  因为Total Sales(All Countries)上修改的Context是Sales Territory[Country]，所以外部FC2不受影响，FC2还是作用于这个Measure

  
  
  image.png

• 示例3， 示例1中我们看到多了一个NA，如何修复这个问题？

Total Sales (All Countries) = 
IF(
    ISBLANK([Total Sales]), 
    BLANK(),
    CALCULATE(
        [Total Sales],
        ALL('Sales Territory'[Sales Territory Country])  //指定 表名[列名]
        )
)

image.png

• 示例4， 在示例2的基础上，ALL中仅指定Table会怎么样
  和示例2相比，唯一的差别是ALL中仅指定了表名。
  Total Sales(All Countries) 上ALL(‘Sales Territory’）消除外部FilterContext上在这个表上的限制

FC2 - 'Sales Territory'[Sales Territory Group]

CALCULATE(
        [Total Sales],
        ALL('Sales Territory')   //仅指定 表名
        )

image.png

• 示例5， ALL写法等价的写法 REMOVEFILTER
  下面两组的写法左右两边的效果是等价的。

  
  
  image.png

image.png

3.5 时间相关 YTD， PRIOR YEAR

image.png

• TOTALYTD with default 12/31 end date

YTD Sales = 
TOTALYTD(
    [Total Sales],
    'Date'[Date])

• TOTALYTD with specified end date
  say fiscal year is by end of 6/30

Fiscal YTD Sales = 
TOTALYTD(
    [Total Sales],
    'Date'[Date],
    "06/30")

image.png

• Prior Year with SAMEPERIODLASTYEAR

Prior Year Sales = 
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(
        'Date'[Date])
)

3.6 时间相关 某周期结束时的数据

比如计算库存，账户余额，通常都需要在周期的开始，或者结束获取库存信息。
数据说明
Product Inventory ：
1.Unit Balance不能累加，是某个时间节点的库存量
2.时间不连续，周末没有数据

image.png

• 计算某周期最后一天的Unit Balance
  比如周期是year,那就是年底最后一天各产品的库存 Unit Balance

1.LASTDATE的结果可能在Product Inventory里不存在而导致空值。
如2005/07 和2005/12 的最后一天在表中都不存在。

Close Balance = 
CALCULATE(
    [Product Inventory],
    LASTDATE('Date'[Date]))

2. 非空值版本

Close Balance (Non Blank) = 
CALCULATE(
    [Product Inventory],
    LASTNONBLANK(
        'Date'[Date],
        [Product Inventory]
    )
)

image.png

3.7 时间相关 某周期开始时的数据

• 计算某周期开始的Unit Balance
  所谓的周期开始，就是上个周期结束时的值

1. 有可能有空值的版本

Open Balance G2 = 
OPENINGBALANCEMONTH(
    [Product Inventory],
    'Date'[Date]
)

2. 非空值版本

Open Balance G2 (Non Blank) = 
CALCULATE(
    [Product Inventory],
    LASTNONBLANK(
        PARALLELPERIOD(
            'Date'[Date],
            -1,
            MONTH
        ),
        [Product Inventory]
    )
)

image.png

3.8 Context Transition

Customer表上创建如下信息
1.new column

Max OrderDate Column = Max('Internet Sales'[Order Date])

2.new measure

Max OrderDate Measure = MAX('Internet Sales'[Order Date])

3.new column using measure

Max OrderDate using Measure = [Max OrderDate Measure]

尽管使用的公式一摸一样，但是下表中看到的内容不一样。
1.Max OrderDate Column
这是InternetSales中的所有记录最大的OrderDate
2.Max OrderDate using Measure
这种情况下row context也被加入了Filter Context

image.png

image.png