Sort slicer value based on the numeric Value

[Anonymous]

Hello,

I am looking to sort my Slicer value according to the numeric field ( Not ascending or descending).

For example below country is sorted based on alphabets in a table but the requirement is in the slicer country should be sorted based on population.

Canada should come first 

Then Australia

Then India

and Then US

f

How to achieve this?

Thanks in advance.

Zeenat

[v-lili6-msft]

hi, @Anonymous 

For your case, you could use this way as below:

Step1:

Add a dim country table that contains the order column

Step2:

Then create a relationship with fact table by Country column

Step3:

Then use Sort by column for dim table:

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

https://radacad.com/sort-by-column-in-power-bi

Step4:

When create the report, use country column from Dim country table as a slicer and Country from fact table in other visual.

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Did you hard coded the values in DIm_Table where we have all countries? I cant take hard coding as in actual data is in trillions.

Also, Sort by Column should have a single value for each country but my actual data look like this

[v-lili6-msft]

hi, @Anonymous 

For the dim country table, you could Duplicate the fact table and then Group By it, and then add a conditional column for sort column in edit queries.

https://support.office.com/en-us/article/group-rows-in-a-table-power-query-e1b9e916-6fcc-40bf-a6e8-ef928240adf1

https://support.office.com/en-us/article/add-a-conditional-column-power-query-f2422ed1-f565-4e64-ba3f-4f8d4616254e

Either way, you need to define the rules in advance.

Regards,

Lin

[Anonymous]

Hello Lin,

I have created a column

1Rank = RANKX(FILTER('Time','Time'[Region M3]=EARLIER('Time'[Region M3])),'Time'[Actual],,DESC,Dense)

but when I am trying to sort my column Region M3 based on 1Rank, I am getting this error

Can you help? I am trying to sort the values in Slicer for Region M3 and Market M15 though they are not getting used in any visuals.

[MCornish]

You would need to have a 1to1 mapping from Region M3 to Rank1

[Anonymous]

Is there any way to achieve it? as per my knowledge data is vast and there are multiple region_M3 and market_M15 and aggregation is an issue.

[MCornish]

It will depend on your table structure, but you could do this:

Country	Population	Order
Australia	4797400	2
Canada	7964900	1
India	4217600	3
US	3839600	4

Then use the Sort By Column on the modeling tab

This will sort the countrys by the sort order ascending

[Anonymous]

This will work if I have to sort in the table, but I want to values to be sorted in the Slicer, so it doesn't solve the purpose OR I am unable to understand your solution, could you elaborate.

[MCornish]

It works for slicer ordering to. Below is the one im currently working on. You can see these are sorted descending, not ascending like default