Forum Discussion
Power BI Roadmap - Sorting by Parent
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] * -1This 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,
Super UserHi 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] * -1This 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,
Community SupportHi ATB1999
I wanted to follow up regardings response to your inquiry about Power BI roadmap that is able to show me from top to bottom certain tasks with a parent ordering. If his solution was helpful, please consider marking it as the Accepted Answer, as it could assist others encountering similar challenges.
If you still have questions or need further clarification, don’t hesitate to provide more details we’re here to help!
Thank you for being an active member of the Microsoft Fabric Community!