Question

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.

Formula 100rows vs 50rows

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.

 3  58  3
1 Jan 1970

Solution

 2

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.

2024-07-10
davidebacci

Solution

 1

This is a strange one and I'd be interested to hear the thoughts of others. I would also raise this with Microsoft as the behaviour is strange. The cut off is 65 rows BTW (64 works fine and 65 throws the issue). Even using a slicer makes no difference.

Could this be to do with eager v strict evaluation in the SUMX()? I have no idea and would be interested in other theories (further reading: https://www.sqlbi.com/articles/understanding-eager-vs-strict-evaluation-in-dax/).

For the record, this forces your measure to work:

Indis_2 = 
VAR x =     
    FILTER(
        'Table100rows',
        'Table100rows'[cfId] == 10029
    )
VAR y = ADDCOLUMNS(x, "@test", 1)
RETURN

SUMX(
    y,
    VALUE('Table100rows'[fieldValue])
)

Simply forcing the evaluation of the filter by adding a dummy column means the measure now works.

2024-07-09
davidebacci