Adjusting Columns/Rows Sort Order

[taylororiger]

I have these "Years as EL" in those groups.  Is there a way to display those in the correct order? (5-9 should be after 0-4, for example).  I have this issue with a few tables where the sorting needs to be rearranged.

[PijushRoy]

Hi @taylororiger 

Create a Calculated Column with below DAX
Sort Col= VAR _yearsasel = SELECTEDVALUE(Table'Years as EL')
SWITCH(
TRUE(),
_yearsasel = "0-4",1,
_yearsasel = "5-9",2,
_yearsasel = "10-14",2,
so on

Click the column YEARS AS EL and select SORT BY from ribbon and select new calculated column.
In the visual, 3 dot, Sort by YEARS AS EL and acceding order

If you have any question, please let me know.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

Other option you can follow - https://community.powerbi.com/t5/Desktop/quot-Sort-by-column-quot-from-a-related-table/m-p/89265#M37...

[sevenhills]

To get the custom order in the desired way:

a) You need to have a dimension table and have a column to denote the sort order.

    Say "Years as EL - Sort Order" , and you can hide this column for report view

    Select the column "Years as EL" and then assign the "Sort by Column"
    "Years as EL"          "Years as EL - Sort Order"
    0-4                             1
    5-9                             2

   ... 

Rough idea, how it will look after the above steps:
 

b) refresh the chart, if it is not refreshed already

[taylororiger]

How do I create a dimension table?

[sevenhills]

In the home tab, create a table using 

My bad, Dimension table is a concept and you dont see it on any menu. Lookup or dimension table are data concepts. 

[taylororiger]

Thank you!  Would this work similarly for columns that need to be re-sorted?

[sevenhills]

Concept is same. You need additonal column to dictate the sort order for your requirements.

I like and do the lookup / dimension table concept.

For every custom display sort order type that we need, create a additional (separate) column to dictate the desired sort order.

Steps, more or less:

(A) if there is no separate table, add a new table without duplicates

    Say, in your case "Years as EL - Sort Order".

(B) A column to dictate the desired sort order. If no column, create new column.

      Say, in your case we added "Years as EL - Sort Order"

      Note: If there is another column that dictates the desired sort order, you can use that too.

           Let us take Calendar/Date table example,

           You have Month column: Jan, Feb, ...

           You have "Month Number" column: 1, 2, ... 

           Here you don't need new column, you can use Month Number column to dictate the sort order column for Jan, Feb.

(C) Set the sort order using power bi menu

(D) Hide the desired sort order column as it does not make sense for reporting visuals. 

Same thing you can achieve with DAX column also.

Build a dax colum like @PijushRoy  mentioned

[taylororiger]

The columns I am trying to re-order are the years, I want them shown the opposite way (starting with 2021 farthest left).  Would you be able to show how that would be done?

[Anonymous · Accepted solution]

Hi @taylororiger,

According to your description, it sounds like you want to achieve 'custom sort' effect. If that is the case, you can refer to the following link to create a custom sort table with sort order and set the 'sort by column' property.
Then you can link it to the raw table field and use new table fields on the matrix to replace the old one to apply for new sorting order.

Custom Sorting in Power BI 
Regards,

Xiaoxin Sheng

[taylororiger]

I belueve your instructions are for re-sorting rows.  I was able to fix that issue, I am now trying re-sort the columns with my own custom sort order.  Is there a way to do this?  (I want the years displayed most recent to oldest from left to right).

[taylororiger]

Would you be able to visually show this?  I don't think I am following your instructions