PowerBI商业数据分析第三章PowerBI数据建模.pptx

《PowerBI商业数据分析第三章PowerBI数据建模.pptx》由会员分享，可在线阅读，更多相关《PowerBI商业数据分析第三章PowerBI数据建模.pptx（97页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、PowerBI商业数据分析第三章xxxPowerBI数据建模3.1 认识数据建模目录CONTEXT3.2 基本概念3.3 度量值3.4 认识DAX3.5 CALCULATE3.6 常用的DAX函数3.7 VAR变量3.8 常用的分析3.4 计算列和度量值3.10 数据分析，不仅仅是DAXPower BI在多个表之间建立关系，将多个分散个表格变成一个协同的模型，以便能按不同的维度、不同的逻辑来聚合分析数据。这个过程就是数据建模。3.1 认识数据建模3.1 认识数据建模以这个的示例来认识数据建模。模拟某电商订单明细数据。订单表中详细记录每天的销售明细，包括产品名称、客户姓名、客户所在城市、发货地点

2、等信息；如何根据这些维度来对销售额进行分析？3.1 认识数据建模为了能按日期、按产品、按客户、按发货地点等维度分析销售金额，分别建立单独的：日期表客户表城市表产品表仓库表3.1 认识数据建模将订单表和各个维度表导入到PowerBI Desktop后，进入建模视图，可以点击一个表中的字段托到另一个表的对应字段上，两个表就建立了关系。通过这种方式，将上述维度表都分别与订单表建立关系。一个简单的模拟就建好了。3.1 认识数据建模新建度量值在编辑栏中输入:销售金额 = SUM(订单表销售额)3.1 认识数据建模字段区中的度量值：销售金额销售金额前面有个计算器的标识3.1 认识数据建模度量值建好后，不会

3、立即计算，只有放到可视化图表中才会计算并显示结果。用卡片图来显示度量值用卡片图来显示度量值：销售金额：销售金额因为没有任何筛选，这个数字是订单表中的合计销售额添加产品类别切片器，选择“手机配件”，这个度量值显示就是手机配件的销售额3.1 认识数据建模添加更多的切片器，比如添加年份和客户城市切片器，度量值会自动计算交叉筛选后的销售额。通过这个例子可以看出：这个计算出结果并不是一个表得出的，而是根据之前建立的关系模型，销售明细表被日期表的这个计算出结果并不是一个表得出的，而是根据之前建立的关系模型，销售明细表被日期表的 年度年度 、客户城市表中的客户城市表中的 城市城市 和产品表中的和产品表中的

4、产品类别产品类别 字段所筛选，然后汇总形成计算结果，这就是数据模字段所筛选，然后汇总形成计算结果，这就是数据模型的威力。型的威力。3.2 基本概念PowerBI 数据建模：基本概念3.2 基本概念字字 段段字段是表的一列，只包含一类信息，列名就是字段名，相对于字段这个概念，更应该注意的是字段的设置，在进行分析之前，最好做到： 1，检查字段的数据类型是否正确，比如数值型、文本型等；2，设置字段的显示格式，比如百分比、千分位等；3，检查字段的默认汇总方式，比如求和、计数、不汇总等；4，检查字段的数据类别，比如地理字段，可以设置为国家、城市、经纬度等。3.2 基本概念计算列计算列计算列也是一个字段，

5、但它不是从原始数据加载进来的，而是在数据模型中，使用DAX新建的列，在数据视图中，选择需要新建列的表，点击上方功能区的新建列，即可输入DAX公式，在该表中添加一个新列，这个新建的列可以同源数据的其他列一样使用。计算列仅当刷新表数据时才执行计算所使用的DAX表达式，计算列的生成值存储在数据模型中，占用内存，如果在很大的表中添加列可能会对数据模型的内存大小产生显著影响，所以非必须的情况以下，一般不建议使用新建列。计算列不涉及用户交互；计算列的类型和格式等设置同字段的设置。3.2 基本概念度量值度量值度量值是PowerBI数据分析的核心，它是一个公式，与计算列一样同样是使用DAX建立的，但它不属于任

6、何表，新建的度量值保持休眠状态，不执行计算，直到将其用于视觉对象中。度量值计算出的结果是动态的，在不同的上下文中，执行不同的计算，所以也被称为移动的公式。可以响应用户交互，它可以快速重新计算，但不将输出存储在数据模型中，因此对数据模型的物理大小没有影响。数据模型中计算度量的数量增加不会影响静态模型的速度或大小，是数据分析的首选方式。点击某个度量值，在上面功能区会出现【度量工具】栏，然后可以设置该度量值的显示格式等，与字段的设置类似。3.2 基本概念DAXDAXData Analysis Expression的缩写，也就是数据分析表达式，是PowerBI的中数据建模语言，DAX 是一种专门为计算

7、数据模型中的商业逻辑而设计的语言。计算列和度量值都是用DAX生成的。DAX可帮助您从模型中已有的数据中创建新信息，学习如何创建有效的DAX公式将帮助您充分利用数据。利用DAX不仅可以快速得到分析结果，还可以灵活的驱动可视化。3.2 基本概念上下文上下文上下文是理解DAX的核心概念，简单来说，上下文就是DAX代码所处的外部环境，它又分为行上下文和筛选上下文。筛选上下文对数据进行筛选，而行上下文对表进行迭代，行上下文一般可以理解为表的当前行，并不产生筛选作用；计算列和度量值的区别之一就是所处的上下文不同。度量值的动态计算，也是由于上下文的切换。上下文的概念很简单，你也许能快速理解，但它也是DAX中

8、最复杂的主题，如果想要彻底地理解其细节和精妙之处，需要花费一定的时间和精力来摸索。也只有精准的理解了上下文，才能真正理解和掌握DAX。3.2 基本概念事实表事实表在PowerBI中并没有事实表和维度表的区分，这里引用数据库中的相关概念，以便更容易理解和解释数据模型。事实表，又叫明细表，表示业务开展而产生的结果记录，比如订单表，如果有10000次销售，理论上，订单表包含10000行。一个事实表最好只包含一种业务记录，比如订单表只含有销售记录，采购表只含有采购记录，而不应混合在一起。维度表维度表维度表，又叫查找表。通常用来做分析问题的角度，比如按照产品分析，应该制作一个产品维度表，包含所有产品的不

9、重复列表；按客户分析，应制作一个客户维度表，包含所有客户的不重复列表。维度表是上下文的来源，切片器的字段、图表的轴，都应来自于维度表。刚开始学习PowerBI的人可能不习惯用维度表，所有的数据都放到一张大表中，这非常不利于数据分析，可以说数据模型的好坏取决于维度表的设计和维度的质量。3.2 基本概念事实表事实表在PowerBI中并没有事实表和维度表的区分，这里引用数据库中的相关概念，以便更容易理解和解释数据模型。事实表，又叫明细表，表示业务开展而产生的结果记录，比如订单表，如果有10000次销售，理论上，订单表包含10000行。一个事实表最好只包含一种业务记录，比如订单表只含有销售记录，采购表

10、只含有采购记录，而不应混合在一起。维度表维度表维度表，又叫查找表。通常用来做分析问题的角度，比如按照产品分析，应该制作一个产品维度表，包含所有产品的不重复列表；按客户分析，应制作一个客户维度表，包含所有客户的不重复列表。维度表是上下文的来源，切片器的字段、图表的轴，都应来自于维度表。刚开始学习PowerBI的人可能不习惯用维度表，所有的数据都放到一张大表中，这非常不利于数据分析，可以说数据模型的好坏取决于维度表的设计和维度的质量。3.2 基本概念关系关系两个表之间的联系称为关系，它是数据建模最基础也是最重要的概念。从建模视图直观来看，关系就是一条线，在关系线的中间带有箭头，两端还有1或者*的符

11、号，这些都是关系的属性。在建模视图中，拖动一个表的字段到两个表的字段上，Power BI会自动分析检测数据，以便判断哪端是1，哪端是多(*)，箭头一般是从1端指向多端的方向。当然前提是，两个表确实有共同的关系列。模型中可能有多个表，但一个关系仅存在于两个表之间，可以点击这个关系线进去看看建立模型的相关参数。3.2 基本概念基数基数在数据建模中，基数是两个表的对应关系，关系是有次序的，分为左表和右表，两个表之间有多对一、一对一、一对多和多对多四种关系：多对一（*：1）：这是最常见的类型，代表左表中的关系列有重复值，而在右表中是单一值一对一（1：1）：左表和右表关系列中的值都是唯一的一对多（1：*

12、）：与多对一正好相反多对多（*：*）：左表和右表关系列中均有重复值，尽量避免使用这种关系在关系的一端的表通常是维度表，而在关系的多端的表为事实表。在上述的关系图上，订单表中，与其他建立关系的关系列都不是唯一的，比如产品列，同一个产品会有多条销售记录，客户列，也会存在同一个客户多次购买的情况。而在维度表中，对应的关系列都是唯一值，产品表就是不重复的产品列表，客户表是不重复的客户列表。3.2 基本概念数据模型数据模型在PowerBI中，字段、度量值、事实表、维度表、关系的集合构成了数据模型。一个数据模型中，可以将来自不同表、甚至是不同数据源的表建立关系，使各个独立的表，变成相互联系的有意义的数据模

