DAX专题7 : VALUES、HASONEVLAUE、SELECTEDVALUE、CONCATENATEX -读书笔记(15)
在开始本章内容之前，我们先理解一下虚拟表和物理表的概念，虚拟表是DAX表函数返回的结果表，物理表是PBI模型中从数据源导入的表，虚拟表和物理表是虚和实的对应关系。在PBI模型视图界面，我们可以看到的导入的表就是物理表，本书示例中，一共导入了5个表 (Calendar, Customers, Products, Territories, and Sales)，它们都是物理表。
DAX语言里，我们把返回结果是一个表的函数称为表函数，这类函数返回的表在模型视图是看不到的，也不会存储到模型中。其实虚拟表在背后悄悄干了很多工作，作用跟物理表在某些方面是一样的，只是我们看不到它而已。
1️⃣ CALCULATE 里面的表函数 Table Functions Inside CALCULATE
表函数最常见的使用方法是放在CALCULATE函数里，第9章介绍CALCULATE函数时介绍过：CALCULATE的第二参数可以是简单条件筛选或筛选表。就像这种写法：Part of sales = calculate([total sales],TableName[ColumnName] = some value)。像函数定义的那样，CALCULATE第二参数筛选表就是用输入的表做为筛选条件。我们可以使用一个现有的表做为筛选条件筛选出符合条件的行，也可以使用由表函数返回的表做为筛选表，CALCULATE应用筛选以后再按第一参数表达式进行计算。
2️⃣使用虚拟表 Using Virtual Tables
我们可以把度量值中用表函数返回的表想像成一个虚拟的表，它站在模型里但不存储在里面。虚拟表是在DAX度量值公式中动态创建的，是公式筛选出来的符合条件的表。强调一点：用公式创建的虚拟表跟物理表一样，跟模型中的其它表自动建立虚拟关系，也会按筛选的逻辑(按箭头方向传递筛选，一端表筛选多端表)传递筛选条件来筛选模型。虚拟表继承了产生此虚拟表的母表的所有特性( 一脉相承，具有相同的数据沿袭 ) 。
3️⃣ VALUES函数 The VALUES() Function
我们来看看第一个表函数VALUES。在公式栏输入VALUES时通过智能提示我们知道：这个函数返回一个表
关于VALUES我们要知道：它返回的表也服从来自视觉对象的初始筛选上下文，VALUES的参数表如何被视觉对象筛选的，那VALUES返回的表也会被筛选。
4️⃣ 示例：日期表
新建一个矩阵，把日期表中的年字段放到矩阵的行上。
计算日期表中每年包括的月数 Total Months in Calendar = COUNTROWS(VALUES('Calendar'[MonthName]))
下面这样写是错误的：Total Months in Calendar wrong = COUNTROWS('Calendar'[MonthName])
错误提示：无法确定表'alendar'中列]"MonthName'的单个值。当度量公式引用包含许多值的列，且未指定用于获取单一结果的 min、 max,count 或sum等聚合时可能发生这种情况。
之所以报错，是因为 COUNTROWS的参数要求是一张表，列不能在此做参数。所以我们给'Calendar'[MonthName]套上个VLAUES就可以了VLAUES('Calendar'[MonthName])，返回的是一个虚拟表，它虽然只有一个月份列，但它依然是一张表，它延续了它的母表(日期表)在模型中与其它表的关系，并且在矩阵中也是母表的初始的筛选上下文(之前介绍过这一概念)， 但我们不能把VLAUES函数直接扔到度量值里，需要对它创建的虚拟表进行聚合，本例中 COUNTROWS函数正好派上用场，它可以计算表的行数，也就是月份的数量。
上图中我们看到，2016年只有6个月，因为数据表的日期是从2016-6-1开始的，所以年份放到矩阵行筛选以后，2016年必然只有6个月，其它年份包括所有月份，所以都是12个月。再重复一次度量值的计算过程：先按矩阵的行字段进行筛选，再用表达式来计算。
❇️ 不能将VALUES()创建的这个新表单独放到度量中，除非将它放在返回单个值的其他函数中(像COUNTROWS()等这样的聚合函数)。
Total Months in Calendar = COUNTROWS(VALUES('Calendar'[MonthName]))
5️⃣ 具像化虚拟表 Conceptualising Virtual Tables
VALUES函数只有一个参数，参数是一张表或表的一个列，当参数是表列时，返回的表只有一列且这一列的值是不重复的，VALUES(表[列名])是去重复的函数。参数是表时也一样，会去掉重复的表的行。使用虚拟表时，要在脑海中脑补出一张图出来。
再次强调一下：下面这张图只是为了让你能比较清楚地理解虚拟表在模型中的作用，实际上由表函数创建的虚拟表在模型中不是实实在在的存在的(由源导入模型的表就是现实的存在的)。实际上即使虚拟表不存在，但它也像存在一样，延续保持着和母表的一种虚拟关系，其实这就是数据沿袭。并且虚拟表服从所在的可视化对象的当前筛选上下文。
6️⃣ 返回一个值 Returning a Single Value
上一节示例中，如果我们把矩阵的行字段换成月份MonthName,矩阵中Total Months in Calendar的值都变成了1
❇️ 函数补充：
HASONEVALUE
语法：HASONEVALUE(<columnName>)
参数：表的列
返回值：如果筛选 columnName 的上下文后仅剩下一个非重复值，则返回 TRUE 。 否则返回 FALSE 。
NOTE:
○ COUNTROWS(VALUES(<columnName>)) = 1 是 HASONEVALUE() 的等效表达式。
○ 在已计算的列或行级安全性 (RLS) 规则中使用时，不支持在 DirectQuery 模式下使用此函数。
返回一个值时，VALUES有种特殊用法，我们可以使用IF 、HASONEVALUE 和VLAUES 写个度量值，当月份只有一个值时返回月份的名称，否则不显示，这种用法通常用于让总计不显示。 像本章中第四小节计算月份数量时，总计不等于列之和，这样看起来很别扭，我们就用这种方法让总计不显示。
示例1：补充
Total Months in Calendar = COUNTROWS(VALUES('Calendar'[MonthName]))
monthname hasonvalue = if(HASONEVALUE('Calendar'[MonthName]),VALUES('Calendar'[MonthName]))
Total Months in Calendar no granttotal = if(HASONEVALUE('Calendar'[CalendarYear]),[Total Months in Calendar])
示例2：
为了让大家看得更清楚一点关于返回一个值的概念，本例中我们用产品表写这样两个度量值
Total Nmuber of Produncts COUNTROWS Ver = COUNTROWS(VALUES(Products[ProductName]))
Product Name(Values) =
IF(HASONEVALUE(Products[ProductName]),
VALUES(Products[ProductName])
)
创建一个矩阵，把Product表的ModelName和ProductName两个列放在行上，刚写的两个度量值放在值上。
我们可以看到：产品数是1的 ModelName 行可以显示出ProductName的值，不是1的则为空。
示例3：
下面举个错误的例子
如果我们这样写度量值，然后把度量值放入矩阵中，则报错
Product Name(Values) Wrong = VALUES(Products[ProductName])
The key information in this error message is “A table of multiple values was supplied where a single value was expected.” You can use VALUES() in a measure only if it returns a single value. If VALUES() returns more than a single value, it throws an error . The HASONEVALUE() function is used to protect against the specific case where VALUES() returns more than a single value, and the IF(HASONEVALUE()) pattern prevents this error from occurring.
此错误消息中的关键信息是“在预期只有一个值的地方提供了一个包含多个值的表。”
❇️ NOTE: 只有当度量返回单个值时，才可以在度量中使用VALUES()。如果VALUES()返回多个值，则抛出错误。HASONEVALUE()函数SK C 用于防止VALUES()返回多个值的特定情况，而IF(HASONEVALUE())模式可防止发生此错误。
SELECTEDVALUE函数 The SELECTEDVALUE() Function
语法：SELECTEDVALUE(ColumnName, AlternateResult)
SELECTEDVALUE() was created to replace the following complex formula from the previous section:
❇️SELECTEDVALUE是为替换复杂的公式面生的。下面这两个公式等效
Product Name(Values) =
IF(HASONEVALUE(Products[ProductName]),
VALUES(Products[ProductName])
)
Product Name Alternate = SELECTEDVALUE(Products[ProductName])
Under the hood, SELECTEDVALUE() performs the IF(HASONEVALUE()) test, and it returns the single value in the column if there is just one. AlternateResult is BLANK() by default.
在底层，SELECTEDVALUE()执行IF(HASONEVALUE())测试，如果列中只有一个值，则返回该列中的单个值。默认情况下，AlternateResult为BLANK()。
Note: The SELECTEDVALUE() function is not available in Power Pivot for Excel at the time of this writing, so you need to use the IF(HASONEVALUE()) formula instead when using Excel.
撰写本书时，SELECTEDVALUE()函数在Power Pivot for Excel中还不能用，因此在Power Pivot 里面还得用IF(HASONEVALUE())公式。
8️⃣ CONCATENATEX函数是个援兵 CONCATENATEX() to the Rescue
Power BI has a special DAX function called CONCATENATEX() that iterates over a list of values in a table and concatenates them together into a single value.
Power BI有一个特殊的DAX函数，称为CONCATENATEX()，它迭代表中的值列表，并将它们连接到一个单独的值。
第7小节中，我们可以把公式改成这样，
Product Name(Values) ConcatenateX =
CONCATENATEX(
VALUES(Products[ProductName]),
[ProductName],
", "
)
切片器选中ModelName后，结果就变成了这个样子
9️⃣ 说说如何给月份排序 Changing the Month Name Sort Order
前节的例子中，我们把月份列放到矩阵行上时，月份并没有按现实中月份的先后顺序排列，PBI中默认是按月份的首字母先后排序的。
我们可以按下面步骤操作，让月份按自然月份的顺序排列
转到数据视图，选中 MonthName 列，在"列工具"菜单找到并点击"按列排序"按钮，选择 MonthNumberOfYear 列，把它作为排序的列，返回矩阵，你会发现现在月份已经是我们想要的顺序了
注：我们还可以按其它方法对月份排序，原则是找到文本月份对应的数字列，只要对应的数字列能按从小到大与文本月份对应即可。如果找不到对应的数字列，可以进入PowerQuery,手工制造一个数字列，再上载到数据模型，按新创建的列排序即可。
🔟 练习：省略。
11、"新建表"菜单 New Table
对没有IT背景的PBI用户来说，因为看不到VALUES创建的表，所以理解这个函数有些困难，尽管我们可以给VALUES函数外面套个COUNTROWS，至少可以知道一下VALUES创建的表有多少行。PBI有个新建表的功能，这个功能在EXCEL内置的POWERPIVOT中没有设置。PBI中的新建表菜单如下图
注：
点击新建表菜单功能按钮时，公式栏提示你输入公式创建一个表(像用VALUES函数创建一个表的公式)。使用新建表创建的表是实实在在地添加到数据模型的表，模型中这种表的图标是这样的
从数据源导入模型的表图标是这样的
