DAX中CALCULATE函数报错求助：SUM不能用作表筛选表达式

Hey there! Let's break down what's going wrong with your vMotor_Paid measure and get it working correctly.

The Root Cause of the Error #

Your mistake lies in the structure of the CALCULATE function. Remember, CALCULATE follows this core syntax:

CALCULATE(<expression_to_calculate>, <filter_condition1>, <filter_condition2>, ...)

You placed the SUM function inside the filter arguments section instead of using it as the first parameter (the value you want to compute). That's why you're getting the error about using SUM in a filter expression—it doesn't belong there.

Corrected DAX Expression #

Here's the fixed version, with a few reliability improvements:

vMotor_Paid = 
VAR VpeDate = DATE(2017, 12, 23) -- Use DATE() instead of a string to avoid regional format conflicts
RETURN
CALCULATE (
    SUM ( Paid_excel[PAID_AMOUNT] ), -- This is the value we want to aggregate (first parameter)
    Paid_excel[Flag] = "Paid", -- Filter: Only include marked "Paid" records
    Premium_Excel[LOB] = "MOTOR", -- Filter: Only MOTOR line of business
    Paid_excel[PAID_DATE] = VpeDate -- Filter: Match your fixed target date
)

Key Changes Explained: #

• Reordered SUM: Moved SUM(Paid_excel[PAID_AMOUNT]) to the first position in CALCULATE—this tells DAX exactly what value to compute after applying all your filters.
• Reliable Date Handling: Replaced the string date "23-12-2017" with DATE(2017,12,23). String dates can break if your Power BI/DAX environment uses a different regional date format; the DATE() function guarantees the value is interpreted correctly.
• Simplified Date Match: Removed the curly braces {} around VpeDate. Since this is a single fixed date, direct equality works perfectly—no need to wrap it in a table structure.

Quick Check for Table Relationships #

Make sure Paid_excel and Premium_Excel have an active relationship in your data model. This is required for the Premium_Excel[LOB] = "MOTOR" filter to correctly apply to the Paid_excel table. If there's no relationship, adjust that filter line to RELATED(Premium_Excel[LOB]) = "MOTOR" instead.

内容的提问来源于stack exchange，提问作者SUPER_USER