Power BI-DAX

Power BI-DAX

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2016/ee634396(v=sql.130)

https://dax.guide/

1、数据的提取和合并

根据数据的位置提取数据
left:从左取

=left([字段],3)     # 从左取3位

right：从右取（写法与left一致）
mid：中间取

=mid([字段],4，3)    # 从第4位开始取3位（索引值从1开始，取的时候包括第4位）

len：长度
find/search：查找

=find("要找的东西",[从哪个字段找])
=search("要找的东西",[从哪个字段找])
    
# 两个返回的都是索引值，基本一样，find区分大小写，search不分大小写

=find("要找的东西",[从哪个字段找],开始查找的位置,找到返回值)
=find("要找的东西",[从哪个字段找],1,0)

# 后面两个参数可以不写

CT-21911后面的数字长度不一样，要想提取后面的数字：(下图）
len([会员ID])：整个字符串的长度（8）
find("-",[会员ID])：‘-’的位置是3
那么8-3=5就是右边数字的长度
right：从右边提取数字


=right([会员ID],len([会员ID])-find("-",[会员ID]))

求年龄

=year(today())-[出生年代]

2、数据的格式转换

value：转换成数值
format：日期或数字格式的转换
https://mp.weixin.qq.com/s?__biz=MzI4NTEzNzQ2NQ==&mid=2650500289&idx=1&sn=b171ff6552b4833095fa7f272f2b5726&chksm=f3ff0397c4888a81611dba91abc62717cff19b906b0c6feeebdd3b874efa57374e5b560f91d2&scene=21#wechat_redirect

=format([日期],"yyyy-mm-dd")

格式规范	说明
y	将年显示为不带前导零的数字（0-9）。如果这是用户定义数字格式的唯一字符，则使用%y。
yy	以带一个前导零的两位数字格式显示为年（如果适用）。
yyyy	以四位数字显示格式显示年
M	将月显示为不带前导零的数字（例如一月：1）。如果这是用户定义数字格式的唯一字符，则使用%M。
MM	以带一个前导零的两位数字格式显示为月（如：01、02、03）。
MMM	将月显示为缩写（如：Jan）
MMMM	将月显示为完整月份，如：January
d	将天显示为不带前导零的数字（例如一号：1）。如果这是用户定义数字格式的唯一字符，则使用%d。
dd	以带一个前导零的两位数字格式显示为天（如：01、02、03）。
ddd	将天显示为缩写（如：Sun）
MMMM	将天显示为完整月份，如：Sunday

replace：替换

# CT-21911中第4位的2替换成8（CT-81911）

=replace([会员ID],4,2,8)

substitute：查找和替换

# CT-21911中的“-”替换成“/”  -->  CT/21911

=substitute([会员ID],"-","/")

3、常用的DAX函数

3.1、聚合函数

函数	说明
SUM	求和
AVERAGE	求平均值
MEDIEN	求中位数
MAX	最大值
MIN	最小值
COUNT	数值格式的计数
COUNTA	所有格式的计数
COUNTBLANK	空格计数
COUNTROWS	表格行数
DISINCTCOUNT	不重复计数

COUNT和COUNTA不计空白值

3.2、逻辑函数

函数	说明
IF	根据某个/几个逻辑判断是否成立，返回指定的数值
IFERROR	如果计算出错，返回指定值
AND	逻辑关系的“且”-&&
OR	逻辑关系的“或”-ll
SWITCH	数值转换

=if([金额]>1000,"Y","N")

AND里面只能写两个表达式，如果需要三个就需要在写一个AND，嵌套

=AND([金额]>1000,[送货时长]<10)

多个并列可以有个两个连接符

=IF([金额]>1000&&[送货时长]<10,"T","F")

IFERROR

# 如果计算错误返回0，或返回空白（BLANK())
=IFORROR([销售金额]/[任务额],0)

SWITCH

=SWITCH([客户省份],"广东","北上广","北京","北上广","上海","北上广","非北上广")

=SWITCH(true(),[客户省份]="广东"||'订单明细'[客户省]="北京"|| '订单明细'[客户省]="上海","北上广","非北上广")

3.3、信息函数

函数	说明
ISBLANK	是否空值
ISNUMBER	是否数值
ISTEXT	是否文本
ISNONTEST	是否非文本
ISERROR	是否错误

返回TRUE、FALSE

3.4、数学函数

函数	说明
ABS	绝对值
ROUND	四舍五入
ROUNDUP	向上舍入
ROUNDDOWN	向下舍入
INT	向下舍入到整数（取整数）

3.5、文本函数

3.6、转换函数

函数	说明
FORMAT	日期或数字格式转换
VALUE	转换成数值
INT	转换成整数
DATE	转换成日期格式
TIME	转换成时间格式
CURRENCY	转换成货币

3.7、日期和时间函数

# 求保质期至
=EDATE([生产日期],[保质期])

3.8、关系函数

函数	说明
RELATED	从“一”端提取/引用“多”端的列值
RELATEDTABLE	从“多”端提取/引用“一”端的相关行的表格

相当于表格的vlookup

3.9、Calculate函数

=Calculate(表达式(度量值),筛选条件1,...筛选条件n)

把条件写死，不受切片器的影响

3.10、divide(安全除法）

3.11、手写切片器

营业收入 = SWITCH(TRUE(),
SELECTEDVALUE('抵消-筛选器'[抵消筛选])="抵消前",
CALCULATE(SUM('事实表-实际&预算&预测明细'[实际金额]), '事实表-实际&预算&预测明细'[一级科目] IN { "营业收入" }),
SELECTEDVALUE('抵消-筛选器'[抵消筛选])="合并后",
CALCULATE(SUM('事实表-实际&预算&预测明细'[合并后实际金额]), '事实表-实际&预算&预测明细'[合并后一级科目] IN { "营业收入" })

3.12、小技巧

power bi怎样获取时间切片器上的值，如何让时间筛选只跟一个区间的最后一个时间有关？

①、我先将Date类型的字段拖进去后，选择TopN，设置Top1后在把Yearmonth字段拖进去，设置最大值的Yearmonth。完成！

②、

只显示切片器最后一个时间的值 = 
IF(
MAX('日期表'[月份])=MAXX(ALLSELECTED('日期表'[月份]),'日期表'[月份]),
[销售金额]
)

# 切片器的最大值，最小值的取值
MAXX(ALLSELECTED('维度表-时间'[月]),'维度表-时间'[月])
MINX(ALLSELECTED('维度表-时间'[月]),'维度表-时间'[月])

# 使用FILTER先过滤下，使得返回一张表日期只有最大月份的
人数 = CALCULATE(SUM('事实表-人员清单'[在职人数]),FILTER('事实表-人员清单','事实表-人员清单'[报表期间月]=MAXX(ALLSELECTED('维度-时间'[月ID]),'维度-时间'[月ID])))


https://www.zhihu.com/question/328099158

VAR的用法

分组聚合函数- SUMMARIZE

查询语句

返回的是一张表格

=FILTER('销售记录','销售记录'[产品]="自行车")

=CALCULATE(SUM('销售记录'[金额],'销售记录'[产品]="自行车")

summarize：汇总功能，相当于group by

summarize(表格,汇总列1,....,"名称"，表达式)

返回的是一张表格，最终的结果是汇总所有订单下的销售金额总和
=summarize('销售记录','销售记录'[订单编号],"金额",'销售记录'[销售金额])

RFM分析

定义：

类别	含义	分析语言	演示公式
R	Recent近度	最近一次交易到今天的天数	MIN([近度])；[近度]=TODAY()-[下单日期]
F	Frequency频度	交易次数	DISTINCTCOUNT([订单编号])
M	Monetary平均消费额度	交易金额/交易次数	DIVIDE(‘销售记录’[销售金额],‘销售记录’[订单数量])

客户分类：

R	F	M	客户类型
近	高	高	重要价值客户
远	高	高	重要保持客户
近	低	高	重要发展客户
远	低	高	重要挽留客户
近	高	低	一般价值客户
远	高	低	一般保持客户
近	低	低	一般发展客户
远	低	低	一般挽留客户

近度
近度=today()-[下单日期]         (新建列）
R=MIN([近度])                       （度量值）

订单的不重复计数
F=DISTINCTCOUNT([订单编号])                （度量值）

M=DIVIDE('销售记录'[销售金额],'销售记录表'[F])            （度量值）

=SUMMARIZE('销售记录','销售记录'[客户名称],'销售记录'[客户ID],"R",'销售记录'[R],"F",'销售记录'[F],"M",'销售记录'[M])

GENERATE(笛卡尔积表）

=generate(表1,表2)

高级聚合函数

函数	说明
SUMX	求和
AVERAGEX	求平均值
MAXX	求最大值
MINX	求最小值
COUNTX	数值格式的计数
COUNTAX	所有格式的计数
MEDIENX	求中位值
RANKX	排名

SUMX：求表达式里的和（单价*金额）
=SUMX('销售记录','销售记录'[单价]*'销售记录'[数量])

常用的筛选器函数

函数	说明
FILTER	按条件筛选数据
VALUES	返回列或者表去重后的结果\
TOPN	返回前几名的数据\
ALL	所有数据
ALLEXCEPT	所欲数据除了…
ALLNONBLANKROW	返回非空白的数据

FILTER

=FILTER('销售记录','销售记录'[产品]="自行车")

VALUES

返回非重复的一列值（去重之后的数据）
=VALUES('销售记录'[产品名称])

TOPN

这张表的前3行
TOP3=TOPN(3,'销售记录')

销售金额的前3（默认降序）
TOP3=TOPN(3,'销售记录','销售记录'[金额])
升序
TOP3=TOPN(3,'销售记录','销售记录'[金额],ASC)

Except
Except(A, B)
A中除去B中的剩下所有。（A-B）如下图阴影部分

![在这里插入图片描述](https://img-blog.csdnimg.cn/20191011141852992.png

ALLEXCEPT(除什么之外的所有)

返回新的一张表里面会少“产品名称”这一列
=ALLEXCEPT('销售记录','销售记录'[产品名称])

求区域经理对应的销售金额，区域经理跟销售记录，省份区域都没有关联

=SUMX(FILTER('销售记录','区域经理'[区域]=RELATED('省份区域'[区域])),'销售记录'[金额])

EARLIER

=SUMX(FILTER('产品销售表',EARLIER('产品销售表'[销售金额])<='产品销售表'[销售金额]),'产品销售表'[销售金额])

累计百分比（all）

='产品销售表'[销售金额]/sumx(all('产品销售表'),'产品销售表'[销售金额])

使用earlier进行排名

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

EARLIER区分新老客户
金额有数值就是老客户，空白的就是新客户

=IF(SUMX(FILTER('销售记录',EARLIER('销售记录'[客户ID])='销售记录'[客户ID]&&EARLIER('销售记录'[下单日期])>'销售记录'[下单日期]，'销售记录'[金额])>0,"老客户","新客户")

%=[销售额]/CALCULATE([销售额],ALL'产品分类'))

USERELATIONSHIP

两张表之间是虚线关系,需要用USERELATIONSHIP来表明关系

=CALCULATE(SUM([数量]),USERELATIONSHIP('销售记录'[实际送货日期],'日历年'[日期])）

RELATEDTABLE

排名：可以展开二级分类，在分类里可以有自己的排名

参数DESC/ASC,升降序；Dense/Skip：相同排名是否跳过去
=RANKX(ALLSELECTEC('产品分类'),'销售记录'[销售额])

为了排除总计算进去排名

HASONEVALUE：在这张表里找到的产品名称就是TRUE，没有就是FALSE
H=HASONEVALUE('产品分类'[产品名称])

=IF(H,RANKX(ALLSELECTEC('产品分类'),'销售记录'[销售额]),BLANK())

TOP10分析
根据切片器的选择进行排名规则，前N名

MIN=MIN('排序依据'[ID])

SWITCH=SWITCH('排序依据'[MIN],1,'销售记录'[销售额],2,'销售记录'[销售数量],3,'销售记录'[订单数量])

RANK=RANKX(SUMMARIZE(ALL('销售记录'),'销售记录'[客户名称],'排序依据'[SWITCH])

经销商RFY分析

缩写	解释	公式
R	Reach，达成	DISTINCTCOUNT([客户名称])
F	Frequency，频率	订单数/客户数
Y	Yield，收益	销售额/订单数

使用父子层级

创建层级
层级path=path('打车记录'[姓名],'打车记录'[经理])

层级深度
=PATHLENGTH('打车记录'[层级path])

移动平均

用量增长分析

当月MTD=TOTALMOD('用量'[总用量],'日历年'[日期],'日历年'[日期]'<TODAY())

上月PM=CALCULATE('用量'[总用量],PREVIOUSMONTH('日历年'[日期]))

月度净增长='用量'[当月MTD]-'用量'[上月PM]

powerbi知识大集合

https://www.jianshu.com/p/ad534d6b84c7