Hierarchy sorting

[tearless]

I have a hierarchy above in my report and it goes down to level 9. 

I also have a reporting order ID tagged at level 1 and when I try to sort this, it shows the following error. 

From level 1 to level 9, it should be sorted in a specific order and I don't know how to do this.. 

Can someone please help?

[PijushRoy]

Hi @tearless 

Please add SortOrder column for each 9 columns in your database
or use Power Query conditional column to add a SortOrder Column.

[tearless]

This is what I did. 

I created hierarchy tables Level1... to Level 9 with ID that shows the order of each hierarchy member. 

Then I joined these 9 tables with the data table which has those hierarchy member. 

Then, I selected each hierarchy table and sorted by those IDs.. 

But the report still does not get sorted as needed. 

[Ritaf1983]

Hi @tearless 

To sort a hierarchy properly in Power BI, you need a separate sort column for each level of the hierarchy. In your case, since the hierarchy goes down to 9 levels, you should create individual sort columns for each level. These sort columns will define the specific order for each hierarchy level.

Example:

Let's assume your hierarchy has the following levels:

• Level 1: ReportingLevel01
• Level 2: ReportingLevel02
• ...
• Level 9: ReportingLevel09

You need to create sort columns like:

• SortLevel01 for ReportingLevel01
• SortLevel02 for ReportingLevel02
• ...
• SortLevel09 for ReportingLevel09

Each sort column should have a unique value for each row to define the sorting order for that level.

Once you have these sort columns, you can set the sorting for each hierarchy level in Power BI by going to the column tools and selecting "Sort by Column" and choosing the appropriate sort column for each hierarchy level.

By doing this, you ensure that each level of the hierarchy is sorted in the desired order.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[tearless]

This is what I did. 

I created hierarchy tables Level1... to Level 9 with ID that shows the order of each hierarchy member. 

Then I joined these 9 tables with the data table which has those hierarchy member. 

Then, I selected each hierarchy table and sorted by those IDs.. 

But the report still does not get sorted as needed. 

[Ritaf1983]

Hi @tearless 
If you’ve correctly defined a sort order for each level in the hierarchy (numeric, which is required), sorted all the tables, and placed the dimensions from the relevant tables into the visualization, with the visualization itself sorted by what’s in the 'Rows', it should work. If it’s not working, something must have gone wrong along the way. To figure it out, I’d need to see the file. Please share a link to it (without sensitive data), and I’ll be happy to check it. Alternatively, I’m attaching a file I created for you with 4 hierarchies—this is enough to understand the logic, as it’s not dependent on the number of levels in the hierarchy.

Results :

+ you can download the pbix and follow my steps

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly