A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expre

kmkwongaa , Try like

 IF('Follow_Up_Item_Summary_Status (Overdue)'[Category]=="1", CALCULATE(DISTINCTCOUNT('Follow_Up_Item_Summary_Status (Overdue)'[Batch no]),FILTER(ALL('Follow_Up_Item_Summary_Status (Overdue)'),'Follow_Up_Item_Summary_Status (Overdue)'[timespent]>14 && 'Follow_Up_Item_Summary_Status (Overdue)'[timespent]<21)),
 if('Follow_Up_Item_Summary_Status (Overdue)'[Category]=="2", CALCULATE(DISTINCTCOUNT('Follow_Up_Item_Summary_Status (Overdue)'[Batch no]),FILTER(ALL('Follow_Up_Item_Summary_Status (Overdue)'),'Follow_Up_Item_Summary_Status (Overdue)'[timespent]>30 && 'Follow_Up_Item_Summary_Status (Overdue)'[timespent]<45)), IF('Follow_Up_Item_Summary_Status (Overdue)'[Category]=="3", CALCULATE(DISTINCTCOUNT('Follow_Up_Item_Summary_Status (Overdue)'[Batch no]),FILTER(ALL('Follow_Up_Item_Summary_Status (Overdue)'),'Follow_Up_Item_Summary_Status (Overdue)'[timespent]>90 && 'Follow_Up_Item_Summary_Status (Overdue)'[timespent]<135)),0)))

Hi kmkwongaa ,

 

The error is caused by using a TRUE/FALSE expression, something that evaluates to TRUE or FALSE, to filter the table in a way that CALCULATE or CALCULATETABLE doesn’t support.  So the error is saying you can’t use a Boolean comparison to filter your table except in very specific circumstances.

 

You may change your formulas like DAX below.

Cat1 =
SWITCH (
    'Follow_Up_Item_Summary_Status (Overdue)'[Category],
    "1", CALCULATE (
        DISTINCTCOUNT ( 'Follow_Up_Item_Summary_Status (Overdue)'[Batch no] ),
        'Follow_Up_Item_Summary_Status (Overdue)'[timespent] < 14
    ),
    "2", CALCULATE (
        DISTINCTCOUNT ( 'Follow_Up_Item_Summary_Status (Overdue)'[Batch no] ),
        'Follow_Up_Item_Summary_Status (Overdue)'[timespent] < 30
    ),
    "3", CALCULATE (
        DISTINCTCOUNT ( 'Follow_Up_Item_Summary_Status (Overdue)'[Batch no] ),
        'Follow_Up_Item_Summary_Status (Overdue)'[timespent] < 30
    ),
    0
)



Cat2 =
SWITCH (
    'Follow_Up_Item_Summary_Status (Overdue)'[Category],
    "1", CALCULATE (
        DISTINCTCOUNT ( 'Follow_Up_Item_Summary_Status (Overdue)'[Batch no] ),
        FILTER (
            ALL ( 'Follow_Up_Item_Summary_Status (Overdue)' ),
            'Follow_Up_Item_Summary_Status (Overdue)'[timespent] > 14
                && 'Follow_Up_Item_Summary_Status (Overdue)'[timespent] < 21
        )
    ),
    "2", CALCULATE (
        DISTINCTCOUNT ( 'Follow_Up_Item_Summary_Status (Overdue)'[Batch no] ),
        FILTER (
            ALL ( 'Follow_Up_Item_Summary_Status (Overdue)' ),
            'Follow_Up_Item_Summary_Status (Overdue)'[timespent] > 30
                && 'Follow_Up_Item_Summary_Status (Overdue)'[timespent] < 45
        )
    ),
    "3", CALCULATE (
        DISTINCTCOUNT ( 'Follow_Up_Item_Summary_Status (Overdue)'[Batch no] ),
        FILTER (
            ALL ( 'Follow_Up_Item_Summary_Status (Overdue)' ),
            'Follow_Up_Item_Summary_Status (Overdue)'[timespent] > 90
                && 'Follow_Up_Item_Summary_Status (Overdue)'[timespent] < 135
        )
    ),
    0
)

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.