在開始本章內(nèi)容之前，我們先理解一下虛擬表和物理表的概念，虛擬表是DAX表函數(shù)返回的結(jié)果表，物理表是PBI模型中從數(shù)據(jù)源導(dǎo)入的表，虛擬表和物理表是虛和實(shí)的對應(yīng)關(guān)系。在PBI模型視圖界面，我們可以看到的導(dǎo)入的表就是物理表，本書示例中，一共導(dǎo)入了5個表 (Calendar, Customers, Products, Territories, and Sales)，它們都是物理表。

DAX語言里，我們把返回結(jié)果是一個表的函數(shù)稱為表函數(shù)，這類函數(shù)返回的表在模型視圖是看不到的，也不會存儲到模型中。其實(shí)虛擬表在背后悄悄干了很多工作，作用跟物理表在某些方面是一樣的，只是我們看不到它而已。

1?? CALCULATE 里面的表函數(shù) Table Functions Inside CALCULATE

?表函數(shù)最常見的使用方法是放在CALCULATE函數(shù)里，第9章介紹CALCULATE函數(shù)時介紹過：CALCULATE的第二參數(shù)可以是簡單條件篩選或篩選表。就像這種寫法：Part of sales = calculate([total sales],TableName[ColumnName] = some value)。像函數(shù)定義的那樣，CALCULATE第二參數(shù)篩選表就是用輸入的表做為篩選條件。我們可以使用一個現(xiàn)有的表做為篩選條件篩選出符合條件的行，也可以使用由表函數(shù)返回的表做為篩選表，CALCULATE應(yīng)用篩選以后再按第一參數(shù)表達(dá)式進(jìn)行計(jì)算。

2??使用虛擬表 Using Virtual Tables

我們可以把度量值中用表函數(shù)返回的表想像成一個虛擬的表，它站在模型里但不存儲在里面。虛擬表是在DAX度量值公式中動態(tài)創(chuàng)建的，是公式篩選出來的符合條件的表。強(qiáng)調(diào)一點(diǎn)：用公式創(chuàng)建的虛擬表跟物理表一樣，跟模型中的其它表自動建立虛擬關(guān)系，也會按篩選的邏輯(按箭頭方向傳遞篩選，一端表篩選多端表)傳遞篩選條件來篩選模型。虛擬表繼承了產(chǎn)生此虛擬表的母表的所有特性( 一脈相承，具有相同的數(shù)據(jù)沿襲 )? 。

3?? VALUES函數(shù) The VALUES() Function

我們來看看第一個表函數(shù)VALUES。在公式欄輸入VALUES時通過智能提示我們知道：這個函數(shù)返回一個表

關(guān)于VALUES我們要知道：它返回的表也服從來自視覺對象的初始篩選上下文，VALUES的參數(shù)表如何被視覺對象篩選的，那VALUES返回的表也會被篩選。

4?? 示例：日期表

新建一個矩陣，把日期表中的年字段放到矩陣的行上。

計(jì)算日期表中每年包括的月數(shù) Total Months in Calendar = COUNTROWS(VALUES('Calendar'[MonthName]))

下面這樣寫是錯誤的：Total Months in Calendar wrong = COUNTROWS('Calendar'[MonthName])

錯誤提示：無法確定表'alendar'中列]"MonthName'的單個值。當(dāng)度量公式引用包含許多值的列，且未指定用于獲取單一結(jié)果的 min、 max,count 或sum等聚合時可能發(fā)生這種情況。

之所以報錯，是因?yàn)? COUNTROWS的參數(shù)要求是一張表，列不能在此做參數(shù)。所以我們給'Calendar'[MonthName]套上個VLAUES就可以了VLAUES('Calendar'[MonthName])，返回的是一個虛擬表，它雖然只有一個月份列，但它依然是一張表，它延續(xù)了它的母表(日期表)在模型中與其它表的關(guān)系，并且在矩陣中也是母表的初始的篩選上下文(之前介紹過這一概念)， 但我們不能把VLAUES函數(shù)直接扔到度量值里，需要對它創(chuàng)建的虛擬表進(jìn)行聚合，本例中 COUNTROWS函數(shù)正好派上用場，它可以計(jì)算表的行數(shù)，也就是月份的數(shù)量。

上圖中我們看到，2016年只有6個月，因?yàn)閿?shù)據(jù)表的日期是從2016-6-1開始的，所以年份放到矩陣行篩選以后，2016年必然只有6個月，其它年份包括所有月份，所以都是12個月。再重復(fù)一次度量值的計(jì)算過程：先按矩陣的行字段進(jìn)行篩選，再用表達(dá)式來計(jì)算。

?? 不能將VALUES()創(chuàng)建的這個新表單獨(dú)放到度量中，除非將它放在返回單個值的其他函數(shù)中(像COUNTROWS()等這樣的聚合函數(shù))。

Total Months in Calendar = COUNTROWS(VALUES('Calendar'[MonthName]))

5?? 具像化虛擬表 Conceptualising Virtual Tables

VALUES函數(shù)只有一個參數(shù)，參數(shù)是一張表或表的一個列，當(dāng)參數(shù)是表列時，返回的表只有一列且這一列的值是不重復(fù)的，VALUES(表[列名])是去重復(fù)的函數(shù)。參數(shù)是表時也一樣，會去掉重復(fù)的表的行。使用虛擬表時，要在腦海中腦補(bǔ)出一張圖出來。

再次強(qiáng)調(diào)一下：下面這張圖只是為了讓你能比較清楚地理解虛擬表在模型中的作用，實(shí)際上由表函數(shù)創(chuàng)建的虛擬表在模型中不是實(shí)實(shí)在在的存在的(由源導(dǎo)入模型的表就是現(xiàn)實(shí)的存在的)。實(shí)際上即使虛擬表不存在，但它也像存在一樣，延續(xù)保持著和母表的一種虛擬關(guān)系，其實(shí)這就是數(shù)據(jù)沿襲。并且虛擬表服從所在的可視化對象的當(dāng)前篩選上下文。

6?? 返回一個值 Returning a Single Value

上一節(jié)示例中，如果我們把矩陣的行字段換成月份MonthName,矩陣中Total Months in Calendar的值都變成了1

?? 函數(shù)補(bǔ)充：?

HASONEVALUE

語法：HASONEVALUE(<columnName>)?

參數(shù)：表的列

返回值：如果篩選 columnName 的上下文后僅剩下一個非重復(fù)值，則返回 TRUE 。 否則返回 FALSE 。

NOTE:??

○ COUNTROWS(VALUES(<columnName>)) = 1 是 HASONEVALUE() 的等效表達(dá)式。

○ 在已計(jì)算的列或行級安全性 (RLS) 規(guī)則中使用時，不支持在 DirectQuery 模式下使用此函數(shù)。

返回一個值時，VALUES有種特殊用法，我們可以使用IF 、HASONEVALUE 和VLAUES 寫個度量值，當(dāng)月份只有一個值時返回月份的名稱，否則不顯示，這種用法通常用于讓總計(jì)不顯示。 像本章中第四小節(jié)計(jì)算月份數(shù)量時，總計(jì)不等于列之和，這樣看起來很別扭，我們就用這種方法讓總計(jì)不顯示。

示例1：補(bǔ)充

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：

為了讓大家看得更清楚一點(diǎn)關(guān)于返回一個值的概念，本例中我們用產(chǎn)品表寫這樣兩個度量值

Total Nmuber of Produncts COUNTROWS Ver = COUNTROWS(VALUES(Products[ProductName]))

Product Name(Values) =

? ? IF(HASONEVALUE(Products[ProductName]),

? ? VALUES(Products[ProductName])

)

創(chuàng)建一個矩陣，把Product表的ModelName和ProductName兩個列放在行上，剛寫的兩個度量值放在值上。

我們可以看到：產(chǎn)品數(shù)是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.

此錯誤消息中的關(guān)鍵信息是“在預(yù)期只有一個值的地方提供了一個包含多個值的表?！?/span>

?? NOTE:? 只有當(dāng)度量返回單個值時，才可以在度量中使用VALUES()。如果VALUES()返回多個值，則拋出錯誤。HASONEVALUE()函數(shù)SK C 用于防止VALUES()返回多個值的特定情況，而IF(HASONEVALUE())模式可防止發(fā)生此錯誤。

SELECTEDVALUE函數(shù) The SELECTEDVALUE() Function

語法：SELECTEDVALUE(ColumnName, AlternateResult)

SELECTEDVALUE() was created to replace the following complex formula from the previous section:

??SELECTEDVALUE是為替換復(fù)雜的公式面生的。下面這兩個公式等效

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()執(zhí)行IF(HASONEVALUE())測試，如果列中只有一個值，則返回該列中的單個值。默認(rèn)情況下，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()函數(shù)在Power Pivot for Excel中還不能用，因此在Power Pivot 里面還得用IF(HASONEVALUE())公式。

8?? CONCATENATEX函數(shù)是個援兵 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函數(shù)，稱為CONCATENATEX()，它迭代表中的值列表，并將它們連接到一個單獨(dú)的值。

第7小節(jié)中，我們可以把公式改成這樣，

Product Name(Values) ConcatenateX =

? ? CONCATENATEX(

? ? ? ? VALUES(Products[ProductName]),

? ? ? ? [ProductName],

? ? ? ? ", "

? ? )

切片器選中ModelName后，結(jié)果就變成了這個樣子

9?? 說說如何給月份排序 Changing the Month Name Sort Order

前節(jié)的例子中，我們把月份列放到矩陣行上時，月份并沒有按現(xiàn)實(shí)中月份的先后順序排列，PBI中默認(rèn)是按月份的首字母先后排序的。

我們可以按下面步驟操作，讓月份按自然月份的順序排列

轉(zhuǎn)到數(shù)據(jù)視圖，選中 MonthName 列，在"列工具"菜單找到并點(diǎn)擊"按列排序"按鈕，選擇 MonthNumberOfYear 列，把它作為排序的列，返回矩陣，你會發(fā)現(xiàn)現(xiàn)在月份已經(jīng)是我們想要的順序了

注：我們還可以按其它方法對月份排序，原則是找到文本月份對應(yīng)的數(shù)字列，只要對應(yīng)的數(shù)字列能按從小到大與文本月份對應(yīng)即可。如果找不到對應(yīng)的數(shù)字列，可以進(jìn)入PowerQuery,手工制造一個數(shù)字列，再上載到數(shù)據(jù)模型，按新創(chuàng)建的列排序即可。

?? 練習(xí)：省略。

11、"新建表"菜單? New Table

對沒有IT背景的PBI用戶來說，因?yàn)榭床坏絍ALUES創(chuàng)建的表，所以理解這個函數(shù)有些困難，盡管我們可以給VALUES函數(shù)外面套個COUNTROWS，至少可以知道一下VALUES創(chuàng)建的表有多少行。PBI有個新建表的功能，這個功能在EXCEL內(nèi)置的POWERPIVOT中沒有設(shè)置。PBI中的新建表菜單如下圖

注：

點(diǎn)擊新建表菜單功能按鈕時，公式欄提示你輸入公式創(chuàng)建一個表(像用VALUES函數(shù)創(chuàng)建一個表的公式)。使用新建表創(chuàng)建的表是實(shí)實(shí)在在地添加到數(shù)據(jù)模型的表，模型中這種表的圖標(biāo)是這樣的

從數(shù)據(jù)源導(dǎo)入模型的表圖標(biāo)是這樣的