Power BI Roadmap - Sorting by Parent

[ATB1999]

Hi everybody.

I have been able to craft a Power BI roadmap that is able to show me from top to bottom certain tasks with a parent ordering. Nonetheless, when I group by task, the descending sorting of the parent breaks down: it goes from 5-1 to 1-5). This is very frustrating, as the roadmap is supposed to show the impact from highest to lowest... Is there any way in which we could just change around the parent ordering so that it shows the inverted version of the roadmap? For your information, I tried out the following without success:

- Creating a custom column (character and numeric) and sorting by this other column.

- Trying to put the parent in a field so we can sort the axis from the visual itself in a descending fashion.

For your information, the visual was working fine until I grouped by task. Is there anything that can be done to keep the task from 5 to 1 in a descending manner? I would appreciate your input!

[DataNinja777]

Hi @ATB1999 ,

It's a common frustration in Power BI when grouping alters a carefully set sort order. This can be resolved by creating a permanent sorting rule in your data model. The most reliable method is to create a new calculated column that defines your desired order and then use the "Sort by Column" feature to associate it with your parent field.

To begin, navigate to the Data view in Power BI and select the table containing your roadmap data. You will add a new column that inverts the numeric order of your parent groups. From the "Column tools" tab, select New column and enter the following DAX expression, making sure to replace [Parent] with the name of your column that contains the numbers 1 through 5.

Parent Sort Order = [Parent] * -1

This creates a helper column where the value 5 becomes -5, 4 becomes -4, and so on. After the column is created, the crucial next step is to link this new sorting logic to your original parent column. While still in the Data view, select your original [Parent] column by clicking on its header. Then, go to the Column tools ribbon, click the Sort by column button, and choose the Parent Sort Order column you just made from the dropdown list.

Finally, return to your report. Select your roadmap visual and ensure the axis is configured to sort by your original [Parent] column. In the visual's sorting options, which can be found in the "..." menu, set the direction to Sort ascending. Although this seems counterintuitive, Power BI will now use the logic from your Parent Sort Order column (where -5 is the smallest value) to sort the [Parent] column, resulting in the correct 5, 4, 3, 2, 1 descending order that will persist even when you group by task.

Best regards,