Forum Discussion

Rahul_Nair's avatar
Rahul_Nair
Regular Visitor
4 months ago
Solved

Power BI – “Sort by column” error in calculated hierarchy (duplicate labels across hierarchy levels)

Hi everyone,

I’m facing an issue with sorting hierarchy levels in Power BI and would appreciate some guidance on the correct approach.

I have a hierarchy table that is used in a matrix visual. The hierarchy levels (Level 01 to Level 07) are not coming directly from a source table but arecalculated columns created using PATH, PATHITEM, and CALCULATE to fetch descriptions from a dimension table (DIM_HEADER). So essentially, each level returns a display label (text)based on a node key derived from the path.

 

In addition, I have corresponding numeric sort columns (Sort Lvl 02, Sort Lvl 03, etc.) already present in the table.

The issue is with applying Sort by column. Sorting works fine for Level 02 using Sort Lvl 02, but when I try to sort Level 03 using Sort Lvl 03, Power BI throws the error:

“There can't be more than one value in 'Sort Lvl 03' for the same value in 'Level 03'”

After investigating, I see that the same Level 03 label (for example “Admin”) appears under different branches of the hierarchy, but has different sort values depending on the parent node. So effectively:

  • Level 03 is a non-unique display column
  • Sort Lvl 03 is context-dependent (varies per hierarchy branch)

This creates a conflict because Power BI requires aone-to-one mapping between the column and its sort column, which is not possible in this structure.

 

I’ve already tried several approaches such as:

  • Creating unique concatenated columns
  • Using PATH or path-based keys
  • Creating combined/global sort keys
  • Building separate sort tables and relationships
  • Using CALCULATE / MIN or LOOKUPVALUE

However, these either still result in the same error or introduce circular dependency issues, mainly because the Level columns themselves are calculated using CALCULATE against a dimension table.

The requirement is to use “Sort by column” (no workaround like prefixing numbers in the label).

 

So my question is:

In a hierarchy built using calculated columns (PATH + lookup to dimension tables), where the same label can appear under multiple parents with different sort orders, what is the correct modeling or DAX approach to make “Sort by column” work?

 

Is this something that requires restructuring the model (e.g., introducing a proper node key for each hierarchy element), or is there a recommended pattern to handle this scenario within the current design?

Thanks in advance.

Need Help
Tips and Tricks
  • stoic-harsh's avatar
    stoic-harsh
    4 months ago

    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_03Sort_Order
    Admin1
    Admin7

     

    In the correct modeling approach, the hierarchy should be based on unique node identifiers. So, the hierarchy internally would look like:

    Node_IdParent_Node_IdLabelSort_Order
    Fin_AdminFinanceAdmin1
    HR_AdminHRAdmin7

    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_Sort

    and use the label column only for display purposes.

    It became too long while writing. Apologies.

    Hope this helps!

    Best,

    Harshit

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 months ago

    Hi Rahul_Nair ,

     

    How is the sort columns being calculated is it based on a composite key made for the previous lever is it based on the path key?

     

    If you are using a path sintax depending on how you identify each of the parents you should have unique values for each level since one of the ways that the path formula works is having unique value to sort the path for each level.

     

    Can you share a small mockup of your data please.

  • alish_b's avatar
    alish_b
    Icon for Super User rankSuper User
    4 months ago

    Hi 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!

  • stoic-harsh's avatar
    stoic-harsh
    Icon for Super User rankSuper User
    4 months ago

    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_03Sort_Order
    Admin1
    Admin7

     

    In the correct modeling approach, the hierarchy should be based on unique node identifiers. So, the hierarchy internally would look like:

    Node_IdParent_Node_IdLabelSort_Order
    Fin_AdminFinanceAdmin1
    HR_AdminHRAdmin7

    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_Sort

    and use the label column only for display purposes.

    It became too long while writing. Apologies.

    Hope this helps!

    Best,

    Harshit

  • v-moharafi-msft's avatar
    v-moharafi-msft
    Icon for Community Support rankCommunity Support
    3 months ago

    Hi  Rahul_Nair ,


    Thank you for reaching out to Microsoft Fabric Community and Thanks to MFelix   , alish_b  and stoic-harsh     for Sharing valuable insights.


    Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

     

     Best Regards,

    Abdul Rafi.

  • v-moharafi-msft's avatar
    v-moharafi-msft
    Icon for Community Support rankCommunity Support
    3 months ago

    Hi Rahul_Nair  ,

     

    Could you please confirm if the issue has been resolved? If not, feel free to reach out if you have any further questions.

    Your update would be helpful for other members who may face a similar issue.

     

    Best Regards,

    Abdul Rafi