Power Pivot常用函数

一.筛选类函数

1.ALL, ALLEXCEPT, ALLSELECTED

ALL(table/column) --清除整个表或某个字段的筛选条件, 通常配合CALCULATE使用

ALLEXCEPT(table/column) --删除表中除已应用于指定列的筛选器之外的所有上下文筛选器

ALLSELECTED(table/column) --从当前查询的列和行中删除上下文筛选器, 同时保留所有其他上下文筛 选器或显式筛选器

2.CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]]

CALCULATE(SUM('订单表'[数量]), '产品表'[颜色]="黑色" , '产品表'[尺码]="中码")，“且”条 件筛选

CALCULATE(SUM('订单表'[数量]), '产品表'[颜色]="黑色" && '产品表'[尺码]="中码")，“且”条 件筛选，错误写法，因为不同字段

CALCULATE(SUM('订单表'[数量]), '产品表'[颜色]="黑色" || '产品表'[颜色]="白色")，“或”条 件筛选，必须同一字段

CALCULATE+filter/all/values等筛选器函数可实现更复杂的数据筛选

3.FILTER(table, <filter>) --根据筛选条件返回一张表, 整表逐行扫描, 计算量很大, 所以尽量选择 维度表筛选

4.HASONEVALUE(<columnName>) --如果筛选columnName的上下文后仅剩一个非重复值，则返回TRUE， 否则返回FALSE

5.RELATED(<column>) --将“一”端表的字段数据关联到“多”端表，“一”端表即维度 表，“多”端表即事实表

RELATEDTABLE() --将“多”端表的字段数据关联到“一”端表, 由于返回的是一张表，所以 通常搭配聚合函数使用

6.VALUES(<TableNameOrColumnName>) --返回包含指定列中非重复值的列表，重复值被删除，仅返回 唯一值

7.EARLIER函数，例如计算每个产品的销售金额排名

COUNTROWS(FILTER('产品销售表', EARLIER('产品销售表'[销售金额]) < '产品销售表'[销售金 额])) + 1

--EARLIER可以理解为当前行, 按当前行进行逐行扫描

--SUMX+FILTER+EARLIER也是常用函数组合

--由于Earlier对每一行数据都进行计算，计算量相当于行数的平方

--所以数据量大时尽量少用, 可能导致运算缓慢

二.逻辑类函数

1.IF(<logical_test>, <value_if_true>[, <value_if_false>])

--检查条件，如果为 TRUE，则返回一个值，否则返回第二个值

2.SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

SWITCH(WEEKDAY([日期], 1), 1, "周日", 2, "周一", 3, "周二", 4, "周三", 5, "周四", 6, "周五", 7, "周六", "未知")

3.CONTAINS(InternetSales, [ProductKey], 214, [CustomerKey], 11185)

--是否存在客户ID为11185，且产品ID为214的销售订单

4.ISBLANK(column) --检查值是否为空白，并返回 TRUE 或 FALSE

5.ISNONTEXT(column) --检查值是否为非文本（空单元格为非文本），并返回 TRUE 或 FALSE

6.ISNUMBER(column) --检查值是否为数值，并返回 TRUE 或 FALSE

7.ISTEXT(column) --检查值是否为文本，并返回 TRUE 或 FALSE

8.ISLOGICAL(column) --检查值是否为逻辑值（TRUE 或 FALSE），并返回 TRUE 或 FALSE

三.日期时间类函数

1.计算年初至今累计，TOTALYTD函数

[年累计金额]：=TOTALYTD([销售金额], '日历表'[日期])

[截止某时点的年累计金额]：=TOTALYTD([销售金额], '日历表'[日期], '日历表'[日期]
<DATE(2016,6,1))

# 计算季初至今累计，TOTALQTD函数

# 计算月初至今累计，TOTALMTD函数

2.计算上一年/季/月的销售额，DATEADD函数

[上一年销售额]: =CALCULATE([销售金额], DATEADD('日历表'[日期], -1, YEAR))

# DATEADD第二参数为偏移数量：向过去偏移为负数，向未来偏移为正数

# DATEADD第三参数为时间单位：YEAR/QUARTER/MONTH/DAY

3.计算同比/环比增长率

[上一年销售额]: =CALCULATE([销售金额], DATEADD('日历表'[日期], -1, YEAR))

[同比增长率]: =DIVIDE([销售金额] - [上一年销售额], [上一年销售额])

[上一月销售额]: =CALCULATE([销售金额], DATEADD('日历表'[日期], -1, MONTH))

[环比增长率]: =DIVIDE([销售金额] - [上一月销售额], [上一月销售额])

4.计算两个日期之间的时间间隔，DATEDIFF函数

[时间间隔]: =DATEDIFF([开始日期], [结束日期], 'Y')

# DATEDIFF第三参数，年/月/日/时/分/秒: Y/M/D/HOUR/MINUTE/SECOND

5.其他日期时间函数

DATE(2009, 7, 8) --返回'2009/7/8 0：00：00'

DATEVALUE("8/1/2009") --将文本格式的日期转换为日期/时间格式的日期, 返回'2009/8/1 0：00：00'

DATEADD([日期], 3, DAY) --日期加减函数

EOMONTH([日期], 3) --返回开始日期之前或之后指定月数的月份的最后一天日期

EDATE([日期], 3) --返回开始日期之前或之后指定月数的日期

NOW() --返回当前的日期时间

TODAY() --返回当前的日期

WEEKNUM([日期], 1) --一年中的第几周, 1代表从周日开始计数, 2代表从周一开始计数

WEEKDAY([日期], 1) --一周中的第几天, 1代表从周日开始计数, 2代表从周一开始计数

YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

四.统计聚合函数

1. SUMX(table,<expression>)

--SUMX与CALCULATE功能类似, 但SUMX属于迭代函数, 计算量大, AVERAGEX/MINX/MAXX同理

2. SUM(), MAX(), MIN(), SQRT()

DISTINCTCOUNT() --对列中的非重复值数目进行计数

COUNTROWS() --计算表的行数

COUNT() --计算列中为数字的单元的数目

COUNTA() --计算列中不为空的单元的数目

COUNTBLANK() --计算列中为空的单元的数目

3.DIVIDE(分子, 分母) --安全除法，防止分母为零而报错

表操作函数，返回一张表

4.CROSSJOIN(<table1>, <table2>) --生成笛卡尔积表

GENERATE(<table1>, <table2>) --生成笛卡尔积表

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>,
<expression>]…)

SUMMARIZE('订单表', '订单表'[月份], '订单表'[产品], "销售量", SUM('订单表'[数量]), "销
售额", SUM('订单表'[金额]))

        --table, 数据表，或返回数据表的DAX表达式

        --groupBy_columnName, 分组字段

        --name, 聚合计算后生成新列的别名

        --expression, 聚合计算的DAX表达式

五.文本函数

1. LEFT(), RIGHT(), MID(), FIND(), SEARCH(), LEN() --字符串提取函数

2. REPLACE(), SUBSTITUTE() --字符串替换函数

3. TRIM(), LOWER(), UPPER()

4. FORMAT(), 日期格式化函数, 对于日期格式的自定义设置如下图：