POWERBI数据处理和分析-数据分析进阶

数据分析进阶1章节内容9.1DAX语言高阶函数应用9.1.1数据分组和合并函数9.1.2数据查询函数9.1.3日期时间函数9.1.4排名函数9.2DAX语言数据分析高阶技巧9.2.1VAR的用法9.2.2度量值的收纳盒9.3DAX语言数据分析深入理解9.3.1公式引擎9.3.2存储引擎9.3.3DAX公式性能分析29.1.1数据分组和合并函数数据分组和合并函数用于构造作为计算基础的逻辑数据表，可以作为计算函数的数据源参数，也可以用于构造计算表。两张基础表Product1=DataTable("ProductID",STRING,"ProductName",STRING,"ProductPrice",DOUBLE,{{"001","Coffee",20.0},{"002","tea",15.0},{"003","Juice",18.0}})Product2=DataTable("ProductID",STRING,"ProductName",STRING,"ProductPrice",DOUBLE,{{"003","Juice",18.0},{"004","Cake",12.0},{"005","Bread",10.0}})39.1.1数据分组和合并函数1.UNION函数UNION函数实现集合的并运算，其原型是：UNION(<table_expression1>,<table_expression2>[,<table_expression>]…)其中table_epression可以是已有的数据表名，也可以是返回表对象的DAX表达式，UNION要求至少有2个参数，作为合并参数的多个表的结构需要保持一致，即列数相同，列属性相容，最终结果表的列属性名由第一个参数给出的表的列属性名决定。最终返回的逻辑表包含作为参数的表对象的所有数据行，因此可能会有重复行出现。例如，ProductTable1=UNION(product1,product2)的计算结果表如图9-1所示。49.1.1数据分组和合并函数2.EXCEPT函数EXCEPT函数实现集合的减运算，其原型是：EXCEPT(<table_expression1>,<table_expression2>其中table_expression1和table_expression2是两个具有相同结构的表对象，可以是已有的数据表名，也可以是返回表对象的DAX表达式。返回值是用表对象table_expression1减去表对象table_expression2之后得到的逻辑表，即包含属于table_expression1但是不属于table_expression2的数据行，最终结果表对象的列属性名由参数table_expression1给出的表的列属性名决定。例如，ProductTable2=EXCEPT(Product1,Product2)的计算结果如图9-2所示。59.1.1数据分组和合并函数3.INTERSECT函数INTERSECT函数实现集合的交运算，其原型是：INTERSECT(<table_expression1>,<table_expression2>)其中table_expression1和table_expression2是两个具有相同结构的表对象，可以是已有的数据表名，也可以是返回表对象的DAX表达式。INTERSECT函数判断table_expression1中的每个数据行是否出现在table_expression2中，如果出现则作为结果表对象中的一个数据行。由于两个表中的任何一个表都可能会有重复的数据行，因此用两个表作为参数调用INTERSECT函数时，两个表放置的参数位置不同，函数返回的结果表对象也可能是不同的。69.1.1数据分组和合并函数ProductTable3=INTERSECT(Product1,Product2)的计算结果如下图所示。

ProductTable4=INTERSECT(ProductTable1,Product2)的计算结果如下图所示，注意作为第一个操作数的表中具有重复行对结果的影响。79.1.2数据查询函数案例基础：完整的产品表Product：Product=UNION(Product1,EXCEPT(Product2,ProductTable3))假设还有一张产品销售表SALESSales=DataTable("ProductID",STRING,"Count",INTEGER,"SALESDATE",DATETIME,{{"001",200,"2021-1-1"},{"002",100,"2021-1-1"},{"003",500,"2021-1-2"},{"001",200,"2021-1-2"},{"002",100,"2021-1-3"},{"004",100,"2021-1-3"},{"001",200,"2021-1-4"},{"002",100,"2021-1-4"},{"001",500,"2021-1-4"},{"005",200,"2021-1-5"},{"002",100,"2021-1-5"},{"003",100,"2021-1-5"}})89.1.2数据查询函数PRODUCT和SALES两表之间通过ProductID字段建立1对多的联系99.1.2数据查询函数1. RELEATED函数对于已建立好关系的两张表如果以其中一张表为基准表，另一张表为查询表的话，则用查询表的某个数据列作为参数调用RELATED函数时，RELATED函数会为基准表中的每个数据行，根据关系从查询表中提取作为RELATED函数参数的数据列的值。因此RELATED函数主要有两种用途一个是通过关系提取查询表的指定列数据从而为基准表构造一个新的计算列；另一个就是在不构造计算列的情况下，通过对提取自查询表的数据进行筛选，从而为基于基准表的计算构造新的筛选上下文。109.1.2数据查询函数RELATED函数的原型是：RELATED(<column>)基准表由调用RELATED函数的表达式上下文确定，而RELATED函数唯一的参数<column>是指查询表中的某个数据列名称（希望根据关系从查询表中提取的数据列）。例如，如果要为SALES表添加一个计算列Productname，则基准表是SALES表，查询表为Product表，计算列Productname的度量值公式如下：Productname=RELATED('Product'[ProductName])119.1.2数据查询函数2.RELATEDTABLE函数RELATEDTABLE函数主要用于将建立好关系的两张表连接起来，从而构造计算所基于的筛选上下文。其函数原型是：RELATEDTABLE(<tableName>)调用RELATEDTABLE函数时，通常将建立好关系的两张表中的一张表作为基准表，参数tableName是与基准表连接的另一张表，RELATEDTABLE函数会根据基准表中的每个数据行依据关系去tableName表中查找匹配的数据行，函数调用结果所得到的是一个临时逻辑表。案例：统计Product表中每种商品的销售总量：销售总量=sumx(RELATEDTABLE(Sales),Sales[Count])129.1.2数据查询函数3.LOOKUPVALUE函数当两张表之间没有建立关系时，可以使用LOOKUPVALUE函数实现查找任务。LOOKUPVALUE函数可以在指定的目标数据源表中查找满足参数给出条件的数据行，并返回指定的数据列的属性值，并且LOOKUPVALUE函数在目标表中查找时，会忽略所有加在目标表上的筛选条件，因此适合于在复杂的上下文环境中进行查找。其函数原型是：LOOKUPVALUE(<result_columnName>,<search_columnName>,<search_value>[,<search_columnName>,<search_value>]…[,<alternateResult>])139.1.2数据查询函数案例：实现为SALES表增加一个计算列“产品名称”显示每条销售记录中产品的名称产品名称=lookupvalue('Product'[ProductName],'Product'[ProductID],Sales[ProductID],"未找到")149.1.2数据查询函数4.VALUES函数VALUES函数用于对数据列的值去重或者对数据表的数据行去重，提取不包含重复值的结果。其函数原型是：VALUES(<TableNameOrColumnName>)唯一的参数<TableNameOrColumnName>表示需要提取不重复数据的数据源表或数据列的名称。VALUES函数的主要用途是作为其他函数调用时的参数为统计和计算提供数据源或筛选条件来源。案例：得到SALES表中哪些产品有销售记录，实现公式如下：产品名称表=values(Sales[ProductID])159.1.2数据查询函数5.HASONEVALUE函数HASONEVALUE函数通常用于筛选器使用情况判断。如果作为筛选器使用的表的字段被筛选后在使用的当前上下文中只剩下一个非重复的值，则返回真，而如果对作为筛选器的表字段没有使用，或者筛选后留下多个值，则返回假。函数原型是：HASONEVALUE(<columnName>)唯一的参数<columnName>表示作为筛选器使用的表字段。HASONEVALUE函数通常可以作为IF函数的第一个参数实现动态计算。169.1.2数据查询函数案例：结合卡片图和筛选器实现查看单独一个产品的销售量。销售总量的度量值：总销售量=if(HASONEVALUE('Product'[ProductName]),sum(Sales[Count]),"选中不止一种产品")179.1.3日期时间函数1. 构造基于时间智能分析的日历表在商业智能分析中，往往需要基于销售或生产等商业活动的日期进行不同粒度的时间智能分析，例如分析不同年份、季度、月份、星期的商业活动指标，以及这些指标的同比、环比等分析。商业活动的年度分析往往与自然年不同，并不都是从1月1日开始到12月31日结束，因此这些时间智能分析需要构造和商业活动紧密相关的日历表，日历表中包含着和商业活动相关的年度、季度、月度、星期、日期以及其他可能的不同粒度的日期和时间数据。189.1.3日期时间函数CALENDAR函数用于构造日历表，其函数原型是：CALENDAR(<start_date>,<end_date>)参数start_date表示开始日期，end_date表示结束日期，CALENDAR函数将构造一个从start_date开始直到end_date为止的所有日期组成的日历表，这个日历表只有一列数据，列名为date。。例。在二手房数据库中，如下公式将创建一张名为“2000至2020年日期表”的新表，该表包含2000年1月1日到2020年12月31日的所有日期。2000至2020年日期表=CALENDAR(DATE(2000,1,1),DATE(2020,12,31))199.1.3日期时间函数通常会基于应用系统数据库中包含交易日期的某个数据表调用CALENDAR函数生成定制的日历表，然后结合日期函数来构造年、月、日、星期、季度等计算列，从而形成面向应用的具有各个时间粒度数据的日历表，作为调用日期时间智能函数进行沿时间维度的数据分析的基础209.1.3日期时间函数219.1.3日期时间函数时间智能分析以二手房数据库为例，首先介绍一下基于时间的智能分析的概念。在关系视图中将house表的sales_data列与housesales_calendar表的date列用鼠标拉到一起，就将前面构造的日历表housesales_calendar与二手房销售表house建立了关系，接下来就可以通过对housesales_calendar表的筛选来构造不同粒度的时间智能分析。229.1.3日期时间函数在报表页中放置两个卡片图可视化对象，分别显示“二手房销售数量”和“二手房每平米均价”两个量化指标。使用二手房销售日历表housesales_calendar构造一个切片器对象，则可以根据切片器对象的值属性是使用的二手房销售日历表的哪一列，能够对二手房销售数据进行不同年份、季度等粒度的日期智能分析。239.1.3日期时间函数创建一个矩阵对象，设置其行属性为日历表的“年季度”列，值属性为“二手房销售数量”和“二手房每平米均价”两个度量值。这对于该矩阵的每一行，都会根据这一行的“年季度”列的值，对二手房销售信息表的数据行进行筛选，在此筛选上下文环境中，再计算度量值，就得到了矩阵对象中对于该行年季度值的“二手房销售数量”和“二手房每平米均价”。249.1.3日期时间函数二手房销售量年同比增长率的计算公式是：年度同比增长率=divide(count(house[no])-CALCULATE(count(house[no]),SAMEPERIODLASTYEAR('housesales_calendar'[Date])),CALCULATE(count(house[no]),SAMEPERIODLASTYEAR('housesales_calendar'[Date])))259.1.1数据分组和合并函数年度同比增长率2=divide(count(house[no])-CALCULATE(count(house[no]),PREVIOUSYEAR('housesales_calendar'[Date])),CALCULATE(count(house[no]),PREVIOUSYEAR('housesales_calendar'[Date])))269.1.1数据分组和合并函数279.1.4排名函数1. RANKX函数RANKX的功能是按照某个规则给数据表中的每一行进行排名，其函数原型是：RANKX(<table>,<expression>[,<value>[,<order>[,<ties>]]])第一个参数<table>是要进行排名的数据源表第二个参数<expression>是返回唯一一个标量值的DAX表达式，通常会包含<table>表中的列属性，指定了为<table>表中每个数据行计算排名值的规则。第三个可选参数<value>，如果不给出，则默认值就是第二个参数<expression>。第四个可选参数<order>用于规定排序的方向，不设置时默认值为0或者FALSE，表示按照降序进行排名，如果设置为1或者TRUE，则表示按照升序进行排名。第五个可选参数<ties>用于规定当用于排名的数据行的<expression>值相同时，如何规定相同值之后的值的排名。289.1.4排名函数假设希望从装修程度角度分析一下二手房价格的排名，则可以定义按照装修程度对二手房每平米均价排名的度量值：装修程度二手房均价排名=rankx(all(house[equipment]),house[每平米均价])然后在报表页中创建一个矩阵可视化对象，将其行属性设置为house表的equipment列，值属性设置为度量值“每平米均价”和“装修程度二手房均价排名”。299.1.4排名函数通过对比2010年前后二手房均价的相对排名，演示第三个参数的作用。2010年之前装修程度二手房均价排名=rankx(all(house[equipment]),[2010年之前每平米均价])2010年起装修程度二手房均价相对2010年前的排名=rankx(all(house[equipment]),[2010年之前每平米均价],[2010年及之后每平米均价])在报表页中创建一个矩阵可视化对象，将其行属性设置为house表的equipment列，值属性设置为度量值“2010年之前每平米均价”、“2010年之前装修程度二手房均价排名”、“2010年及之后每平米均价”和“2010年起装修程度二手房均价相对2010年前的排名”。309.1.4排名函数319.1.4排名函数2. TOPN函数如果需要按照某种规则进行排名后返回排行较前的若干数据，则可以使用TOPN()函数，其函数原型如下：TOPN(<n_value>,<table>,<orderBy_expression>,[<order>[,<orderBy_expression>,[<order>]]…])第一个参数<n_value>说明返回的数据行数第二个参数<table>说明数据源表第三个参数<orderBy_expression>给出排名依据的表达式，通常会包含数据源表中的列属性。可选参数<order>说明是按照升序还是降序排名，其用法与RANKX()函数中一致。如果进行排名时，当<orderBy_expression>的值相同时，仍然希望继续根据其他规则分出排名高低，则可以继续使用可选的后续<orderBy_expression>。TOPN函数实际上返回的是参数<table>的一个子集，包含按照给定表达式排名后的<n_value>个数据行。329.1.4排名函数可以使用TOPN来构造生成表，提取二手房销售表中面积最大的10套二手房，其DAX公式定义如下，而生成的数据表如图所示：面积最大的10套二手房=TOPN(10,house,house[area])339.2.1VAR的用法VAR方法的基本语法是：度量值=VAR临时变量名1=DAX公式1VAR临时变量名2=DAX公式2…VAR临时变量名n=DAX公式nRETURN临时变量名n后面出现的临时变量名的计算可以利用前面已经得到的临时变量名的计算结果。此外需要注意，度量值名字可以使用汉字，但VAR中使用的临时变量名只能由字母和数字构成，并且不能以数字开头，另外不能和内置函数和表名重名。349.2.1VAR的用法计算信息表=varmaxprice=max(house[unit_price])varminprice=min(house[unit_price])varavgprice=AVERAGE(house[unit_price])vart1=row("属性","最高价","值",maxprice)vart2=row("属性","最低价","值",minprice)vart3=row("属性","平均价","值",avgprice)vartotal=union(union(t1,t2),t3)returntotal359.2.1VAR的用法季度同比增长率VAR方法=VARCURSALES=count(house[no])VARPREVSALES=CALCULATE(count(house[no]),PREVIOUSQUARTER('housesales_calendar'[Date]))VARRES=divide(CURSALES-PREVSALES,PREVSALES)RETURNRES369.2.1VAR的用法VAR用法的优点主要是：可以将复杂的计算过程分解表达，从而便于实现，也便于理解；VAR中定义的名字对象的计算，其计算过程不受上下文筛选环境的影响，仅仅由其DAX公式定义本身决定，而如果不使用VAR用法，则名字对象所对应的子表达式的计算会受其所应用的外部表达式的上下文筛选环境约束。VAR定义的名字被计算后，就被保存下来，后面需要时就可以直接使用，而不需要重新计算，从而可以提高性能。可以方便进行调试。379.2.2度量值的管理随着数据分析的进度不断拓展，从不同角度对当前的数据集建立了不同的分析模型后，每个分析模型都需要建立相应的度量值，因此度量值会越来越多，而且分属于不同的数据表，这样会使得当前数据表视图显得非常杂乱，难以管理。389.2.2度量值的管理当面临复杂的数据分析模型时，首先在创建度量值时对属性列名要使用带表名限定的形式，然后可以根据需要创建一张或多张专门存储度量值的表，以分类管理所有的度量值。（1）创建一张专门的度量值管理表。（2）将分散在其他表中的度量值移动到专门的度量值管理表中。399.2.2度量值的管理通过PowerBIDesktop“主页”中的“输入数据”按钮，调出“创建表”对话框，表名可以自己决定，需要顾名思义，例如可以命名为“房价相关的度量值”；该表只有一列，这一列其实没有实际用途，仅仅是为了满足建表的结构需要，因此列名也可以随便取名。409.2.2度量值的管理首先选中要管理的度量值，然后通过PowerBIDesktop的“度量工具”选项卡中的“主表”按钮调出度量值归属表的下拉列表，选择“房价相关的度量值”表，这样就将选中的度量值的归属表从原表调整为“房价相关的度量值”表。419.3DAX语言数据分析深入理解任何DAX公式的计算过程都是由以下两个引擎处理的：公式引擎和存储引擎。当PowerBI将一个DAX公式发送给包含着语义模型的分析服务引擎后，分析服务引擎会产生一个相应的查询计划，并由公式引擎和存储引擎具体执行。存储引擎负责提取数据，公式引擎负责完成存储引擎无法有效完成的复杂的计算和处理操作。429.3.1公式引擎公式引擎负责将DAX公式查询分解为由一系列具体执行步骤构成的查询计划，这些具体的执行步骤包括表的连接、结合筛选上下文条件对数据的筛选、数据的聚合计算以及数据的查询等等。公式引擎能够实现对数据的计算和处理，但是当需要数据时，公式引擎依赖于存储引擎提供数据。439.3.2存储引擎当收到公式引