Matrix Visual Multi-Layer Hierarchy Sorting

[tsdick]

I have created a matrix visual w/ row hierarchy (4 layers) using a separate sort table w/ sort index.  All data, hierarchy, expand and collapse are spot on except the multi-layer sort is a default alpha sort.  I am really struggling to set up the sort (1) hierarchy and (2) corresponding sort table index (relational or not?).   This seems like it should be relatively easy.

I have watched U-tubes w/ simple set up.  I have tried countless combinations of hierarchy and sort table index.  Results are no where close to desired sorting....

Appreciate any assistance you can provide.

Thanks.

Tim

[Sivakumar_H]

Hi @tsdick 

Try to create two seperete sort columns for Year and Weekend date and Sort Year to Year sort column and Weekend Date to Weekend Date Sort column. This should work and we can order any type ascending or descending to the columns which we required.

Say for example we want hierachy order as A-P1,A-P3,A-P2 for all the parent values then we can create sort by column according to that required order.

Source

Output

[Anonymous]

Hi @tsdick,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept suitable suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

HI @tsdick,

It sounds like you want to achieve multiple column sorting order on your matrix visual with hierarchy structure, right?

If that is the case, I'd like to suggest you extract all the row category fields to a new table and add a custom ranking field based on these fields' values.

Then you can add a column on the new table to concatenate all fields values, and you can set the ranking field as the 'sorting by column' of the concatenated field.

After these steps, you can add the concatenated field to the original table, setting it as the relationship key field link to the new table, and replace the old matrix row category fields. These fields should keep the sorting order as you defended on the new table.

Reference links:

Custom Sorting in Power BI 

Sorting by multiple columns 

Relationship in Power BI with Multiple Columns - RADACAD

Regards,

Xiaoxin Sheng

[TomMartens]

Hey @tsdick ,

I have my difficulties to understand your requirement, to avoid misunderstandings, I ask you to create a Power BI file that contains sample data, but still reflects your data model. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Please explain the expected result based on the sample data you provided. Maybe you can create a simple table in Excel that represents the matrix visual.

Regards,

Tom