cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Employee

@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

6 REPLIES 6
Employee

@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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.

Super User

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],"")) `

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors