topic Re: Dax measure error in a table with 100 rows but not in one with 50 rows in DAX Commands and Tips

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

ruanolin — Tue, 09 Jul 2024 12:39:36 GMT

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.

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

some_bih — Tue, 09 Jul 2024 12:57:29 GMT

Hi ruanolin

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

try

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

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

ruanolin — Tue, 09 Jul 2024 13:02:52 GMT

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

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

Anonymous — Thu, 25 Jul 2024 08:37:43 GMT

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.