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

[ruanolin]

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.

[v-kongfanf-msft · Accepted solution]

Hi @ruanolin ,

The error you're encountering in your DAX measure with the 100-row table is due to the presence of a text value that cannot be converted to a number. Specifically, the value 'N0' in the fieldValue column is causing the issue when trying to apply the VALUE function.
To fix this, you need to ensure that all values in the fieldValue column can be successfully converted to numbers. One approach is to filter out non-numeric values before performing the calculation. You can use the ISNUMBER function to check if a value can be converted to a number.

Try to modify your formula like below:

Indis = 
SUMX(
    FILTER(
        'Table100rows',
        'Table100rows'[cfId] = 10029 && ISNUMBER(VALUE('Table100rows'[fieldValue]))
    ),
    VALUE('Table100rows'[fieldValue])
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @ruanolin 

I could not access your file, but you should check part ‘Table50rows’[cfId] = 10029 ),

try 

‘Table50rows’[cfId] = "10029"),

[ruanolin]

cfId field is of type Whole number, so it should work well without quotes. Otherwise it works well in the 50 rows table.