powerbi知识大集合

2019-02-26 本文已影响375人 BI罗

两个文件：xlsx与pbi都在网盘

一、常见公式套路：

重要：理解dax公式有3点:
1.对 DAX 从内层向外逐层分解
2.内部上下文（例如sumx类迭代函数的所在的行），内部筛选条件（filter或布尔值），外部上下文（表格或矩阵的行标签），外部筛选条件（切片器等）
3.计算方式（聚合函数）怎么运行，在哪里运行。

1.DAX公式的灵魂：Calculate

1.1 筛选条件为空，不影响外部上下文

销量=calculate(sum(销量))

1.2 添加限制条件，缩小上下文

华为销量=calculate(sum(销量),' 产品明细 '[产品名]="华为")

1.3 结合 ALL 函数，扩大上下文

所有手机销量=calculate(sum(销量) ,all( ' 产品明细 '[产品名]))

1.4 结合 ALL 函数，重置上下文

所有手机销量=calculate(sum(销量) ,all( ' 产品明细 '[产品名]),' 产品明细 '[类别]="手机"))

2.单位成本

 单位成本 =
 CALCULATE (MIN (' 成本表 '[成本金额]), 
TOPN (1,
 FILTER (ALL (' 成本表 '), ' 成本表 '[生效日期]<= EARLIER (' 订单表 '[订单日期])&&
' 成本表 '[产品名称]=EARLIER (' 订单表 '[产品名称])),
 ' 成本表 '[生效日期] ) )

（EARLIER 函数只能用在添加计算列，EARLIER 函数只能用在添加计算列，EARLIER 函数只能用在添加计算列）

成本表.png

成本单价.png
2.1EARLIER 函数返回当前行对应的参数列，就是返回本行和参数列交叉的单元格。
2.2.FILTER (ALL (' 成本表 '), ' 成本表 '[生效日期]<= DATE (2018,2,8)&&' 成本表 '[产品名称]="手机"）
2.3TOPN 函数返回按生效日期排序的第 1 行。
2.4MIN 函数，实际上用 MAX 函数也是一样的，因为只有一个值，最小值和最大值是相同的，如果是标量值（数字）sum也可以。
2.5CALCULATE (MIN (' 表 2'[采购单价]),' 表 2')
链接：https://www.jianshu.com/p/f71ba89b9704

3.类别最大销售量字段名

类别最大销售量字段名 =
if(
HASONEVALUE('订单表'[子产品类别]),"",CALCULATE(MAX('订单表'[子产品类别]),
FILTER('订单表','订单表'[销售量]=MAX('订单表'[销售量]))))

3.1 新建表看看筛选的结果，表 = FILTER('订单表','订单表'[销售量]=MAX('订单表'[销售量]))

max表.png

3.2 CALCULATE+MAX ('订单表'[子产品类别])) 的方式获取当前筛选条件下的[子产品类别]) 名称（ps: 此处 max 只是为了将单一行聚合为值，也可以使用 min 替代）
3.3 if+HASONEVALUE('订单表'[子产品类别])返回值是否行的值，等于就返回空

类别最大.png

4.产品内排名

产品内排名 =
 RANKX(ALLEXCEPT('订单表','订单表'[产品名称]),CALCULATE(SUM('订单表'[销售量])))

在网页看到感觉这个公式其实是有问题的建议用：

自己简书的见解链接：https://www.jianshu.com/p/f521b9c7768d

rankx-all排序 = RANKX(ALL('订单表'[子产品类别]),CALCULATE(SUM('订单表'[销售量])))

产品内排名.png

如图产品名称列有两个筛选：电脑手机类还有子产品类共同筛选出一个子集（电脑—戴尔），allexcept保留了产品名称的筛选但是清除了子产品类的筛选（就剩下电脑类【戴尔，惠普，联想】，然后传递给外层的rankx排序）

5.销量排名第二的子产品

套用上面两个公式可以得到排名第二的产品名称

销量排名第二的子产品 = 
CALCULATE(MIN('订单表'[子产品类别]),
FILTER(VALUES('订单表'[子产品类别]),
RANKX(ALL('订单表'[子产品类别]),CALCULATE(SUM('订单表'[销售量])))=2))

排名第二.png
链接：http://dy.163.com/v2/article/detail/DSF5GJJF0516GFHA.html

6.中国式报表

//新建一个表输入公式
多层级表 = 
var a =ADDCOLUMNS(SELECTCOLUMNS('订单表',"L2",'订单表'[子产品类别],"L1",'订单表'[产品名称]),"L0","类别")
var b =ADDCOLUMNS(SELECTCOLUMNS('订单表',"L2",BLANK(),"L1",'订单表'[月份]),"L0","月份")
return UNION(a,b)

行列交叉的计算

多层级销售量 = 
SWITCH(TRUE(),
SELECTEDVALUE('多层级表'[L0])="月份",
CALCULATE(SUM('订单表'[销售量]),TREATAS(VALUES('多层级表'[L1]),'订单表'[月份])),
SELECTEDVALUE('多层级表'[L0])="类别",
CALCULATE(SUM('订单表'[销售量]),TREATAS(VALUES('多层级表'[L2]),'订单表'[子产品类别])))

中国式报表.png
链接：https://www.cnblogs.com/yeacer/p/9330081.html

7.总计不等于明细之和

大于2000的城市销量 =
CALCULATE('订单表'[总销售量],FILTER(ALL('订单表'[城市]),'订单表'[总销售量]>1500))

总计不等于明细之和.png
筛选条件：外部上下文的子类别（例如oppo）和内部筛选城市销量大于1500
魅族，小米，总计的数值都是独立计算的，都是正确的（总计算的是城市销量大于1500的所有总和），但是这图很别扭。

解决办法：
1.隐去总计

= IF (HASONEVALUE (' 订单表 '[子产品类别]),
 [大于2000的城市销量])，
BLANK()
//HASONEVALUE 判断外部上下文（例如总计）是否在订单表 '[子产品类别]中

= SUMX (' 订单表 ', [大于2000的城市销量]])
//或者
 = SUMX (FILTER (DISTINCT('订单表'[城市]),[总销售量]>1500),'订单表'[总销售量])

sumx是迭代函数，根据行标和'订单表'[城市]的交集查找并累计求和，而总计不在'订单表'[城市]中，就直接累计前面的求和

8.动态分析

8.1动态指标计算
输入数据，新建一个表给切片器

指标表.png
切片器水平设置

切片器水平设置.png

switch+selectedvalue创建动态度量

销售金额或销售数量指标 = 
SWITCH(TRUE(),
  SELECTEDVALUE('指标表'[分析指标])="销售金额",'订单表'[销售金额],
  SELECTEDVALUE('指标表'[分析指标])="销售量",'订单表'[总销售量])

动态指标.gif

8.2动态坐标轴

动态指标轴表 =
VAR a =
    SELECTCOLUMNS (
        ADDCOLUMNS ( VALUES ( '订单表'[城市] ), "城市 — 子产品指标", "城市" ),
        "城市 — 子产品指标", [城市—子产品指标],
        "指标明细", [城市]
    )
VAR b =
    SELECTCOLUMNS (
        ADDCOLUMNS ( VALUES ( '订单表'[子产品类别] ), "城市 — 子产品指标", "子产品" ),
        "城市 — 子产品指标", [城市—子产品指标],
        "指标明细", [子产品类别]
    )
RETURN
    UNION ( a, b )

SELECTCOLUMNS新建一个两列表，ADDCOLUMNS从别的表选一列并重命名。建好指标轴表，将[城市—子产品指标]放到切片器

动态订单数据 =
IF (
    HASONEFILTER ( '动态指标轴表'[城市—子产品指标] ),
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( '动态指标轴表'[城市—子产品指标] ) = "城市", CALCULATE (
            SUM ( '订单表'[销售量] ),
            TREATAS ( VALUES ( '动态指标轴表'[指标明细] ), '订单表'[城市] )
        ),
        SELECTEDVALUE ( '动态指标轴表'[城市—子产品指标] ) = "子产品", CALCULATE (
            SUM ( '订单表'[销售量] ),
            TREATAS ( VALUES ( '动态指标轴表'[指标明细] ), '订单表'[子产品类别] )
        )
    ),
    ERROR ( "数据错误，请单选坐标轴指标！" )
)

利用treatat函数关联动态指标轴表和订单表

动态坐标轴.gif

9.返回文本或者日期

sumx可以返回日期，但不能返回文本

earlier返回下一天时间 = SUMX(FILTER('销售情况',[序号]=EARLIER([序号])+1),'销售情况'[日期])

增加计算列利用calculate+min+earlier返回文本或日期

earlier返回文本 =
   CALCULATE(MIN('成本表'[产品名称]),FILTER('成本表','成本表'[产品名称]=EARLIER('成本表'[产品名称])))

10.时间智能函数

运用时间智能函数最好新建一个日期表，并与其它有关的表关联

时间智能函数.jpg

时间智能函数分为以下两类：
10.1 返回期间的时间智能函数
10.2 返回期间并执行运算的时间智能函数

链接：https://www.jianshu.com/p/10ffe1c2fb0a

求同期问题，昨天，上个月，上一年
1.利用today函数做锚点
2.利用{}把计算出的值转化为表

11.其他合并数值太大排序在最前问题

字段其他合并后数值总是排第一

其他合并排序.png

12.累计问题

12.1 M语言累计

Table.SelectRows筛选出比当前行小于等于的行，深化出[销售量],然后list.sum求和

= Table.AddColumn(已添加索引, "M累计", each List.Sum (Table.SelectRows (已添加索引,(x)=>x[索引]<=_[索引])[销售量]))

12.2 earlier累计
最重要的是新建索引列，因为销售量有可能相等，做不了比较

earlier累计 = CALCULATE(SUM('累计'[销售量]),FILTER(ALL('累计'),'累计'[索引]<=EARLIER('累计'[索引])))

12.3 var累计
推荐用var的方式，因为比earlier灵活且不需要新建列

var累计 = var a = [索引] 
return CALCULATE(SUM('累计'[销售量]),FILTER(ALL('累计'),'累计'[索引]<=a))

sumx替换calculate也可以

13.快速度量值-星级评分

星级评分.png

14.占比问题

ALL 和 ALLSELECTED 计算得出的4个占比
链接：https://www.jianshu.com/p/85ff4468ff4f
链接：https://www.jianshu.com/p/94970d0a0a1e

15.求移动平均迭代问题（不明白，以后再想）

正确写法，分开两个度量值

销售额1 = SUM('移动平均'[销售额])

移动平均 = AVERAGEX(DATESINPERIOD('日期表'[日期],MIN('日期表'[日期]),-3,DAY),[销售额1])

正确

合并写法显示错误的数据

移动平均sum = AVERAGEX(DATESINPERIOD('日期表'[日期],MAX('日期表'[日期]),-3,DAY),SUM('移动平均'[销售额]))

错误，两个数值重叠

为什么直接在公式内求和就出错了？永远得到当日的数据，而不是3天内的平均求和？sum是sumx的特殊形式，再建一个sumx的销售额看看

sumx销售额 = SUMX('移动平均',SUM('移动平均'[销售额]))

移动平均sumx = AVERAGEX(DATESINPERIOD('日期表'[日期],MAX('日期表'[日期]),-3,DAY),[sumx销售额])

** 1.这个公式内的度量值sum销售额或sumx销售额不受行上下文的影响，averagex的第一个参数传递给第二个参数（成为sumx的过滤条件）然后计算，如果在内部直接写聚合函数，聚合函数收到行上下文的影响，总是返回当前日期的销售额（3天的销售额都是当天再除以3，所以这个平均是错误的）。**
2.第二个解答：sum包含一个隐式的calculate，averagex的第一个参数作为calculate的筛选条件

16.earlier与earliest函数引发的上下文思考

（重要）
EARLIER()和EARLIEST()函数是DAX中较难掌握的两个函数
链接：https://zhuanlan.zhihu.com/p/32554533

17.模型关系

USERELATIONSHIP 可以激活候选关系
候选关系：假设两张表，订单表和日期表，订单表中有订单日期和发货日期，需要按照订单日期和发货日期分别进行分析，可是两个表之间只能有一个字段与日期表建立关系，如果按照订单日期与日期表建立了实线关系，那么发货日期与日期表建立的关系只能用虚线表示（激活后该计算中会屏蔽其中的实线关系）。

TREATAS 函数建立虚拟关系

各种关系的的速度：
实线连接：速度最快
备用的虚线：速度次之
虚拟关系：速度最慢，但最灵活，不受原模型的影响。

18.关系的筛选

单方向筛选只能从一端传递到另一端，双方向是相互的

筛选交叉.png

19.词根模糊匹配

匹配的城市 =
FIRSTNONBLANK (
    FILTER (
        VALUES ( '城市-词根模糊匹配'[城市] ),
        SEARCH ( '城市-词根模糊匹配'[城市], '详细地址-词根模糊匹配'[地址], 1, 0 )
    ),
    1
)

SEARCH ( '城市-词根模糊匹配'[城市], '详细地址-词根模糊匹配'[地址], 1, 0 )返回数字，
FILTER (VALUES ( '城市-词根模糊匹配'[城市] ),数字)，数字代表True，所以返回values一列
然后就不知道FIRSTNONBLANK 的原理了。
（留以后研究）

20.lookupvalue

查找语文成绩是90，数学是100的学生姓名

lookupvalue结果 =
LOOKUPVALUE ( '雷达学生成绩 透视'[学生姓名], '雷达学生成绩 透视'[语文], 90, '雷达学生成绩 透视'[数学], 100 )

21.条件筛选：HASONEVALUE vs. ISFILTERED vs. HASONEFILTER

=IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

=IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())

=IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())

链接：https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

作图技巧

1.其他合并数值太大排序在最前问题

在powerquery增加列

自定义序号.png

工具提示排序.png

这里偷懒了，其他为2，剩下的就是1，应该在数据源对字段的销量进行从大到小标记序号

2.新建表

直接新建表
2.2 合并 = union ('1 月 ','2 月 ')

2.1 先把这个表中的 [产品] 列和合并表中的 [商品名称] 建立关系，
join联结 = NATURALINNERJOIN (' 合并 ',' 采购表 ')

2.3 提取维度表
产品表 = DISTINCT (' 合并 '[名称])

2.帕累托图问题

帕累托图.png

帕累托1.png

上面的图有两个错误的帕累托图是因为其数据源非重复，造成累计占比计算出错

帕累托2.png

3.参数表与动态卡片（文本拼接计算结果）

在 PowerBI Desktop 中，在 “建模” 选型卡下，点击 “新建参数”，

参数值  = SELECTEDVALUE (' 参数 '[参数])
 
前N名利润 = 
"前"&[参数值]&"名利润总计"&UNICHAR(10)&CALCULATE([sum利润],TOPN([参数值],ALL('帕累托表'),[sum利润]))&"元"

UNICHAR(10)是换行符
卡片的显示方式可以去下载自定义可视化对象 - Rotating Tile
动态卡片教程链接：https://www.jianshu.com/p/a1a129fdb38d

4.书签

4.1普通书签跳转

从上方视图打开书签窗格，为谋一页报表制作书签，添加按钮（图片，形状皆可），操作选择书签
在桌面版PowerBI，按住Ctrl+鼠标点击才能跳转，网页发布版只需要点击

4.2也可以幻灯片方式放映：

书签放映.gif

4.3配合按钮同一页面内部分视觉图像的转换
如下图
重要：打开书签窗格，选择窗格，选择隐藏或显示视觉图像

4.4.切片器不同书签页面传递筛选结果
有两种方法：
1.利用同步切片器

同步切片器.png

2.制作书签时部锁定数据

切片器不同标签页面传递数据.png

5.自制作的切片器组隐藏

制作两个书签（隐藏或显示切片器组），添加两个按钮用于控制书签

切片器隐藏与否.gif
参考链接：https://www.cnblogs.com/yeacer/p/9844624.html

5.筛选交互

选中一个视觉，点击菜单的格式窗格，然后点击编辑交互
筛选传动可以点击视觉图像上的【突出显示】或者【禁止筛选】按钮

筛选交互.gif

6.排序技巧

类似文本的【1,2,3,12】，排序变成【1,12,2,3】
或者【一月，二月，三月】，排序变成【二月，三月，一月】
解决办法：在数据编辑页为需要排序的文本添加序号，然后排序按照序号来排

排序.jpg

DaxStudio

6.1编辑

EVALUATE
ROW("总分数",SUM('雷达学生成绩'[分数]))

以EVALUATE开头，并且可以在多个公式前加EVALUATE，主要用来逐步调试查看多个嵌套公式的结果

6.2格式化 DAX 代码
点击Format Query即可，在选项里可以选长/短格式

6.3从 pbix中导出数据

//导出一个现有表
EVALUATE 
' 学生成绩表'

//条件过滤
EVALUATE 
FILTER(' 学生成绩表 ',YEAR(' 学生成绩表 '[姓名])="小明")

//导出汇总表并按照年度和月份进行排序
EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        ' 日期表',
        ' 日期表 '[年度],
        ' 日期表 '[月份]
    ),
    "销售额", [销售金额]
)
ORDER BY
    ' 日期表 '[年度],
    ' 日期表 '[月份]

6.4导出所有表
在默认的功能区中没有显示导出所有表这个功能，在 option 中切换到 Advanced 页面，勾选：Show Export All Data button

6.5导出所有字段信息
在不需要导出所有数据的情况下，可以方便地查看字段分别是什么类型、大小分别是多少，以便对模型有个整体的了解

SELECT dimension_name AS table_name,
attribute_name AS column_name,
datatype,(dictionary_size/1024) AS size_kb
FROM $system.discover_storage_table_columns
WHERE dictionary_size > 0

6.6导出 PowerBI 文件中所有度量值

select
   MEASURE_NAME, 
   EXPRESSION
from $SYSTEM.MDSCHEMA_MEASURES
where MEASURE_AGGREGATOR = 0
order by MEASUREGROUP_NAME

6.7测试性能

链接：
https://www.jianshu.com/p/40b8c685583e
https://www.jianshu.com/p/8d1220aabd04
https://www.jianshu.com/p/185edd99083f
https://mp.weixin.qq.com/s/nWqrV3OXZKp1T2rCRv8bJw

7.分组

7.1新建表

7.2度量值

分组表销售金额 = SUM('分组'[销售额])

//一定要先写这个总销售额，下面的averagex是迭代函数才能正常运行

按平均分组销售 =
VAR avg_sale =
    AVERAGEX (
        ALL ( '分组'[品类] ),
        [分组表销售金额]
    )
RETURN
    IF (
        [分组表销售金额] >= avg_sale
            && SELECTEDVALUE ( '分组平均表'[按平均分组] ) = "高于平均值",
        [分组表销售金额],
        IF (
            [分组表销售金额] < avg_sale
                && SELECTEDVALUE ( '分组平均表'[按平均分组] ) = "低于平均值",
            [分组表销售金额]
        )
    )

7.3图例区分颜色，工具提示是用来按总金额排序的

7.4最后，年月用powerquery截取日期，加个年月切片器