学习地址:https://www.youtube.com/watch?v=QJw4HkagVWc
一、什么是DAX
Data Anlysis Expressions, Power BI, Power Pivot, SSAS Tabular语言
二、Day 1
目录
1、DAX Fundamentals DAX 基础
2、DAX Evaluation Context 理解上下文
3、Time Series Analysis 时间序列分析
4、Semi-Additive Measure 复合度量值
5、Context Transition 上下文转换
1、DAX Fundamentals
计算列:
- 建立新的一列;
- 建立联系;
计算度量值:
- 创建Dynamic聚合计算;
- 百分比计算;
- 时间智能计算;
- 复杂的关系;
计算表格:
- 从已知表中创建新的表;
- 虚拟表;
2、DAX Evaluation Context
Row Context 理解行上下文
Eg:
现在有两张表,上面的表是维度表1(table1),下面的是主表2(F_table),靠ID连接2张表。其中[消费次数]是计算列。
对比:COUNTROWS('F_table) 和 COUNTROWS(RELATEDTABLE('F_table')) 这两个计算列的区别
答:对于第1个计算列表达式,没有让两张表的关系活跃,不会根据每一行的ID去计算行数,
对于第2个计算列表达式,使用了RELATEDTABLE函数,此函数根据原本两表存在的关系,依据ID,先返回了一张根据ID在F_table中匹配的所有相关信息,然后再在这张返回出来的表根据ID计算行数。
table 1 | F_table | |||||
ID | 名字 | 消费次数 | 消费时间 | ID | 消费金额 | |
1 | A | xxx | 1 | 12 | ||
2 | B | xxx | 1 | 12 | ||
3 | C | xxx | 1 | 12 | ||
4 | D | xxx | 1 | 12 | ||
xxx | 2 | 12 | ||||
xxx | 2 | 12 | ||||
xxx | 2 | 12 | ||||
xxx | 3 | 12 | ||||
xxx | 3 | 12 | ||||
xxx | 4 | 12 | ||||
xxx | 4 | 12 | ||||
xxx | 4 | 12 | ||||
xxx | 4 | 12 | ||||
xxx | 4 | 12 |
Eg: 理解’X‘函数
对比:MAX('F_table'[消费日期]) 和 MAXX(RELATEDTABLE('F_table'),'F_table'[消费日期])
答:分两步骤,X代表,对于当前行在表中迭代计算得出一张表,然后再选出所需要的列
'X' 函数,返回一列。
Filter Context 理解筛选上下文
- Row and Column Headers;
- Slicers and Filters;
- DAX Formula Filters;
3、Time Series Analysis
YTD Sales、Prior Year
- Comparing Data over time
Year-to-Date: YTD, Fiscal YTD
Prior Year
Year Over Year
- Requirements
- Date Table;
- One Row for Every Date (No gaps);
- Span Range of Possible Dates
4、Semi-Additive Measure
Closing Balance 期末余额
Opening Balance 期初余额
5、Context Transition 上下文转换
Adding Row Filters to Filter
Context
计算列可以引用度量值,效果大不同。
原因:度量值可以激活当前行的Filter,row filters added to the context
要谨慎使用context transition,有时可以帮助我们解决问题,有时会有performance的问题
举例
Total Sales = SUM('table'[ACTL]
---------------------------------
Total Sales (All Countries) =
CALCULATE(
[Total Sales],
ALL('table'[country] ))
或
Total Sales (All Countries) =
CALCULATE(
[Total Sales],
REMOVEFILTERS('table'[country] ))
或
--如果出现NA
Total Sales (All Countries) =
IF(
ISBLANK[Total Sales], BLANK(),
CALCULATE(
[Total Sales],
REMOVEFILTERS('table'[country] )))
---------------------------------
Percent of Total =
DIVIDE(
[Total Sales],
[Total Sales (All Countries)])
或
使用show as grand of total,根据业务需求调整
---------------------------------
度量值中可以使用函数 IN {}
---------------------------------
--每一年的累加,最终的截至累加日期是一年的最后一天
YTD Sales =
TOTALYTD(
[Total Sales],
'Date'[Date]
)
--可以自定义截至日期,定义财务年
Fiscal YTD Sales =
TOTALYTD(
[Total Sales],
'Date'[Date],
"09/30"
)
--可以自定义去除周末累加
Fiscal YTD Sales =
TOTALYTD(
[Total Sales],
'Date'[Date],
'Date'[Day Number of Week] IN {2,3,5,6}
)
---------------------------------
Prior Year Sales =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(
'Date'[Date])
)
---------------------------------
Prior Year Sales =
CALCULATE(
[YTD Sales],
SAMEPERIODLASTYEAR(
'Date'[Date])
)
---------------------------------
Product Inventory =
SUM('table'[Units Balance]
---------------------------------
Closing Balance (Last Date) =
CALCULATE(
[Product Inventory],
LASTDATE('Date'[Date])
)
-- 计算出上一个不为空的值,首先是你想要选择哪一列返回值,然后再给出怎么判断不为空,下面给出的例子是通过[Product Inventory]是否为空,来寻找date列的最新日期。
Closing Balance (Non Blank) =
CALCULATE(
[Product Inventory],
LASTNONBLANK('Date'[Date],[Product Inventory])
)
---------------------------------
Opening Balance Month =
OPENINGBALANCEMONTH(
[Product Inventory],
'Date'[Date]
)
--上一个月的期末余额为这个月的期初余额,但是OPENINGBALANCEMONTH解决不了BLANK问题,所以需要思考寻找上一个月的数。PARALLELPERIOD会返回所有的日期
Opening Balance Month (NON BLANK) =
CALCULATE(
[Product Inventory],
LASTNONBLANK(
PARALLELPERIOD(
'Date'[Date],
-1,
MONTH
),
[Product Inventory]
)
---------------------------------
对于Closing Balance (Last Date),6.30号没数,所以为空,对于Closing Balance (NON BLANK)则是通过Proeduct Inventory是否为空找到6月份最后一个不为空的值。
(To be continued...)
函数表: