An unexpected exception occued. An unexpected error occurred (file '', line, function '').

[DickLam660128]

Exhibit 1, I input the DAX formula for a newly created column which every rows has the same value and the formula is as follows:

Tmp = SUMX(FILTER(Sheet1, Sheet1[InvDate] < DATE(2024, 12, 31)), Sheet1[InvAmt]) ---- picture 1

Exhibit 2, when I change the formula to the following

Tmp = SUMX(FILTER(Sheet1, Sheet1[InvDate] > DATE(2024, 12, 31)), Sheet1[InvAmt]) ---- Picture 2 appear with the error message.

It is odd enough! I never come across with it. Can anyone help?

Exhibit 1

Exhibit 2

[v-hashadapu]

Hi @DickLam660128 , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

[v-hashadapu]

Hi @DickLam660128 ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @DickLam660128 ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[rohit1991]

Hi @DickLam660128 ,

What you're encountering is a known quirk in Power BI's calculated column behavior when using functions like SUMX over the same table that the column is being created in. Essentially, Power BI is attempting to evaluate the expression row by row, but when your FILTER condition doesn't return any matching rows—or introduces a context that Power BI struggles to evaluate recursively—it can throw an unexpected error instead of simply returning a blank or zero.

To sidestep this issue, try using CALCULATE instead of SUMX, as it handles filter context more reliably in calculated columns:

Tmp = CALCULATE(SUM(Sheet1[InvAmt]), FILTER(Sheet1, Sheet1[InvDate] > DATE(2024, 12, 31)))

This approach avoids deep row-by-row evaluation and instead shifts the logic into a filter context that the engine processes more efficiently. If the goal is just to populate the same aggregated value across every row, this should work smoothly. However, if the issue persists or if the formula becomes more complex, it’s often better to perform this logic in a measure rather than a calculated column, as measures don’t run into the same row-context limitations and are optimized for these types of calculations.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[DickLam660128]

thanks for your reply.  But unfortunately, it does not work.  The issue with ">" still exist

[v-hashadapu · Accepted solution]

Hi @DickLam660128 , Thank you for reaching out to the Microsoft Community Forum.

Please try below:

Total_InvAmt_AfterCutoff =

SUMX(

    Sheet1,

    IF (

        NOT ISBLANK(Sheet1[InvDate]) &&

        Sheet1[InvDate] >= DATE(2025, 1, 1),

        Sheet1[InvAmt],

        0

    )

)

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[GrowthNatives]

Hi @DickLam660128 , i understand you are facing an error with creation of calculated columns in power bi. The logic I believe behind this error is ellaborated below.

🎓 Why Did < Work but > Didn’t?

Here’s your original column logic:

Tmp = SUMX(FILTER(Sheet1, Sheet1[InvDate] < DATE(2024, 12, 31)), Sheet1[InvAmt])

This ran without an error, and returned a constant value in every row.

Then you changed it to:

Tmp = SUMX(FILTER(Sheet1, Sheet1[InvDate] > DATE(2024, 12, 31)), Sheet1[InvAmt])

Suddenly, you got an unexpected exception.

🧠 What’s Really Going On?

✅ When You Use < DATE(...):

If at least one row in the table matches the filter (InvDate < 2024-12-31), the FILTER() function returns a valid table → SUMX processes it → result is a constant value → valid for column creation.

No circular logic occurs because the current row is usually included, and Power BI doesn't choke on it.

❌ When You Use > DATE(...):

If no rows in the table match the condition (InvDate > 2024-12-31), then:

• FILTER() returns an empty table.

• SUMX() evaluates nothing (result is zero).

• But since this is a calculated column inside the same table, Power BI has to resolve whether current row is in the filter or not, and this causes ambiguous context — especially when the result is an empty filter.

This triggers internal recursion/conflict, which results in an unexpected exception.

⚠️ Power BI sometimes tolerates risky logic if the data returns something, but throws an error when the logic results in zero rows, especially inside calculated columns.

✅ Fix : Always Use a Measure for Aggregation

If you're aggregating across the table, even conditionally, use a measure instead of a column:

Total_After_Cutoff =
CALCULATE(
    SUM(Sheet1[InvAmt]),
    FILTER(ALL(Sheet1), Sheet1[InvDate] > DATE(2024,12,31))
)

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]

[DickLam660128]

Originally, I am using measure; the examples I show in table is to list out the data.  Actually, when I am using a measure, it does not work for ">".  No use

[DataNinja777]

Hi @DickLam660128 ,

The issue you're facing is likely due to Power BI struggling with row context and recursive evaluation when you're using SUMX on the same table you're creating a calculated column in. Although your DAX formula is syntactically valid, Power BI sometimes throws an exception when the engine can't resolve dependencies cleanly, especially with filters applied on the same table. To work around this, rewrite the formula using CALCULATE instead of SUMX, which handles context in a way that avoids these recursive evaluation errors.

Try replacing your formula with the following:

Tmp = CALCULATE(SUM(Sheet1[InvAmt]), FILTER(Sheet1, Sheet1[InvDate] > DATE(2024, 12, 31)))

This change should avoid the crash and return the same constant value in each row. If this still triggers the error, consider creating the calculation as a measure instead of a calculated column, since measures evaluate in filter context and are less prone to this kind of failure.

Best regards,