Forum Discussion

Rahul146's avatar
Rahul146
New Member
8 years ago
Solved

Custom Sort on Grouped column

I have a text column in my query. I grouped it in the data fields and was able to create a matrix. However I need to change the default sort order on this grouped column. Tried reading through the tu...
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    Rahul146

     

    Since your City field is group on State field, which means it has multiple entries for same State in the table, it's not possible to custom a sort order column to have the State column sorted by this numeric column. 

     

    Regards,

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Once you have grouped these items, a new "Field Name (grouped)" field appears in the data table view in PowerBI as a new column in the same table as the original field. Just like any other field, you can set a sort order for this field. 

In your case, like the previous commenter said, you CANNOT use the same sort order field as for the initial field (since values likely aren't distinct). However, that doesn't need to stop you from creating a new sorting field!

 

For example, create a table that looks like:

 

State  SortOrder

MA       1

CA        2

WA       3

Then, link this table to your existing table. Then, in the existing table, create a new field called "Sort Order for States" that is just =related(SortOrderTable[SortOrder]). 

Then, that field becomes the one you can use to sort your groups using the "Sort by column" button in the modeling tab. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

This worked for me.

 

Thanks!