DAX使用变量封装KEEPFILTERS筛选规则触发语法错误求助

错误原因 #

KEEPFILTERS语法要求必须直接作为CALCULATE/CALCULATETABLE的顶层筛选参数，你当前将包含KEEPFILTERS的表达式封装到变量后再传入CALCULATE，不符合语法规则，因此触发报错。

优化修正方案 #

你可以将公共筛选条件提到外层CALCULATE中统一声明，既保留筛选规则复用的效率优化效果，也符合DAX语法要求，修正后的代码如下：

GB 1-2 Login Counts =  
RETURN
CALCULATE(
    SWITCH(
        MAX(GBvsProg[Filter Order]),
        1, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PROGRESS_GO_PASSRATECAT] = "Failing 100%")),
        2, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PASSING_GO_PASSRATECAT] = "Failing 100%")),
        3, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PROGRESS_GO_PASSRATECAT] = "Passing 0-49%")),
        4, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PASSING_GO_PASSRATECAT] = "Passing 0-49%")),
        5, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PROGRESS_GO_PASSRATECAT] = "Passing 50-74%")),
        6, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PASSING_GO_PASSRATECAT] = "Passing 50-99%")),
        7, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PROGRESS_GO_PASSRATECAT] = "Passing 100%")),
        8, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(D_STUDENTS[PASSING_GO_PASSRATECAT] = "Passing 100%"))
    ) + 0,
    -- 公共筛选条件统一在外层声明，复用且符合语法要求
    KEEPFILTERS(D_STUDENTS[GO_LOGIN_CAT] <> ""),
    KEEPFILTERS(D_STUDENTS[GO_LOGIN_CAT] = "1-2")
)

如果你希望保留变量声明的写法，也可以将纯布尔筛选逻辑存入变量，在CALCULATE顶层参数中用KEEPFILTERS包裹变量即可，示例：

GB 1-2 Login Counts =  
var ds_glc_not_empty = D_STUDENTS[GO_LOGIN_CAT] <> ""
var ds_glc_eq_12 = D_STUDENTS[GO_LOGIN_CAT] = "1-2"

RETURN
SWITCH(MAX(GBvsProg[Filter Order]),
    1, CALCULATE(COUNT(D_STUDENTS[GO_LOGIN_CAT]), KEEPFILTERS(ds_glc_not_empty), KEEPFILTERS(ds_glc_eq_12), KEEPFILTERS(D_STUDENTS[PROGRESS_GO_PASSRATECAT] = "Failing 100%")),
    -- 其余分支按照相同规则修改即可
) + 0

内容的提问来源于stack exchange，提问作者Chicken Sandwich No Pickles