本文翻译自Marco Russo & Alberto Ferrari的文章—《Best practices for using KEEPFILTERS in DAX》来源:SQLBI 本文描述了在使用CALCULATE函数的筛选器参数时,决定何时使用(以及何时不使用)KEEPFILTERS的最佳实践。
常见的用例
KEEPFILTERS是一个筛选器修饰符,应该根据具体情况进行评估,以确保其使用符合计算的特定要求。然而,我们可以描述几种常见情况,在这些情况下,我们可以有95%的确定性来判断是否使用KEEPFILTERS:
当你在一列中筛选一个值时,通常不使用KEEPFILTERS。
当你在一列中筛选两个或更多值时,通常使用KEEPFILTERS。
当你在两列或更多列中筛选时,通常使用KEEPFILTERS。
这个原理是,每当你筛选一个值时,你可能想要覆盖同一列上的任何现有筛选器。的确如此,否则,现有筛选器和新筛选器之间的组合可能会返回一个空值。每当你在一列中筛选两个或更多值时,你可能实际上想要筛选同时存在于两个筛选器——新筛选器和现有筛选器——中的那些值。
显然,也有例外情况。有时,你可能不想应用常见的用例,因为那是需求的一部分。但是,原则应该是你能为例外情况辩护,而不是约束。换句话说,你必须能够在代码中添加注释并解释为什么你要偏离常见情况。这将有助于未来的你和其他人维护代码,并理解这个选择是故意的并且是有动机的,而不是一个错误。
不要在一列的单一值筛选器中使用KEEPFILTERS
单一值筛选器的常见情况是在KPI中用作分母的计算。例如,考虑以下颜色KPI度量,它将所选颜色的销售额与红色产品的销售额进行比较:
销售表中的度量值(或指标)
Color KPI =
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
'Product'[Color] = "Red"
)
)
例如,橙色和粉色产品的销售额大约是红色产品的一半,而黑色产品的收入则是红色产品的八倍以上。
如果在这个情况下使用了KEEPFILTERS,除了红色之外的所有行都会显示空白结果;例如,在第一行中,筛选器会查找同时是天蓝色和红色的产品,但一个产品只能有一种颜色!
这个情况的例外可能是,当用户选择不包括要筛选的值时,应该忽略所选的单一值(因此产生空白结果)。但对于单一值选择来说,这种情况不太可能发生,我们也没有这个情况的实际例子。请查看以下多值或列筛选器的例子,以了解何时应在计算中考虑用户选择的情况。
使用KEEPFILTERS在一列中筛选两个或更多值
当一列上的筛选器可能有多个值时,应该保留现有的筛选器,以避免用户选择被忽略并被新筛选器覆盖。重要的是要考虑到,除了=和==之外的任何谓词都可能生成值列表!像IN、<、>、<=、>=和<>这样的运算符通常会返回多个值,因此,它们应该被KEEPFILTERS涵盖。
第一个例子是“% Trendy Colors”度量,它显示了趋势颜色(蓝色、红色或白色)产品的销售百分比。需要使用KEEPFILTERS来将这组颜色与报告中的任何现有选择进行交集操作:
销售表中的度量值(或指标)
% Trendy Colors =
DIVIDE (
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Color] IN { "Blue", "Red", "White" } )
),
[Sales Amount]
)
结果显示了流行颜色对于每个产品品牌的重要性。
“流行颜色百分比”这个度量值总是小于或等于100%,因为它代表可用颜色集合的一个子集。然而,之前的报告没有对“产品[颜色]”进行筛选。为了理解KEEPFILTERS的重要性,我们应该考虑一个报告,在这个报告中用户选择了一组任意的颜色。在下面的报告中,“错误的流行颜色百分比”这个度量值没有使用KEEPFILTERS。
如果用户选择了天蓝色、黑色、蓝色和棕色,预期的结果是所选颜色中具有流行颜色的产品子集——这正是“流行颜色百分比”度量值产生的结果。而“错误的流行颜色百分比”不保留用户的选择,因此它总是将所有流行颜色(蓝色、红色和白色)与用户所选颜色进行比较,得出的百分比可能会大于100%——所有百分比都是错误的,但如果用户无法评估其他百分比,那么超过100%的数字显然会被视为错误。
作为第二个例子,考虑以下的“高价销售”度量值,它返回净价大于500的产品的收入。因为对净价的筛选创建了一个值列表(所有大于500的现有价格),所以需要KEEPFILTERS:
销售表中的度量值(或指标)
Expensive Sales =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( Sales[Net Price] > 500 )
)
在以下报告中,KEEPFILTERS的重要性显而易见,因为用户仅筛选了净价低于1,000的产品。
使用KEEPFILTERS的“高价销售”度量值返回价格在500到1,000之间的产品的收入,这些收入必须小于或等于同一报告中的销售额。而“错误的高价销售”度量值没有使用KEEPFILTERS,它显示了所有价格大于500的产品,忽略了用户应用的净价筛选器(价格小于1,000)。因此,结果可能会大于同一报告中的销售额,就像高亮显示的单元格那样。
使用KEEPFILTERS来筛选两列或更多列
大多数情况下,对两列或更多列进行筛选会包含多个值,否则它可以通过对单个列使用单个筛选器来描述。因此,尽管在前面的情况下已经包含在内,但将基本规则具体化为包括多列会更容易推广,尽管这不是严格必要的。
第一个示例显示了一个“Sales New Year 2018”度量值,它返回2018年12月和2019年1月的收入:
销售表中的度量值(或指标)
Sales New Year 2018 =
CALCULATE (
[Sales Amount],
KEEPFILTERS (
( 'Date'[Year], 'Date'[Month] )
IN { ( 2018, "December" ), ( 2019, "January" ) }
)
)
KEEPFILTERS的存在保证了用户的选择得以保留。以下报告比较了相同度量的两个版本,一个使用了KEEPFILTERS(正确的版本),另一个没有使用(后者是“Wrong Sales New Year 2018”度量值)。
没有使用KEEPFILTERS的度量值总是返回这两个月的总和,而不考虑在行中筛选的年份。然而,可能有些情况下需要这种行为——那将是规则的一个例外情况。
另一个例子是一个筛选器,我们比较两列的乘积与一个固定值:
在“销售”表中的度量值(或指标)
Sales 32 =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( Sales[Quantity] * Sales[Net Price] = 32 )
)
你不应该被操作符所混淆:数量和净价的多种组合可能会返回32!实际上,如果用户将净价筛选到20.00以下,Tailspin Toys(其净价为32.00)的销售在“销售32”中会被忽略,而在没有使用KEEPFILTERS的“错误销售32”度量值中则会被包含进去。
一个更常见的例子是对两列或更多列进行数学运算,并使用小于或大于运算符进行比较。然而,那将是一个与前面描述的单列多个值类似的情况;我们想要展示一个使用“等于”运算符的特定情况,因为它不应该与单值筛选器混淆。只有当比较是针对单列时,“=”运算符的存在才不需要KEEPFILTERS!
结论
KEEPFILTERS不应用于筛选器的唯一情况是当谓词筛选单列中的单个值时。在其他所有情况下——两个或更多值,两个或更多列——你应该应用KEEPFILTERS,除非你能描述出该规则的特定例外情况。本文仅涵盖列筛选器;你应该始终筛选列,而不是表。如果你有表筛选器,你应该根据具体情况仔细考虑是否应该应用KEEPFILTERS。
长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~