[SOLVED] Dax measure error in a table with 100 rows but not in one with 50 rows

Dax measure error in a table with 100 rows but not in one with 50 rows

I have the following PowerBI file with 2 tables.

One of the tables contains 50 rows, and the other contains 100 (both tables have the same values; only the number of repeated rows changes).

I also have the following formula:

Indis = SUMX(
             FILTER( ‘Table50rows’,
                     ‘Table50rows’[cfId] = 10029 ),
             VALUE(‘Table50rows’[fieldValue]) 
             )

The problem is that the formula works correctly with the table of 50 rows, but it gives me the following error with the table of 100 rows:

MdxScript(Model) (17,5) Calculation error in measure: Cannot convert value 'N0' of type Text to type Number.

Solution

I have spoken to Jeffrey Wang (the father of DAX) and he has provided the definitive answer. Quoted below with his permission:

This is due to different execution strategies of the Vertipaq Engine that doesn't guarantee that VALUE() is called after the where clause. To guarantee VALUE() is called only when safe, use the SUMX(..., IF()) pattern: SUMX('Table100rows', IF('Table100rows'[cfId] = 10029, VALUE('Table100rows'[fieldValue])))

This applies to all aggregation expressions pushed to the Vertipaq Engine. Aggregation expressions should not assume that the where clause is applied first; instead, they should ensure their own safety during execution. I am not implying that aggregation expressions will be evaluated during execution while ignoring the where clause, which is clearly a performance concern. It's just that the expression may be evaluated during the Vertipaq Engine's preparation phase against some values without the where clause being applied.