Has Anyone Found Reliable DAX Patterns for Converting Text to Numbers Without Breaking Query Folding

[nigama]

This morning, a DAX measure that had been working flawlessly for ages suddenly threw a query folding error:

"OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression."

I was using this measure to sum values from a Text column in Dataverse (which I can’t change due to schema dependencies):

ExpectedTotalMeasure = SUMX(
    FILTER(TableCounts, TableCounts[Category]="A"), 
    VALUE(TableCounts[Expected Counts])
)

No changes were made to the data source, so what happened?

The source is Dataverse, and I cannot change the column data type due to dependencies. Copilot suggested restructuring it like this, which worked:

ExpectedTotalMeasure =
SUMX(
    SELECTCOLUMNS(
        FILTER(TableCounts, TableCounts[Category] = "A"),
        "ExpectedNumeric", VALUE(TableCounts[Expected Counts])
    ),
    [ExpectedNumeric]
)

It resolved the issue, but I’m wondering: is there a more resilient way to handle this going forward?

My constraints:

• I must use Dataverse and cannot change source data types

• My reports need to display real-time data. I use DirectQuery so that I can just refresh the report page or visuals to pull the latest values instantly. Since Import mode or calculated columns only update when the semantic model is refreshed, they don’t suit this use case.

• Because of this, everything has to happen inside measures, and I refresh the semantic model frequently (about 8 times a day), while relying on report-level refresh to keep the data live.
  
  

Has anyone found bulletproof DAX patterns or other approaches to avoid query folding errors like this? Open to creative solutions, even obscure ones!

[v-sshirivolu · Accepted solution]

Hi @nigama ,
Thank you for reaching out to Microsoft Fabric Community Forum.

Using VALUE() directly within SUMX can disrupt query folding, particularly in DirectQuery mode when dealing with text columns. Your approach with SELECTCOLUMNS is effective. For added safety, especially if there could be non-numeric strings like notanumber, consider this slightly safer pattern:

ExpectedTotalMeasure =
SUMX(
SELECTCOLUMNS(
FILTER(TableCounts, TableCounts[Category] = "A"),
"ExpectedNumeric", IFERROR(VALUE(TableCounts[Expected Counts]), BLANK())
),
[ExpectedNumeric]
)

Using VALUE() within SELECTCOLUMNS ensures that query folding is preserved. Including IFERROR(..., BLANK()) helps prevent runtime errors from invalid strings. This approach keeps all logic within the measure, so there’s no need to modify the schema or add calculated columns.

I tested this in a sample table using Enter Data with values such as "100", "150", and "notanumber" it only summed the valid numeric strings.

I hope this is helpful. If this resolves your issue, you might mark this as a helpful reply so others can benefit as well.

Regards,
Sreeteja.

[nigama]

Thank you for the detailed information. That explains a lot and answered my question!

[Thomaslleblanc]

You would need to create a new column in PowerQUery to have a numeric column of the text column. Then use that column in the DAX measure, not the text column