简单筛选选函数

和下面的效果一样

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] )    安全除法，若被除数为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] )  参数（ 表，表达式）。

COUNTROWS ( Table ) = COUNTA ( Table[Column] ) + COUNTBLANK ( Table[Column] )  非空+   空等于总行数

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] )  没有建立关系则有这个函数

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表示一个变动的参数，表示在当前的自己之前的数，语句意思是计算价格排名，计算在产品表里面产品的价格比自己高的数的个数，计算行多少就是有多少高出自己。

CALCULATE (表达式  条件，条件  ...)

[Sales2006] :=

              CALCULATE (

                                          SUM ( Sales[Sales Amount] ),

                                           Order Date[Year] = 2006

)

增加筛选条件，更颗粒化的选择数据

[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 )

)

区别之处，是否计算空值

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] ) )

简单时间智能函数

[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] ) )

但更灵活：计算上个周期的数值（周期自定）

[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个月为一年，而非去年的现在同期月

[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] ) )

)

有点复杂了，嵌套了好几个函数

[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

)

统计函数

移动平均模板

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] )

)

)

参数表

[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 ()

)

)   带参数表的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] )

)