DAX:使用 SELECTEDVALUE 而不是 VALUESDAX: Use SELECTEDVALUE instead of VALUES

作为数据建模人员,有时可能需要编写 DAX 表达式来测试列是否按特定值进行筛选。As a data modeler, sometimes you might need to write a DAX expression that tests whether a column is filtered by a specific value.

在较早版本的 DAX 中,通过使用涉及三个 DAX 函数的模式可以安全地实现此要求。In earlier versions of DAX, this requirement was safely achieved by using a pattern involving three DAX functions. 函数为 IFHASONEVALUEVALUESThe functions are IF, HASONEVALUE and VALUES. 下面的度量值定义显示一个示例。The following measure definition presents an example. 它计算销售税金额,但仅限于面向澳大利亚客户的销售。It calculates the sales tax amount, but only for sales made to Australian customers.

Australian Sales Tax =
IF(
    HASONEVALUE(Customer[Country-Region]),
    IF(
        VALUES(Customer[Country-Region]) = "Australia",
        [Sales] * 0.10
    )
)

在此示例中,仅当单个值筛选 Country-Region 列时,HASONEVALUE 函数才返回 TRUE 。In the example, the HASONEVALUE function returns TRUE only when a single value filters the Country-Region column. 如果为 TRUE,VALUES 函数将与文本“澳大利亚”进行比较。When it's TRUE, the VALUES function is compared to the literal text "Australia". VALUES 函数返回 TRUE 时,“Sales”度量值将乘以 0.10(表示 10%) 。When the VALUES function returns TRUE, the Sales measure is multiplied by 0.10 (representing 10%). 如果 HASONEVALUE 函数返回 FALSE(因为有多个值对列进行筛选),则第一个 IF 函数将返回 BLANK。If the HASONEVALUE function returns FALSE—because more than one value filters the column—the first IF function returns BLANK.

使用 HASONEVALUE 是一项防御性技术。The use of the HASONEVALUE is a defensive technique. 这是必需的,因为可能有多个值对 Country-Region 列进行筛选 。It's required because it's possible that multiple values filter the Country-Region column. 在这种情况下,VALUES 函数将返回一个包含多行的表。In this case, the VALUES function returns a table of multiple rows. 将包含多行的表与标量值进行比较会导致错误。Comparing a table of multiple rows to a scalar value results in an error.

建议Recommendation

建议使用 SELECTEDVALUE 函数。We recommend that you use the SELECTEDVALUE function. 该函数可得出与本文描述的模式相同的结果,但更高效、更顺畅。It achieves the same outcome as the pattern described in this article, yet more efficiently and elegantly.

借助 SELECTEDVALUE 函数现可对示例度量值定义进行重写。Using the SELECTEDVALUE function, the example measure definition is now rewritten.

Australian Sales Tax =
IF(
    SELECTEDVALUE(Customer[Country-Region]) = "Australia",
    [Sales] * 0.10
)

提示

可以将替代结果值传递给 SELECTEDVALUE 函数 。It's possible to pass an alternate result value into the SELECTEDVALUE function. 如果没有筛选器(或多个筛选器)应用于该列,则返回备用结果值。The alternate result value is returned when either no filters—or multiple filters—are applied to the column.

后续步骤Next steps

有关本文的详细信息,请参阅以下资源:For more information about this article, check out the following resources: