cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sheap069
Helper III
Helper III

Sort Slicer Values by Number and Category

Hello,

I have data as follows

IDStatus
1Inactive
2Inactive
3Active
4Inactive
5Active
6Inactive
7Inactive
8Inactive
9Active
10Active

I want to make a slicer for the IDs, and I want all of the Inactive IDs to be listed first, then the Active IDs, while maintaining the numerical order.

The desired output should be:

1

2

4

6

7

8

3

5

9

10

 

Thank you

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

Hi @sheap069 ,

According to your description, here's my solution.

1.Create a calculated column.

Rank = RANKX('Table','Table'[Status],,DESC,Dense)

 Get this.

vkalyjmsft_0-1645597345480.png

2.Put the ID column in a slicer, first click the slicer visual, then click the ID column in the Fields pane.

vkalyjmsft_1-1645597457686.png

Then under the Column tools ribbon, select Sort by column>Rank.

vkalyjmsft_2-1645597643075.png

Get the expected result.

vkalyjmsft_3-1645597718636.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

 

View solution in original post

6 REPLIES 6
v-kalyj-msft
Community Support
Community Support

Hi @sheap069 ,

According to your description, here's my solution.

1.Create a calculated column.

Rank = RANKX('Table','Table'[Status],,DESC,Dense)

 Get this.

vkalyjmsft_0-1645597345480.png

2.Put the ID column in a slicer, first click the slicer visual, then click the ID column in the Fields pane.

vkalyjmsft_1-1645597457686.png

Then under the Column tools ribbon, select Sort by column>Rank.

vkalyjmsft_2-1645597643075.png

Get the expected result.

vkalyjmsft_3-1645597718636.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

 

parry2k
Super User
Super User

@sheap069 try this

 

Column from Slicer  =

Table[Status] & "-" & FORMAT(Table[Id], "0000" )

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@sheap069 add new column

 

Column from Slicer  =

Table[Status] & "-" & Table[Id]

 

and use this column in the slicer, and sort slicer desc

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hello @parry2k ,

I tried this and I have data that goes beyond 100. I'm getting this output:

Inactive - 1

Inactive - 10

Inactive - 11

...

How can this be fixed?

Thanks

parry2k
Super User
Super User

@sheap069 you need to add a new column that concatenates Status and ID and then use this new column in the slicer, and in the slicer, use the sort option.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hello,

Sorry I don't quite understand what you mean, can you be more specific? If I do this I still have the order as 

1

2

3

4

5

6

7

8

9

10

Instead of the desired output above. I need the inactives listed first in numerical order, then the actives in numerical order. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors