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

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.