Cannot convert value " of type Text to type Number

[Anonymous]

Hi everyone!

When I tried to convert text into number, this error showed up: Cannot convert value " of type Text to type Number. It's probably because there are some empty strings. So I tried to convert them into 0. But again, the same error showed up and I don't know why. Could you help me figure out with this? Thanks in advance.

Here's my dax function:

Count# = VALUE(COALESCE([Count], 0))

[Count] is the original attribute in text format.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check the following results:

Measure 3 = VAR _t =ADDCOLUMNS('Table',"Total",SUMX(FILTER(ALL('Table'),[planning cycle]=EARLIER([planning cycle])),[Measure 2]))
RETURN MAXX(_t,[Total])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Anonymous]

Hi @Anonymous ,

I made simple samples and you can check the results below:

Measure = IF(VALUE(MAX('Table'[Value]))=BLANK(),0,VALUE((MAX('Table'[Value]))))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Anonymous]

Hi Scott,

Thanks.

I tried your way but still failed. Is it because my raw data has something like this? two types of blank?

[Anonymous]

Hi @Anonymous ,

Try replacing blank() with ""

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Anonymous]

it says can't compare type of number with type of text.

[Anonymous]

Hi @Anonymous ,

Please try:

Measure = IF(MAX('Table'[Value])="",0,VALUE(MAX('Table'[Value])))

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

[Anonymous]

Thanks it works. But how can I sum up these number by different dimension?

[Anonymous]

Hi @Anonymous ,

Depends on your individual needs, for example, if you need to take the value of column A as 1 for summing, you can use the following expression:

Measure = CALCULATE([measure],FILTER('Table',[A]=1))

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Anonymous]

what if I want to show the total count of each planning cycle?

here's my raw data:

planning cycle	product name	measure
19	A	0
19	B	1
20	C	1
20	D	1
20	E	0

I want to get summary table like this:

planning cycle	measure
19	1
20	2

[Ahmedx]

Measure = Count(Yourtable[Count])

or share the file to help you

[Anonymous]

Thanks for your help. I tried but failed again. and I'm sorry I'm afraid I can't share the file because of the confidentiality.

[Ahmedx]

pls try this

or this

IFERROR(VALUE([Count]),0)

[Anonymous]

Thanks. But it turned to be that Count# is different with Count, do you know why is that?

[Ahmedx]

show how you wrote the measure

[Anonymous]

[Ahmedx]

you wrote a column and you need to write your measure [Count]

write like this

Measure = IFERROR(CONVERT([Count],INTEGER),0)

[Anonymous]

But [Count] is actually a column. How can I write a measure based on a column?