Power BI：在DAX中转移筛选器
Power BI：在DAX中转移筛选器
文章背景： 最近在学习DAX权威指南的第15章，高级关系。在某些情况下，我们很难在两个实体之间创建物理关系。可喜的是，DAX表达式有多种方式模拟这种关系。
计算实例：Contoso公司在当地报纸和网络上做广告，每月选择一个或多个品牌进行推广。这些信息存储在一个名为Advertised Brands的表中，该表中包含年份、月份和推广的品牌。在图1中，你可以看到该表的摘录信息。
图1 该表中包含每月、每个品牌的推广记录
需要注意的是，表中每月具备唯一值的列。因此，这个表不能位于关系的一端。
我们的需求是创建一个度量值来计算产品在推广时间段内的销售额。在不创建物理关系的情况下，我们可以采取如下几个方案。
1 依靠迭代
第一种次优的解决方案是依靠迭代。我们可以逐行迭代Sales表，在每一行上检查正在销售的产品的品牌是否在该月进行过广告宣传。下面的度量值可以作为解决方案，但它不是最好的。
Advertised Brand Sales =
SUMX (
FILTER (
Sales,
CONTAINS (
'Advertised Brands',
'Advertised Brands'[Brand], RELATED ( 'Product'[Brand] ),
'Advertised Brands'[Calendar Year], RELATED ( 'Date'[Calendar Year] ),
'Advertised Brands'[Month], RELATED( 'Date'[Month] )
)
),
Sales[Quantity] * Sales[Net Price]
) 在这个度量值中，使用了CONTAINS函数，它可以搜索表中是否存在满足要求的行。在这个例子中，如果在Advertised Brands表中至少有一条记录对应的品牌是当前品牌，年份是当前年份，月份是当前月份，那么CONTAINS会返回True。这里的当前，指的是当前被FILTER迭代的Sales表的记录。
上述代码有两个无法忽视的问题：
- FILTER迭代Sales表（这是一个很大的表），并为其每一行调用CONTAINS函数。尽管CONTAINS是一个运算很块的函数，但是数百万次的调用还是会导致公式性能的下降。
- Advertised Brand Sales度量值没有利用已经计算好销售额的Sales Amount度量值。这种方法不是最好的，因为表达式在迭代过程中被重复计算了。
2 使用TREATAS函数转移筛选器
首选的也是最好的做法是，使用TREATAS将Advertised Brands表的筛选器转移到其他表上。
借助TREATAS，我们可以改变Advertised Brands表的数据沿袭，使其可以作为CALCULATE的筛选器参数，并将其筛选器作用于整个数据模型。下面的度量值执行了这个操作：
Advertised Brand Sales TreatAs =
VAR AdvertisedBrands =
SUMMARIZE (
'Advertised Brands',
'Advertised Brands'[Brand],
'Advertised Brands'[Calendar Year],
'Advertised Brands'[Month]
)
VAR FilterAdvertisedBrands =
TREATAS (
AdvertisedBrands,
'Product'[Brand],
'Date'[Calendar Year],
'Date'[Month]
)
VAR Result =
CALCULATE ( [Sales Amount], KEEPFILTERS ( FilterAdvertisedBrands ) )
RETURN
Result需要注意的是，KEEPFILTERS是必需的。Sales表需要同时接收来自视觉对象的筛选器（可能只筛选一年或一个品牌）。
这个版本的写法比使用迭代的版本要好得多。因为它复用了Sales Amount度量值，避免了重写代码，并且不需要迭代Sales表来执行查询。
3 使用INTERSECT函数转移筛选器
获得相同结果的另一种做法是使用INTERSECT函数。下面的代码使用了基于INTERSECT的解决方案：
Advertised Brand Sales Intersect =
VAR SelectedBrands =
SUMMARIZE (
Sales,
'Product'[Brand],
'Date'[Calendar Year],
'Date'[Month]
)
VAR AdvertisedBrands =
SUMMARIZE (
'Advertised Brands',
'Advertised Brands'[Brand],
'Advertised Brands'[Calendar Year],
'Advertised Brands'[Month]
)
VAR Result =
CALCULATE (
[Sales Amount],
INTERSECT (
SelectedBrands,
AdvertisedBrands
)
)
RETURN
ResultINTERSECT保留了它接收到的第一个表的数据沿袭。因此，所得到的表仍然可以筛选Product表和Date表。这一次不需要使用KEEPFILTERS，因为第一个SUMMARIZE已经包含了可见的品牌和月份；INTERSECT只从这个列表中删除了没有广告宣传记录的组合。
从性能上看，这段代码需要扫描Sales表来生成现有的品牌和月份列表，另外还需要一次扫描来计算销售额。因此，它比使用TREATAS的版本要慢一些。但是这种做法仍然是值得学习的，因为它可能在其他需要使用集合函数（比如UNION和EXCEPT）的场景中很有用。
4 使用FILTER函数转移筛选器
DAX开发人员还有第四种选择：使用FLILTER和CONTAINS。其代码与使用SUMX第一个版本类似，主要区别在于它使用CALCULATE而不是SUMX，并且避免了迭代Sales表。下面的代码实现了这个替代方案：
Advertised Brand Sales Contains =
VAR SelectedBrands =
SUMMARIZE (
Sales,
'Product'[Brand],
'Date'[Calendar Year],
'Date'[Month]
)
VAR FilterAdvertisedBrands =
FILTER (
SelectedBrands,
CONTAINS (
'Advertised Brands',
'Advertised Brands'[Brand], 'Product'[Brand],
'Advertised Brands'[Calendar Year], 'Date'[Calendar Year],
'Advertised Brands'[Month], 'Date'[Month]
)
)
VAR Result =
CALCULATE (
[Sales Amount],
FilterAdvertisedBrands
)
RETURN
Result由于拓展表的存在，在CALCULATE中使用Sales表作为筛选器是很危险的。因此，只筛选三个列是更好的选择。此外，这里不需要使用KEEPFILTERS，因为SUMMARIZE已经保留了品牌、年份和月份在当前筛选上下文中的可见值。
5 性能比较
借助DAX Studio，我们可以计算四个度量值各自的运行时间，从而更直观地比较四种解决方案的性能。
使用的代码如下：
EVALUATE
ADDCOLUMNS(
SUMMARIZE(Sales,
'Date'[Calendar Year],
'Product'[Category]
),
"Advertised Brand Sales_SUMX", [Advertised Brand Sales],
)
EVALUATE
ADDCOLUMNS(
SUMMARIZE(Sales,
'Date'[Calendar Year],
'Product'[Category]
),
"Advertised Brand Sales_TREATAS", [Advertised Brand Sales TreatAs],
)
EVALUATE
ADDCOLUMNS(
SUMMARIZE(Sales,
'Date'[Calendar Year],
'Product'[Category]
),
"Advertised Brand Sales_INTERSECT", [Advertised Brand Sales Intersect],
)
EVALUATE
ADDCOLUMNS(
SUMMARIZE(Sales,
'Date'[Calendar Year],
'Product'[Category]
),
"Advertised Brand Sales_CONTAINS", [Advertised Brand Sales Contains]
)对上述代码分别运行了两次，结果如下：
（1）
（2）
从运行结果来看，TREATAS方案和INTERSECT方案的性能占优，这是预料之中的。另外，方案四的性能最差，比方案一的运行时间还长，这个是在预料之外的。
参考资料：
[1] DAX权威指南（https://item.jd.com/13168782.html）