Forum Discussion
Power BI – “Sort by column” error in calculated hierarchy (duplicate labels across hierarchy levels)
Hey Rahul_Nair,
Laying out the problem statement:
Power BI requires a one-to-one mapping between the displayed value and the sort value. In your hierarchy, the same label (Admin) exists under multiple parents with different sort orders:
Level_03 Sort_Order Admin 1 Admin 7 In the correct modeling approach, the hierarchy should be based on unique node identifiers. So, the hierarchy internally would look like:
Node_Id Parent_Node_Id Label Sort_Order Fin_Admin Finance Admin 1 HR_Admin HR Admin 7 Then:
PATH() could use Node_ID and Parent_Node_ID
PATHITEM() would return Node_IDs
Use labels as display/descriptive attribute
And sort on the unique node-level column
Implementation:
Hierarchy_Path = PATH ( Table[Node_ID], Table[Parent_Node_ID] )
Level_03_Node = PATHITEM ( Table[Hierarchy_Path], 3 )
Level_03_Label = LOOKUPVALUE ( DIM_HEADER[Description], DIM_HEADER[Node_ID], Table[Level_03_Node] )Then configure:
Level_03_Node Sort by -> Level_03_Sortand use the label column only for display purposes.
It became too long while writing. Apologies.
Hope this helps!
Best,
Harshit
Super UserHi Rahul_Nair ,
Since you are open to concatenated unique columns, I would suggest doing it entirely in Power Query (M) than in DAX, both the concatenated column and the sort column. This should eliminate the circular dependency issues.
Basically, you would have a unique label (albeit a longer one now given you have to concat) and a unique sort number to go with it and so you should have one-to-one mapping that Power BI requires for the "Sort by column" to work.
Power BI enforces the sort mapping across the entire column at once so I don't think there is a pattern to handle that contextually such that the same label, such as 'Admin', can take up a different sort value based on context or hierarchy. So, the unique concatenated approach should be clean and reliable way to move forward.
Hope that helps!