一.筛选类函数
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(), 日期格式化函数, 对于日期格式的自定义设置如下图:
更多推荐
Power Pivot常用函数
发布评论