Sort Slicer Values by Number and Category

[sheap069]

Hello,

I have data as follows

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

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

[v-yanjiang-msft · Accepted solution]

Hi @sheap069 ,

According to your description, here's my solution.

1.Create a calculated column.

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

 Get this.

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

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

Get the expected result.

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]

@sheap069 try this

Column from Slicer  =

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

✨ Follow us on LinkedIn and  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.

[parry2k]

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

[sheap069]

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]

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

[sheap069]

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.