|
DAX 表達式主要用于創(chuàng)建度量列（Measure），度量值是根據(jù)用戶選擇的Filter和公式，計算聚合值，DAX表達式基本上都是引用對應(yīng)的函數(shù)，函數(shù)的執(zhí)行有表級（Table-Level）上下文和行級（Row-Level）上下文之別；其交互行為都是通過表之間的關(guān)系實現(xiàn)的，用戶選擇的Filter，會通過關(guān)系對數(shù)據(jù)進行過濾，是PowerBI報表呈現(xiàn)的數(shù)據(jù)具有動態(tài)交互的特性。在我開發(fā)的PowerBI報表項目中，經(jīng)常使用的DAX表達式函數(shù)，其實并不是很多，本文分享一些入門級的常用函數(shù)，附上簡單的注釋，希望對新手設(shè)計PowerBI報表有所幫助。
我的PowerBI開發(fā)系列的文章目錄：PowerBI開發(fā) 一，常用的操作符在DAX表達式中，常用的操作符是：
二，過濾函數(shù)過濾函數(shù)能夠操作數(shù)據(jù)的上下文，以實現(xiàn)數(shù)據(jù)的動態(tài)計算，功能非常強大。 1，關(guān)聯(lián)函數(shù) Related函數(shù)返回跟當(dāng)前的數(shù)據(jù)行有關(guān)系的表的單個值， RELATED(related_table[column]) Related函數(shù)要求當(dāng)前表和關(guān)聯(lián)表之間存在關(guān)系（Relationship），當(dāng)前表和關(guān)聯(lián)表之間存在many-to-one的關(guān)系，從關(guān)聯(lián)表中返回單個值。 Related函數(shù)運行在行上下文（row context），因此，只能用于計算列的表達式。 示例：表InternetSales_USD和SalesTerritory之間存在關(guān)系，從表SalesTerritory獲取SalesTerritoryCountry的值，并通過FILTER函數(shù)進行過濾，統(tǒng)計表InternetSales_USD中字段[SalesAmount_USD]的加和： = SUMX(FILTER( 'InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry]) <>"United States" ) ,'InternetSales_USD'[SalesAmount_USD]) RELATEDTABLE 用于one-to-many的關(guān)系，用于返回被過濾的所有數(shù)據(jù)行組成的表。 2，過濾函數(shù) Filter函數(shù)，返回被過濾之后的表，是表數(shù)據(jù)的子集： FILTER(<table>,<filter>) 通過過濾條件，獲取表的子集，過濾函數(shù)返回的表只能用于計算。過濾函數(shù)不是獨立的，必須嵌入（embedded）到其他函數(shù)中作為一個表值參數(shù)。 3，計算函數(shù) Calculate函數(shù)，在過濾器的上下文中計算表達式，返回單個值： CALCULATE(<expression>,<filter1>,<filter2>…) 參數(shù)expression是計算的表達式，受到filter參數(shù)的上下文的影響，如果filter參數(shù)改變數(shù)據(jù)的上下文，那么在新的上下文中計算表達式。計算函數(shù)的最大特點是能夠移除過濾器，在過濾器參數(shù)列表中，如果一個數(shù)據(jù)列上存在多個過濾器，那么Calculate函數(shù)會移除前面的過濾器，而只應(yīng)用當(dāng)前的過濾器。 對于Calculate函數(shù)的Filter，有兩種表達式，一種是布爾表達式，計算的結(jié)果是布爾值；一種是只包含一列的表格表達式，是指計算該表格相關(guān)聯(lián)的數(shù)據(jù)，相當(dāng)于做“相等”過濾。 示例：在計算比例關(guān)系時，使用計算函數(shù)的移除過濾器的功能： =( SUM('ResellerSales_USD'[SalesAmount_USD])) /CALCULATE( SUM('ResellerSales_USD'[SalesAmount_USD]) ,ALL('ResellerSales_USD')) 表達式中的分母，使用計算函數(shù)，其中，第一個過濾器參數(shù)使用ALL過濾函數(shù)，使得sum表達式統(tǒng)計所有的數(shù)據(jù)行，這種行為重寫對數(shù)據(jù)表的隱式過濾器。 4,移除過濾器 ALL函數(shù)返回表中的所有數(shù)據(jù)行，清理任意過濾器，用于對全表執(zhí)行聚合運算。ALL函數(shù)主要用于計算比例關(guān)系，常用于分母中。 ALL( {<table> | <column>)
5，去重函數(shù) Distinct 返回單列的表，包含無重復(fù)的值，也就是說，從表中移除重復(fù)值，只返回列的唯一值。 DISTINCT(<column>) 6，過濾器的值 函數(shù)Values返回被過濾的唯一值，在同一個表中的其他Column被過濾，那么返回被過濾的當(dāng)前Column的唯一值。Values函數(shù)和Distinct函數(shù)很相似，唯一的不同是Values函數(shù)會返回Unknown，這是因為關(guān)聯(lián)的Table中包含部匹配的數(shù)據(jù)行，和Left Join的右表中包含NULL值很相似。 VALUES(<TableNameOrColumnName>) When you use the VALUES function in a context that has been filtered, the unique values returned by VALUES are affected by the filter. 結(jié)合CONCATENATEX函數(shù)，能夠把所有過濾器的值連接成字符串。注意，DAX表達式使用 & 進行字符串的連接。 7，探測直接過濾（filtered directly） 如果指定的Column被直接過濾（filtered directly），函數(shù)返回True；當(dāng)同一個Table中的其他Column被過濾，切片器（Slicer）中被關(guān)聯(lián)的Column，默認(rèn)設(shè)置是全部直接過濾，函數(shù)返回True；如果Column上沒有直接的過濾，或者同一個Table中的其他Column被過濾，或者被有關(guān)系的Table過濾，函數(shù)返回False。 ISFILTERED(<columnName>) 直接過濾器的數(shù)值通過函數(shù)Filters返回： FILTERS(<columnName>) 8，探測關(guān)聯(lián)過濾 如果同一個Table中的其他Column被過濾，或者被有關(guān)系的Table過濾，函數(shù)返回True ISCROSSFILTERED(<columnName>) 9，保持過濾器 函數(shù) KeepFilters 的作用是保持過濾器，用于計算函數(shù)（CALCULATE 和 CALCULATETABLE）中。默認(rèn)情況下，計算函數(shù)的過濾器參數(shù)（filter argument）會對過濾表數(shù)據(jù)，當(dāng)在相同的字段上設(shè)置過濾器參數(shù)（filter argument）時，該參數(shù)會替換已經(jīng)存在的過濾器；當(dāng)相同的字段上沒有過濾器參數(shù)（filter argument）時，已經(jīng)存在的過濾器不受影響。函數(shù)KeepFilters會改變計算函數(shù)的行為，當(dāng)在計算函數(shù)使用函數(shù)KeepFilters時，表的上下文是過濾器參數(shù)（filter argument）和已經(jīng)存在的過濾器的交集，也就是說，表的上下文同時受到已經(jīng)存在的過濾器和計算函數(shù)的過濾器參數(shù)（filter argument）的影響。計算函數(shù)替換已經(jīng)存在的過濾器，而函數(shù) KeepFilters會添加已經(jīng)存在的過濾，求交集。 KEEPFILTERS(<expression>) 三，統(tǒng)計函數(shù)統(tǒng)計函數(shù)是最強力的函數(shù)，同時也是最復(fù)雜的函數(shù)，為數(shù)據(jù)的分析，提供非常強力的工具，同時，在使用統(tǒng)計函數(shù)時，必須考慮到數(shù)據(jù)模型，表之間關(guān)系，數(shù)據(jù)重復(fù)等因素，一般都會搭配過濾函數(shù)實現(xiàn)數(shù)據(jù)的提取和分析。 1，求和函數(shù) 從表中計算每一個行的加和，只有數(shù)值會被加和，忽略空值，date，邏輯值或文本值： SUMX(<table>, <expression>) 示例，第一個參數(shù)是過濾器返回的表值，計算[Freight]的加和： =SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])
可以把SUMX函數(shù)，轉(zhuǎn)換為CALCULATE函數(shù)： =CALCULATE( SUM(InternetSales[Freight]), FILTER(InternetSales, InternetSales[SalesTerritoryID]=5))
2，計數(shù)函數(shù) 計算函數(shù)分為：COUNTX和COUNTAX，COUNTX只統(tǒng)計數(shù)值，忽略空值（blank），date，邏輯值或文本值；COUNTAX函數(shù)統(tǒng)計非空值（blank），包含數(shù)值，date，邏輯值或文本值。 COUNTX(<table>,<expression>) COUNTAX(<table>,<expression>) 例外：如果列中包含表達式，而表達式的結(jié)果是空值，但是，COUNTAX/COUNTX函數(shù)把包含公式的列值作為非空（nonblank）看待，在這種情況下，計數(shù)函數(shù)會增加計數(shù)值。 如果COUNTAX函數(shù)沒有數(shù)據(jù)列做計數(shù)，返回blank；如果COUNTAX函數(shù)聚合的數(shù)據(jù)列都是blank，返回0。 3，唯一值計數(shù) 統(tǒng)計列的唯一值計數(shù)，參數(shù)是表列，允許是任意數(shù)據(jù)類型，當(dāng)找不到任何數(shù)據(jù)行時，返回BLANK，否則，統(tǒng)計唯一值的數(shù)量。 DISTINCTCOUNT(<column>) 4，分組聚合函數(shù) DAX 中功能最強大的函數(shù)，對相互關(guān)聯(lián)的Table按照特定的字段，分組聚合，由于分組列是唯一的，通過SUMMARIZE函數(shù)，可以獲得多列的唯一值。 SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) 示例：對數(shù)據(jù)表 ResellerSales_USD ，按照字段 DateTime[CalendarYear] 和 ProductCategory[ProductCategoryName]分組，計算 SUM(ResellerSales_USD[SalesAmount_USD])和 SUM(ResellerSales_USD[DiscountAmount]) 。  SUMMARIZE(ResellerSales_USD
, DateTime[CalendarYear]
, ProductCategory[ProductCategoryName]
, "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
, "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
)
分組聚合函數(shù)，能夠利用關(guān)系，引用相關(guān)表的字段，也就是說，SUMMARIZE函數(shù)能夠?qū)τ嘘P(guān)系的表執(zhí)行連接（join）運算，計算笛卡爾乘積，對Join的結(jié)果集執(zhí)行分組聚合，例如： summarize
(
'Internet Sales',
'Product'[Product Name],
'Date'[Calendar Year],
"Total Sales Amount", sum('Internet Sales'[Sales Amount])
)
從數(shù)據(jù)表Date中獲取字段Calendar Year的前提是，數(shù)據(jù)表Date和Internet Sales之間存在關(guān)系，從數(shù)據(jù)表Product中獲取字段Product Name的前提是：數(shù)據(jù)表Product和Internet Sales之間存在關(guān)系。 分組聚合函數(shù)用于創(chuàng)建新表，在Modeling菜單中，通過“New Table”從DAX表達式中創(chuàng)建新的Table：
參考文檔：SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI) 四，文本函數(shù)在DAX表達式中，字符串使用雙引號界定。 1，格式函數(shù) 按照指定的格式把值轉(zhuǎn)換成文本 FORMAT(<value>, <format_string>) 2，空值 在DAX中，空值（Blank） 和數(shù)據(jù)庫的NULL值是相同的，通過函數(shù)ISBLANK(value)判斷當(dāng)前的字段值是否是空值。 BLANK() ISBLANK(<value>) 3，查找函數(shù) 在一段文本中查找字符串時，從左向右讀取文本，查找函數(shù)返回第一次匹配的字符的序號，序號從1開始，依次遞增。search函數(shù)不區(qū)分大小寫，而find函數(shù)區(qū)分大小寫。 FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) 參數(shù) NotFoundValue 是可選的，當(dāng)查找不到匹配的子串時，返回該參數(shù)的值，一般設(shè)置為0，-1或BLANK()。 如果不設(shè)置該參數(shù)，而查找函數(shù)查找不到匹配的子串時，函數(shù)返回錯誤?？梢酝ㄟ^IFERROR函數(shù)處理錯誤，例如： = IFERROR(SEARCH("-",[PostalCode]),-1) 5，拼接函數(shù) 把表中的數(shù)據(jù)按照指定的分隔符拼接成字符串 CONCATENATEX(<table>, <expression>, [delimiter]) 示例，Employees表中包含[FirstName] 和 [LastName]兩列，把這兩列拼接成一個字符串： CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”) 五，邏輯函數(shù)1，邏輯判斷函數(shù) 檢查邏輯條件是否滿足，如果滿足，返回value_if_true，如果不滿足，返回value_if_false。 IF(logical_test>,<value_if_true>, <value_if_false>) 等于使用“=”表示，邏輯與使用“&&”表示，邏輯或使用“||”表示，而邏輯非，通常使用NOT()函數(shù)來實現(xiàn)： NOT(<logical>) 2，布爾值函數(shù) TRUE() FALSE() 通常用于表示數(shù)據(jù)庫的bit類型的值 3，錯誤函數(shù) 如果表達式返回錯誤，返回value_if_error；如果表達式不返回錯誤，返回表達式的值。 IFERROR(expression, value_if_error) 錯誤函數(shù)等價于： IFERROR(A,B) := IF(ISERROR(A), B, A) 4，包含邏輯 表（Table）表達式是由大括號構(gòu)成的集合：{value1,value2,,vlaueN} IN操作符的用法是： <scalarExpr> IN <tableExpr> 包含行函數(shù)的用法是： CONTAINSROW(<tableExpr>, <scalarExpr>[, <scalarExpr>, …]) 示例，以下兩個表達式是等價的： [Color] IN { "Red", "Yellow", "Blue" }
CONTAINSROW({ "Red", "Yellow", "Blue" }, [Color])
六，關(guān)系使用DAX表達式創(chuàng)建兩個查詢之間的關(guān)系。 1，為指定的關(guān)系指定查詢時（query-time）的方向（cross-filtering direction） 函數(shù)CrossFilter使用已經(jīng)存在的關(guān)系，重寫的關(guān)系設(shè)置只在查詢時有效。 CROSSFILTER(<columnName1>, <columnName2>, <direction>) 2，使用關(guān)系 函數(shù)UseRelationship只能使用已經(jīng)存在的關(guān)系，通過關(guān)系兩個端點（endpoint）來指定關(guān)系，關(guān)系的狀態(tài)是不重要的，通常，使用該函數(shù)的目的是在Calculate函數(shù)中使用不活躍（Inactive）的關(guān)系。 USERELATIONSHIP(<columnName1>,<columnName2>) 七，自然連接（NaturalJoin）DAX表達式支持自然連接操作，分為：自然內(nèi)連接和自然左外連接，函數(shù)的兩個參數(shù)分別是表表達式。自然連接要求兩個表中必須有同名列，并且公共列的數(shù)據(jù)類型必須相同，按照公共列做連接操作。 NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>) NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>) 自然連接函數(shù)的兩個參數(shù)是表表達式，可以是使用函數(shù)SUMMARIZE()函數(shù)派生的表，例如： AttendeeCohortShare = NATURALINNERJOIN( SUMMARIZE(AttendeeCohort,AttendeeCohort[Cohort_Last],"#LastObjectIDs",DISTINCTCOUNT(AttendeeCohort[ObjectId]),"Cohort",MAX(AttendeeCohort[Cohort_Last])) ,SUMMARIZE(AttendeeCohort,AttendeeCohort[Cohort_Current],"#CurrentObjectIDs",DISTINCTCOUNT(AttendeeCohort[ObjectId]),"Cohort",MAX(AttendeeCohort[Cohort_Current])))
參考文檔： |
|
|
