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 ,

This is a known edge case in Power BI when using SUMX in a calculated column that references the same table it's being created in. The issue usually pops up when your filter condition (like > DATE(...)) returns no rows during row context evaluation. Power BI struggles here because it needs to evaluate each row recursively, and an empty filter causes ambiguity in that recursive context which triggers the "unexpected error".

Why your first version worked:Tmp = SUMX(FILTER(Sheet1, Sheet1[InvDate] < DATE(2024, 12, 31)), Sheet1[InvAmt])

This works because there's almost always at least one row before the given date, so the filter isn't empty, and the engine proceeds smoothly.

Why the second version fails:Tmp = SUMX(FILTER(Sheet1, Sheet1[InvDate] > DATE(2024, 12, 31)), Sheet1[InvAmt])

If no rows meet this condition during evaluation, FILTER() returns an empty table. Then SUMX inside the calculated column tries to iterate over nothing, and Power BI can't resolve that cleanly due to the self-referencing nature of the row context.

Recommended Fix (using a measure, not a column): Instead of creating a calculated column, refactor the logic into a measure like this: 

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

This removes the row context (ALL(Sheet1)), avoids recursion, and calculates correctly even if no rows match the filter, returning 0 without error.

[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,