powerbi
Power Query(M)与DAX
- 首先使用Power Query(M)查询数据源，清理和加载数据
- 然后使用DAX分析Power Pivot中的数据
- 最后，使用Power BI构建数据透视表(Excel)或数据可视化
多端可以向一端索取，一端可以控制传递方向相同的多端数据
一端：维度表，多端：事实表。或者说：一端：数据表，多端：基础表
将第一行用作标题
右键表 -> 编辑查询（进入Power Query）-> 将第一行用作标题 -> 关闭并应用
Lookupvalue
一端：商品表，多端：销售表
销售表向商品表索取商品单价（根据商品名称）
单价 = LOOKUPVALUE('商品表'[进价],'商品表'[品名],'销售表'[商品名称])
RELATED
多端找一端
总价 = '销售表'[销售数量] * RELATED('商品表'[进价])
RELATEDTABLE
一端找多端
订单数 = COUNTROWS(RELATEDTABLE('销售表'))
度量值
总销量 = SUM('销售表'[销售数量])
度量值和新建列的区别
- 度量值自带筛选功能，新建列是行上下文，行上下文没有筛选功能
- 想让行上下文实现筛选功能就要在外面套一个CALCULATE
CALCULATE
筛选引擎，如果在度量值上使用，建议使用一端的字段作为筛选条件，否则会出现意想不到的结果
支持字符串和数值
A产品销量 = CALCULATE([总销量],'商品表'[品名]="A")
A01产品销量 = CALCULATE([总销量],'商品表'[品名]="A",'商品表'[进价]=0.1)
产品销量IN = CALCULATE([总销量],'商品表'[品名] in {"A","B"})
产品销量NOT_IN = CALCULATE([总销量],not '商品表'[品名] in {"A","B","C"})
CALCULATETABLE
筛选表，根据筛选条件，生成一张新表
CALCULATETABLE(表，筛选条件)
新表 = CALCULATETABLE('销售表','商品表'[品名]="A",'商品表'[进价]=0.1)
FILTER
高级筛选器
返回一个表，用于表示另一个表或表达式的子集，不能单独使用
可以配合 CALCULATE、COUNTROWS等使用
CALCULATE(表达式(度量值),Filter('表名', 筛选条件))
COUNTROWS(Filter(表，筛选条件))
在CALCULATE中直接使用筛选条件，只能输入一些固定值进行过滤
但是遇到如下情况，就要使用Filter函数来进行过滤
- [列]=[度量值]
- [列]=公式
- [列]=[列]
- [度量值]=[度量值]
- [度量值]=公式
- [度量值]=固定值
- 等等
filter与上下文
- 总分1 = CALCULATE([总分],FILTER('同学录',[总分]>250)) # 正确
- 总分2 = CALCULATE([总分],FILTER('同学录',SUM('成绩表'[分数])>250)) # 错误
- 总分2 = CALCULATE([总分],FILTER('同学录',Calculate(SUM('成绩表'[分数]))>250)) # 正确
因为，SUM('成绩表'[分数])不是度量值，[总分]才是度量值
VALUES
使用 FILTER 进行过滤时，我们需要使用一端表。但有时候我们只有一张明细表
这个时候就可以配合 VALUES 函数来一起使用
假设：同学录是一端表，成绩表是多端表（包含：语文、数学、英语）
总分1 = CALCULATE([总分],FILTER('同学录',[总分]>250))
总分2 = CALCULATE([总分],FILTER(VALUES('成绩表'[学号]),[总分]>250))
Values函数生成的表是一张虚拟表，在很多时候使用Filter、Calculate、Countrows、SumX、TopN等函数，需要引用表而不能直接引用列
DISTINCT
DISTINCT和VALUES的区别，大部分场景直接使用DISTINCT就行了
VALUES可以用于发现异常数据，比如：销售表的商品在商品表没有，且这2个表存在关联关系（连线了）
当你使用'商品表'[商品名称]进行相关统计的时候，会发现没有商品名称的行（因为销售表中有异常数据）
- Values(表[列])：对列去重，生成一张新表
- Values(表)：复制一张表，没有去重效果
- DISTINCT(表名[字段名])：跟Values效果相同
- DISTINCT(表名)：对表去重，生成一张新表
- DISTINCT(返回表的表达式)
ALL、ALLSELECTED、ALLEXCEPT
ALL函数常用于统计占比（用于清除度量值天生具有的筛选功能）
销售表中的商品数量 = Calculate(CountRows('商品表'), '销售表')
不能筛选的总数 = CountRows(ALL('商品表'))
占比 = [销售表中的商品数量] / [不能筛选的总数]
总结：
- 当ALL参数为表时，忽略所有的筛选条件，无论是该图表内还是外部切片器
- 如果希望外部切片器产生联动，使用ALLSELECTED函数
- 当ALL参数为列时，忽略该列筛选，其它图表字段或外部切片器对其产生作用
- ALL函数在引用列的时候，必需与矩阵的行和列在同一张表
- ALLEXCEPT排除列，ALLEXCEPT(表名[排除列])等同于All(表名[选中列1],表名[选中列2]...)
ALLNOBLANKROW
作用：
- 去重，如果是对表去重，那么相关行上的所有字段都要相同
- 多表关联使用时，会忽略空行
最常用的方式是和ALL一起使用，核对差异
假设一班有学生：张三、李四、王五、马六
成绩表为：张三、李四、王五、马六、赵七，93、94、95、96、97
PS：成绩表统计错了，赵七不是一班的
总分ALL = CALCULATE('成绩'[总分],ALL('一班') )
总分ALLNOBLANKROW = CALCULATE('成绩'[总分],ALLNOBLANKROW('一班'))
总分ALL能看到赵七为97分，而总分ALLNOBLANKROW不能看到赵七的分数
日期函数
- 年 = YEAR([时间日期])
- 月 = MONTH([时间日期])
- 日 = DAY([时间日期])
- 时 = HOUR([时间日期])
- 分 = MINUTE([时间日期])
- 秒 = SECOND([时间日期])
- 季度 = QUARTER([时间日期])
- 当前日期和时间 = NOW()
- 当前日期 = TODAY()
字符串函数
字符串拼接，直接使用 & 符号
- 连接文本 = CONCATENATE([文本1],[文本2])
- 连接数字 = CONCATENATE([数字1],[数字2])
常用聚合函数
总数 = SUM('表'[字段]) 平均数 = AVERAGE('表'[字段]) # 1、2、a、3 1+2+3/3 平均数A = AVERAGEA('表'[字段]) # 1、2、a、3 1+2+3/4 最大值 = Max('表'[字段]) 最小值 = Min('表'[字段]) 记录数 = Countrows('表') 去重计数 = DISTINCTCOUNT('表'[字段]) CountA函数：计算列中单元格不为空的数目 Countblank函数：计算列中单元格为空白数量 Product函数：计算列中单元格乘积
常用迭代函数
净值 = [收入] - [支出]
求和 = sum(净值)
合并上面2句 = CALCULATE((sumx('表',[收入] - [支出])))
SumX、AverageX、MaxX、MinX、CountX、CountaX、ProductX...它们和Filter函数一样都是行上下文函数
Earlier函数
当前行函数，可以配合聚合函数或迭代函数一起使用
日期比当前行小，且商品和当前行一致的产品销量汇总
当前日期的销量汇总 = SUMX(FILTER('表','表'[日期字符串]<=EARLIER('表'[日期字符串]) && '表'[商品] == EARLIER('表'[商品])),'表'[销售量])
日期比当前行小，且购买人姓名相同的购买次数汇总
购买次数 = COUNTROWS(FILTER('表','表'[姓名] == EARLIER('表'[姓名]) && '表'[日期字符串] <= EARLIER('表'[日期字符串])) )
条件判断
IFERROR，如果[销售数量]或[单价]不是数字，会发生错误，用空替代（也可以用数值替代，不能用字符串）
销售金额 = IFERROR([销售数量] * [单价],BLANK())
IF，第2个参数可以必填，不填默认返回空
称呼 = IF([性别]="男","先生","女士")
switch，多条件用switch
月份 = SWITCH([星期],0,'星期日', 1, "星期一", 2,"星期二", 3, "星期三", 4,"星期四", 5, "星期五", 6,"星期六","没有匹配，可不填")
switch，进阶用法（POWERBI中没有elseif，用这种方式写）
评级 = SWITCH(TRUE(),[分数]>=90,"优",[分数]>=80,"良",[分数]>=60,"中","差")
安全除法
替换结果可不填，不填默认空，即：blank()
DIVIDE(分子,分母,[替换结果])
ISINSCOPE
按层级计算，当指定的列是级别层次结构中的级别时，返回true
配合SWITCH函数，可以实现按层级进行百分比统计
'销售表'[总销量] = SUM('销售表'[销售])
层级占比 = SWITCH(TRUE(), ISINSCOPE ('商品表'[商品名称]), DIVIDE([总销量],CALCULATE([总销量],ALLSELECTED('商品表'[商品名称]))), ISINSCOPE ('商品表'[产品类别]), DIVIDE([总销量],CALCULATE([总销量],ALLSELECTED('商品表'[产品类别]))), DIVIDE([总销量],CALCULATE([总销量],ALLSELECTED('商品表'[商品名称]))))
ISCROSSFILTERED和ISFILTERED
ISCROSSFILTERED用于判断某一个表中是否含有筛选字段
ISFILTERED用于判断某一字段是否有筛选关系
FIRSTNONBLANK与LASTNONBLANK
以FIRSTNONBLANK为例，LASTNONBLANK用法一样
筛选第一个销售不为空的日期，CALCULATE->SUM是为了提供一个筛选环境
表 = FIRSTNONBLANK('表'[日期], CALCULATE(SUM('表'[销售])))
筛选每人第一个不为空的销售，先筛选出第一个不为空的日期，再筛选对应的销售额
度量值 = CALCULATE (SUM ( '表'[销售] ),FIRSTNONBLANK ( '表'[日期], CALCULATE ( SUM ( '表'[销售] ) ) ))
复用数据集
把所有需要用到的数据（MySQL、sqlserver等），发布到一个或多个工作区（根据模块归类）
建报表的时候，统一使用数据中心 -> Power BI数据集
数据预处理
如果需要用到数据流（ETL）的，在powerbi server上面，新建数据流，做一些清洗、转换等配置
然后在数据集中引用数据流即可
部署管道
环境隔离，如：开发 -> 测试 -> 发布
禁用隐式度量值
外部工具 -> Tabular Editor（插件） -> 选中Model -> Discourage Implicit Measures -> true
禁用自动日期
文件 -> 选项和设置 -> 选项 -> 数据加载 -> 自动日期/时间 -> 反勾选
运行Python
文件 -> 选项和设置 -> 选项 -> Python -> 设置Python路径
在power bi中运行python一共有四处地方
①. 获取数据，Python脚本。所有的dataframe都可以被读入
import pandas as pd
df = pd.DataFrame({"w": [1, 2, 3], "x": [4, 5, 6], "y": [7, 8, 9], "z": [10, 11, 12]}, index=list('abc'))
②. power query中，选择转换选项卡 -> 运行Python脚本 -> 以增加一列为例
dataset['new_column'] = dataset['z'] * 100
点击列表Value右上角的图标展开，勾选new_column字段
在power query的主页 -> 高级编辑器，可以查看M语言中是如何与python交互的
③. 利用python绘图，视觉对象仪表盘 -> 选择Py -> 利用Python的绘图包绘图，如：matplotlib
import pandas as pd
import matplotlib.pyplot as plt
x = dataset['x']
y = dataset['y']
# 数据准备，x最好是升序的，否则画出来的就不是折线图
# x = [2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021]
# y = [5, 3, 6, 20, 17, 16, 19, 30, 32, 35, 666, 888]
# 使用Matplotlib画折线图
plt.plot(x, y)
plt.show()
④. 导出数据，power query中，选择转换选项卡 -> 运行Python脚本 -> 以导出csv为例（编码一定要gbk）
dataset.to_csv(r'D:test.csv', encoding='gbk')