cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ABNHS84
Frequent Visitor

Does anyone know how to restrict sorting to only valid numbers?

My organisation releases data that has to include small number suppression, which is an asterisk.

 

This is a problem across all data analysis software I've come across, but wondered if there was a trick within Power BI to allow sorting largest to smallest (as opposed to alphabetically).

 

It works fine when I set all asterisk values to blank(), however we are duty bound to show the asterisks to indicate that a number has been suppressed. As soon as I allow them in, and sort by that column the numbers are organised alphabetically:

1

10

100

2

20

200

etc. Which is useless.

 

Is it possible to tell the table visual to treat string values as a certain number, then treat the whole column as numbers when sorting?

 

Any help much appreciated!

1 ACCEPTED SOLUTION

Thanks Dale, 

Your advice wasn't quite what I was looking for, but it did lead me to the correct solution.

 

I created a new column in my table with code

 

My table = selectcolumns(
<My other column codes>,
"ColumnSort", If(OriginalColumn = "*", BLANK(), Value(ThisTable[OriginalColumn])),
)

 

This allowed me to sort the 'Text' colunm as if it were numerical column.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Microsoft
Microsoft

Hi @ABNHS84,

 

Could you please mark the proper answers as solutions?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Dale, 

Your advice wasn't quite what I was looking for, but it did lead me to the correct solution.

 

I created a new column in my table with code

 

My table = selectcolumns(
<My other column codes>,
"ColumnSort", If(OriginalColumn = "*", BLANK(), Value(ThisTable[OriginalColumn])),
)

 

This allowed me to sort the 'Text' colunm as if it were numerical column.

v-jiascu-msft
Microsoft
Microsoft

Hi @ABNHS84,

 

Power BI has a trick indeed. Please refer to the snapshot below. You just need to add a numeric column to indicate the orders.

Does-anyone-know-how-to-restrict-sorting-to-only-valid-numbers\

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors