认识PowerBI数据建模

Excel建立数据模型

        使用excel数据建模，将用到的表格加载到PowerQuery中，关闭并上载至选择仅创建链接，勾选添加到数据模型，再选择PowerPivot-数据模型-管理，选择关系视图建立各个表格之间的关系，客户城市表-城市对应订单表-客户城市，仓库表-仓库对应订单表-发货地，产品表-产品名称对应订单表-产品名称，客户表-客户姓名对应订单表-客户姓名，日期表-日期对应订单表订单日期。

        点击数据视图，书写度量值“销售金额汇总:=SUM([销售额])”，选中度量值右键选择格式可以更改度量值的格式。

        再点击数据透视表，选择新工作表，值选择度量值销售金额汇总，选择不同的行标签和列标签可以查看对应的汇总值。

        点击数据透视表，选择数据透视表分析-筛选-插入切片器。选中切片器右键选择报表连接，勾选两个数据透视表就可以同时筛选两个数据透视表。

PowerBI建立数据模型

        先将excel工作簿导入PowerBI，选择对应的表格选择转换数据，在PowerQuery中检查数据类型，没有问题点击关闭并应用，表格就会被加载到PowerBI，可以在表格视图查看。

        选择关系视图，建立表格之间的关系，默认设置的关系一般是错误的，需要重新建立关系，选择表格和表格之间关联的字段，拖动即可，比如把客户城市表的城市拖动到订单表的客户城市上，就可以建立关系。

        建立好关系好切换回表格视图，在右边的数据窗口点击订单表，界面上方选择表工具-计算-新建度量值，输入公式“销售额汇总=SUM('订单表'[销售额])”,回车，就可以在订单表中找到销售额汇总这个度量值，点击度量值可以修改度量值的格式。

        点击报表视图，在可视化窗口选择“矩阵”，将销售额汇总拖入值，产品表产品类别拖入行，日期表年度拖入列，就可以得到与excel中同样的数据透视表，点击空白处，再选择可视化窗口中的切片器，拖入产品类别，再点击切片器，拖入年度，就可以根据切片器得到不同年度不同产品类别的销售额汇总。可以在设置视觉对象格式-视觉对象-切片对象设置-垂直列表，设置切片器的样式。

        可以选择可视化窗口的设置视觉对象格式，网格-选项设置字体大小。

PowerBI数据建模及DAX函数

基本概念

相关知识点：

        1-  字段：表的一列，列名就是字段名，注意字段类型的检查；

        2-  计算列：也属于字段，但它不是从原始数据加载进来的，而是在数据模型中，使用DAX新建的列。

        说明：计算列存储在数据模型中，占用内存，在数据量较大的表中添加计算列，会对数据模型的内存产生显著影响，所以一般不建议使用新建列。

        中文月份计算公式：=FORMAT([日期],"OOOO")

        3- 度量值：是PowerBI数据分析的核心，与计算列一样同样是使用DAX建立，但它不属于任何表；

        新建的度量值保持休眠状态，不执行计算，直到将其用于视觉对象；

        度量值计算出的结果是动态的，在不同的上下文中，执行不同的计算；

        度量值可以快速响应用户交互，但不将输出存储在数据模型中，因此对数据模型的物理大小没有影响，是数据分析首选。

        4- DAX：Data Analysis Expression的缩写，也就是数据分析表达式，是PowerBI中的数据建模语言，DAX是一种专门为计算数据模型中的商业逻辑而设计的语言。计算列和度量值都是用DAX生成的。

        5- 上下文：是理解DAX的核心概念，简单来讲，上下文就是DAX代码所处的外部环境，它有分为筛选上下文和行上下文。

        筛选上下文对数据进行筛选，行上下文对表进行迭代，行上下文一般可以理解为表的当前行，并不产生筛选作用；计算列和度量值的区别之一就是所处的上下文的不同。

        6- 事实表和维度表：事实表又叫明细表，表示业务开展而产生的结果记录；维度表又叫查找表，通常用来做分析问题的角度。

        7- 关系：两个表之间的联系称为关系，它是数据建模最基础也是最重要的概念。

        8- 数据模型：在PowerBI中，字段(计算列)、度量值、事实表、维度表、关系的集合构成了数据模型。

        如果要添加计算列可以在表格视图，选择需要添加列的表格，表格工具-计算-新建列，输入公式就可以添加计算列，比如在日期表中添加“中文月份”和“星期几”这两列。分别输入公式“中文月份 = FORMAT('日期表'[日期],"OOOO")”和“星期几 = FORMAT('日期表'[日期],"aaaa")”，就可以在日期表中新增两列。

        事实表一般是又高又瘦的，维度表一般是又矮又胖的。

认识PowerBI中的度量值

相关知识点：

        1- 度量值：是PowerBI数据分析的核心，与计算列一样同样是使用DAX建立，但它不属于任何表；

        新建的度量值保持休眠状态，不执行计算，直到将其用于视觉对象；

        度量值计算出的结果是动态的，在不同的上下文中，执行不同的计算；

        已经写好的度量值的结果可以复用；

        度量值可以快速响应用户交互，但不将输出存储在数据模型中，因此对数据模型的物理大小没有影响，是数据分析首选。

问题：

        如何计算本年累计销售额的同比增长率？

        更进一步，如何按月、按年、按产品类别、按城市来分析同比增长率？

        同比增长率 = (销售额_本年度累计 - 销售额_去年累计)/销售额_去年累计

        销售额_本年度累计 = 销售额_本年度累计 = CALCULATE([销售额汇总],DATESYTD('日期表'[日期]))

        销售额_去年累计 = CALCULATE([销售额_本年度累计],SAMEPERIODLASTYEAR('日期表'[日期]))

        同比增长率 = DIVIDE([销售额_本年度累计]-[销售额_去年累计],[销售额_去年累计])

        SAMEPERIODLASTYEAR：返回上一年度当前选择的一组日期

        DATESYTD：返回此年度中截止当前日期的一组日期

        这里的度量值是可以在后续其他度量值的计算中使用的。

        插入两个切片器，字段分别选择产品类别/城市，年，就可以分析不同情况下的同比增长率。

        

了解DAX

DAX语法

相关知识点：

        1-  DAX：Data Analysis Expression的缩写，也就是数据分析表达式，是PowerBI中的数据建模语言，DAX是一种专门为计算数据模型中的商业逻辑而设计的语言。计算列和度量值都是用DAX生成的。

        数据分析可以归纳为从大量数据中筛选、分组、汇总的过程，而DAX的主要功能正是查询和运算，DAX查询函数负责筛选出有用的数据集合，然后利用DAX的聚合函数执行计算，它PowerBI数据分析的灵魂。

        2-   哪些地方可以使用DAX？

        度量值、新建列(计算列)、新建表。

        3-  DAX基本语法：

              销售额_本年累计 = CALCULATE([销售额汇总],DATESYTD('日期表'[日期]))

              3.1 表达式以等号开始；

              3.2 等号(=)前面是表达式名称，如果DAX用于建立的是度量值，它就是度量值名称，如果用于建立计算列，就是计算列名，如果用于建新表，它就是表名；

              3.3 函数后面都用括号()括上参数，参数之间用逗号分隔；

              3.4 表名用单引号''包裹，如：'日期表'

              3.5 字段用中括号[]包裹，并带上表名，如：'日期表'[日期]

              3.6 度量值也用中括号[]包裹，如：DIVIDE([销售额_本年累计]-[销售额_去年累计],[销售额_去年累计]);

              3.7 输入=、()、''、[]等符号时候，都需要将输入法切换至英文状态;

        为了便于区分和增强DAX代码的可读性，字段应始终跟随表名一起书写，而度量值始终不要带表名，因为它不依赖于任何表，可以单独书写。

        新建表可以切换到表格视图，表工具-计算-新建表，比如“表 = GENERATESERIES(1,10,1)”返回一列，数据1-10。

        GENERATESERIES：返回具有一列,并且从始至终用连续值填充的表

格式规范

相关知识点：

DAX格式规范：

        1- 如果函数只有一个参数，则和函数放在同一行；

        2- 如果函数具有两个或更多参数，则将每一个参数都另起一行；

        3- 如果函数及其参数写在多行上：

              3.1 左括号“(”与函数在同一行

              3.2 参数是新行，从该函数对齐位开始缩进4字符

              3.3 右括号“)”与函数开头对齐

              3.4 分隔两个参数的逗号位于前一个参数的同一行

              3.5 如果必须将表达式拆分为更多行，则运算符作为新行中的首字符。

 相关快捷键：

        换行并缩进：Shift+Enter

        换行不缩进：Alt+Enter

        向右缩进：Ctrl+]

        向左缩进：Ctrl+[

常用函数

常用的DAX函数：(官方DAX文档地址：https://learn.microsoft.com/zh-cn/dax/）

聚合函数

• SUM/SUMX
• AVERAGE/AVERGAEX
• MIN/MINX
• MAX/MAXX
• COUNTROWS
• DISTINCTCOUNT

 时间智能函数

• PREVIOUSYEAR/QUARTER/MONTH/DAY:上一年/季/月/日
• NEXTYEAR/QUARTER/MONTH/DAY：下一年/季/月/日
• TOTALYTD/QTD/MTD：年/季/月至今
• SAMEPERIODLASTYEAR：上年同期
• PARALLELPERIOD：上一期
• DATESINPERIOD：指定区间日期
• DATEADD：移动一定间隔的日期

筛选函数

• FILTER：筛选
• ALL：所有值，可以清除筛选
• ALLEXCEPT：保留指定列
• VALUES：返回不重复值

最重要的函数：CALCULATE

CALCULATE函数

相关知识点：

CALCULATE函数：

     注释：在已修改的筛选器上下文中计算表达式；

     语法：CALCULATE(<expression>, <filter1> , <filter2> , …)

                CALCULATE(表达式, 筛选器1,筛选器2,…)

     参数：第一个参数(必需)：要进行求值的表达式，一般为聚合函数

                第二个参数/第三个参数……(可选)：也可以有1个、2个或多个

     返回：值，根据第二个及之后的过滤器筛选后的上下文，返回第一个表达式计算的值。

相关的度量值：

        产品数量 = COUNTROWS('产品表') 

        产品数量1 = CALCULATE([产品数量]) 

        产品数量2 = CALCULATE([产品数量],'产品表'[产品类别] ="手机配件")

        产品数量22 = CALCULATE([产品数量],FILTER(ALL('产品表'[产品类别]),'产品表'[产品类别] ="手机配件"))

        度量值产品数量2和产品数量22是等价的。

        产品数量3 = CALCULATE([产品数量],ALL('产品表')) 

        占比 = DIVIDE([产品数量1],[产品数量3]) 

        产品数量4 = CALCULATE([产品数量],'产品表'[产品类别]="手机配件",ALL('产品表')) 

        产品数量计算的是表格的行数，产品数量1是统计产品数量，两者返回的结果是一样的，添加筛选条件,比如筛选条件是产品类别，返回的结果也是一样的，此时产品数量的公式会自动嵌套CALCULATE函数，产品数量 = CALCULATE(COUNTROWS('产品表') )

FILTER&ALL函数

相关知识点：

CALCULATE函数：

     注释：在已修改的筛选器上下文中计算表达式；

     语法：CALCULATE(<expression>, <filter1> , <filter2> , …)

                CALCULATE(表达式, 筛选器1,筛选器2,…)

     参数：第一个参数(必需)：要进行求值的表达式，一般为聚合函数

                第二个参数/第三个参数……(可选)：也可以有1个、2个或多个

     返回：值，根据第二个及之后的过滤器筛选后的上下文，返回第一个表达式计算的值。

FILTER函数

     注释：CALCULATE函数的最佳搭档，CALCULATE函数第二个及之后的参数是筛选器参数，而FILTER函数就是专门的筛选函数。

     语法：FILTER(表，筛选条件)

     参数：第一个参数一定是表，是不能放入列或者值的；

                第二个参数是筛选条件，如果是多条件筛选，可以用&&(并)、||(或)连接起来。

     返回：返回的也是表，所以不能直接用于建立度量值，但可以新建表，也可以在查询视图里面使用。

      最常用的就是作为CALCULATE的参数，返回表中符合筛选条件的行，然后交给CALCULATE的第一个参数执行聚合运算。

相关度量值：

       U盘_销售额、U盘_并且销往杭州_销售额、U盘_或者销往杭州_销售额。

ALL函数

      注释：返回表中的所有行或列中的所有值，同时忽略可能已应用的任何筛选器。

                 此函数对于清除表中所有行的筛选器以及创建针对表中所有行的计算非常有用。

      语法：ALL(表/列)

      参数：表或者列

      返回：已删除的带筛选器的表或列。

        FILTER函数返回的结果是一个表，比如“表 2 = FILTER('产品表','产品表'[产品类别]="手机配件")”，返回的结果是一个表格。

        还可以在查询视图中使用，EVALUATE不可以删除。

        ALL函数可以去除重复行，比如“表 3 = ALL('产品表'[产品类别])”，返回的结果是去除重复值的产品类别。

        U盘_销售额

        U盘_销售额_ALL

        U盘_销售额_ALL度量值已经加入了ALL函数，此时订单表中的行筛选失效。

时间智能函数

相关知识点：

        使用时间智能函数的前提：有一个规范的日期表，且搭建好数据模型

 时间智能函数

• PREVIOUSYEAR/QUARTER/MONTH/DAY:上一年/季/月/日
• NEXTYEAR/QUARTER/MONTH/DAY：下一年/季/月/日
• DATESYTD/ DATESQTD/DATESMTD  and TOTALYTD/QTD/MTD：年/季/月至今
• SAMEPERIODLASTYEAR：上年同期
• PARALLELPERIOD：上一期
• DATESINPERIOD：指定区间日期
• DATEADD：移动一定间隔的日期

        更多可以参考官方文档：https://learn.microsoft.com/zh-cn/dax/

        DATESYTD、PREVIOUSYEAR、NEXTYEAR、ENDOFYEAR、STARTOFYEAR、TOTALYTD、CLOSINGBALANCEYEAR、OPENINGBALANCEYEAR

        以上8个函数参数中最后都有一个可选参数，

        <year_end_date>，默认情况下就是自然年度的结束日期12月31日，特殊情况可以自己定义，比如财年结束日期。

        写法如下：销售额YTD_4=TOTALYTD([销售额汇总],'日期表'[日期],"9-30")

相关度量值：  

       之前度量值回顾：销售额_本年累计、销售额_去年累计

       三种写法的区别：销售额_YTD、销售额_YTD_2、销售额YTD_3、销售额YTD_4

       销售额_YTD，选择日期表中的最大年份并且日期是小于等于最大的日期，将每个月的销售额汇总累加，如果当前是在2018/02，那么返回的结果就是2018年1月，2月的销售额汇总的累加。

        销售额_YTD_2与销售额_本年累计是一样的。

        销售额_YTD3 = TOTALYTD([销售额汇总],'日期表'[日期])

        TOTALYTD：在应用指定的筛选器后，针对从该年度的第一天开始到指定日期列中的最后日期结束的间隔，计算指定的表达式的值。

        销售额YTD_4=TOTALYTD([销售额汇总],'日期表'[日期],"9-30")  ，截止日期设置为9.30，会从10月重新开始累加。