- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Switch Table Custom Format failing to sort data correctly
This is the code I am using, I have tried with max instead of values and without stating that it can't be 0.
Also have used the "Currency" option, however this put all the negative numbers at the top of the list as they where in ().
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @StuBee
It's a bad idea, indeed, to format a numeric measure with FORMAT and return a string (unless you need to incorporate such a value into some kind of text but even then you create a version of the measure). Such "values" will always be sorted as strings, not numbers. That's one thing. A measure should always return the raw value (always of the same type). Mixing floats with integers is a NO-NO. Measures should be consistent. Otherwise you'll be facing issues. And, as we see, you do. Formatting a measure should be done in the UI option in the ribbon. So, if you have a measure that wants to return different types of info, you're most likely doing it wrong. You should rethink the design.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @StuBee
It's a bad idea, indeed, to format a numeric measure with FORMAT and return a string (unless you need to incorporate such a value into some kind of text but even then you create a version of the measure). Such "values" will always be sorted as strings, not numbers. That's one thing. A measure should always return the raw value (always of the same type). Mixing floats with integers is a NO-NO. Measures should be consistent. Otherwise you'll be facing issues. And, as we see, you do. Formatting a measure should be done in the UI option in the ribbon. So, if you have a measure that wants to return different types of info, you're most likely doing it wrong. You should rethink the design.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ahh ok, so is there no good way to use a switch function to return a whole number and switch to currency?
That's fine, they can see it is revenue from the box they click can't they.
Thanks, you confirmed what I was hoping wasn't the case.

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |