Number Formatting - need to represent "less than" (recognized as Text)

[Anonymous]

I am looking for a suggestion about the formatting of a number/currency column in my data set.

I am using a slicer to show tiered pricing, but I believe the special characer is not recoginizing the column as a Number.  I believe my problem is with the representation <$10,000.

Any suggestion how I can represent "less than" $10,000 and still have the column recognized as a number?

Thanks.

[v-caliao-msft · Accepted solution]

@Anonymous,

Do you have the value <$10,000 in your dataset? I have test it on my local environment, the expression below is for you reference.
Column = IF(ISERROR(VALUE(Table1[Value])),Table1[Value],IF(VALUE(Table1[Value])<20000,"10000~20000",">20000"))

If this is not what you want, please provide us some sample data and elaborate your expected result, so that we can make further analysis.

Regards,

Charlie Liao

[Greg_Deckler]

Do a replace of < $10,000 with $9,999? Then, you could always creat a measure that essentially translated it back if necessary.

[Anonymous]

@Greg_Deckler

Can you add some more detail about how to write the statement in the table?

As always, I really appreciate and value your help.

[Greg_Deckler]

I'm not sure what statement in the table means. If you are in the Query Editor, click on the column, right-click and choose "Replace values..."

In the data model, actually, a calculated column would probably work better:

Column = IF([MyColumn]<10000,"< $10,000",CONCATENATE([Column],"")) 

[Anonymous]

This is the result:

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

[Greg_Deckler]

That's because your column is Text. Just cast it into a number in your Query.