Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
Hi @ruanolin
I could not access your file, but you should check part ‘Table50rows’[cfId] = 10029 ),
try
‘Table50rows’[cfId] = "10029"),
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |