Custom Matrix hierarchy rows sorting

[boudy]

Hi all,

I have a matrix visual where the rows follow a hierarchy structure. My problem is,  PowerBI sorts the rows based on a alphabetical sorting. And I would like to custom this sorting. See below my 3 levels (level 1 is the higest one). The Order column is the order that I would like to apply. 

To do my visaul, I created a hierarchy with this 3 levels to properly display it in the Matrix. And I used a DAX function to handle the null values in my visual. Everything is working fine except the order of my rows. Because Power BI uses a default sorting, and i don't know how to custom this order. 

Problem 1 : I cannot sort each column based on my Order column while Power BI does not allow sort with duplicates values

Problem 2 : While duplicate values is not allow, i tried to sort based on multiple columns (the 3 levels), but not supported by PowerBI yet

The only solution that I have right now is to put a suffix 1. , 2., 3., ... or letters  to create my order. But, I cannot use this solution in my case.

Thanks for your help 

[amitchandak]

@boudy 

I think as of Sort column is the only option,

You can also refer :https://community.powerbi.com/t5/Desktop/Sorting-by-multiple-columns/m-p/536884#M251918

In March -2020, Multiple column sort has been provided on Table.  For matrix, we have to wait

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-2020-feature-summary/

[boudy]

Thanks @amitchandak  for your reply.

 I took a look in the post that you referred to me and I'm not DAX expert but my comprehension after read this post is that : Do I need to create a new measure ? What I need to sort are Dimensions (Level1, Level2, Level3), so the measure are calculations and honestly I do not see how to translate the post option to my case. Do I need to change my hierarchy in DAX function ?

Thanks a lot