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

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

Reply
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
v-caliao-msft
Employee
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"))
Capture.PNG

 

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

View solution in original post

6 REPLIES 6
v-caliao-msft
Employee
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"))
Capture.PNG

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.