13、型，即可在一个图表中或者报告中分析来自不同表格的数据，所以数据模型是进行数据分析的基础，并且数据模型能够处理更大量级的数据，并且速度很快。数据建模对于excel用户是全新的，因为excel用户不能跨表分析，表之间也没有关系的概念。当然数据建模并不只是个概念，一个良好的数据模型可以将多个表像一个表一样使用，它是解决方案的基础，也是一个良好的报告系统的基础，模型建的优秀，可以更简单的完成分析的需要。直观来看，数据建模就是在表之间建立关系，好像很简单，但知道在哪些表之间建立关系，建立什么样的关系并不容易，尤其在复杂的数据分析、表比较多的情况下。建立一个好的数据模型，首先你熟悉数据背后的业务逻辑，然后

14、你需要深入思考，你要做什么分析，需要哪些数据，这些数据分别存放在哪些表中？在深入分析的基础上，还需要对建模的知识有一定的积累，分清楚哪些是事实表、哪些是维度表，维度表是否够用，关系应该如何建立等。3.2 基本概念星型模型星型模型关于数据模型的结构有很多种，其中有一个适合PowerBI分析的模型：星型模型。星型模型的特征是：事实表居于中央，维度表环于周围关系的最大深度为1 这样做的好处是，两个表之间不会有循环关系，一个表到另一个表只有一条路径，这样模型会由较少的表和较少的关系来建立，更便于理解、性能上也更优。3.3 度量值度量值3.3 度量值按照前面的订单数据和模型，如何计算本年累计销售额的同比

15、增长率？问题更细化一下：如何如何按月份、按季度按月份、按季度、按产品按产品、按类别、按类别、按城市来分析同比增长率，或者是多按城市来分析同比增长率，或者是多个个分析分析维度的组合，比如维度的组合，比如20182018年截止年截止8 8月销往北京月销往北京的的手机配件手机配件的累计销售额的同比增长？的累计销售额的同比增长？是不是每种需求你是不是都要单独计算一遍呢？当然不用，利用度量值可以响应你的不同分析需求，快速计算出结果！3.3 度量值一、建立数据模型3.3 度量值二、建立度量值销售金额 = SUM(订单表销售额)本年累计销售额 = TOTALYTD( 销售金额,日期表日期 )上年累计销售额

16、= TOTALYTD( 销售金额,SAMEPERIODLASTYEAR(日期表日期 )累计同比增长率 = DIVIDE( 本年累计 销售额-上年累计 销售额， -上年累计 销售额 )3.3 度量值三、在矩阵中显示度量值的结果图表类型选择“矩阵”月份放入矩阵的【行】度量值放入到【值】并添加年度切片器3.3 度量值三、在矩阵中显示度量值的结果根据分析的需要添加不同维度的切片器。根据度量值的计算结果：20182018年截止年截止8 8月销往北京的月销往北京的手机配件手机配件的累计销售额的同的累计销售额的同比增长比增长率率247%247%。247%247%的上下文就是右图中圈的上下文就是右图中圈住的分

17、析维度值。住的分析维度值。3.3 度量值度量值的特点度量值的特点度量值不浪费内存，只有被拖到图表上才执行运算，如果数据量非常大的时候这点非常有利度量值可以循环使用，比如上面的建立的度量值：本年累计销售额 = TOTALYTD( 销售金额 , 日期表日期 )上年累计销售额 = TOTALYTD( 销售金额 , SAMEPERIODLASTYEAR( 日期表日期 ) )累计同比增长率 = DIVIDE( 本年累计销售额-上年累计销售额， 上年累计销售额 )度量值可以直接调用之前建立好的度量值，所以以后在模型中新建度量值的时候，推荐从最简单的度量值开始分级建。建立良好的数据模型，加上灵活运用度量值，

18、你就可以使用PowerBI灵活处理各种数据分析问题。3.4 DAXPowerBI数据分析语言：DAX3.4 DAX认识认识DAXDAXDAX是英文Data Analysis Expression的缩写，翻译过来是数据分析表达式，从名称上就可以看出，DAX公式是专门用作数据分析的，事实上也确实如此，从数据分析层面认识DAX会更有助于我们理解它。数据分析可以归纳为从大量数据中筛选、分组、汇总的过程，而DAX的主要功能正是查询和运算，DAX查询函数负责筛选出有用的数据集合，然后利用DAX的聚合函数执行计算，它是Power BI数据分析的灵魂。3.4 DAX在哪里使用在哪里使用DAXDAXDAX可以在

19、Power BI Desktop 中使用，也可以在Excel的Power Pivot中使用，在PowerBI中，有三个地方会用到DAX：度量值、计算列、计算表。3.4 DAXDAXDAX基本语法基本语法销售金额 = SUM(订单表销售额)表达式以等号开始；等号（=）前面是表达式名称，如果DAX建立的是度量值，它就是度量值名称，若是用于建计算列，就是计算列名；若是建立一个新表，它就是表名；函数后面都用双括号（）括上参数，参数之间用逗号分割。表名用单引号 包裹，如：订单表字段用中括号 包裹，并带上表名，如订单表销售额度量值也是用中括号 包裹，如DIVIDE(本年累计销售额,上年累计销售额)为了便于

20、区分和增强DAX代码的可读性，字段应始终跟随表名一起书写，而度量值始终不要带表名，因为它并不依赖于任何表，它是独立的存在，可以单独书写。3.4 DAXDAXDAX格式规范格式规范1.如果函数只有一个参数，则和函数放在同一行；2.如果函数具有2个或更多参数，则将每一个参数都另起一行；3.如果函数及其参数写在多行上:3.1左括号(与函数在同一行3.2参数是新行，从该函数对齐位开始缩进4字符3.3右括号)与函数开头对齐3.4分隔两个参数的逗号位于前一个参数的同一行3.5如果必须将表达式拆分为更多行，则运算符作为新行中的首字符熟练掌握DAX编辑器中的换行和缩进：换行并缩进： Shift + Enter

21、换行不缩进： Alt + Enter向右缩进：Ctrl + 向左缩进：Ctrl + 3.4 DAX常用的常用的DAXDAX函数函数聚合函数SUM / SUMXAVERAGE / AVERAGEXMIN / MINXMAX / MAXXCOUNTROWSDISTINCTCOUNT时间智能函数PREVIOUSYEAR/Q/M/D:上一年/季/月/日NEXTYEAR/Q/M/D:下一年/季/月/日TOTALYTD/QTD/MTD:年/季/月初至今SAMEPERIODLASTYEAR:上年同期PARALLELPERIOD:上一期DATESINPERIOD:指定期间的日期DATEADD:移动一定间隔的日

22、期筛选函数FILTER:筛选ALL:所有值，可以清除筛选ALLEXCEPT:保留指定列VALUES:返回不重复值最常用最重要的DAX函数：CALCULATECALCULATE3.5 CALCULATE最重要的DAX函数：CALCULATE3.5 CALCULATECALCULATECALCULATE语法语法3.5 CALCULATECALCULATECALCULATE应用示例：背景数据应用示例：背景数据利用这个简单的产品明细表，帮助你直观的理解CALCULATE的计算逻辑。3.5 CALCULATE基础度量值：产品数量 = COUNTROWS(产品明细)这里外部上下文就是表格每行的行标签，也就

23、是产品名称，每个产品名称在产品表中对应的只有一行数据，所以全部返回1。CALCULATECALCULATE应用示例：背景数据应用示例：背景数据3.5 CALCULATE产品数量1 = CALCULATE(产品数量)在这个度量值中，只是用了第一个参数，筛选条件为空，从这里也可以看出，CALCULATE唯一必须的参数就是第一个参数，后面的参数均是可选的。因为没有内部筛选所以完全依赖外部上下文，出来的结果也和原度量值一致。CALCULATECALCULATE应用示例应用示例1 1：筛选条件为空：筛选条件为空3.5 CALCULATE产品数量2 = CALCULATE( 产品数量, 产品表产品类别=手

24、机配件“)只有类别为“手机配件”的产品计数正常显示出来，而其他产品的数据没有显示。这是因为CALCULATE的第二个参数的限制，只筛选类别为“手机配件”的产品，限制了外部的上下文，非手机配件的产品都不再运算。CALCULATECALCULATE应用示例应用示例2 2：添加限制条件，缩小上下文添加限制条件，缩小上下文3.5 CALCULATE产品数量3 = CALCULATE( 产品数量 , ALL(产品表) )ALL(产品表)的意思是清除产品表里的所有外部上下文，外部筛选器不起作用了，每行统计的都是该产品表中的所有产品的行数，因此每行的数据都是13。CALCULATECALCULATE应用示例

25、应用示例3 3：结合：结合ALLALL函数，扩大上下文函数，扩大上下文3.5 CALCULATE产品数量4 = CALCULATE( 产品数量, ALL(产品表), 产品表产品类别=手机配件“)先用ALL函数清除外部上下文，然后又新增了一个筛选条件，类别为“手机配件”的产品数量。被ALL清除行标签的外部筛选后，从全部产品中统计品类为手机配件的产品的数量，所以每行都返回5.CALCULATECALCULATE应用示例应用示例4 4：重置上下文：重置上下文3.5 CALCULATE通过以上几个简单的示例，可以先初步了解CALCULATE的计算逻辑，通过从第二个参数开始的筛选条件，得到一个数通过从第

26、二个参数开始的筛选条件，得到一个数据集合，并利用第一个参数执行聚合运算，这其实也是据集合，并利用第一个参数执行聚合运算，这其实也是DAXDAX要实现的功能：筛选需要的数据集合并执行聚合运算，所要实现的功能：筛选需要的数据集合并执行聚合运算，所以说以说CALCULATECALCULATE就是实现就是实现DAXDAX功能的引擎。功能的引擎。CALCULATE 作为DAX 函数中最常用也是最强大的函数，其实也是最难学的。在高级的业务分析中，涉及到多重函数的嵌套时，CALCULATE的计算逻辑非常复杂，后面的各种DAX应用基本都离不开CALCULATE的身影，可以慢慢来理解它。3.6 常用的DAX函数

27、3.6 常用的DAX函数1 1、FILTERFILTER函数函数CALCULATE函数的最佳搭档，我们知道CALCULATE函数第二个及之后的参数是筛选条件，而FILTER函数就是专门的筛选函数。语法：FILTER(表，筛选条件)它的第一个参数一定是表，是不能放入列或者值的；第二个参数是筛选条件，如果是多条件筛选，可以用 &（并） 或者 | （或）连接起来。它返回的也是个表，所以不能直接用于建立度量值，但可以新建表。最常用的就是作为CALCULATE的参数，返回表中符合筛选条件的行，然后交给CALCULATE的第一个参数执行聚合运算。3.6 常用的DAX函数1 1、FILTERFILTER函数

28、函数应用示例：应用示例：计算每年销往杭州的U盘的销售金额是多少？（依然用本章第一节的数据模型，下同）FILTER应用 杭州U盘 销售额 = CALCULATE( 销售金额, FILTER( 订单表, 订单表产品名称=U盘&订单表客户城市=杭州市 )3.6 常用的DAX函数2 2、ALLALL函数函数1.1.单独用于创建表，参数为一个表，相当于复制表。单独用于创建表，参数为一个表，相当于复制表。比如点击“新表”，输入：= ALL(产品表)3.6 常用的DAX函数2 2、ALLALL函数函数2.2.单独用于创建表，参数为一列，返回该列的不重复列表。单独用于创建表，参数为一列，返回该列的不重复列表。

29、比如点击“新表”，输入：= ALL(产品表产品类别)3.6 常用的DAX函数2 2、ALLALL函数函数3.3.作为度量值中的参数，清除外部上下文。作为度量值中的参数，清除外部上下文。拿前面介绍FILTER的示例，如果在FILTER的第一个参数表上套上一个ALL函数，变成：ALL应用 杭州U盘 销售额 = CALCULATE( 销售金额, FILTER( ALL(订单表), 订单表产品名称=U盘&订单表客户城市=杭州市 )3.6 常用的DAX函数3 3、VALUESVALUES函数函数1. 1. 返回某列的不重复列表，常用于通过事实表构建返回某列的不重复列表，常用于通过事实表构建维度表。维度表

30、。如果模型中没有发货地点这个单独的维度表，可以从订单表中提取这个维度，新建表，输入：=VALUES(订单表发货地)3.6 常用的DAX函数3 3、VALUESVALUES函数函数2. 2. 保持外部上下文筛选。保持外部上下文筛选。这个功能正好与ALL相反，ALL函数是忽略外部上下文的筛选，而VALUES可以保持上下文的筛选。产品数量4 VALUES = CALCULATE(产品数量, ALL(产品表), 产品表产品类别=手机配件, VALUES(VALUES(产品表产品表产品名称产品名称)在CALCULATE内部添加了VALUES(产品表产品名称)以后，计算结果又恢复了产品名称的筛选，这是VA

31、LUES非常重要的应用。利用ALL和VALUES，可以更加灵活的操纵上下文，需要筛选，还是不需要筛选，你可以在度量值内部灵活的指定。3.6 常用的DAX函数3 3、VALUESVALUES函数函数3. 3. 查找文本型的数据查找文本型的数据这个应用也很常用，看这个例子，从产品表中找出零售价为199元的产品名称是什么？新建度量值如下：VALUES应用 查找文本 = CALCULATE(VALUES(产品表产品名称),产品表零售价=199)VALUES返回的是只有一列的表，如果被上下文筛选为也只有一行，那么就可以正常显示为一个值3.6 常用的DAX函数4 4、HASONEVALUEHASONEVA

32、LUE函数函数它判断该列是否被过滤为仅有 一个值，如果是一个值，返回TRUE，如果没有一个值或者有多个值，则返回FALSE。一般作为IF函数的第一个参数使用，判断交叉筛选后是否为一个值。HASONEVALUE应用 = IF( HASONEVALUE(产品表产品名称), CALCULATE( VALUES(产品表产品名称), 产品表产品类别=手机配件 )当外部没有筛选器时，默认返回空值，3.6 常用的DAX函数5 5、时间智能函数、时间智能函数函数名称函数名称用途用途DATESMTD/DATESQTD/DATESYTDDATESMTD/DATESQTD/DATESYTD月/季/年初至今FIRST

33、DATE/LASTDATE FIRSTDATE/LASTDATE 第一个日期/最后一个日期PREVIOUSDAY/MONTH/QUARTER/YEARPREVIOUSDAY/MONTH/QUARTER/YEAR上一日/月/季/年NEXTDAY/MONTH/QUARTER/YEARNEXTDAY/MONTH/QUARTER/YEAR次日/月/季/年ENDOFMONTH/QUARTER/YEARENDOFMONTH/QUARTER/YEAR月/季/年度的最后一天STARTOFMONTH/QUARTER/YEARSTARTOFMONTH/QUARTER/YEAR月/季/年度的第一天SAMEPERIO

34、DLASTYEAR SAMEPERIODLASTYEAR 上年同期DATEADD DATEADD 移动一定间隔后的时间段DATESBETWEEN DATESBETWEEN 从起始日到结束日的时间段DATESINPERIOD DATESINPERIOD 从指定日期移动一定间隔的时间段PARALLELPERIOD PARALLELPERIOD 移动指定间隔的完整粒度的时间段TOTALMTD/TOTALQTD/TOTALYTDTOTALMTD/TOTALQTD/TOTALYTD月/季/年初至今，内置CALCULATECLOSINGBALANCEMONTH/QUARTER/YEARCLOSINGBAL

35、ANCEMONTH/QUARTER/YEAR月/季/年度的期末数据，内置CALCULATEOPENINGBALANCEMONTH/QUARTER/YEAROPENINGBALANCEMONTH/QUARTER/YEAR月/季/年度的期初数据，内置CALCULATE返回表的时间智能函数返回值的时间智能函数3.6 常用的DAX函数4 4个相对复杂的时间智能函数个相对复杂的时间智能函数DATEBETWEEN返回从起始日到结束日的时间段注释:1. 日期列2. 起始日期3. 结束日期,区间包含起止日期当天参数:示例：CALCULATE(数量, DATESBETWEEN( DATESBETWEEN(日期表

36、日期表日期日期, STARTOFYEAR(STARTOFYEAR(日期表日期表日期日期),), LASTDATE(LASTDATE(日期表日期表日期日期 )）计算年初至今，相当于TOTALYTDDATEADD返回移动一定的间隔后的相同日期区间注释:1. 日期列2. 整数，正数表示向未来移动，负数代表向过去移动3. 移动的粒度，可以是year、quarter、month、day 参数:示例：CALCULATE(数量, DATEADD( DATEADD(日期表日期表日期日期,-1,YEAR),-1,YEAR)计算上年同期销量，等同于SAMEPERIODLASTYEARPARALLELPERIOD

37、返回移动间隔后的完整粒度区间注释：同DATEADD完全一样参数：示例：CALCULATE(数量, PARALLELPERIOD ( PARALLELPERIOD (日期表日期表日期日期,-1,YEAR),-1,YEAR) 计算上年全年的销量DATESINPERIOD 返回从指定日期移动一定间隔的时间段注释:1. 日期列2. 开始日期 （比DATEADD多了这个参数）3. 整数，正数表示向未来移动，负数代表向过去移动4. 移动的粒度，可以是year、quarter、month、day 参数:示例：CALCULATE(数量, DATESINPERIOD(DATESINPERIOD(日期表日期表日期

38、日期, ENDOFMONTH( ENDOFMONTH(日期表日期表日日期期),-5,DAY),-5,DAY),计算每月最后5天的销量3.6 常用的DAX函数8 8个关于年度的时间智能函数的特殊用法个关于年度的时间智能函数的特殊用法DATESYTDPREVIOUSYEARNEXTYEARENDOFYEARSTARTOFYEARTOTALYTDCLOSINGBALANCEYEAROPENINGBALANCEYEAR这 8 个 函 数 的 参 数 中 最 后 都 有 一 个 可 选 参 数,意思是年度结束日期，默认情况下就是自然年度的结束日期12月31日，可以省略，在特定的分析中，年度结束日期不是1

39、2月31日，那么可以使用这个参数来界定。在进行财年的分析时，很多国外的公司的财年都不是自然年度，比如财年结束日期9月30日，2017财年就是从2016年10月1日到2017年9月30日，计算财年的年初至今的销量：=CALCULATE( 数量 , DATESYPD( 日期表日期,9-30）等同于TOTALYTD=（ 数量 , 日期表日期 , 9-30）3.6 常用的DAX函数时间智能函数与日期表时间智能函数与日期表为了正确的使用时间智能函数，它的参数应该是日期表中的日期列，而不应该是事实表中的日期列。所以使用时间智能函数的前提是建立一个规范的日期表，并将日期列与事实表的日期列建立关联。规范的日期

40、表具备以下特征：起止日期涵盖事实表的所有日期日期列包含完整年度日期是连续且不重复的不包含空值3.7 VAR变量3.7 VAR VAR VAR 语法语法VAR 变量名=表达式RETURN 结果表达式其中变量名不能和模型中现有的表名、字段名相同，也不能使用数字作为第一个字符，不能使用空格、不能使用中文等。定义变量是为了定义变量是为了后续的后续的使用，返回结果由使用，返回结果由RETURNRETURN来完成来完成。VAR 和 RETURN 总是一起出现。3.7 VAR VAR VAR 应用示例：计算同比增长率应用示例：计算同比增长率同比增长率=VARVAR Sales=SUM( 销售表销售额 ) V

41、ARVAR Saleslastyear=CALCULATE( SUM( 销售表销售额 ), SAMEPERIODLASTYEAR( 日期表日期 ）RETURNRETURNDIVIDE( Sales-Saleslastyear , Saleslastyear )首先定义第一个变量“Sales”，表达式为本年销售额；然后定义第二个变量Saleslastyear，表达式的结果是上年同期销售额；两个变量定义完成后，利用RETRUN调用两个变量，相除得出最终的结果。VARVAR可以简化度量值的书写，并便于理解。并且它可以简化度量值的书写，并便于理解。并且它完成运算以后结果就被电脑记忆下来，需要时可以完成

42、运算以后结果就被电脑记忆下来，需要时可以随时调用，而无需重新运算，大大提升了随时调用，而无需重新运算，大大提升了DAXDAX的运的运算性能。算性能。3.8 利用PowerBI进行常用的分析3.8 常用的分析1 1、占比计算、占比计算一、总体占比：总体占比 = DIVIDE( 销售金额, CALCULATE(销售金额,ALL(产品表)总计等于100%3.8 常用的分析1 1、占比计算、占比计算二、分类占比：分类占比 = DIVIDE( 销售金额, CALCULATE(销售金额,ALL(产品表产品名称)每个分类等于100%3.8 常用的分析1 1、占比计算、占比计算三、按筛选上下文计算总体占比按筛

43、选上下文计算总体占比：按筛选 总体占比 = DIVIDE( 销售金额, CALCULATE(销售金额,ALLSELECTED(产品表)无论切片器如何选择，总计保持100%不变3.8 常用的分析1 1、占比计算、占比计算四、按筛选上下文计算按筛选上下文计算分类分类占比占比：按筛选 分类占比 = DIVIDE( 销售金额, CALCULATE(销售金额,ALLSELECTED(产品表产品名称)无论切片器如何选择，分类总计保持100%不变3.8 常用的分析1 1、占比计算、占比计算计 算 的 每 一 种 占 比 ， 分 子 都 不 变 ， 只 是 通 过 A L L 和ALLSELECT函数及其参数

44、的选择操控不同的上下文环境，来控制分母的计算，进而得到我们所需要的占比。3.8 常用的分析2 2、排名计算、排名计算认识RANKX函数3.8 常用的分析2 2、排名计算、排名计算一、对全部产品按销售额排序排名 = RANKX(ALL(产品表),销售金额)3.8 常用的分析2 2、排名计算、排名计算这个排名是固定排名，无论选择哪几个产品，每个产品的排名是固定的，所以这种方式也称为整体绝对排名。3.8 常用的分析2 2、排名计算、排名计算这个排名是固定排名，无论选择哪几个产品，每个产品的排名是固定的，所以这种方式也称为整体绝对排名。3.8 常用的分析2 2、排名计算、排名计算二、对所选产品按销售额

45、排序整体 相对排名 = RANKX(ALLSELECTED(产品表),销售金额)3.8 常用的分析2 2、排名计算、排名计算三、类别内对各产品排名类别 绝对排名 = RANKX(ALL(产品表产品名称),销售金额)类别 相对排名 = RANKX(ALLSELECTED(产品表产品名称),销售金额) 3.8 常用的分析2 2、排名计算、排名计算四、对产品类别排名按类别绝对排名 = RANKX( ALL(产品表产品类别), CALCULATE( 销售金额, ALLEXCEPT(产品表,产品表产品类别) )3.8 常用的分析2 2、排名计算、排名计算四、对产品类别排名按类别相对排名 = RANKX(

46、 ALLSELECTED(产品表产品类别), CALCULATE( 销售金额, ALLEXCEPT(产品表,产品表产品类别) )3.8 常用的分析2 2、排名计算、排名计算五、按两个维度进行排名产品名称 城市 综合排名 = RANKX( CROSSJOIN( ALL(产品表产品名称), ALL(客户城市表城市) ), 销售金额)3.8 常用的分析2 2、排名计算、排名计算六、排名应用：显示前5名销售额 前5名=CALCULATE( 销售金额, FILTER(VALUES(产品表),整体 绝对排名=5)3.8 常用的分析2 2、排名计算、排名计算六、排名应用：结合参数，动态显示前N名销售额 前N

47、名 = CALCULATE( 销售金额, FILTER(VALUES(产品表),整体 绝对排名=参数 值)3.8 常用的分析3 3、分组汇总、分组汇总根据某一个维度的数据，进行分组统计，是很常见的做法，比如按年龄对客户进行分组，按考试成绩进行分组统计等。比如右图的成绩表，如何利用在PowerBI，统计不及格、及格、良好、优秀的人数？3.8 常用的分析3 3、分组汇总、分组汇总计算列的做法统计每种类型的人数，先增加一列类型：成绩类型 = SWITCH ( TRUE(), 数学成绩60, 不及格, 数学成绩75, 及格 , 数学成绩90, 良好 , 优秀)然后统计各种类型的数量即可。3.8 常用的

48、分析3 3、分组汇总、分组汇总度量值的做法采用度量值的方式首先定义好分组的类别和标准，然后把需要分组的数据分别统计到指定的分组中。先建立一个分组辅助表。3.8 常用的分析3 3、分组汇总、分组汇总度量值的做法编写度量值如下：成绩分组统计 = CALCULATE( DISTINCTCOUNT(成绩姓名), FILTER(成绩, AND( 成绩数学成绩=SELECTEDVALUE(分组表最小值) ) )总计行没有显示？3.8 常用的分析3 3、分组汇总、分组汇总度量值的做法解决总计行的问题，优化度量值如下：成绩分组统计 优化 = IF(ISFILTERED(分组表分组), CALCULATE( D

49、ISTINCTCOUNT(成绩姓名), FILTER(成绩, AND( 成绩数学成绩=SELECTEDVALUE(分组表最小值) ) ) ), DISTINCTCOUNT(成绩姓名) 3.8 常用的分析3 3、分组汇总、分组汇总总计行错误的简单解决方案：SUMX( VALUES(明细行字段), 明细行正确的度量值)比如本例：总计错误 解决方案 = SUMX( VALUES(分组表分组), 成绩分组统计)3.9 计算列和度量值3.9 计算列和度量值计算列和度量值都是用DAX表达式创建计算列是存在于所在的表中的可见的一列数据；度量值如果不放到可视化对象中，它只是一串公式，它不会立即计算，并且它不属

50、于任何表，你可以随意将它在各个表之间移动。3.9 计算列和度量值怎么写计算列、怎么写度量值？根据这个订单表，如何计算出每个客户的如何计算出每个客户的最后下单日期？最后下单日期？3.9 计算列和度量值客户最后下单日期 = MAX(订单表订单日期)计算列的上下文就是当前行，但是MAX函数（包括SUM、MIN等聚合函数）并不能将行上下文转化为筛选上下文，也就是说当前行没有起到筛选作用，它就会返回整个表的最大订单日期。客户最后下单日期 = CALCULATE( MAX(订单表订单日期) )CALCULATE可以将行上下文转化为筛选上下文上面两种写法都不正确，再强调一遍，计算列的上下文就是计算列的上下文