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

[kmkwongaa]

I have the following column added. 

Cat1 = IF('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),IF('Follow_Up_Item_Summary_Status (Overdue)'[Category]=="2", CALCULATE(DISTINCTCOUNT('Follow_Up_Item_Summary_Status (Overdue)'[Batch no]),'Follow_Up_Item_Summary_Status (Overdue)'[timespent]<30), IF('Follow_Up_Item_Summary_Status (Overdue)'[Category]=="3", CALCULATE(DISTINCTCOUNT('Follow_Up_Item_Summary_Status (Overdue)'[Batch no]),'Follow_Up_Item_Summary_Status (Overdue)'[timespent]<30),0)))

It works and when I want to add another column

Cat2 = 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)))))

The following error occur: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Is there any way to solve this problem?

[amitchandak · Accepted solution]

@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)))

[v-xicai · Accepted solution]

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.

[amitchandak · Accepted solution]

@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)))