簡單篩選選函數(shù)
和下面的效果一樣
IF ( ISBLANK ( Sales[Quantity] ), 1, Sales[Quantity] )??? 用其檢測是否為空
IFERROR ( SQRT ( Test[Omega] ), BLANK() )???? 有錯誤用空值替代
IF ( Sales[Quantity] = 0, BLANK(), Sales[Sales Amount] / Sales[Quantity] )?? 簡單的條件判斷語句
DIVIDE ( Sales[Sales Amount], Sales[Quantity] )??? 安全除法，若被除數(shù)為0歼秽，則返回0
Sales[Gross Margin] = Sales[Sales Amount] - Sales[Total Product Cost]? 直接相減?? 計算列
[Total Sales] := SUM ( Sales[Sales Amount] )?? 度量值
SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] )? 參數(shù)（ 表潘鲫，表達式）余寥。
COUNTROWS ( Table ) = COUNTA ( Table[Column] ) + COUNTBLANK ( Table[Column] )? 非空+?? 空等于總行數(shù)
DISTINCTCOUNT ( Table[Column] ) = COUNTROWS ( DISTINCT ( Table[Column] ) )
SUMX (
? ? ? ? ? ? ? ? ? ? ? ? ? ?? FILTER ( Sales, Sales[Unit Price] > 10 ),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Sales[Sales Amount]
) ? ? ? ? ? ? ? ? ? ?? 計算上下文
Sales[List Amount] = Sales[Order Quantity] * Products[List Price]????? 兩表建立關系
Sales[List Amount] = Sales[Order Quantity] * RELATED ( Products[List Price] )? 沒有建立關系則有這個函數(shù)
Products[Number Orders] = COUNTROWS ( RELATEDTABLE ( Sales ) )
Products[List Amount] =
SUMX (
RELATEDTABLE ( Sales ),
Sales[Order Quantity] * Products[List Price]
) ? 好像是兩表之間是多對一的關系的話就用RELATEDTABLE
Products[Price Ranking] =
COUNTROWS (
FILTER (
Products,
Products[List Price] > EARLIER ( Products[List Price] )
)
) ?? EARLIER表示一個變動的參數(shù)怎爵，表示在當前的自己之前的數(shù)，語句意思是計算價格排名啤呼，計算在產品表里面產品的價格比自己高的數(shù)的個數(shù)卧秘，計算行多少就是有多少高出自己。
CALCULATE (表達式? 條件官扣，條件? ...)
[Sales2006] :=
????????????? CALCULATE (
????????????????????????????????????????? SUM ( Sales[Sales Amount] ),
?????????????????????????????????????????? Order Date[Year] = 2006
)
增加篩選條件翅敌，更顆粒化的選擇數(shù)據(jù)
[Sales2006] :=
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER ( ALL ( Order Date[Year] ), Order Date[Year] = 2006 )
)
VALUES
返回唯一值惕蹄，和distinct差不多
[Sales2006if Selected] :=
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER ( VALUES ( Order Date[Year] ), Order Date[Year] = 2006 )
)
區(qū)別之處蚯涮，是否計算空值
Num Of Products := COUNTROWS ( Products )
Num Of Categories := COUNTROWS ( DISTINCT ( Categories[Product Category Name] ) )
Num Of Categories Including Missing := COUNTROWS ( VALUES ( Categories[Product Category Name] ) )
簡單時間智能函數(shù)
[Sales] := SUM( Sales[Sales Amount] )
[Sales MTD] := TOTALMTD ( [Sales], 'Date'[Date] )? 月累計至今的銷售額
[Sales QTD] := TOTALQTD ( [Sales], 'Date'[Date] )? 季度累計
[Sales YTD] := TOTALYTD ( [Sales], 'Date'[Date] )? 年累計
也可以這樣：
[Sales MTD] := CALCULATE ( [Sales], DATESMTD ( 'Date'[Date] ) )
[Sales QTD] := CALCULATE ( [Sales], DATESQTD ( 'Date'[Date] ) )
[Sales YTD] := CALCULATE ( [Sales], DATESYTD ( 'Date'[Date] ) )
但更靈活：計算上個周期的數(shù)值（周期自定）
[PY Sales] := CALCULATE ( [Sales], DATEADD ( 'Date'[Date], -1, YEAR ) )
[PQ Sales] := CALCULATE ( [Sales], DATEADD ( 'Date'[Date], -1, QUARTER ) )
[PM Sales] := CALCULATE ( [Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )
[PY Sales] := CALCULATE( [Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )?? 依然性的
[PY Total Sales] := CALCULATE( [Sales], PARALLELPERIOD( 'Date'[Date], -1, YEAR ) )? 這個不是按自然年度劃分的，是指過去12個月為一年焊唬，而非去年的現(xiàn)在同期月
[YTD Over Total PY]:= DIVIDE ( [Sales YTD], [PY Total Sales] )??? 恋昼，年累計銷售額占比過去12個月
???????? [PY YTD Sales] :=
CALCULATE (
[Sales],
SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) )
)
[PY YTD Sales] :=
CALCULATE(
[Sales],
DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)
有點復雜了，嵌套了好幾個函數(shù)
[MAT Sales] :=
CALCULATE (
[Sales],
DATESBETWEEN (
'Date'[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE( 'Date'[Date] ) ) ),
LASTDATE ( 'Date'[Date] )
)
)??
不太適合赶促，搞不懂
Units Last Date := CALCULATE ( SUM ( Inventory[Units Balance] ), LASTDATE ( 'Date'[Date] )
Units Last Non Blank :=
CALCULATE (
SUM ( Inventory[Units Balance] ),
LASTNONBLANK (
'Date'[Date],
CALCULATE ( SUM ( Inventory[Units Balance] ) )
)
)
Sales Road650Red44 :=
CALCULATE (
SUM ( Sales[Sales Amount] ),
Products[Product Name] = "Road-650 Red, 44"
比較不同之處
Sales Only Road650Red44 :=
CALCULATE (
SUM ( Sales[Sales Amount] ),
Products[Product Name] = "Road-650 Red, 44",
ALL ( Products )?
自定義的年累計
[YTD] :=
CALCULATE (
[Original Measure],
FILTER (
ALL ( 'Date' ),
?'Date'[Year] = MAX ( 'Date'[Year] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
有幾個客戶
Unique Customers :=
CALCULATE (
DISTINCTCOUNT ( Customers[Customer Code] ),
Sales
)
統(tǒng)計函數(shù)
移動平均模板
Moving Average X 7 Days :=
AVERAGEX (
DATESINPERIOD (
'Date'[Date],
LASTDATE ( 'Date'[Date] ),
-7,
DAY
),
[Total Amount]
)
更快速的方法
Moving Average 7 Days :=
CALCULATE (
IF (
COUNT ( 'Date'[Date] ) >= 7,
SUM ( Sales[Amount] ) / COUNT ( 'Date'[Date] )
),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] > ( MAX ( 'Date'[Date] ) - 7 )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
參數(shù)表
[Sales Amount] :=
IF (
HASONEVALUE ( Scale[Scale] ),
SUM ( Sales[Sales Amount] ) / VALUES ( Scale[Scale] ),
SUM ( Sales[Sales Amount] )
) ? HASONEVALUE 和VALUES合在一起使用液肌，表示選中某一個值
Discounted Sales Amount :=
IF (
HASONEVALUE ( Discounts[Discount Value] ),
[Sales Amount] * ( 1 – VALUES ( Discounts[Discount Value] ) ),
IF (
NOT ( ISFILTERED ( Discounts[Discount] ) ),
[Sales Amount],
BLANK ()
)
)如果啥都沒有選的話就用源值替代
Top Sales Amount :=
IF (
HASONEVALUE ( 'Top'[Top] ),
IF (
RANKX (
ALL ( Sales[Product] ),
[Sales Amount]
) <= VALUES ( 'Top'[Top] ),
[Sales Amount],
BLANK ()
)
) ? 帶參數(shù)表的topN
計算過去12個月的銷售額
[MAT Sales] :=
CALCULATE (
[Sales Amount],
DATESINPERIOD (
'Date'[Date],
LASTDATE ( 'Date'[Date] ),
-1,
YEAR
)
)
多選條件下的排名，帶切片器的那種
[Rank by Brand C] :=
IF (
HASONEVALUE ( Product[Brand] ),
RANKX ( ALLSELECTED ( Product[Brand] ), [Sales Amount] )
)
