Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ruanolin
Regular Visitor

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

Hi @ruanolin 

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

try 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.