DAX Tabular Calculate,Filter,Value和All

Calculate是DAX最常用的的方法之一,语法是CALCULATE( <expression>, <filter1>, <filter2>… ),第一个参数expression是用来出入需要执行的计算公式,例如SUM,AVG,COUNT等,接下来的filter1到filter2则是用来限制expression的结果,类似SQL中的where条件.
但是Calculate有一个问题,就是当tabular的DirectQueryMode选的是DirectQuery时,Calculate是不能用,如果想着要用DirectQuery模式,最后先了解哪些方法不能用在觉得是不是要用这种模式.

Filter在编写度量值时也经常用到,甚至比Calculate更加常用,因为除了Calculate外其他的度量值计算也会用到Filter.语法是FILTER(<table>,<filter>).第一个参数是要筛选的表,第二个参数是筛选条件.

All在编写度量值时也有用到,但并不常用,或者是用了我们也不知道.All的语法是ALL( {<table> | <column>[, <column>[, <column>[,…]]]} ),第一个参数可以是表名或者列名,后面的参数则是列名.ALL作用是清除参数所给的filter,这个清除的优先级似乎很高,无论是在All之前还是All之后的filter都会被出掉.同理如果有一些算法不想被关联,也可以通过All来去掉关联. 更多用法可参照MSDN,除了All还有一个AllExcept作用和All差不多,就是去掉除了所给参数意外的filter.

Values(注意不是VALUE)的作用和All相反,All是去掉Filter,Values是保留Filter.

前面说这么多解释的目的是为了下面的实验更好理解.下面开始做实验.
实验所用数据如下:

USE [test]
GO
/****** Object:  Table [dbo].[Demo2]    Script Date: 7/7/2015 1:20:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Demo2](
    [Name] [nvarchar](50) NULL,
    [Color] [nvarchar](50) NULL,
    [Type] [nvarchar](50) NULL,
    [RowCount] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Tom', N'Red', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Lily', N'Red', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Hugo', N'Red', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Tom', N'Green', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Lily', N'Green', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Hugo', N'Green', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Lily', N'Yellow', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Tom', N'Yellow', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Hugo', N'Yellow', N'Car', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Lily', N'Red', N'Bike', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Hugo', N'Red', N'Bike', 1)
GO
INSERT [dbo].[Demo2] ([Name], [Color], [Type], [RowCount]) VALUES (N'Tom', N'Red', N'Bike', 1)
GO

首先从最简单的开始:

CountRows:=Sum('Demo'[RowCount])

AllNames:=CALCULATE([CountRows],ALL('Demo'[Name]))

结果如下:

因为All去掉了Name的Filter,所以无论Name是什么,它都无视了.

如果我们在度量值的表达式里面要求只算Name是Tom的,但在维度哪里又按Name分组会怎么样呢,有两种情况:

1. 保留所有Filter
2. 保留最先的Filter
3. 保留最后的Filter
   为了方便观察,我把Name=Tom,Type=Bike的记录删掉方便观察,脚本以及结果如图所示:

Count Tom ALL:=CALCULATE(SUM([RowCount]),FILTER(ALL('Demo'[Name]),'Demo'[Name]="Tom"))

Count Tom VALUES:=CALCULATE(SUM([RowCount]),FILTER(VALUES('Demo'[Name]),'Demo'[Name]="Tom"))

Count Tom NoFilter:=CALCULATE(SUM([RowCount]),'Demo'[Name]="Tom")

从结果就能看出,因为[Count Tom ALL]中的All那Name的Filter都去掉了,所以它的值都为3.[Count Tome VALUES]因为保留了Excel后加的Filter和度量值公式中的Filter,Name急既要等于Hugo(Lily),又要等于Tom,所以那两列为空.[Count Tom NoFilter]没有用的Filter,而是直接指定’Demo’[Name]=”Tom”,从结果上来看它其实是等于[Count Tom All].
由此可见,情况1和2是可以实现的,情况3我还不知道怎么做,如果知道的朋友可以告诉我一下.

接下来做的实验是,既然All会清楚掉对应table和column的Filter,那么当有两个Filter用到All的时候,结果会怎么样呢?
第一次情况,同一层用两个有All的Filter:

Count Two All Filter:=CALCULATE(SUM([RowCount]),FILTER(ALL(Demo[Name]),'Demo'[Name]="Tom"),FILTER(ALL(Demo[Name]),'Demo'[Name]="Hugo"))

第二种情况,不同层用两个有All的Filter:

Count Two layer All Filter:=CALCULATE(CALCULATE(SUM([RowCount]),FILTER(ALL(Demo[Name]),'Demo'[Name]="Tom")),FILTER(ALL(Demo[Name]),'Demo'[Name]="Hugo"))

结果如下:

从结果看出,当两个有All的Filter在同一层时,结果集取的是两个Filter的并集.当两个有All的Filter在不同层时,只会保留最里面那一层的All Filter.

在这里顺带说下All在算所占百分比的度量值时的用法,例如我想知道各个Name所有成所占总车辆的百分比,那么公式可以这样写:

% Car of Each Persion:=Sum([RowCount])/CALCULATE(Sum([RowCount]),All(Demo[Name]))

更多的详情,如果可以翻墙,请参看这里

PS:实验所用SQL Server 版本是 2012 SP1.上述实验都只再同一个表内进行,如果在关联表中用Filter,那会怎样呢,预知后事如何,请听下回分解.