Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sort slicer value based on the numeric Value

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 i...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lili6-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      MCornish
      Responsive Resident
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.